Digital oracle | ds

Python SQLite Tutorial

I explain the 20% of Python SQlite that a first-timer needs to know to get up to speed quickly.

This tutorial on Python Sqlite assumes that you are familiar with Python and have some basic database knowledge. The tutorial presents how to use Python Sqlite by breaking the syntax into two components:

  • The Python commands that handle database setup and management and perform execution of sqlite statements
  • The sqlite statements themselves

This organization separates the Python Sqlite interface from the Sqlite statements, enabling you to gain a clearer understanding of how Python Sqlite commands are put together from the above two components.

The Python commands for Sqlite, in a typical sqlite workflow order

# Import sqlite3 library
import sqlite3

# Create a connection to a database
connection = sqlite3.connect("/path/to/mydatabase.db")

# Create a cursor to the connection
cursor = connection.cursor()

# Execute an sqlite command that changes the database in some way
cursor.execute(<sqlite command>)

# Save changes to database
connection.commit()

# Undo last saved changes
connection.rollback()

# Execute more sqlite commands...
# When you're done with the databse, close its connection
connection.close()

Recap

  • You create a connection to a database by calling method connect() from sqlite3 module, with the path to the database as argument. If this database does not exist, it will be created.
  • This connection allows you to do three operations: creating a cursor to the database , commit changes to the database, and close the connection.
  • The cursor allows you to execute the sqlite statement given to it. cursor.execute() is the most used command in the Python Sqlite interface. The Sqlite statements must be string objects. The following section Sqlite statements shows you what you can put in cursor.execute().

Sqlite statements

An SQL database is a collection of tables, storing data of a collection of similar objects and linked together by their ids.

Table operations

Sqlite tables are the fundamental organizational blocks of a database. The following statements perform three essential operations with table: CREATE, ALTER (modify), and DROP (delete) a table from a database.

# Create a table
"CREATE TABLE table_name (column1_name column1_data_type, column2_name column2_data_type, ...)"

# Alter a table
"ALTER table_name <an operation that changes the table in some way>"

# Drop (delete) a table
"DROP table_name"

Recap

  • You CREATE a table by giving the table name, followed by a list of pairs of column names and respective data types, separated by commas and enclosed in a pair of parentheses. There must be a space between a column name and its data type. The data type can be one of the following: TEXT, REAL, INTEGER, BLOB, NULL.
  • You ALTER a table by giving the table name, followed by the specific alteration you want to perform on the table, such as adding a column to the table.
  • You DROP a table by giving just the table name.

Data operations

A table’s data is organized by rows and columns. Each row corresponds to an identifiable object. Each column corresponds to one single attribute.

The essential operations with data are: ADD COLUMN to a table, INSERT, UPDATE, DELETE, and SELECT (retrieve) data to/from a table.

ADD COLUMN, DELETE and SELECT are presented first, followed by INSERT and UPDATE. This is because INSERT and UPDATE are slightly more complicated, requiring the use of placeholders.

# Add a new column to a table, without any row value
"ALTER TABLE table_name ADD COLUMN column_name column_data_type"

# Data operations
# Data deletion
# Only row deletion is permitted. Sqlite3 does not support column deletion.
"DELETE FROM table_name WHERE column_name = <some value>"

# Data selection (retrieval)
# Select everything in a table
"SELECT * FROM table_name"

# Select all values in row(s) that match some value in one column
"SELECT * FROM table_name WHERE column_name = <some value> "

# Select all values in a specific column
"SELECT column_name FROM table_name"

# Select value of a specific column in a row that match some value in another column
"SELECT column_name FROM table_name WHERE another_column_name = <some value>"

# Select data for up to a number of rows that match some value in a column
"SELECT * FROM table_name WHERE column_name = <some value> LIMIT <number of rows>"

Recap

  • There are two ways to specify the location of desired data: by columns, and by rows.
  • You specify the desired columns by a parentheses-enclosed, comma-separated list of column names.
  • You specify the desired rows by the keyword WHERE, followed by conditions that match the desired rows. These conditions are of the form column_name = some value.
  • “*” means “everything”. If no column names are specified, then all columns will be captured. If no row-matching conditions are specified, then all rows will be captured.
  • You can combine WHERE with “*” in a statement to capture all columns data in only rows that match some condition.

Data INSERT and UPDATE require the use of placeholders, which are marked by question marks.

# Insert column values into a single row
"INSERT INTO table_name(column1_name, column2_name,...) VALUES(?,?,...)", (column1_value, column2_value, ...)

# Data update
"UPDATE table_name SET column1_name = ? WHERE column2_name = ?", (column1_value, column2_value)

Recap

  • The keyword VALUES allows you to “pipe” data into a table. It is preceded by a list of column names, and followed by a pair of parentheses, comma-separated list of question marks. Each question mark reserves place for one and only one column, so there must be as many question marks as there are columns.
  • The Sqlite statement is followed by the variable(s) referencing the input data, in the order that the corresponding column names appear in the Sqlite statement.

Templated commands

Since Sqlite statements are strings, you can use Python string formatting operations to template them. WARNING: while this is convenient, it is insecure, so should be used cautiously. See Stackoverflow for more information.

# Add a new column to a table, with a default row value
default_value = <some value(s)>
"ALTER TABLE table_name ADD COLUMN column_name column_data_type DEFAULT {}".format(default_value)

# Use wildcards for multiple column placeholders
# Suppose our table has 5 columns, named a, b, c, d, e
data = [1,2,3,4,5]
table_name = 'mytable'
wildcards = ','.join(['?'] * len(data))
column_names = ['a', 'b', 'c', 'd', 'e']
"INSERT INTO {}({}) VALUES({})".format(table_name, column_names, wildcards), data)

Inspection operations

The following statements retrieve some basic information about your table/database.

# Retrieve a table's column names
"PRAGMA TABLE_INFO(table_name)"

# Get the total number of rows in a table
"SELECT COUNT(*) FROM table_name"

# List all tables in a database
"SELECT name FROM sqlite_master WHERE type = "table" "

Fetching data from cursor

The cursor.execute() command “stores” the sqlite statement in cursor, which is an sqlite3.Cursor object. cursor.execute() does not directly return the data resulted from the sqlite statement’s execution.

In order to fetch the data from an execution, call one of the following methods on the cursor object: fetchall() fetches all the data objects that result from an execution, and put them in a list; fetchone() fetches one data object at a time, in the order from left to right in the list that would result from fetchall().

Once the last data object has been fetched, cursor “releases” the sqlite statement it was holding. Since now cursor is not “bounded” to any sqlite statement, calling cursor.fetchall() or cursor.fetchone() now will produce nothing.

You can assign cursor.execute(<sqlite statement>) to a variable, then use that variable as if it’s a generator. See below for an example.

Putting the two components together

To execute an Sqlite statement, input it (and its data variables if using) to cursor.execute().

The following is an example of an Sqlite session demonstrating a typical workflow in SQL.

connection = sqlite3.connect("mydb.db")
cursor = connection.cursor()
cursor.execute("CREATE TABLE mytbl(id INTEGER PRIMARY KEY, name TEXT, height REAL)")
record = ["me", 1.75]
cursor.execute("INSERT INTO mytbl(name, height) VALUES(?,?)", record)
connection.commit()
connection.close()

The following session retrieves data from a table and its column names. Note the use of fetchall(). Also observe the assignment of cursor.execute(<sqlite statement>) to a variable that is then used as a generator in a for loop (the for loop is doing fetchone() one at time).

connection = sqlite3.connect("mydb.db")
cursor = connection.cursor()
cursor.execute("SELECT * FROM mytbl")
print "data in mytbl:"
print cursor.fetchall()

print '\nmytbl column names:'
colnames = cursor.execute("PRAGMA TABLE_INFO(mytbl)")
for name in colnames:
    print name

print '\ncolnames has been exhausted'
print colnames.fetchall()

data in mytbl:
[(1, u'me', 1.75)]

mytbl column names:
(0, u'id', u'INTEGER', 0, None, 1)
(1, u'name', u'TEXT', 0, None, 0)
(2, u'height', u'REAL', 0, None, 0)

colnames has been exhausted
[]

Related Posts