Custom queries II: SQLite

Let's see how to connect to an SQLite database from Python. The current version is SQLite3.
Open a Python file, and import the library:
import sqlite3

Now, open a connection to the database. If the .db file already exists, connection to that file is opened, else a file of that name is created and connection is opened.
conn = sqlite3.connect ("/foo/bar/filename.db")

For executing queries, we need a cursor. So let's initialize that:
cur = conn.cursor ()

Now let's write a query.
cur.execute ("select something from some_table")

You can directly pass the string as a parameter, but for more customisations, you should initialize it separately and pass it as follows:
query = "select foo from bar"
cur.execute (query)

If you want to insert data dynamically into your query, the following is the syntax:
THIS DOES NOT WORK:
query = "select empid from employee where name = '%s'" % name
cur.execute (query)

THIS SHOULD BE DONE:
query = "select empid from employee where name = ?"
data = (name,)
cur.execute (query)

Note that data has to be a list or tuple. The syntax shown forces a single element to be a tuple. However, if there are multiple elements, just list them out normally like this:
query = "select empid, dept from employee where name = ? and salary = ?"
data = (name, sal)
cur.execute (query)

No data needs to be passed for insert and update queries, so just write those normally.
For delete, pass data in the same way as shown.
Now we need to fetch the data we get from select statement. Here's how you do it (assume I executed the previous query):
for row in cur:
    print row[0], row[1]

We selected two values here, namely empid and dept. So row[0] and row[1] will hold empid and dept respectively. This can be extended for several values.

Once you're done, you should commit the changes and close the connection.
conn.commit ()
conn.close ()

In the next article, we'll see how to handle a MySQL database in Python.
Cheers!