python sqlite3学习笔记

sql基本语句：

--创建表

CREAT TABLE category(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
Name NOT NULL,
Description TEXT
);

CREAT TABLE book (
Title TEXT NOT NULL,
Author TEXT,
Publisher TEXT,
Price REAL,
Discontinued INTEGER,
Quantity INTEGER,
PublishData TEXT,
CategoryID INTEGER,
FOREING KEY (CategoryID) REFERENCES category(ID)
);
--查询数据
select * from book;
select Title,Author,Price from book;
select Title as 书名，Author from book;
select ROWID as ID,* from book;
select ROWID as ID,* from book where Author = 'Tom';
select ROWID as ID,* from book where Author like '%o%';
select ROWID as ID,* from book where Priece <> 50 and Author like '%o';
select AVG(Price) from book; --MAX,MIN

--关联两张表的关系
select category.Name,book.* from category INNER JOIN book on category.ID = book.CategoryID;





 --插入数据
insert into book
(Title,Author,Publisher,Price,Discontinued,Quantity,PublishDate,CategoryID)
values ('HTML--JIAOCHENG','Jane','hafu',44.88,0.98,'2017-03-03',1);

 --更新数据
update book set Title = 'Web--JIAOCHENG', Price = 49.30 WHERE ROWID = 6;

 删除数据
delete from book where ROWID = 6;

导入库

import sqlite3

连接数据库：(没有就新建）

db = sqlite3.connect(r'd:\wjd\sql1.db')

数据库的连接对象，有以下几种操作行为：

1 )、commit() ，事务提交
2 )、rollback() ，事务回滚
3 )、cursor() ，创建游标
4 )、close() ， 关闭一个连接

在创建了游标之后，它有以下可以操作的方法

execute()，执行sql语句
scroll()，游标滚动
close()，关闭游标
executemany，执行多条sql语句 
fetchone()，从结果中取一条记录
fetchmany()，从结果中取多条记录
fetchall()，从结果中取出多条记录

具体的python3例子：

import sqlite3
db = sqlite3.connect('sql2.db')
cur = db.cursor()
cur.execute("""create table book(id integer primary key,name varchar(10) UNIQUE,price integer)""" ) #执行sql语句
cur.execute("insert into book(name,price) values('红楼梦',50)")
db.commit()                   #事务提交
n = 1
price_data = 56
cur.execute("update book set name = '三国演义', price = {} where id = {}".format(price_data,n))  #将外部变量更新到数据库
db.commit()
cur.execute("select * from book")
l = cur.fetchall()

desc = cur.description                #获取表头信息
print(desc[0][0]+' '+desc[1][0],' ',desc[2][0])            #打印表头
print(l)
cur.close()
db.close()