python系列均基于python3.4环境

1、新建数据表

• 新建表，命名为student（id, name, score, sex, age），id为关键字，代码如下：

import sqlite3

test.db is a file in the working directory

conn = sqlite3.connect("test.db")
c = conn.cursor()

create tables

sql = '''create table student (id int primary key, name varchar(20), score int, sex varchar(10), age int)'''
c.execute(sql)

save the changes

conn.commit()

close the connection with the database

conn.close()

（1）如果数据库test.db不存在的话，会自动创建数据库test.db

（2）如果sql语句比较长的话，需要换行，又要保持格式的话，可以使用三引号(python--基础学习（三）字符串单引号、双引号、三引号)

2、插入数据

• 代码示例

import sqlite3

conn = sqlite3.connect("test.db")
c = conn.cursor()

students = [(2, 'mark', 80, 'male', 18),
(3, 'tom', 78, 'male', 17),
(4, 'lucy', 98, 'female', 18),
(5, 'jimi', 60, 'male', 16)]

第一种：execute "INSERT"

c.execute("insert into student(id, name, score, sex, age) values (1,'jack',80,'male',18)")

第二种：execute multiple commands

c.executemany('insert into student values (?,?,?,?,?)', students)

第三种：using the placeholder

c.execute("insert into student values (?,?,?,?,?)", (6, 'kim', 69, 'male', 16))

conn.commit()
conn.close()

3、查询

• 代码示例

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()

第一种：retrieve one record

c.execute('select * from student order by score desc')
print(c.fetchone()) #第1条记录
print(c.fetchone()) #第2条记录

第二种：retrieve all records as a list

c.execute('select * from student order by score desc')
print(c.fetchall())

第三种：terate through the records

rs = c.execute('select * from student order by score desc')
for row in rs:
print(row)

conn.commit()
conn.close()

• 运行结果：

#第一种
(4, 'lucy', 98, 'female', 18)
(1, 'jack', 80, 'male', 18)

#第二种
[(4, 'lucy', 98, 'female', 18), (1, 'jack', 80, 'male', 18), (2, 'mark', 80, 'male', 18), (3, 'tom', 78, 'male', 17), (6, 'kim', 69, 'male', 16), (5, 'jimi', 60, 'male', 16)]

#第三种
(4, 'lucy', 98, 'female', 18)
(1, 'jack', 80, 'male', 18)
(2, 'mark', 80, 'male', 18)
(3, 'tom', 78, 'male', 17)
(6, 'kim', 69, 'male', 16)
(5, 'jimi', 60, 'male', 16)

4、修改

• 代码示例

import sqlite3

conn = sqlite3.connect("test.db")
c = conn.cursor()

sql = "update student set name='jerry' where id = 2"
c.execute(sql)

conn.commit()
conn.close()

5、删除

• 删除数据，代码示例

conn = sqlite3.connect("test.db")
c = conn.cursor()

c.execute('delete from student where id=2')

conn.commit()
conn.close()

• 删除数据表

c.execute('drop table tableName')

(@_@)Y 学习总结到此结束，待续！