0. Introduction

Python provides SQLite 3-based modules, which are installed by default in Python 2.7.3. Here we just need to use it.
Why call sqlite module in Python? This is because Python is an excellent scripting language (which insults Python a little, but Python can do a lot of tasks as a scripting language), so that we can accomplish some database tasks more efficiently.

1. Using sqlite3 to Program Python

In the previous chapter, we used the command line provided by sqlite to operate the database. Here we will use the API interface of python to create, search and delete the database.

1.1 Database Links

To operate the database, first of all, we need to be able to connect to the database, as shown below.

import sqlite3
conn = sqlite3.connect('C:\\sqlite\\test\\testDB.db')

Here you need to import sqlite3 module first, otherwise you can't find the corresponding command.
Secondly, in windows environment, we need to add two''signs to our whole path. The first is an escape character, which is not explained in detail here.
Third, if testDB.db does not exist, the program will create it before connecting.
Once the database is connected, other operations can be done accordingly.

1.2 Create database tables

cur = conn.cursor()
createSQL = 'create table tbl_Device (ID INT PRIMARY KEY NOT NULL,\
                    DEVNAME TEXT NOT NULL,\
                    USERID INT NOT NULL)'
cur.execute(createSQL)
conn.commit()

The first statement is to get the cursor of the database, the third statement is to execute the database, and the last statement is to submit modifications. If not, in fact, the database will not execute the statement. The second sentence is the statement of creating tables. We find that it is in fact the same as the command in the previous chapter.

1.3 Insert Data

After creating the table, if there is no data, then it is useless. Let's insert data into the table.

insertSQL1 = 'insert into tbl_Device values (1,"NE=1",1)'
insertSQL2 = 'insert into tbl_Device values (2,"NE=2",1)'
cur.execute(insertSQL1)
cur.execute(insertSQL2)

1.4 Find Data

After inserting the data, we can use the select command to see if the inserted data exists.

selectSQL = 'select * from tbl_Device'
cur.execute(selectSQL)
name=[f[0] for f in cur.description]
for row in cur.fetchall():
    for pair in zip(name,row):
        print pair

Ultimately, the following results will be displayed:

('ID', 1)
('DEVNAME', u'NE=1')
('USERID', 1)
('ID', 2)
('DEVNAME', u'NE=2')
('USERID', 1)

1.5 Update data

Similarly, update commands can be used to update data

updateSQL = 'update tbl_Device set DEVNAME=''NE=3 where ID=1'
cur.execute(selectSQL)

1.6 Other

For all sql commands, you can use the above way to execute statements, including delete commands, drop commands and so on. Their behavior is the same, so I will not explain them in detail here.

1.7 Close Connection

When we have executed all the database commands, we usually do the following steps

conn.commit()
conn.close()

Submitting things and closing database connections

2. Basic API Interface

The application of sqlite in Python is summarized above. Now let's analyze the database API interface of sqlite in detail.

2.1 Interface to connect database

sqlite3.connect(database [,timeout ,other optional arguments])

2.2 Interface for Acquiring Cursors

connection.cursor([cursorClass])

2.3 Interface for Executing Statements

cursor.execute(sql [, optional parameters])
connection.execute(sql [, optional parameters])

2.4 Execution script

cursor.executescript(sql_script)

2.5 Submitting Current Things

connection.commit()

2.6 rollback

connection.rollback()

2.7 Close database connection

connection.close()

2.8 Read the next line

cursor.fetchone()

2.8 Read all remaining results

cursor.fetchall()

Basically, these are the common interfaces. For more detailed information, see Here

3 Output Display of Find

Above, we use cursor to execute the select statement, then construct it into zip format, and print it out in fetchall mode.
Here we use the cursor directly to print out the results, as shown below.

conn1=sqlite3.connect('C:\\sqlite\\test\\testDB.db')
createSQL1 = 'create table tbl_User (ID INT PRIMARY KEY NOT NULL,\
                    NAME TEXT NOT NULL,\
                    AGE INT NOT NULL,\
                    sex TEXT)'

conn1.execute(createSQL1)

insertSQL3 = 'insert into tbl_User values (1,"ahmed",30,"male")'
insertSQL4 = 'insert into tbl_User values (2,"Bob",28,"male")'
insertSQL5 = 'insert into tbl_User values (3,"cristina",18,"female")'

conn1.execute(insertSQL3)
conn1.execute(insertSQL4)
conn1.execute(insertSQL5)

conn1.commit()


selectSQL1 = 'select * from tbl_User'
cur = conn1.execute(selectSQL1)

for row in cur:
    print "ID = ", row[0]
    print "NAME = ",row[1]
    print "AGE = ", row[2]
    print "SEX = ",row[3]

conn1.close()

Here the cursor points directly to the result data after execution, and then directly reads the result pointed by the cursor.
The specific output format is as follows:

ID =  1
NAME =  ahmed
AGE =  30
SEX =  male
ID =  2
NAME =  Bob
AGE =  28
SEX =  male
ID =  3
NAME =  cristina
AGE =  18
SEX =  female

You can print out the information if you want to go the same way.

4. Executing sql scripts

In reality, we don't write create or insert statements in python scripts. Programming like this is messy. In practice, sql statements that need to be executed are usually written in sql scripts, and then invoked and executed in python scripts.

python script file

import sqlite3

conn = sqlite3.connect('testDB.db')

cursor = conn.cursor()

f = open(r"test.sql")

cursor.executescript(f.read())

conn.commit()

conn.close()

sql script file:

create table tbl_User (ID INTEGER PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INTEGER NOT NULL,sex TEXT);
insert into tbl_User values (1,"ahmed",30,"male");
insert into tbl_User values (2,"Bob",28,"male");
insert into tbl_User values (3,"cristina",18,"female");

Execute the result query:

D:\eclipseWorkspace\PythonLearning\src>sqlite3 testDB.db
SQLite version 3.19.2 2017-05-25 16:50:27
Enter ".help" for usage hints.
sqlite> .table
COMPANY   tbl_User
sqlite> select * from tbl_User;
1|ahmed|30|male
2|Bob|28|male
3|cristina|18|female