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.
- 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.
- 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.
- 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.
- 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.
- 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()