SQLite (https://www.sqlite.org/) It is a software library, small and flexible, self-sufficient, server free, zero configuration and transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. No configuration management is required. You can add environment variables after unpacking the installation package. It is compact and flexible, and supports multiple programming language interfaces.
Python installs the SQLite interface by default. You can view the version information of the SQLite interface through print(sqlite3.version).
0. What is SQLite?
SQLite is an in-process library that implements a self-sufficient, serverless, zero configuration, transactional SQL database engine. It is a zero configuration database, which means that unlike other databases, you do not need to configure it in the system.
Like other databases, the SQLite engine is not an independent process and can be statically or dynamically connected according to application requirements. SQLite directly accesses its storage files.
0.1 installing SQLite
go to SQLite download page , Download precompiled binaries for Windows from the Windows area.
You need to download SQLite tools Win32 - Zip and SQLite DLL Win32 - Zip compressed file.
Create the folder D:\SQLite3, and extract the above two compressed files under this folder to get SQLite3 def,sqlite3.dll and SQLite3 Exe and other files. Then add D:\SQLite3 to the PATH environment variable. Finally, at the command prompt, use SQLite3 command to display the following results.
Note: the path of SQLite3 should not be placed under Chinese and paths with spaces, otherwise there may be an inoperable problem
0.2 create database
In order to use the sqlite3 module, you must first create a connection object representing the database, and then you can optionally create a cursor object, which will help you execute all SQL statements.
SQLite's sqlite3 command is used to create a new SQLite database. You do not need any special permissions to create a data.
$ sqlite3 DatabaseName.db .database # View database path .quit # Exit SQlite3 # More commands can be passed help view
In general, the database name should be unique within the RDBMS.
By default, the path where the command line is located in the cmd window is the database save address, so you must cd the path in advance when creating the database
In addition, we can also use open to create a new database file:
sqlite>.open test.db
The above command creates the database file test DB, located in the same directory as SQLite3 command.
It is also used to open an existing database Open command, if the above command is test If DB exists, it will be opened directly. If it does not exist, it will be created.
1. CONNECT operation
The following Python code shows how to connect to an existing database. If the database does not exist, it will be created and finally a database object will be returned.
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('D:/SQLite3/test.db')
print("Opened database successfully")
Here, you can also copy the database name to a specific name: memory:, which will create a database in RAM. Now, let's run the above program and create our database test. Net in the current directory db. You can change the path as needed.
If the given database name filename does not exist, the call creates a database.
If you do not want to create a database in the current directory, add a file name with the specified path so that you can create the database anywhere.
Save the above code to SQLite Py file and execute as follows. If the database is created successfully, the following message is displayed:
$chmod +x sqlite.py $./sqlite.py Open database successfully
2. Create table
The following Python code snippet will be used to create a table in the previously created database:
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print("Opened database successfully")
c = conn.cursor()
c.execute('''CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);''')
print("Table created successfully")
conn.commit()
conn.close()
When the above program is executed, it will be in test Create the COMPANY table in dB and display the following message:
Opened database successfully Table created successfully
3. INSERT operation
The following Python program shows how to create records in the COMPANY table created above:
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print("Opened database successfully")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (1, 'Paul', 32, 'California', 20000.00 )")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (2, 'Allen', 25, 'Texas', 15000.00 )")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )")
conn.commit()
print("Records created successfully")
conn.close()
When the above program is executed, it will create the given record in the COMPANY table and display the following two lines:
Opened database successfully Records created successfully
4. SELECT operation
The following Python program shows how to get and display records from the COMPANY table created earlier:
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print("Opened database successfully")
cursor = c.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print("ID = ", row[0])
print("NAME = ", row[1])
print("ADDRESS = ", row[2])
print("SALARY = ", row[3], "\n")
print("Operation done successfully")
conn.close()
When the above program is executed, it will produce the following results:
Opened database successfully ID = 1 NAME = Paul ADDRESS = California SALARY = 20000.0 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000.0 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
5. UPDATE operation
The following Python code shows how to use the UPDATE statement to UPDATE any record, and then get and display the updated record from the COMPANY table:
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print("Opened database successfully")
c.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")
conn.commit()
print("Total number of rows updated :", conn.total_changes)
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print("ID = ", row[0])
print("NAME = ", row[1])
print("ADDRESS = ", row[2])
print("SALARY = ", row[3], "\n")
print("Operation done successfully")
conn.close()
When the above program is executed, it will produce the following results:
Opened database successfully Total number of rows updated : 1 ID = 1 NAME = Paul ADDRESS = California SALARY = 25000.0 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000.0 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
6. DELETE operation
The following Python code shows how to DELETE any records using the DELETE statement, and then get and display the remaining records from the COMPANY table:
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print("Opened database successfully")
c.execute("DELETE from COMPANY where ID=2;")
conn.commit()
print("Total number of rows deleted :", conn.total_changes)
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print("ID = ", row[0])
print("NAME = ", row[1])
print("ADDRESS = ", row[2])
print("SALARY = ", row[3], "\n")
print("Operation done successfully")
conn.close()
When the above program is executed, it will produce the following results:
Opened database successfully Total number of rows deleted : 1 ID = 1 NAME = Paul ADDRESS = California SALARY = 20000.0 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
7. SQLite3 module API
7.1 sqlite3.connect()
sqlite3.connect(database [,timeout ,other optional arguments])
This API opens a link to the SQLite database file database. You can use ": memory:" to open a database connection to the database in RAM instead of on disk. If the database is successfully opened, a connection object is returned.
When a database is accessed by multiple connections and one of them modifies the database, the SQLite database is locked until the transaction is committed. The timeout parameter indicates the duration of the connection waiting for locking until an abnormal disconnection occurs. The timeout parameter defaults to 5.0 (5 seconds).
If the given database name filename does not exist, the call creates a database. If you do not want to create a database in the current directory, you can specify a file name with a path so that you can create the database anywhere.
7.2 connection.cursor([cursorClass])
This routine creates a cursor that will be used in Python database programming. This method accepts a single optional parameter cursorClass. If this parameter is provided, it must be an extension from SQLite3 Custom cursor class for cursor.
7.3 cursor.execute(sql [, optional parameters])
This routine executes an SQL statement. The SQL statement can be parameterized (that is, use placeholders instead of SQL text). The sqlite3 module supports two types of placeholders: question marks and named placeholders (named styles).
For example: cursor execute(“insert into people values (?, ?)”, (who, age))
7.4 connection.execute(sql [, optional parameters])
This routine is a shortcut to the method provided by the cursor object executed above. It creates an intermediate cursor object by calling the cursor method, and then calls the execute method of the cursor through the given parameters.
7.5 cursor.executemany(sql, seq_of_parameters)
This routine is for SEQ_ of_ Execute an SQL command for all parameters or mappings in parameters.
7.6 connection.executemany(sql[, parameters])
This routine is a shortcut to the middle cursor object created by calling the cursor method, and then calls the executenamy method of the cursor through the given parameters.
7.7 cursor.executescript(sql_script)
Once the routine receives the script, it will execute multiple SQL statements. It first executes the COMMIT statement and then executes the SQL script passed in as a parameter. All SQL statements should use semicolons; separate.
7.8 connection.executescript(sql_script)
This routine is a shortcut to the middle cursor object created by calling the cursor method, and then calls the executescript method of the cursor through the given parameters.
7.9 connection.total_changes()
This routine returns the total number of database rows that have been modified, inserted, or deleted since the database connection was opened.
7.10 connection.commit()
This method commits the current transaction. If you do not call this method, any action you have taken since you last called commit() will not be visible to other database connections.
7.11 connection.rollback()
This method rolls back changes made to the database since the last call to commit().
7.12 connection.close()
This method closes the database connection. Note that this does not automatically call commit(). If you do not call the commit() method before, close the database connection directly, and all your changes will be lost!
7.13 cursor.fetchone()
This method obtains the next row in the query result set and returns a single sequence. When there is no more available data, it returns None.
7.14 cursor.fetchmany([size=cursor.arraysize])
This method obtains the next row group in the query result set and returns a list. When no more rows are available, an empty list is returned. This method attempts to get as many rows as possible specified by the size parameter.
7.15 cursor.fetchall()
This routine gets all (remaining) rows in the query result set and returns a list. When no rows are available, an empty list is returned.