【SQLite3】Python使用SQLite3大全

---

一、python使用sqlite3数据库命令大全

1.1、步骤拆解

1.1.1、Python安装SQLite3插件

Python3自带SQLite3，不用单独安装

1.1.2、导入sqlite3包

import sqlite3

import sqlite3

1.1.3、连接到SQLite数据库

# 连接到SQLite数据库
# 数据库文件是 test.db，如果文件不存在，会自动在当前目录创建:
conn = sqlite3.connect('test.db')

1.1.4、创建一个Cursor

# 创建一个Cursor:
cursor = conn.cursor()

1.1.5、执行一条SQL语句，创建user表:

# 执行一条SQL语句，创建user表:
cursor.execute('CREATE TABLE IF NOT EXISTS user (id VARCHAR(20) PRIMARY KEY, name VARCHAR(20))')

1.1.6、关闭Cursor

# 关闭Cursor:
cursor.close()

1.1.7、执行查询语句，查询user表的所有数据

# 执行查询语句，查询user表的所有数据:
cursor = conn.cursor()
cursor.execute('SELECT * FROM user')
values = cursor.fetchall()
print(values)

1.1.8、使用参数化查询，防止SQL注入

# 使用参数化查询，防止SQL注入:
cursor.execute('INSERT INTO user (id, name) VALUES (?, ?)', ('1', 'Michael'))

1.1.9、执行更新语句，更新user表的数据

# 执行更新语句，更新user表的数据:
cursor.execute('UPDATE user SET name = ? WHERE id = ?', ('John', '1'))

1.1.10、关闭Cursor

# 关闭Cursor:
cursor.close()

1.1.11、提交事务

# 提交事务:
conn.commit()

1.1.12、关闭Connection

# 关闭Connection:
conn.close()

1.2、完整代码

import sqlite3
 
# 连接到SQLite数据库
# 数据库文件是 test.db，如果文件不存在，会自动在当前目录创建:
conn = sqlite3.connect('test.db')
 
# 创建一个Cursor:
cursor = conn.cursor()
 
# 执行一条SQL语句，创建user表:
cursor.execute('CREATE TABLE IF NOT EXISTS user (id VARCHAR(20) PRIMARY KEY, name VARCHAR(20))')
 
# 关闭Cursor:
cursor.close()
 
# 执行查询语句，查询user表的所有数据:
cursor = conn.cursor()
cursor.execute('SELECT * FROM user')
values = cursor.fetchall()
print(values)
 
# 使用参数化查询，防止SQL注入:
cursor.execute('INSERT INTO user (id, name) VALUES (?, ?)', ('1', 'Michael'))
 
# 执行更新语句，更新user表的数据:
cursor.execute('UPDATE user SET name = ? WHERE id = ?', ('John', '1'))
 
# 关闭Cursor:
cursor.close()
 
# 提交事务:
conn.commit()
 
# 关闭Connection:
conn.close()

二、SQLite数据字典

SQLite数据字典是SQLite数据库内部的一个特殊的表，它包含了数据库元数据，例如表的名字、列的名字以及数据类型等信息。在SQLite中，数据字典通常是通过特定的系统表查询得到的，而不是作为一个单独的特性来访问的。

如果你想要查询SQLite数据库的数据字典信息，你可以使用sqlite_master表来获取数据库中所有表的结构信息。以下是一个查询数据字典的SQL示例：

SELECT name, sql FROM sqlite_master
WHERE type='table';

这个查询会返回数据库中所有用户定义表的名字和创建这些表的SQL语句。

SELECT name 
  FROM sqlite_master 
 WHERE type IN ('table','view') 
   AND name NOT LIKE 'sqlite_%'
UNION ALL 
SELECT name 
  FROM sqlite_temp_master 
 WHERE type IN ('table','view') 
 ORDER BY 1;

三、获取某个特定表的列信息

如果你想要获取某个特定表的列信息，你可以使用PRAGMA table_info()函数，如下所示：

PRAGMA table_info([table_name]);

将[table_name]替换为你想要查询的表名。这将返回表中所有列的详细信息，包括列的名字、数据类型等。

四、查询所有表的创建语句

cursor.execute("SELECT name, sql FROM sqlite_master WHERE type='table';")
tables = cursor.fetchall()
for table_name, create_sql in tables:
    print(f"Table: {table_name}")
    print(create_sql)

五、查询特定表的列信息

table_name = 'your_table_name'
cursor.execute(f"PRAGMA table_info({table_name});")
columns = cursor.fetchall()
for column in columns:
    print(column)

将’table_name’替换为你想要查询的表名。

六、查询SQLite3小工具的版本

select sqlite_version();

七、写用户自定义函数，查询SQLite3数据库中的数据

7.1、插入数据函数

def insert_user_info(user_id_string, user_name_string):
    conn = sqlite3.connect('test.db')
    cursor = conn.cursor()
    cursor.execute('INSERT INTO user (id, name) VALUES (?, ?)', (user_id_string, user_name_string))
    conn.commit()
    cursor.close()

7.2、查询数据函数

def get_userid_by_username(user_name_string):
    conn = sqlite3.connect('test.db')
    cursor = conn.cursor()
    cursor.execute('SELECT id FROM user where name = ?', (user_name_string,))
    # 检查结果集是否为空
    # result = cursor.fetchone()
    results = cursor.fetchall()
    # 如果result为None，则数据不存在；否则，数据存在。
    if results is None:
        print("数据不存在")
        cursor.close()
        conn.close()
        return None
    else:
        print("数据存在")
        for row in results:
            print(row[0])
        cursor.close()
        conn.close()
        return results

7.3、测试

# insert_user_info('4', 'Sarah')
# insert_user_info('5', 'Sarah')
user_name_s = "Sarah"
print(get_userid_by_username(user_name_s))