Connecting to an SQLite database in Python is easy, because it is simply a file.
import sqlite3 # Import the module
conn = sqlite3.connect('example.db') # Connect to the database.
c = conn.cursor()
Why do I need a cursor?
So far we have been executing SQL queries right from inside SQL itself and every command we wanted to execute, we did so using a cursor, the flashing one on the screen! In Python we are not typing our commands directly, so we have to create a virtual cursor and Python will ‘type’ in to SQL on our behalf.
entry_tuple = (col1_data, col2_data, col3_data, col4_data)
c.execute(‘INSERT INTO table_name VALUES (?,?,?,?)’, entry_tuple)
Adding data to an SQLite database is easy in Python, we simply execute gather the data we want to add and then execute an SQL statement as normal.
entry = ("Bob","Jones","01523 2233434","[email protected]")
c.execute('INSERT INTO stocks VALUES (?,?,?,?)', entry)
Caution!
Take care that the number of items in your tuple are the same as the number of Question Marks in your query.
Also if you don’t want to specify the contents of every column in the table (e.g. you don’t know a person’s email address or you have set a primary key columnn that is auto incrementing) then you will have to use the slightly longer version of the query where you specify the column names that you will be adding to.
Example
c.execute('INSERT INTO stocks (firstname,lastname,telephone,email) VALUES (?,?,?,?)', entry)
Why do we need to use the question marks???
Using the ? marks instead of using string substitution helps protect your Python program from sql injection attacks, as it sanitises the input data first.
Creative commons – Source: http://xkcd.com/
for row in c.execute(‘SELECT * FROM table_name‘):
print(row) #or do something else here
If we want to display the contents of a table, we need to use the select query. The select query returns a queryset object that we can combine with a for loop to print out the results(or do other things).
search_tuple = ("bob","jones")
rows = c.execute('SELECT * FROM contacts WHERE firstname=?, lastname = ? ', search_tuple)
for row in rows:
print(row)
c.execute(‘UPDATE table_name SET column_name = ? WHERE column_name =?’, entry_tuple)
entry_tuple = ("[email protected]", 45)
c.execute('UPDATE contact SET email = ? WHERE contactId = ?', entry_tuple)
Helpful hint!
The safest and most effective way of updating entries within a table is to set up an auto incrementing primary key and then use that reference when developing your programs. This avoids accidentally updating the wrong entry(for example if two people shared the same name!).