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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
|
#!/usr/bin/env python
# -*-coding:utf-8 -*-
import os
import re
import linecache
import math
# import time
# import datetime
import shutil
import numpy as np
import pandas as pd
import sqlite3
import matplotlib.pyplot as plt
os.chdir(os.path.split(os.path.realpath(__file__))[0])
#删除数据库
dir = './test.db'
if os.path.exists(dir):
os.remove(dir)
#新建数据库
con = sqlite3.connect('./test.db')
# con = sqlite3.connect(':memory:') #在内存中创建
cur = con.cursor()
#创建表
cur.execute('CREATE TABLE IF NOT EXISTS hoge (hoge1, hoge2)')
cur.execute('CREATE TABLE IF NOT EXISTS fuga (fuga1, fuga2, fuga3)')
#获取表名
cur.execute('SELECT name from sqlite_master where type="table";')
print(cur.fetchall())
#获取列
cur.execute('PRAGMA TABLE_INFO(hoge)')
print(cur.fetchall())
#写记录
cur.execute('CREATE TABLE hoge2(id INTEGER PRIMARY KEY AUTOINCREMENT, name STRING, age INTEGER)')
cur.execute('INSERT INTO hoge2(name, age) values("Taro", 12)')
cur.execute('INSERT INTO hoge2(name) values("Hanako")')
cur.execute('INSERT INTO hoge2(id, name, age) values(20, "Akiko", 2)')
cur.execute('INSERT INTO hoge2(name, age) values("Jiro", 10)')
cur.execute('INSERT INTO hoge2 values(3, "Julia", 15)')
cur.execute('SELECT * FROM hoge2')
print(cur.fetchall())
#使用?阻止SQL注入攻击
sql = 'INSERT INTO hoge2 VALUES(?, ?, ?)'
data = ((4, 'John', 30))
cur.execute(sql, data)
cur.execute('SELECT * FROM hoge2')
print(cur.fetchall())
#一次增加多行记录
data = [(5, "Taro", 18),
(None, "Bob", 19),
(None, "Masa", 16),
(None, "Jiro", 35),
(None, "Satoshi", 40)]
cur.executemany('INSERT INTO hoge2 VALUES(?,?,?);',data);
cur.execute('SELECT * FROM hoge2')
print(cur.fetchall())
#修改记录
cur.execute('update hoge2 set age=20 where name="Julia"')
cur.execute('SELECT * FROM hoge2')
print(cur.fetchall())
#删除记录
cur.execute('delete from hoge2 where age>14')
cur.execute('SELECT * FROM hoge2')
print(cur.fetchall())
#读取指定行
cur.execute('SELECT * FROM hoge2')
print(cur.fetchone()) #第一行
cur.execute('SELECT * FROM hoge2')
print(cur.fetchmany(2)) #前两行
#读取指定列
cur.execute('SELECT id, name FROM hoge2') #读取id, name列
print(cur.fetchall())
#读取符合条件的记录
cur.execute('SELECT * FROM hoge2 WHERE age <= 12')
print(cur.fetchall())
cur.execute('SELECT * FROM hoge2 WHERE name = "Hanako"')
print(cur.fetchall())
cur.execute('SELECT * FROM hoge2 WHERE name <> "Jiro"')
print(cur.fetchall())
cur.execute('SELECT * FROM hoge2 order by age asc') #按照年龄顺序排序
print(cur.fetchall())
cur.execute('SELECT * FROM hoge2 order by age desc') #按照年龄降序排序
print(cur.fetchall())
#增加一列
cur.execute('ALTER TABLE hoge ADD COLUMN hoge3')
cur.execute('PRAGMA TABLE_INFO(hoge)')
print(cur.fetchall())
#重命名列
cur.execute('ALTER TABLE hoge RENAME TO hoge_tmp')
cur.execute('CREATE TABLE hoge (hogehoge1, hogehoge2)')
cur.execute('INSERT INTO hoge (hogehoge1, hogehoge2) SELECT hoge1, hoge2 FROM hoge_tmp')
cur.execute('DROP TABLE hoge_tmp')
cur.execute('PRAGMA TABLE_INFO(hoge)')
print(cur.fetchall())
#删除列
cur.execute('ALTER TABLE hoge RENAME TO hoge_tmp')
cur.execute('CREATE TABLE hoge (hogehoge2)')
cur.execute('INSERT INTO hoge (hogehoge2) SELECT hoge2 FROM hoge_tmp')
cur.execute('DROP TABLE hoge_tmp')
cur.execute('PRAGMA TABLE_INFO(hoge)')
print(cur.fetchall())
#重命名表
cur.execute('ALTER TABLE hoge rename to hogehoge')
cur.execute('ALTER TABLE fuga rename to fugafuga')
cur.execute('SELECT name from sqlite_master where type="table";')
print(cur.fetchall())
#删除表
cur.execute('DROP TABLE hogehoge')
cur.execute('SELECT name from sqlite_master where type="table";')
print(cur.fetchall())
con.commit()
cur.close()
con.close()
|
