SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.
First step in your python scripts it will be to import the library:
If you get an error message, check for your python installation.
conn = sqlite3.connect('/tmp/test.db') cursor = conn.cursor()
If you want to check before create a table, you can use the very simple sql statement CREATE TABLE IF NOT EXISTS… This is supported by the latests sqlite versions, and it's a good way to create a table only if it's needed.
If you don't want to use this, or you just need a method to check for the table:
cursor.execute(""" SELECT COUNT(*) FROM sqlite_master WHERE name = ? """, (tablename, )) res = self.cursor.fetchone() print bool(res) # True if exists
Let's suppose we want to create a table called customer:
cursor.execute(""" CREATE TABLE customer( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, firstname VARCHAR(50), lastname VARCHAR(50), age INTEGER ) """)
Populate the table using placeholders (to prevent sql injections):
q = """ INSERT INTO customer(firstname, lastname, age) VALUES(?, ?, ?) """ cursor.execute(q, ('John', 'Doe', 34)) conn.commit()
You need to explicitly commit; sqlite3 for python uses transactions by default and before every UPDATE/INSERT/DELETE/REPLACE statements, sqlite3 opens a transaction (it automatically commits to a nonquery statement as CREATE TABLE). If you don't want to use this feature (useful if something gets wrong along the way – your db will be unaffected), just open the connection as:
conn = sqlite3.connect('/tmp/test.db', isolation_level=None)
A very efficient way to insert data is by using executemany method. executemany - Executes an SQL command against all parameter sequences or mappings found in the sequence sql.
Create a list of tuples with desired data, let's say in our case:
multiple_customers = [ ('Derek', 'Patrick', 24), ('Simoncelli', 'Rock', 53), ('Jesus', 'Christ', 25) ]
And then use executemany to fill up the table:
conn.executemany("INSERT INTO customer(firstname, lastname, age) VALUES(?, ?, ?)", multiple_customers) conn.commit()
Using executescript - This is a nonstandard convenience method for executing multiple SQL statements at once. It issues a COMMIT statement first, then executes the SQL script it gets as a parameter.
script = """ CREATE TABLE another_one(first, last, number); CREATE TABLE important_data(id, data); INSERT INTO important_data(id, data) VALUES(1, 'some string'); """ cursor.executescript(script)
cursor.execute(“SELECT * FROM customers”) for cust in cursor.fetchall(): print “ID %d NAME %s %s” % (cust, cust, cust)
If you want to access column by name, rather than by index, you must set first the attribute row_factory to sqlite3.Row class.
conn = sqlite3.connect(“mydb”) conn.row_factory = sqlite3.Row curs = conn.cursor() curs.execute(“SELECT firstname FROM customer WHERE 1”) for row in curs: print row['firstname']
As described before, SQLite supports only a limited set of types natively. To use other Python types with SQLite, you must adapt them to one of the sqlite3 module’s supported types for SQLite: one of None, int, long, float, str, unicode, buffer.
Let's suppose you want to store a tuple (eg. (1,2,3)) in a sqlite database. You need to transform it into a string. We'll use in our class a conform method who will return the accepted type (string in our case):
class tupleExample(): def __init__(self, optuple): self.tup = optuple def __conform__(self, protcol): if protocol is sqlite3.PrepareProtocol: return self.tup.__str__() con = sqlite3.connect(":memory:") curs = con.cursor() tu = tupleExample((1, 4, 5)) curs.execute("select ?", (tu,)) print curs.fetchone()
Another way to achieve this is to create a function who make this transformation and then register it with register_adapter():
class tupleExample(object): def __init__(self, optuple): self.optuple = optuple def adapt_tuple(tupleob): return tupleob.optuple.__str__() sqlite3.register_adapter(tupleExample, adapt_tuple) con = sqlite3.connect(":memory:") curs = con.cursor() tu = tupleExample((1, 4, 5)) curs.execute("select ?", (tu,)) print curs.fetchone()