SQLite is an embedded database, and its database is a file. Since SQLite itself is written in C and is small in size, it is often integrated into various applications, even in iOS and Android apps. Python has built-in SQLite3, so if you use SQLite in Python, you don't need to install anything and use it directly.
Before using SQLite, we must first figure out a few concepts:
A table is a collection of relational data stored in a database. A database usually contains multiple tables, such as student tables, class tables, school tables, and so on. The table and the table are related by a foreign key.
To operate a relational database, you first need to connect to the database. A database connection is called Connection;
After connecting to the database, you need to open the cursor, call it Cursor, execute the SQL statement through Cursor, and then obtain the execution result.
Python defines a set of API interfaces for operating databases. To connect any database to Python, you only need to provide a database driver that conforms to the Python standard.
Since the SQLite driver is built into the Python standard library, we can directly operate the SQLite database.
Let's practice it on the Python interactive command line:
# Import SQLite driver:import sqlite3
# Connect to SQLite database
# The database file is test.db
# If the file does not exist, it will be automatically created in the current directory:
conn = sqlite3.connect('test.db')
# Create a Cursor:
cursor = conn.cursor()
# Execute a SQL statement to create the user table:
cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')<sqlite3.cursor object="" at=""0x10f8aa260=""
# Continue to execute a SQL statement, insert a record:
cursor.execute('insert into user (id, name) values (\'1\', \'Michael\')')
# Get the number of rows inserted through rowcount:
cursor.rowcount
1
# Close Cursor:
cursor.close()
# Commit transaction:
conn.commit()
# Close Connection:
conn.close()</sqlite3.cursor </sqlite3.cursor
Let's try to query the record again:
conn = sqlite3.connect('test.db')
cursor = conn.cursor()
# Execute query:
cursor.execute('select * from user where id=?','1')<sqlite3.cursor object="" at=""0x10f8aa340=""
# Get query result set:
values = cursor.fetchall()
values
[( u'1', u'Michael')]
cursor.close()
conn.close()</sqlite3.cursor
When using Python's DB-API, as long as you figure out the Connection and Cursor objects, remember to close them after opening them, and you can use them with confidence.
When using the Cursor object to execute insert, update, and delete statements, the execution result is returned by rowcount to the number of rows affected, and the execution result can be obtained.
When using the Cursor object to execute the select statement, the result set can be obtained through featchall(). The result set is a list, each element is a tuple, corresponding to a row of records.
If the SQL statement has parameters, you need to pass the parameters to the execute() method according to the position. How many? placeholders must correspond to several parameters, for example:
cursor.execute('select * from user where id=?','1')
SQLite supports common standard SQL statements and several common data types. Please refer to the official SQLite website for specific documents.
summary
When operating a database in Python, you must first import the driver corresponding to the database, and then manipulate the data through the Connection object and the Cursor object.
To ensure that the opened Connection object and Cursor object are properly closed, otherwise, resources will be leaked.
How can we ensure that Connection and Cursor objects are also closed in case of error? Please recall the use of try:…except:…finally:….
Knowledge point expansion
Introduction to SQLite3
SQLite3 can be integrated with Python using the sqlite3 module. The sqlite3 module was written by Gerhard Haring. It provides a SQL interface compatible with the DB-API 2.0 specification described by PEP 249. You don't need to install this module separately, because Python 2.5.x and above have this module by default.
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.
How about that sounds great! Then come and learn to use it.
Connect to the database
The following Python code shows how to connect to an existing database. If the database does not exist, then it will be created, and finally a database object will be returned.
#- *- coding:utf-8-*-import sqlite3
conn = sqlite3.connect('mysql_person.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 us run the above program to create our database mysql_person.db in the current directory. You can change the path as needed. Save the above code to the sqlite.py file and execute it as shown below. If the database is successfully created, the message shown below will be displayed:
$chmod +x sqlite.py
$./sqlite.py
Open database successfully
This is the end of this article on how to use SQLite in Python. For more simple applications of SQLite in Python, please search ZaLou.Cn
Recommended Posts