Python Access SQLite Database Example

SQLite is an embedded database which store all database data in a file. Because SQLite itself is written in C and small in size, it is often integrated into various applications, such as iOS and Android Apps. Python has built-in SQLite3 support, so you don’t need to install anything to use SQLite in Python.

1. Some concepts about database table and database operation in Python.

1. A table is a collection of relational data stored in a database. A database usually contains multiple tables, such as the tables of schools, students and so on. Tables are associated with each other by foreign keys.
2. To operate a relational database, you first need to connect to the database use a connection object, then all database operations will process through this connection object.
3. Once connected to the database, you need to open a Cursor object which executes the SQL statement and then obtains the result of the execution.
4. Python defines a set of API interfaces to operate on a database. To connect any database in Python, you only need to provide a database driver that conforms to Python standards.
5. Because the Python SQLite driver has been built into the Python standard library, we can operate the SQLite database directly.

2. Python insert data to SQLite table example.

# import SQLite db driver for Python.
>>> import sqlite3
# connect to SQLite database, the database data will be saved in file test.db, if the db file do not exist then create it automatically.
>>> conn = sqlite3.connect('test.db')
# create a cursor object from the connection object.
>>> cursor = conn.cursor()
# execute a sql statement which will create user table.
>>> cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')
<sqlite3.Cursor object at 0x10f8aa260>
# execute another sql statement which will insert one record in user table
>>> cursor.execute('insert into user (id, name) values (\'1\', \'Michael\')')
<sqlite3.Cursor object at 0x10f8aa260>
# get inserted row count by the cursor's rowcount property
>>> cursor.rowcount
1
# do not forget close cursor at the end of the programe.
>>> cursor.close()
# commit the transaction to database to make it changed forever.
>>> conn.commit()
# you should close the connection object at the end.
>>> conn.close()

3. Python select data from SQLite table example.

# get SQLite databse connection object.
>>> conn = sqlite3.connect('test.db')
# get db cursor object.
>>> cursor = conn.cursor()
# execute select sql statement
>>> cursor.execute('select * from user where id=?', ('1',))
<sqlite3.Cursor object at 0x10f8aa340>
# get all select result
>>> values = cursor.fetchall()
# print out the records value
>>> values
[('1', 'Michael')]
# close the cursor and connection at the end.
>>> cursor.close()
>>> conn.close()