演示用SQLite3包操作SQLite数据库的代码:
#!/usr/bin/python
# -*- coding: UTF-8 -*-
#这里演示用SQLite3包操作AQLite数据库的代码
import sqlite3
conn = sqlite3.connect('test.db')
#获取游标对象
cur = conn.cursor()
#执行一系列SQL语句
#建立一个表
cur.execute("CREATE TABLE demo1(num int, str varchar(20));")
#插入一些记录
cur.execute("INSERT INTO demo1 VALUES(%d,'%s')"%(1,'aaa'))
cur.execute("INSERT INTO demo1 VALUES(%d,'%s')"%(2,'bbb'))
cur.execute("INSERT INTO demo1 VALUES(%d,'%s')"%(3,'ccc'))
#更新一条记录
cur.execute("UPDATE demo1 SET str = '%s' WHERE num=%d"%('ddd',3))
#查询
cur.execute("SELECT * FROM demo1")
rows = cur.fetchall()
print "number of records: ",len(rows)
for i in rows:
print i
#提交事务
conn.commit()
#关闭游标对象
cur.close()
#关闭数据库连接
conn.close()
运行结果
number of records: 3
(1, u'aaa')
(2, u'bbb')
(3, u'ddd')
二. peewee库编程以及CRUD内容
#!/usr/bin/python
# -*- coding: UTF-8 -*-
#从peewee包中引入所有内容
from peewee import *
from bench import db
from scipy.optimize._tstutils import description
#建立一个SQLite数据库引擎对象,该引擎打开数据库文件sampleDB.db
db = SqliteDatabase("sampleDB.db")
#定义一个ORM的基类,在基类中,指定本ORM所使用的数据库
#这样在之后所有的子类中就不用重复申明数据库了
class BaseModel(Model):
class Meta:
database = db
#定义course表,继承自BaseModel
class Course(BaseModel):
id = PrimaryKeyField()
title = CharField(null= False)
period = IntegerField()
description = CharField()
class Meta:
order_by = ('title',)
db_table = 'course'
#定义Teacher表,继承自BaseModel
class Teacher(BaseModel):
id = PrimaryKeyField()
name = CharField(null = False)
gender = BooleanField()
address = CharField()
course_id = ForeignKeyField(Course,to_field="id",related_name="course")
class Meta:
order_by = ('name',)
db_table = 'teacher'
#建表
course = Course.create_table()
teacher = Teacher.create_table()
#新增行
course.create(id=1, title='经济学', period=320,description='文理科学生均可选修')
course.create(id=2, title='大学英语', period=300,description='文一学生必修课')
course.create(id=3, title='哲学', period=100,description='必修课')
course.create(id=104, title='编译原理', period=100,description='计算机系选修')
teacher.create(id=1234, name='白展堂', gender=True,address='...',course_id = 1)
teacher.create(id=12345, name='展鸿林', gender=False,address='...',course_id = 2)
#查询一行
record = course.get(course.title='大学英语')
print "课程:%s,学时:%s"%(record.title,record.period)
#更新
record.period = 200
record.save()
#删除
record.del_instance()
#查询所有记录
courses = Course.select()
#带条件查询
courses = Course.select().where(Course.id<10).order_by(Course.period.desc())
#统计所有课程的平均学时
total = Course.select(fn.Avg(Course.period).alias('avg_period'))
#更新多个记录
Course.update(period=300).where(Course.id > 100).execute()