轻量级SQL数据库Sqlite

SQLite是一款轻型的关系型数据库,它本身是C写的,所以体积小巧,占用资源低,支持Windows/Linux/Unix等主流操作系统,非常适合用来进行本地数据管理。Python 2.5.x 以上版本默认内置SQLite3,无需单独安装和配置,可直接使用。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
import pandas as pd
import sqlite3

class SqliteDb():
def __init__(self, DBname):
self.conn = sqlite3.connect(DBname+".db")

# 创建表
def create_table(self, tbname, content):
sql = "CREATE TABLE IF NOT EXISTS "+tbname+" ("+content+");"
self.conn.execute(sql)
self.conn.commit()

# 删除表
def delete_table(self, tbname):
try:
self.conn.execute('DROP TABLE '+tbname)
self.conn.commit()
except:
print('表不存在')

# DataFrame导入Sqlite
def pd_to_table(self, df, tbname, mode):
try:
df.to_sql(tbname, self.conn,
if_exists=mode, # replace:若表存在则覆盖原来表里的数据/append:若表存在将数据写到原表的后面
index=False, # 是否将df的index单独写到一列中
index_label=None, chunksize=None, dtype=None)
except:
print('DataFrame导入失败')

# 查询数据
def query(self, sql, col_names=None):
if sql.split('from')[0].strip()[-1] == '*' and len(sql.split('from')[1].strip().split(' ')) == 1:
result = self.conn.execute("PRAGMA table_info("+str(sql.split('from')[1].strip())+")")
col_names = [i[1] for i in result]
return pd.DataFrame(self.conn.execute(sql).fetchall(), columns=col_names)

# 插入数据
def insert(self, tbname, names, values):
self.conn.execute("INSERT INTO "+tbname+"("+names+") VALUES ("+values+");")
self.conn.commit()

# 更新数据
def update(self, tbname, alteration, condition):
self.conn.execute("UPDATE "+tbname+" SET "+alteration+" WHERE "+condition+";")
self.conn.commit()

# 删除数据
def delete(self, tbname, condition):
self.conn.execute("DELETE FROM "+tbname+" WHERE "+condition+";")
self.conn.commit()

# 关闭连接
def close(self):
self.conn.close()

c = SqliteDb('test')

c.delete_table('user')
c.create_table('user',
'''
user_id INTEGER PRIMARY KEY,
user_name CHAR(50) ,
user_age INTEGER
''')
c.insert('user', 'user_name, user_age','"张三", 20')
c.insert('user', 'user_name, user_age','"李四", 25')
c.update('user', 'user_age=26', 'user_name="李四"')
c.delete('user', 'user_name="张三"')
print(c.query('select * from user'))
0%