How to install sqlite? A brief explanation of the usage of self in Python

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

Python has built-in SQLite3, so when using SQLite in Python, you don’t need to install anything, just use it directly.

Before using SQLite, we must first understand 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 table, school table, etc. Tables are related through foreign keys.

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 a cursor, called Cursor, and execute SQL statements through Cursor. Then, get the execution results.

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 complies with Python standards.

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:

# 导入SQLite驱动:
>>> import sqlite3
# 连接到SQLite数据库
# 数据库文件是test.db
# 如果文件不存在，会自动在当前目录创建:
>>> conn = sqlite3.connect('test.db')
# 创建一个Cursor:
>>> cursor = conn.cursor()
# 执行一条SQL语句，创建user表:
>>> cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')

# 继续执行一条SQL语句，插入一条记录:
>>> cursor.execute('insert into user (id, name) values (\'1\', \'Michael\')')

# 通过rowcount获得插入的行数:
>>> cursor.rowcount
1
# 关闭Cursor:
>>> cursor.close()
# 提交事务:
>>> conn.commit()
# 关闭Connection:
>>> conn.close()

Let’s try querying records again:

>>> conn = sqlite3.connect('test.db')
>>> cursor = conn.cursor()
# 执行查询语句:
>>> cursor.execute('select * from user where id=?', '1')

# 获得查询结果集:
>>> values = cursor.fetchall()
>>> values
[(u'1', u'Michael')]
>>> cursor.close()
>>> conn.close()

When using Python’s DB-API, just figure out the Connection and Cursor object, be sure to remember to close it after opening it, and you can use it with confidence.

When using the Cursor object to execute insert, update, and delete statements, the execution result is returned by rowcount, and the number of affected rows is returned, and the execution result can be obtained.

When using the Cursor object to execute the select statement, the result set can be obtained through featall(). The result set is a list, and each element is a tuple, corresponding to a row of records.

If the SQL statement has parameters, then the parameters need to be passed to the execute() method according to their position. How many? placeholders must correspond to how many parameters there are, for example:

cursor.execute('select * from user where id=?', '1')

SQLite Supports common standard SQL statements and several common data types. For specific documentation, please refer to the SQLite official website.

Summary

When operating a database in Python, you must first import the driver corresponding to the database, and then , operate data through Connection object and Cursor object.

Make sure that the opened Connection object and Cursor object are closed correctly, otherwise, resources will be leaked.

How can we ensure that the Connection object and Cursor object are also closed in the event of an error? Please recall the usage of try:...except:...finally:....

The above is the detailed content of How to install sqlite? A brief explanation of the usage of self in Python. For more information, please follow other related articles on the PHP Chinese website!