python学习笔记十（连接并操控数据库）

数据库编程接口

连接对象

数据库连接对象(Connection Object)主要提供获取数据库游标对象和提交、回滚事务的方法，以及关闭数据库连接。
connect() 函数常用的参数及说明如下表

参数	说明
dns	数据源名称，给出该参数表示数据库依赖
user	用户名
password	用户密码
host	主机名
database	数据库名称

connect = pymysql.connect(host="localhost",
                          user="user",
                          password="root",
                          db="ivps",
                          charset="utf-8",
                          cursorclass=pymysql.cursors.DictCursor)

连接对象方法如下表

方法名	说明
close()	关闭数据库连接
commit()	提交事务
rollback()	回滚事务
cursor()	获取游标对象，操作数据库，如执行DML操作，调用存储过程等

游标对象的方法如下表

方法名	说明
callproc(procname,[, parameters])	调用存储过程，需要数据库支持
close()	关闭当前游标
execute(operation[, parameters)	执行数据库操作，SQL语句或者数据库命令
executemany(operation,seq_of_ params)	用于批量操作，如批量更新
fetchone()	获取查询结果集中的下一条记录
fetchmany(size)	获取指定数量的记录
fetchall()	获取结果集的所有记录
nextset()	跳至下一个可用的结果集
arraysize	指定使用fetchmany()获取的行数，默认为1
setinputsizes(sizes)	设置在调用execute*()方法时分配的内存区域大小
setoutputsize(sizes)	设置列缓冲区大小，对大数据列(如LONGS和BLOBS)尤其有用

SQLite数据库

与许多其他数据库管理系统不同，SQLite 不是一一个客户端/服务器结构的数据库引擎，而是一种嵌入式数据库，它的数据库就是一个文件。SQLite 将整个数据库，包括定义，表、索引以及数据本身，作为一个单独的、可跨平台使用的文件存储在主机中。由于SQLite本身是C写的，而且体积很小，所以，经常被集成到各种应用程序中。Python 就内置了sQLite3, 所以在Python中使用SQLite，不需要安装任何模块，直接使用。

import sqlite3  #导入模块
connect = sqlite3.connect("ces.db") #连接数据库
cursor = connect.cursor()  #创建游标
cursor.execute("create table user(id int(10) primary key,name varchar(20))") #执行sql语句
cursor.close() #关闭游标
connect.close() #关闭连接

添加数据

由于是新增数据，需要使用commit()方法提交事务。因为对于增加、修改和删除操作，
使用commit)方法提交事务后，如果相应操作失败，可以使用rollback()方法回滚到操作之前的状态

import sqlite3  #导入模块
connect = sqlite3.connect("ces.db")
cursor = connect.cursor()
cursor.execute("insert into user(id,name) values(1,'我是一号')")
cursor.execute("insert into user(id,name) values(2,'我是二号')")
cursor.execute("insert into user(id,name) values(3,'我是三号')")
cursor.close()
connect.commit() #提交事务
connect.close()

查看用户信息

获取查询数据方法如下表

方法名	说明
fetchone()	获取查询结果集中的下一条记录
fetchmany(size)	获取指定数量的记录
fetchall()	获取结构集的所有记录

import sqlite3  #导入模块
connect = sqlite3.connect("ces.db")
cursor = connect.cursor()
cursor.execute("select * from user")
result1 = cursor.fetchone()
result2 = cursor.fetchmany(2)
result3 = cursor.fetchall()
print(result1)  #得到 (1, '我是一号')
print(result2)  #得到 [(1, '我是一号'), (2, '我是二号')]
print(result3)  #得到 [(1, '我是一号'), (2, '我是二号'), (3, '我是三号')]
cursor.close()
connect.close()

如果已经打印了result1，那么再次打印的result2中不包含result1的内容

修改cursor.execute("select * from user")为cursor.execute("select * from user where id > ?",(1,))
得出
[(2, '我是二号'), (3, '我是三号')]
即不打印第一行数据

修改数据

语法：update 表名 set 字段名 = 字段值 where 条件

import sqlite3  #导入模块
connect = sqlite3.connect("ces.db")
cursor = connect.cursor()
cursor.execute("update user set name=? where id=?",("我被修改了",1))
cursor.execute("select * from user")
result = cursor.fetchall()
print(result)
connect.commit()
cursor.close()
connect.close()
[(1, '我被修改了'), (2, '我是二号'), (3, '我是三号')]

删除数据

语法：delete from 表明 where 查询条件

import sqlite3  #导入模块
connect = sqlite3.connect("ces.db")
cursor = connect.cursor()
cursor.execute("delete from user where id=?",(1,))
cursor.execute("select * from user")
result = cursor.fetchall()
print(result)
connect.commit()
cursor.close()
connect.close()
[(2, '我是二号'), (3, '我是三号')]

Mysql

安装pymysql

pip install pymysql

连接数据库

import pymysql
db = pymysql.connect("localhost","root","root","ces") #分别代表主机地址、用户名、密码、数据库名称
#连接数据库一般以下面的方法为准，不会出错
db = pymysql.connect(host="127.0.0.1",
                       port=3306,
                       user="root",
                       passwd="root",
                       db="mrsoft",
                       charset="utf8")
cursor = db.cursor() #创建游标
cursor.execute("select version()") #通过游标查询数据库版本
result = cursor.fetchall() #获取全部查询结果
print(result) #打印查询结果
cursor.close() #关闭游标
db.close() #关闭连接

创建表格

import pymysql
dbmysql = pymysql.connect("localhost","root","root","ces") #分别代表主机地址、用户名、密码、数据库名称
cursor = dbmysql.cursor() #创建游标
cursor.execute("DROP TABLE IF EXISTS book") #如果存在book表则删除
sql = """
create table book(
xno int(4) primary key,
xname varchar(20) not null,
loc varchar(20),
index collage_index(xname)
)engine=innodb default charset=utf8; 
"""
cursor.execute(sql)
cursor.execute("SHOW DATABASES;") #查询数据库所有表名称
result = cursor.fetchall() #获取全部查询结果
dbmysql.commit()
print(result) #打印查询结果
cursor.close() #关闭游标
dbmysql.close() #关闭连接

插入MySQL表的数据

import pymysql
db = pymysql.connect("localhost","root","root","ces")
cursor = db.cursor()
data = [(1,'lihua','dhdt'),
        (2,'daming','hd'),
        (3,'xiaohong','rrr'),
        ]
try:
    cursor.executemany("insert into book(xno,xname,loc) VALUES (%s,%s,%s)",data)
    #cursor.execute("INSERT INTO book(xno,xname,loc) VALUES(1,'lihua','dhdt'),(2,'daming','hd'),(3,'xiaohong','rrr')")
    db.commit()
except:
    print("发送错误，回滚")
    db.rollback()
db.close()