python sqlite库_在了解sqlite的基础上，如何使用python3自带的sqlite3库？

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.

参考资料：