The WA5PB Blog

Python affords a great opportunity for one to learn basic SQL commands and operations by having SQLite3 built into its standard library.   To learn more, here are some links for Python’s implementation of the API and also the SQLite3 website.

http://docs.python.org/library/sqlite3.html

http://www.sqlite.org/index.html

Here is a brief example of  creating a simple SQLite3 database in Python, performing a query against it with parameters.  The use of parameters is demonstrated in 3 ways.

>>> import sqlite3
>>> conn = sqlite3.connect(‘c:\sqlite3\example’)
>>> c = conn.cursor()
>>> c.execute(“”"create table contacts(client_id integer primary key, first_name text, last_name text, email text)”"”)
>>> c.execute(“”"insert into contacts values(NULL, ‘Bob’, ‘Dole’, ‘bob.dole@gmail.com’)”"”)
>>> conn.commit()
>>> c.execute(“”"insert into contacts values(NULL, ‘Tom’, ‘Cruz’, ‘tom.cruz@xenu.com’)”"”)
>>> conn.commit()

First parameters are passed using the ? placeholder.  The values are supplied by a collection, a list in this example.  A tuple could also be used.

>>>params = ['Tom','Bob']

>>> c.execute(“”"select client_id, first_name, last_name, email from contacts where first_name in (?,?)”"”, params)
>>> for i in c:
print(i)

Second, a mapping object (dictionary) is used to supply the parameters via its keys.   This allows the parameters to be named.

names = {“name1″: ‘Bob’, “name2″:’Tom’}

>>> c.execute(“”"select client_id, first_name, last_name, email from contacts where first_name in (name1,name2)”"”, names)
>>> for i in c:
print(i)

Third, we use the built in mapping that stores information about local program variables to supply the parameters.  The locals() function is used to return the mapping information.  This allows the parameters to be simple variables, rather than members of a collection or keys in a mapping.

name1 = ‘Bob’; name2 = ‘Tom’

>>> c.execute(“”"select client_id, first_name, last_name, email from contacts where first_name in (name1,name2)”"”, locals())
>>> for i in c:
print(i)

All three methods produce the very same result of the query on the database:

(1, ‘Bob’, ‘Dole’, ‘bob.dole@gmail.com’)
(2, ‘Tom’, ‘Cruz’, ‘tom.cruz@xenu.com’)
>>> c.close()

Additionally, remember the parameter needs to be passed to the execute() function by a collection or mapping type variable.   So, if you would like to loop through the database, say by the indices, and are using a simple integer variable in the loop, you can take that and convert it to a list variable on the fly (a list containing one element) and use that.    Like this:

out_list = []
for x in range(1,4):
    y = [x]        #here we take the integer x and use it to create the single element list y for use in c.execute()
    c.execute("""select client_id, first_name, last_name, email from contacts where client_id = :y""", y)
    for i in c:
        out_list.append(i)
print(out_list)