 |  |  | Python Library Reference |  |  |  |
| | | Python Library Reference | | | |
SQLite is a C library that provides a lightweight disk-based databasethat doesn't require a separate server process and allows accessingthe database using a nonstandard variant of the SQL query language.Some applications can use SQLite for internal data storage. It's alsopossible to prototype an application using SQLite and then port thecode to a larger database such as PostgreSQL or Oracle.
pysqlite was written by Gerhard Häring and provides a SQL interfacecompliant with the DB-API 2.0 specification described byPEP 249.
To use the module, you must first create aConnection objectthat represents the database. Here the data will be stored in the/tmp/example file:
conn = sqlite3.connect('/tmp/example')You can also supply the special name ":memory:" to createa database in RAM.
Once you have aConnection, you can create aCursor object and call itsexecute() method to perform SQL commands:
c = conn.cursor()# Create tablec.execute('''create table stocks(date text, trans text, symbol text, qty real, price real)''')# Insert a row of datac.execute("""insert into stocks values ('2006-01-05','BUY','RHAT',100,35.14)""")# Save (commit) the changesconn.commit()# We can also close the cursor if we are done with itc.close()Usually your SQL operations will need to use values from Pythonvariables. You shouldn't assemble your query using Python's stringoperations because doing so is insecure; it makes your programvulnerable to an SQL injection attack.
Instead, use the DB-API's parameter substitution. Put "?" as aplaceholder wherever you want to use a value, and then provide a tupleof values as the second argument to the cursor'sexecute()method. (Other database modules may use a different placeholder,such as "%s" or ":1".) For example:
# Never do this -- insecure!symbol = 'IBM'c.execute("... where symbol = '%s'" % symbol)# Do this insteadt = (symbol,)c.execute('select * from stocks where symbol=?', t)# Larger examplefor t in (('2006-03-28', 'BUY', 'IBM', 1000, 45.00), ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00), ('2006-04-06', 'SELL', 'IBM', 500, 53.00), ): c.execute('insert into stocks values (?,?,?,?,?)', t)To retrieve data after executing a SELECT statement, you can either treat the cursor as an iterator, call the cursor'sfetchone()method to retrieve a single matching row, or callfetchall() to get a list of the matching rows.
This example uses the iterator form:
>>> c = conn.cursor()>>> c.execute('select * from stocks order by price')>>> for row in c:... print row...(u'2006-01-05', u'BUY', u'RHAT', 100, 35.140000000000001)(u'2006-03-28', u'BUY', u'IBM', 1000, 45.0)(u'2006-04-06', u'SELL', u'IBM', 500, 53.0)(u'2006-04-05', u'BUY', u'MSOFT', 1000, 72.0)>>>See Also:
| | | Python Library Reference | | | |