In this tutorial , We will use Python Process programmatically SQLite3 database .
SQLite It is usually a serverless database , You can include Python It is used in almost all programming languages including . No server means there is no need to install a separate server to use SQLite, So you can connect directly to the database .
SQLite Is a lightweight database , It can provide a zero configuration relational database management system , Because you can use it without configuring or setting anything .
We will use SQLite edition 3 or SQLite3, So let's start .
Catalog
To be in Python Use in SQLite3, First , You must import _sqlite3_ modular , Then create a connection object , It connects us to the database and lets us execute SQL sentence .
You can use _connect()_ Function to create a connection object ：
import sqlite3
con = sqlite3.connect('mydatabase.db')
This will create a file named “mydatabase.db” The new document of .
To be in Python In the implementation of SQLite sentence , You need a cursor object . You can use _cursor()_ Method to create it .
SQLite3 Cursors are a way to connect objects . To execute SQLite3 sentence , The first thing to do is to establish a connection , Then use the connection object to create the cursor object , As shown below ：
con = sqlite3.connect('mydatabase.db')
cursorObj = con.cursor()
Now we can use cursor object to call _execute()_ Method to perform any SQL Inquire about .
When you create with SQLite When the , If it doesn't exist , It will automatically create a database file . This database file is created on disk ; We can also use :memory: and connect Function in RAM Create database . This database is called memory database .
Consider the following code , We use _try_、_except_ and _finally_ Block creates a database to handle any exceptions ：
import sqlite3
from sqlite3 import Error
def sql_connection():
try:
con = sqlite3.connect(':memory:')
print("Connection is established: Database is created in memory")
except Error:
print(Error)
finally:
con.close()
sql_connection()
First , We import _sqlite3_ modular , And then we define a function sql_connection. Inside this function , We have a _try_ block ,_connect()_ Function returns a connection object after establishing a connection .
Then we have _except_ block , It will print an error message in any abnormal situation . If there are no mistakes , The connection will be established and the following message will be displayed .
after , We are _finally_ Block closes our connection . Closing the connection is optional , But this is a good programming habit , So you can free memory from any unused resources .
To be in SQLite3 Create a table , You can go to _execute()_ Method used in Create Table Inquire about . Consider the following steps ：
Let's create employees with the following attributes ：
employees (id, name, salary, department, position, hireDate)
The code will look like this ：
import sqlite3
from sqlite3 import Error
def sql_connection():
try:
con = sqlite3.connect('mydatabase.db')
return con
except Error:
print(Error)
def sql_table(con):
cursorObj = con.cursor()
cursorObj.execute("CREATE TABLE employees(id integer PRIMARY KEY, name text, salary real, department text, position text, hireDate text)")
con.commit()
con = sql_connection()
sql_table(con)
In the code above , We define two methods , The first method is to establish a connection , The second method creates a cursor object to execute create table sentence .
stay _ Submit （）_ Method to save all our changes . Last , Both methods are called .
To check whether our table has been created , You can use SQLite Of Database browser To view your table . Use this program to open mydatabase.db file , You should see your watch ：
To insert data into a table , We use INSERT INTO sentence . Consider the following lines of code ：
cursorObj.execute("INSERT INTO employees VALUES(1, 'John', 700, 'HR', 'Manager', '2017-01-04')")
con.commit()
To check whether the data has been inserted , Please click on the DB Browser Medium Browse Data：
We can also set the value / Parameter passed to _execute()_ Methods INSERT sentence . You can use a question mark () As a placeholder for each value .INSERT The syntax of is as follows ：
cursorObj.execute(‘’‘INSERT INTO employees(id, name, salary, department, position, hireDate) VALUES(, , , , , )’‘’, entities)
The values of the entity containing placeholders are as follows ：
entities = (2, ‘Andrew’, 800, ‘IT’, ‘Tech’, ‘2018-02-06’)
The whole code is as follows ：
import sqlite3
con = sqlite3.connect('mydatabase.db')
def sql_insert(con, entities):
cursorObj = con.cursor()
cursorObj.execute('INSERT INTO employees(id, name, salary, department, position, hireDate) VALUES(?, ?, ?, ?, ?, ?)', entities)
con.commit()
entities = (2, 'Andrew', 800, 'IT', 'Tech', '2018-02-06')
sql_insert(con, entities)
To update the table , Just create a connection , Then use this connection to create a cursor object , Last in _execute()_ Method used in UPDATE sentence .
Suppose we want to update id be equal to 2 The name of the employee . To update , We will use UPDATE Statement and id be equal to 2 The employees' . We will use WHERE Clause as a condition for selecting the employee .
Consider the following code ：
import sqlite3
con = sqlite3.connect('mydatabase.db')
def sql_update(con):
cursorObj = con.cursor()
cursorObj.execute('UPDATE employees SET name = "Rogers" where id = 2')
con.commit()
sql_update(con)
This will change the name from Andrew Change to Rogers, As shown below ：
You can use select Statement to select data from a specific table . If you want to select all columns of data from the table , You can use asterisks (*). The syntax is as follows ：
select * from table_name
stay SQLite3 in ,SELECT Statement is in the cursor object execute Method . For example, choose employees All columns of the table , Run the following code ：
cursorObj.execute('SELECT * FROM employees ')
If you want to select several columns from the table , Please specify the columns shown below ：
select column1, column2 from tables_name
for example ,
cursorObj.execute(‘SELECT id, name FROM employees’)
select Statement to select the required data from the database table , If you want to get the selected data , Use the _fetchall()_ Method . We will demonstrate this in the next section .
To get data from the database , We will carry out SELECT sentence , Then use the _fetchall()_ Method stores the value in a variable . after , We will traverse the variables and print all the values .
The code will look like this ：
import sqlite3
con = sqlite3.connect('mydatabase.db')
def sql_fetch(con):
cursorObj = con.cursor()
cursorObj.execute('SELECT * FROM employees')
rows = cursorObj.fetchall()
for row in rows:
print(row)
sql_fetch(con)
The above code will print out the records in our database as follows ：
You can also _ In a row _ Use _fetchall()_, As shown below ：
[print(row) for row in cursorObj.fetchall()]
If you want to get specific data from the database , have access to WHERE Clause . for example , We want to earn more than 800 Of our employees id And name . So , Let's fill our table with more rows , Then execute our query .
You can use insert statements to fill in data , It can also be in DB Manually enter them in the browser program .
Now? , To get a salary greater than 800 The man's id And name ：
import sqlite3
con = sqlite3.connect('mydatabase.db')
def sql_fetch(con):
cursorObj = con.cursor()
cursorObj.execute('SELECT id, name FROM employees WHERE salary > 800.0')
rows = cursorObj.fetchall()
for row in rows:
print(row)
sql_fetch(con)
Above SELECT In the sentence , We don't use asterisks (*), It's a designation id and name attribute . The results will be as follows ：
SQLite3 rowcount Used to return the most recently executed SQL The number of rows affected or selected by the query .
When we're in SELECT Use in statement rowcount when , Will return -1, Because before all extraction , How many rows are selected is unknown . Consider the following example ：
print(cursorObj.execute(‘SELECT * FROM employees’).rowcount)
therefore , To get the number of rows , Need to get all the data , Then get the length of the result ：
rows = cursorObj.fetchall()
print len (rows)
When you use without any conditions （where Clause ） Of DELETE When the sentence is , All rows in the table will be deleted , And back to rowcount Total number of deleted rows in .
print(cursorObj.execute(‘DELETE FROM employees’).rowcount)
If no rows are deleted , It will return zero .
To list SQLite3 All the tables in the database , You should inquire sqlite_master surface , And then use _fetchall()_ from SELECT Get the result in the statement .
sqlite_master yes SQLite3 Main table in , Store all the tables .
import sqlite3
con = sqlite3.connect('mydatabase.db')
def sql_fetch(con):
cursorObj = con.cursor()
cursorObj.execute('SELECT name from sqlite_master where type= "table"')
print(cursorObj.fetchall())
sql_fetch(con)
This will list all tables , As shown below ：
Create table time , We should make sure that the table does not exist . Again , Delete / When deleting a table , The table should exist .
To check whether the table already exists , We are CREATE TABLE Use in statement “if not exists”, As shown below ：
create table if not exists table_name (column1, column2, …, columnN)
for example ：
import sqlite3
con = sqlite3.connect('mydatabase.db')
def sql_fetch(con):
cursorObj = con.cursor()
cursorObj.execute('create table if not exists projects(id integer, name text)')
con.commit()
sql_fetch(con)
Again , To check whether the table exists when deleting , We are DROP TABLE Use in statement “if exists”, As shown below ：
drop table if exists table_name
for example ,
cursorObj.execute(‘drop table if exists projects’)
We can also check whether the table we want to access exists by executing the following query ：
cursorObj.execute('SELECT name from sqlite_master WHERE type = "table" AND name = "employees"')
print(cursorObj.fetchall())
If the employee table exists , It will return its name as follows ：
If the table name we specified does not exist , Will return an empty array ：
You can use DROP Statement delete / Delete table .DROP The syntax of the statement is as follows ：
drop table table_name
To delete a table , The table should exist in the database . therefore , It is suggested that drop Use in statement “if exists”, As shown below ：
drop table if exists table_name
for example ,
import sqlite3
con = sqlite3.connect('mydatabase.db')
def sql_fetch(con):
cursorObj = con.cursor()
cursorObj.execute('DROP table if exists employees')
con.commit()
sql_fetch(con)
Exceptions are runtime errors . stay Python Programming , All exceptions are from BaseException Instances of derived classes .
stay SQLite3 in , We have the following main Python abnormal ：
Any database related error will cause DatabaseError.
IntegrityError yes DatabaseError Subclasses of , In case of data integrity problems, it will cause . for example , External data in all tables has not been updated , This leads to inconsistent data .
When there is a syntax error or the table is not found or the wrong number of parameters are used / Parameter when calling a function , Exception will be thrown ProgrammingError.
This exception will be thrown when the database operation fails , For example, abnormal disconnection . It's not the programmer's fault .
When you use some methods that are not defined or supported by the database , Will lead to NotSupportedError abnormal .
You can use executemany Statement inserts more than one line at a time .
Consider the following code ：
import sqlite3
con = sqlite3.connect('mydatabase.db')
cursorObj = con.cursor()
cursorObj.execute('create table if not exists projects(id integer, name text)')
data = [(1, "Ridesharing"), (2, "Water Purifying"), (3, "Forensics"), (4, "Botany")]
cursorObj.executemany("INSERT INTO projects VALUES(?, ?)", data)
con.commit()
Here we create a table with two columns ,“data” Each column has four values . We pass variables and queries to _executemany()_ Method .
Please note that , We use placeholders to pass values .
The above code will produce the following results ：
After using the database , It's best to close the connection . You can use _close()_ Method to close the connection .
To close the connection , Please use the connection object and call _close()_ Method , As shown below ：
con = sqlite3.connect('mydatabase.db')
#program statements
con.close()
stay Python SQLite3 In the database , We can import _datatime_ The module easily stores the date or time . The following formats are the most common formats available for date and time ：
YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
Consider the following code ：
import sqlite3
import datetime
con = sqlite3.connect('mydatabase.db')
cursorObj = con.cursor()
cursorObj.execute('create table if not exists assignments(id integer, name text, date date)')
data = [(1, "Ridesharing", datetime.date(2017, 1, 2)), (2, "Water Purifying", datetime.date(2018, 3, 4))]
cursorObj.executemany("INSERT INTO assignments VALUES(?, ?, ?)", data)
con.commit()
In this code , We first introduced datetime modular , And created a assignments Table of , It contains three columns .
The data type of the third column is date . To insert the date in the column , We used _datetime.date_. Again , We can use _datetime.time_ To deal with time .
The above code will generate the following output ：
SQLite3 The great flexibility and mobility of database make it the first choice for any developer to use it and publish it with any product .
You can go to Windows、Linux、Mac OS、Android and iOS Project use SQLite3 database , Because they have excellent portability . therefore , You send a file with the project , That's it .
