Python 使用 SQLite3

SQLite 是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。

SQLite 使用起来极度的方便，下面展示 Python 操作 SQLite：

import sqlite3

# 连接数据库
conn = sqlite3.connect('数据库路径')


# 创建游标来执行SQL语句
cur = conn.cursor()


# 获取表名
tables = cur.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchall()


# 获取表的列名
columns_name = cur.execute('SELECT * FROM table_name').description


# 获取表结构的所有信息
info = cur.execute('PRAGMA table_info(table_name)').fetchall()


# 创建表
cur.execute('''CREATE TABLE COMPANY
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')


# 插入数据
cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
			VALUES (1, 'Paul', 32, 'California', 20000.00 )");


# 批量插入数据
cur.executemany('insert into user_tb values(?, ?, ?)',
    (('sun', '123456', 'male'),
    ('bai', '123456', 'female'),
    ('zhu', '123456', 'male'),
    ('niu', '123456', 'male'),
    ('tang', '123456', 'male')))


# 查询数据
cur.execute('select * from test_table;')


# 更新数据
cur.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")


# 批量更新数据
c.executemany('update user_tb set name=? where _id=?',
			(('小孙', 2),
			('小白', 3),
			('小猪', 4),
			('小牛', 5),
			('小唐', 6)))


# 删除数据：
c.execute("DELETE from COMPANY where ID=2;")