SQLite is a self-contained, server-less, config-free transactional SQL database engine. Python gained the sqlite3 module all the way back in version 2.5 which means that you can create SQLite database with any current Python without downloading any additional dependencies. Mozilla uses SQLite databases for its popular Firefox browser to store bookmarks and other various pieces of information. In this article you will learn the following:
This article will be similar in function to the recent SQLAlchemy tutorial that appeared on this site earlier this month. If you want to inspect your database visually, you can use the SQLite Manager plugin for Firefox or if you like the command line, you can use SQLite's command line shell
Creating a database in SQLite is really easy, but the process requires that you know a little SQL to do it. Here's some code that will create a database to hold music albums:
import sqlite3
conn = sqlite3.connect("mydatabase.db") # or use :memory: to put it in RAM
cursor = conn.cursor()
# create a table
cursor.execute("""CREATE TABLE albums
(title text, artist text, release_date text,
publisher text, media_type text)
""")
First we have to import the sqlite3 library and create a connection to the database. You can pass it a file path, file name or just use use the special string ":memory:" to create the database in memory. In our case, we created it on disk in a file called mydatabase.db. Next we create a cursor object, which allows you to interact with the database and add records, among other things. Here we use SQL syntax to create a table named albums with 5 text fields: title, artist, release_date, publisher and media_type. SQLite only supports five data types: null, integer, real, text and blob. Let's build on this code and insert some data into our new table!
# insert some data
cursor.execute("INSERT INTO albums VALUES ('Glow', 'Andy Hunter', '7/24/2012', 'Xplore Records', 'MP3')")
# save data to database
conn.commit()
# insert multiple records using the more secure "?" method
albums = [('Exodus', 'Andy Hunter', '7/9/2002', 'Sparrow Records', 'CD'),
('Until We Have Faces', 'Red', '2/1/2011', 'Essential Records', 'CD'),
('The End is Where We Begin', 'Thousand Foot Krutch', '4/17/2012', 'TFKmusic', 'CD'),
('The Good Life', 'Trip Lee', '4/10/2012', 'Reach Records', 'CD')]
cursor.executemany("INSERT INTO albums VALUES (?,?,?,?,?)", albums)
conn.commit()
Here we use the INSERT INTO SQL command to insert a record into our database. Note that each item had to have single quotes around it. This can get complicated when you need to insert strings that include single quotes in them. Anyway, to save the record to the database, we have to commit it. The next piece of code shows how add multiple records at once be using the cursor's executemany method. Note that we're using question marks (?) instead of string substitution (%s) to insert the values. Using string substitution is NOT safe and should not be used as it can allow SQL injection attacks to occur. The question mark method is much better and using SQLAlchemy is even better because it does all the escaping for you so you won't have to mess with the annoyances of converting embedded single quotes into something that SQLite will accept.
Being able to update your database records is key to keeping your data accurate. If you can't update, then your data will become out of date and pretty useless very quickly. Sometimes you will need to delete rows from your data too. We'll be covering both of those topics in this section. First, let's do an update!
import sqlite3
conn = sqlite3.connect("mydatabase.db")
cursor = conn.cursor()
sql = """
UPDATE albums
SET artist = 'John Doe'
WHERE artist = 'Andy Hunter'
"""
cursor.execute(sql)
conn.commit()
Here we use SQL's UPDATE command to update out albums table. You can use SET to change a field, so in this case we change the artist field to be "John Doe" in any record WHERE the artist field is set to "Andy Hunter". Wasn't that easy? Note that if you don't commit the changes, then your changes won't be written out to the database. The DELETE command is almost as easy. Let's check that out!
import sqlite3
conn = sqlite3.connect("mydatabase.db")
cursor = conn.cursor()
sql = """
DELETE FROM albums
WHERE artist = 'John Doe'
"""
cursor.execute(sql)
conn.commit()
Deleting is even easier than updating. The SQL is only 2 lines! In this case, all we had to do was tell SQLite which table to delete from (albums) and which records to delete using the WHERE clause. Thus is looked for any records that had "John Doe" in its artist field and deleted it.
Queries in SQLite are pretty much the same as what you'd use for other databases, such as MySQL or Postgres. You just use normal SQL syntax to run the queries and then have the cursor object execute the SQL. Here are a few examples:
import sqlite3
conn = sqlite3.connect("mydatabase.db")
#conn.row_factory = sqlite3.Row
cursor = conn.cursor()
sql = "SELECT * FROM albums WHERE artist=?"
cursor.execute(sql, [("Red")])
print cursor.fetchall() # or use fetchone()
print "\nHere's a listing of all the records in the table:\n"
for row in cursor.execute("SELECT rowid, * FROM albums ORDER BY artist"):
print row
print "\nResults from a LIKE query:\n"
sql = """
SELECT * FROM albums
WHERE title LIKE 'The%'"""
cursor.execute(sql)
print cursor.fetchall()
The first query we execute is a SELECT * which means that we want to select all the records that match the artist name we pass in, which in this case is "Red". Next we execute the SQL and use fetchall() to return all the results. You can also use fetchone() to grab the first result. You'll also notice that there's a commented out section related to a mysterious row_factory. If you un-comment that line, the results will be returned as Row objects that are kind of like Python dictionaries and give you access to the row's fields just like a dictionary. However, you cannot do item assignment with a Row object.
The second query is much like the first, but it returns every record in the database and orders the results by the artist name in ascending order. This also demonstrates how we can loop over the results. The last query shows how to use SQL's LIKE command to search for partial phrases. In this case, we do a search of the entire table for titles that start with "The". The percent sign (%) is a wildcard operator.
Now you know how to use Python to create a SQLite database. You can also create, update and delete records as well as run queries against your database. Go out and start your own neat databases and / or share your experiences in the comments!
Copyright © 2024 Mouse Vs Python | Powered by Pythonlibrary