Skip to main content

SQLite Python:从表中选择数据

要从Python查询SQLite数据库中的数据,请使用以下步骤:

在下面的示例中,我们将使用“创建表”教程中创建的任务表。

首先,创建与文件指定的SQLite数据库的连接:

def create_connection(db_file):
""" create a database connection to the SQLite database
specified by the db_file
:param db_file: database file
:return: Connection object or None
"""
conn = None
try:
conn = sqlite3.connect(db_file)
except Error as e:
print(e)

return conn

此函数从任务表中选择所有行,并显示数据:

def select_all_tasks(conn):
"""
Query all rows in the tasks table
:param conn: the Connection object
:return:
"""
cur = conn.cursor()
cur.execute("SELECT * FROM tasks")

rows = cur.fetchall()

for row in rows:
print(row)

在select_all_tasks()函数中,我们创建了一个游标,执行select语句,并调用fetchall()从tasks表中获取所有任务。

此功能用于按优先级查询任务:

def select_task_by_priority(conn, priority):
"""
Query tasks by priority
:param conn: the Connection object
:param priority:
:return:
"""
cur = conn.cursor()
cur.execute("SELECT * FROM tasks WHERE priority=?", (priority,))

rows = cur.fetchall()

for row in rows:
print(row)

在select_task_by_priority()函数中,我们根据特定的优先级选择任务。问号(?)在查询中是占位符。当光标执行SELECT语句时,它替换了问号(?)根据优先权的论点。fetchall()方法按优先级获取所有匹配的任务。

此main()函数用于创建与数据库C:\sqlite\db\pythonsqlite的连接。db和调用函数来查询任务表中的所有行,并选择优先级为1的任务:

def main():
database = r"C:\sqlite\db\pythonsqlite.db"

# create a database connection
conn = create_connection(database)
with conn:
print("1. Query task by priority:")
select_task_by_priority(conn, 1)

print("2. Query all tasks")
select_all_tasks(conn)

以下是完整的程序:

import sqlite3
from sqlite3 import Error


def create_connection(db_file):
""" create a database connection to the SQLite database
specified by the db_file
:param db_file: database file
:return: Connection object or None
"""
conn = None
try:
conn = sqlite3.connect(db_file)
except Error as e:
print(e)

return conn


def select_all_tasks(conn):
"""
Query all rows in the tasks table
:param conn: the Connection object
:return:
"""
cur = conn.cursor()
cur.execute("SELECT * FROM tasks")

rows = cur.fetchall()

for row in rows:
print(row)


def select_task_by_priority(conn, priority):
"""
Query tasks by priority
:param conn: the Connection object
:param priority:
:return:
"""
cur = conn.cursor()
cur.execute("SELECT * FROM tasks WHERE priority=?", (priority,))

rows = cur.fetchall()

for row in rows:
print(row)


def main():
database = r"C:\sqlite\db\pythonsqlite.db"

# create a database connection
conn = create_connection(database)
with conn:
print("1. Query task by priority:")
select_task_by_priority(conn, 1)

print("2. Query all tasks")
select_all_tasks(conn)


if __name__ == '__main__':
main()

在本教程中,您学习了如何开发一个Python程序,从SQLite数据库的表中查询数据。