SQLite 3 - A. Connect/Create/Insert/Drop 2020

"SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world."
- http://www.sqlite.org/

To use the SQLite3 module we need to add an import statement to our python script:

>>> import sqlite3

We can check sqlite version:

>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.7.17

The sqlite.version is the version of the pysqlite (2.6.0), which is the binding of the Python language to the SQLite database. The sqlite3.sqlite_version gives us the version of the SQLite database library. In our case it is 3.7.17.

To connect to the database, we can use sqlite3.connect function by passing the name of a file to open or create it:

>>> import sqlite3
>>> db = sqlite3.connect('data/test.db')

We can use the argument ":memory:" to create a temporary DB in the RAM:

>>> import sqlite3
>>> dbm = db = sqlite3.connect(':memory:')

When we are done working with the DB we need to close the connection:

>>> db.close()
>>> dbm.close()

Another way of creating db is to use the sqlite3 command line tool:

$ ls
$ sqlite3 test.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
sqlite> .exit
$ ls
test.db

The .tables command gives a list of tables in the test.db database. We don't have any tables now. The .exit command terminates the interactive session of the sqlite3 command line tool.

To use the database, we need to get a cursor object and pass the SQL statements to the cursor object to execute them. Then, we should commit the changes.

We are going to create a books table with title, author, price and year columns.

>>> cursor = db.cursor()
>>> cursor.execute('''CREATE TABLE books(id INTEGER PRIMARY KEY, 
...                   title TEXT, author TEXT, price TEXT, year TEXT)
... ''')
>>> db.commit()

Note that the commit function is invoked on the db object, not the cursor object.

To drop a table:

>>> cursor = db.cursor()
>>> cursor.execute('''DROP TABLE books''')
>>> db.commit()

To insert data we use the cursor to execute the query. In this example we are going to insert two books in the database, their information will stored in python variables.

>>> db.close()
>>> import sqlite3
>>> db = sqlite3.connect('data/test.db')
>>> cursor = db.cursor()
>>> cursor.execute('''CREATE TABLE books(id INTEGER PRIMARY KEY,
...                    title TEXT, author TEXT, price TEXT, year TEXT)
...                ''')
>>> db.commit()

>>> import sqlite3
>>> db = sqlite3.connect('data/test.db')
>>> cursor = db.cursor()
>>> title1 = 'Learning Python'
>>> author1 = 'Mark Lutz'
>>> price1 = '$36.19'
>>> year1 ='Jul 6, 2013'
>>> 
>>> title2 = 'Two Scoops of Django: Best Practices For Django 1.6'
>>> author2 = 'Daniel Greenfeld'
>>> price2 = '$34.68'
>>> year2 = 'Feb 1, 2014'

>>> cursor.execute('''INSERT INTO books(title, author, price, year)
...                   VALUES(?,?,?,?)''', (title1, author1, price1, year1))

>>> cursor.execute('''INSERT INTO books(title, author, price, year)
...                   VALUES(?,?,?,?)''', (title2, author2, price2, year2))

>>> db.commit()

Note: If we need values from Python variables it is recommended to use the "?" placeholder. Never use string operations or concatenation to make your queries because is very insecure.

The values of the Python variables are passed inside a tuple.

If we have more books to insert, we can continue. But this time, we'll do it another way: passing a dictionary using the ":keyname" placeholder:

>>> title3 = 'Python Cookbook'
>>> author3 = 'David Beazley'
>>> price3 = '$30.29'
>>> year3 = 'May 29, 2013'

>>> cursor.execute('''INSERT INTO books(title, author, price, year)
...                   VALUES(:title, :author, :price, :year)''',
...                   {'title':title3, 'author':author3, 'price':price3, 'year':year3})
<sqlite3.Cursor object at 0x7f1d2717d650>
>>> 
>>> db.commit()

If we need to insert several users, we can use executemany and a list with the tuples:

>>> title4 = 'The Quick Python Book'
>>> author4 = 'Naomi R. Ceder'
>>> price4 = '$16.39'
>>> year4 = 'Jan 15, 2010'
>>> 
>>> title5 ='Python Testing'
>>> author5 ='David Sale'
>>> price5 = '$38.20'
>>> year5 = 'Sep 2, 2014'

>>> books = [(title4,author4, price4, year4),
...          (title5,author5, price5, year5)]
>>> cursor.executemany('''INSERT INTO books(title, author, price, year) VALUES(?,?,?,?)''', books)
>>> db.commit()

Let's see what we've done so far using sqlite command shell:

$ sqlite3 test.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> .tables
books

sqlite> SELECT * FROM books;
1|Learning Python|Mark Lutz|$36.19|Jul 6, 2013
2|Two Scoops of Django: Best Practices For Django 1.6|Daniel Greenfeld|$34.68|Feb 1, 2014
3|Python Cookbook|David Beazley|$30.29|May 29, 2013
4|The Quick Python Book|Naomi R. Ceder|$16.39|Jan 15, 2010
5|Python Testing|David Sale|$38.20|Sep 2, 2014

sqlite> .mode column
sqlite> .headers on
sqlite> SELECT * FROM books;
id          title            author      price       year       
----------  ---------------  ----------  ----------  -----------
1           Learning Python  Mark Lutz   $36.19      Jul 6, 2013
2           Two Scoops of D  Daniel Gre  $34.68      Feb 1, 2014
3           Python Cookbook  David Beaz  $30.29      May 29, 201
4           The Quick Pytho  Naomi R. C  $16.39      Jan 15, 201
5           Python Testing   David Sale  $38.20      Sep 2, 2014
sqlite> 

Note that we modified the way the data is displayed in the console. We used the column mode and turned on the headers.