Using SQLite in Python

Reference text

Liaoche Python Tutorials

Using SQLite

SQLite is an embedded database , and its database is a file. Since SQLite is written in C and is small in size, it is often integrated into a variety of applications, even in iOS and Android apps .

Python has built-in SQLite3, after connecting to the database, you need to open the cursor cursor, execute the SQL statement through the cursor, and then get the execution results, Python defines a set of Operational database API interface, Any database that you want to connect to Python requires only a Python-compliant database driver . Try it:

#Import SQLite driver:
import sqlite3
#Connect to the SQlite database
#Database file is test.db, if it does not exist, it will be created automatically
conn = sqlite3.connect (‘test.db’)
#Create a cursor:
cursor = conn.cursor ()
#Execute a SQL statement: create a user table
cursor.execute (‘create table user (id varchar (20) primary key, name varchar (20))’)
#Insert a record:
cursor.execute (‘insert into user (id, name) values (\‘ 1 \ ‘, \‘ Michael \ ‘)‘)
#Get the number of rows inserted by rowcount:
print (cursor.rowcount) #reusult 1
#Close Cursor:
cursor.close ()
#Submit transaction:
conn.commit ()
#Close connection:
conn.close () 

Try the query again:

#Import SQLite driver:
import sqlite3
#Connect to the SQlite database
#Database file is test.db, if it does not exist, it will be created automatically
conn = sqlite3.connect (‘test.db’)
#Create a cursor:
cursor = conn.cursor ()
#Execute the query:
cursor.execute (‘select * from user where id =?’, (‘1‘,))
#Using featchall to get the result set (list)
values = cursor.fetchall ()
print (values) #result: [(‘1‘, ‘Michael’)]
#Close cursor
#Close conn
cursor.close ()
conn.close () 

Tips: When working with a database in Python, you first import the driver for the database, and then manipulate the data through the connection object and the cursor object. to ensure that both the connection object and the cursor object are turned off correctly , the resource will be compromised.

Using SQLite in Python