sqlite3学习笔记

目录

概要

Python sqlite3实现流程

Python sqlite3具体操作

---

 

概要

认识sqlite3：sqlite3数据库是一款非常小巧的嵌入式开源数据库软件，也就是说没有独立的维护进程，所有的维护都来自于程序本身。它使用一个文件存储整个数据库，操作十分方便。它的最大优点是使用方便，功能相比于其它大型数据库来说，确实有些差距。

使用步骤：

1. 用db.connect创建数据库连接，假设连接对象为conn。
2. 如果该数据库操作不需要返回结果，就直接用conn.execute查询，根据数据库事务隔离级别的不同，可能修改数据库需要conn.commit。
3. 如果需要返回查询结果则用conn.cursor创建游标对象cur, 通过cur.execute查询数据库，用cur.fetchall/cur.fetchone/cur.fetchmany返回查询结果。根据数据库事 务隔离级别的不同，可能修改数据库需要conn.commit。
4. 关闭cur, conn。

Python sqlite3实现流程

sqlite3安装：（pycharm自带）

python3.6 以上版本 默认自带sqlite3模块

sqlite3数据库连接

# 导入sqlite3模块
import sqlite3
 
# 创建链接对象
# 打开一个到 SQLite 数据库文件 db.sqlite3 的链接
# 如果该数据库不存在则会自动创建，可以指定带有文件路径的文件名
conn = sqlite3.connect('db.sqlite3')

获取游标对象

# 获取游标对象用来操作数据库
cursor = conn.cursor()

执行操作（简单示例）

* 创建表
    ```
    cursor.execute('''create table user(id integer primary key autoincrement,name varchar(20) not null)''')
    ```
* 插入记录
    ```
    # 插入一条id=1 name='xiaoqiang'的记录
    cursor.execute('''insert into user(id,name) values(1,'xiaoqiang')''')
    ```
* 查找记录
    ```
    # 查找user表中id=1的记录
    cursor.execute('''select * from user where id=1''')
    # 获得结果
    values = cursor.fetchall()
    values
    [(u'1', u'Michael')]
    ```
* 删除记录
    ```
    # 删除id=1的记录
    sursor.excute('''delete from user where id=1''')
    ```
* 修改记录
    ```
    # 修改id=1记录中的name为xiaoming
    sursor.excute('''update user set name='xiaoming' where id=1''')
    ```

提交并关闭数据库连接

# 修改id=1记录中的name为xiaoming
sursor.excute('''update user set name='xiaoming' where id=1''')
# 提交事务
conn.commit()
# 关闭链接
conn.close()

 

 

 

Python sqlite3具体操作

新建数据表操作

import sqlite3
conn=sqlite3.connect('2.db')#不存在会自动创建

cur=conn.cursor()#获取管理权
cur.execute('create table star(id integer,name text,age integer,address text)')#创建表，注意不能重复创建，名字相同会出错
rows=[(1,'张三',14,'重庆'),(2,'张4',16,'重庆2')]
for item in rows:
    cur.execute('insert into star (id,name,age,addres) values (?,?,?,?)',item)#写入表，star表名

sql='select * from star'#查询表的结构
cur.execute(sql)#执行SQL
print(cur.fetchall())#取出数据
conn.close

查询数据操作

import sqlite3
cur = sqlite3.connect('stu.db').cursor()
query = cur.execute('select name,wuli,wulibm from cj where xuejie = 2019 and name = "高天雪" and wulibm <= 19')
colname = [ d[0] for d in query.description ]
result_list = []
# print(query.fetchall())
for r in query.fetchall():
  row = []
  for i in range(len(colname)):
    print(r[i])
    row.append(r[i])
  result_list.append(row)
  del row

cur.close()
cur.connection.close()
print(result_list )

插入数据操作

import sqlite3
def insert_score_data():#插入
    conn=sqlite3.connect('scores.db')#如果不存在则新建
    # sql='select * from cj'#编写sql语句
    cur=conn.cursor()#获取管理权
    sql= 'insert into score (stu_name,math_score,chinese_score) values(?,?,?)'
    data=('发货',90,100)
    cur.execute(sql,data)#执行SQL
    conn.commit()#结束插入
    # print(cur.fetchall())#取出数据
    cur.close()
    conn.close

删除数据操作

import sqlite3
def delete_score_data():#删除
    conn=sqlite3.connect('scores.db')
    # sql='select * from cj'#编写sql语句
    cur=conn.cursor()#获取管理权
    sql= 'delete from score where id=?'
    id=(3,)
    cur.execute(sql,id)#执行SQL
    conn.commit()#提交
    # print(cur.fetchall())#取出数据
    cur.close()
    conn.close

更新数据操作

import sqlite3
def update_score_data():#修改
    conn=sqlite3.connect('scores.db')
    # sql='select * from cj'#编写sql语句
    cur=conn.cursor()#获取管理权
    sql= 'update  score set math_score = ?, chinese_score=? where id =2'
    data=(3,3,)
    cur.execute(sql,data)#执行SQL
    conn.commit()#提交
    # print(cur.fetchall())#取出数据
    cur.close()
    conn.close

数据排序（条件查询）

import sqlite3
conn=sqlite3.connect('scores.db')
# sql='select * from score order by math_score'#数学成绩升序
# sql='select * from score order by math_score desc'#数学成绩降序
sql='select * from score order by math_score desc,id'#数学成绩降序,id升序
cur=conn.cursor()#获取管理权
cur.execute(sql)#执行SQL
print(cur.fetchall())#取出数据
conn.close

数据筛选（条件查询） 

import sqlite3
conn=sqlite3.connect('stu.db')
# sql='select * from score where math_score >=80'#筛选出数学>=80
# sql='select * from score where math_score >=80 and chinese_score >=80'#筛选出数学语文>=80
# sql='select * from score where math_score  IN ( 25, 27 )'#筛选出数学成绩是25，27的
# sql='select * from score where stu_name like "zh%" '#筛选名字列zh开头的，后面不计
# # sql='select chinese_score from score where stu_name like "zh%" '#筛选名字列zh开头的，后面不计的语文成绩
# sql='select  chinese_score from score '#筛选所有人语文成绩

sql='select  name,wulinm from cj '#筛选任意几列数据
cur=conn.cursor()#获取管理权
cur.execute(sql)#执行SQL
print(cur.fetchall())#取出数据
conn.close

行对象（row操作）

from  sqlite3 import connect,Row
con=connect('scores.db')
con.row_factory=Row
cur=con.cursor()
cur.execute('select * from score')
row=cur.fetchone()#获取一行
print(type(row))
print('以列名访问：',row['stu_name'])
print('以索访问：',row[1])
print('以迭代的访问')
for item in row:#列的每个元素
    print(item)
print('len():',len(row))#列数
con.close

批量操作（row操作） 

from  sqlite3 import connect,Row
con=connect('scores.db')
con.row_factory=Row
cur=con.cursor()
rows=[(10,'z10',80,80),(11,'z11',80,80)]#可以更多数据
cur.executemany('insert into score (id,stu_name,math_score,chinese_score) values (?,?,?,?)',rows)
cur.execute('select * from score')
for row in cur:
    for r in row:
        print(r)
con.commit()
con.close