Python SQLite conn.execute: Complete Guide

1 year ago
Emily Johnson
2 minutes

The conn.execute method in Python’s SQLite3 module is used to run SQL statements. Its usage is as follows:

conn.execute(sql_statement)

conn is an object of the Connection class in the SQLite3 library, and sql_statement is the SQL statement to be executed in string format.

After executing the SQL statement, conn.execute() will return a Cursor object, which can be used to retrieve the execution results. The results of the query can be obtained using methods provided by the Cursor object, such as:

  1. fetchone(): Retrieve the next row of data from the result set and return a tuple.
  2. fetchall(): Retrieve all rows of data from the result set and return a list containing multiple tuples.
  3. fetchmany(size): retrieves a specified number of rows from the result set, returning a list containing multiple tuples, with the number determined by the size parameter.

In addition to query statements, conn.execute() can also execute other types of SQL statements such as insert, update, and delete operations. The number of affected rows can be obtained through the rowcount attribute of the Cursor object.

It is important to note that conn.execute() can only execute one SQL statement. If you need to execute multiple statements, you can use the executescript() method.

Example usage:

import sqlite3

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

# 创建表
conn.execute('''CREATE TABLE IF NOT EXISTS employees
             (id INT PRIMARY KEY     NOT NULL,
             name           TEXT    NOT NULL,
             age            INT     NOT NULL);''')

# 插入数据
conn.execute("INSERT INTO employees (id, name, age) VALUES (1, 'John', 30)")
conn.execute("INSERT INTO employees (id, name, age) VALUES (2, 'Alice', 25)")

# 查询数据
cursor = conn.execute("SELECT id, name, age from employees")
rows = cursor.fetchall()
for row in rows:
    print(row)

# 更新数据
conn.execute("UPDATE employees SET age = 35 WHERE name = 'John'")

# 删除数据
conn.execute("DELETE FROM employees WHERE name = 'Alice'")

# 提交事务
conn.commit()

# 关闭连接
conn.close()

The above code demonstrates several common uses of conn.execute(), including creating tables, inserting data, querying data, updating data, and deleting data.

bannerAds