How to connect to SQLite using Python

Connecting to SQLite using Python

The sqlite3 module is part of Python’s standard library, and includes all of the functionality you need to access and work with SQLite databases. You do not need to install any vendor-specific modules to work with SQLite in Python.

The following sample code demonstrates how to connect to a SQLite database using the sqlite3 module and how to do some basic data manipulation. The code works with Python 2.7 and Python 3.x:

#!/usr/bin/python

from __future__ import print_function
from sqlite3 import connect

# Replace username with your own Hostvento Hosting account username:
conn = connect('/home/username/test.db')
curs = conn.cursor()

curs.execute("CREATE TABLE employees (firstname varchar(32), lastname varchar(32), title varchar(32));")
curs.execute("INSERT INTO employees VALUES('Kelly', 'Koe', 'Engineer');")
conn.commit()

curs.execute("SELECT firstname, lastname FROM employees;")
for firstname, lastname in curs.fetchall():
    print(firstname, lastname)

conn.close()

In this example, we first create a Connection object that opens the SQLite database. If the test.db file already exists, Python opens that file. Otherwise, Python creates a new database in the test.db file.

After we have a Connection object associated with the database, we can create a Cursor object. The Cursor object enables us to run the execute() method, which in turn enables us to run raw SQL statements (such as CREATE TABLE and SELECT).

Finally, we call the close() method to close the connection to the database.

If you modify any database contents (as in the example above), you should call the commit() method to make sure the changes are saved in the database.