|
Python - SQLite
SQLite is a lightweight, serverless, self-contained, and transactional SQL database engine that comes as a public domain library. It is widely used for its ease of setup and minimal configuration. Python has built-in support for SQLite through the sqlite3 module, which allows users to interact with SQLite databases seamlessly.
Here goes a high-level overview of SQLite in Python. This shows the overall procedure of utilizing SQLite in a python script.
- Importing SQLite module: To use SQLite in Python, you need to import the sqlite3 module
- Creating a connection: A connection is required to interact with an SQLite database. You can create a connection using the connect() function. This function accepts the database filename as an argument. If the file doesn't exist, SQLite will create a new one.
- Cursor object: To execute SQL queries, you need a cursor object. You can create a cursor object by calling the cursor() method on the connection object. The cursor object is the primary interface for executing SQL commands and fetching data.
- Executing queries: SQL queries are executed using the execute() method of the cursor object. You can run different types of queries such as SELECT, INSERT, UPDATE, DELETE, and others.
- Fetching data: After executing a SELECT query, you can retrieve the data using various fetch methods such as fetchone(), fetchmany(), and fetchall(). These methods return the queried data as tuples or lists of tuples.
- Committing changes: After executing queries that modify data (e.g., INSERT, UPDATE, DELETE), you need to commit the changes to the database by calling the commit() method on the connection object. If you don't commit the changes, they won't be saved.
- Handling errors: Python SQLite provides error handling through exceptions. Some common exceptions include Error, DatabaseError, IntegrityError, and OperationalError. You can catch and handle these exceptions using try-except blocks.
- Closing the connection: It's essential to close the connection to the database once you're done using it. You can do this by calling the close() method on the connection object. This will release any resources associated with the connection and ensure data integrity.
NOTE 1 : All the examples in this page are written in Python 3.x. It may not work if you use Pyton 2.x
NOTE 2 : All the examples in this page are assumed to be written/run on Windows 7 unless specifically mentioned. You MAY (or may not) need to modify the syntax a little bit if you are running on other operating system.
Where is SQLite ?
If you are using a relatively recent version of Python, SQLite would be included in the installation folder as shown below (I am using Python 3.6 for this example)
You can get the sqlite dll and sqlite command line tools from following sites if you want to install on your own.
Basic Command Test
|
import sqlite3
conn = sqlite3.connect('Contact.db')
c = conn.cursor()
# Delete the table if it is already exists. I put this so that the sample DB is always created new.
# In real life, it would be less likely to do this operation.
c.execute('''DROP TABLE IF EXISTS phoneBook''')
# Create table
c.execute('''CREATE TABLE phoneBook (contactID INTEGER PRIMARY KEY, LastName TEXT, FirstName TEXT, PhoneNumber TEXT)''')
# Insert a row of data
c.execute('''INSERT INTO phoneBook(LastName,FirstName,PhoneNumber) VALUES ('Benjamin','Smith','123-4567')''')
c.execute('''INSERT INTO phoneBook(LastName,FirstName,PhoneNumber) VALUES ('Jacob','John','123-8421')''')
c.execute('''INSERT INTO phoneBook(LastName,FirstName,PhoneNumber) VALUES ('Robert','Williams','456-1234')''')
# Save (commit) the changes
conn.commit()
# Query Data
c.execute('''SELECT * FROM phoneBook''')
retrievedData = c.fetchall();
# Print Result
for r in retrievedData :
print(r)
# Close the connection
conn.close()
|
|
(1, 'Benjamin', 'Smith', '123-4567')
(2, 'Jacob', 'John', '123-8421')
(3, 'Robert', 'Williams', '456-1234')
|
SELECT *
NOTE : The database file used in this example is from http://www.sqlitetutorial.net/sqlite-sample-database/
and you can see the structure of the whole database in this page. The database file name is chinook.db and you should put the file into the same folder as the python script you run.
Example 01 > 'tracks' table
|
import sqlite3
conn = sqlite3.connect('chinook.db')
c = conn.cursor()
# Query Data
c.execute('''SELECT * FROM tracks''')
retrievedData = c.fetchall();
# Print Result. Printing out the whole data is too much. So I restricted the range of the print to a small segment
# just to show that some data are retrieved from the table.
for r in retrievedData[0:9] :
print(r[0:5])
conn.close()
|
|
(1, 'For Those About To Rock (We Salute You)', 1, 1, 1)
(2, 'Balls to the Wall', 2, 2, 1)
(3, 'Fast As a Shark', 3, 2, 1)
(4, 'Restless and Wild', 3, 2, 1)
(5, 'Princess of the Dawn', 3, 2, 1)
(6, 'Put The Finger On You', 1, 1, 1)
(7, "Let's Get It Up", 1, 1, 1)
(8, 'Inject The Venom', 1, 1, 1)
(9, 'Snowballed', 1, 1, 1)
|
Example 02 > 'media_types' table
|
import sqlite3
conn = sqlite3.connect('chinook.db')
c = conn.cursor()
# Query Data
c.execute('''SELECT * FROM media_types''')
retrievedData = c.fetchall();
# Print Result
for r in retrievedData :
print(r)
conn.close()
|
| (1, 'MPEG audio file')
(2, 'Protected AAC audio file')
(3, 'Protected MPEG-4 video file')
(4, 'Purchased AAC audio file')
(5, 'AAC audio file')
|
SELECT * WHERE
NOTE : The database file used in this example is from http://www.sqlitetutorial.net/sqlite-sample-database/
and you can see the structure of the whole database in this page. The database file name is chinook.db and you should put the file into the same folder as the python script you run.
Example 01 > 'tracks' table
|
import sqlite3
conn = sqlite3.connect('chinook.db')
c = conn.cursor()
# Query Data
c.execute('''SELECT * FROM tracks WHERE MediaTypeId=4''')
retrievedData = c.fetchall();
# Print Result. Printing out the whole data is too much. So I restricted the range of the print to a small segment
# just to show that some data are retrieved from the table.
for r in retrievedData :
print(r[0:4])
conn.close()
|
|
(3336, 'War Pigs', 260, 4)
(3414, 'Symphony No. 104 in D Major "London": IV. Finale: Spiritoso', 283, 4)
(3452, 'SCRIABIN: Prelude in B Major, Op. 11, No. 11', 318, 4)
(3479, 'Prometheus Overture, Op. 43', 324, 4)
(3480, 'Sonata for Solo Violin: IV: Presto', 325, 4)
(3496, 'Étude 1, In C Major - Preludio (Presto) - Liszt', 340, 4)
(3498, 'Concerto for Violin, Strings and Continuo in G Major, Op. 3, No. 9: I. Allegro', 342, 4)
|
(INNER) JOIN
NOTE : The database file used in this example is from http://www.sqlitetutorial.net/sqlite-sample-database/
and you can see the structure of the whole database in this page. The database file name is chinook.db and you should put the file into the same folder as the python script you run.
Example 01 > 'tracks' table and 'media_types' table
|
import sqlite3
conn = sqlite3.connect('chinook.db')
c = conn.cursor()
# Query Data
c.execute('''SELECT TrackId,[tracks].Name,[media_types].Name FROM tracks JOIN media_types
ON [tracks].MediaTypeId=[media_types].MediaTypeId''')
retrievedData = c.fetchall();
# Print Result. Printing out the whole data is too much. So I restricted the range of the print to a small segment
# just to show that some data are retrieved from the table.
for r in retrievedData[0:10] :
print(r)
conn.close()
|
|
(1, 'For Those About To Rock (We Salute You)', 'MPEG audio file')
(6, 'Put The Finger On You', 'MPEG audio file')
(7, "Let's Get It Up", 'MPEG audio file')
(8, 'Inject The Venom', 'MPEG audio file')
(9, 'Snowballed', 'MPEG audio file')
(10, 'Evil Walks', 'MPEG audio file')
(11, 'C.O.D.', 'MPEG audio file')
(12, 'Breaking The Rules', 'MPEG audio file')
(13, 'Night Of The Long Knives', 'MPEG audio file')
(14, 'Spellbound', 'MPEG audio file')
|
|
|