0. 总结
1. SQL基础
SQL语言很简单，会看会写。装个SQL，然后随便找个教程敲一次大概就能知道能做什么，快的话一小时内可以敲完。数据库，其实只是一种“.db”格式的文件，文件存储方式是关系模式。
我跟着敲的教程是 菜鸟教程
进阶的数据库内容可以去上MOOC人大公开课。
2. python3如何使用sqlite？
2.1 基础调用格式
- sqlite3 是python3.6 自带的库，直接引用就行。
- 使用格式几乎都如下，类似之前的文件调用方式。
- 调用库
sqlite3 - 打开并连接数据库文件（没有数据库文件时新建）
- 做一些操作 excute，有更改操作时需要commit记录更改记录。例如insert，update等等
- excute中就是SQL语句了，其实就只是连到sqlite中执行操作而已。
- 操作完后关闭数据库文件
import sqlite3
# open/connect to the database
conn = sqlite3.connect('test.db')
c = conn.cursor()
# execute SQL commands, commit if changes are needed
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");
# Save (commit) the changes
conn.commit()
# close the database when the operation is completed
# Just be sure any changes have been committed or they will be lost.
conn.close()
2.2 增删改数据
- 用占位符
?去替换SQL中要增删改的数据。不要直接用SQL语言插入，不安全。用?代替要插入的数据位置，然后把python的tuple格式数据插进去。 - 大批量的数据可以用数组+tuple元素一次插入。
- 返回值，返回一行
fetchone()，全部符合行fetchall() - 创建表时先丢掉之前的记录，防止错误
# Never do this -- insecure!
symbol = 'RHAT'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)
# Do this instead
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(c.fetchone())
# Larger example that inserts many records at a time
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
('2006-04-06', 'SELL', 'IBM', 500, 53.00),
]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)
一个简单的例子：
import sqlite3
conn = sqlite3.connect('music.sqlite')
cur = conn.cursor()
# create a table named Tracks
cur.execute('DROP TABLE IF EXISTS Tracks')
cur.execute('CREATE TABLE Tracks (title TEXT,plays INTEGER)')
# insert value and then save the change
cur.execute('INSERT INTO Tracks (title,plays) VALUES (?,?)',('THUNDER',20))
cur.execute('INSERT INTO Tracks (title,plays) VALUES (?,?)',('MYWAY',15))
conn.commit()
print('tracks:')
cur.execute('SELECT title,plays FROM Tracks')
for row in cur:
print(row)
# delete related rows and save the change
cur.execute('DELETE FROM Tracks WHERE plays < 100')
conn.commit()
cur.close()
2.3 Basic data modeling,关系模型
唯一性，同样的东西尽量不写两次，而是用编码代替。合并表格时用JOIN
一个例子.png
join
2.4 对多张表格进行编程
2.5 数据库适用范围
数据库调用比表格困难，能不用数据库就不用。主要适用于数据量很大的情况，一般适用范围如下：
(1) when your application needs to make small many random updates within a large data set,
(2) when your data is so large it cannot fit in a dictionary and you need to look up information repeatedly, or
(3) when you have a long-running process that you want to be able to stop and restart and retain the data from one run to the next.
参考资料：
