Python-Python与数据库

创建数据库

updata------------>dml (数据操作语言)

insert------------>dml (数据操作语言)

delete------------>dml (数据操作语言)

select------------>dql (数据查询语言)

创建数据库语法

create database 数据库名;

创建数据表

语法

create table if not exists table_name(column_name column_type);

数据类型

类型	大小	用途
INT或INTEGER	4 Bytes	整数
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
DATE	YYYY-MM-DD	日期值
TIME	HH:MM:SS	时间值或持续时间
YEAR	YYYY	年份值

约束

主键自增（int）

primary key auto_increment

 外键

constraint <约束名>
foreign key <外键名>(字段名1，字段名2...)
references <主表名>(主键字段名)

唯一约束

 <字段名> <数据类型> unique

非空约束

not null

默认约束

<字段名> <数据类型> default <默认值>

切换数据库

 使用/切换这个数据库： use 数据库名字；

增(添加)

insert into 表名（列名1，列名2） values（值1，值2）

删

delete from 表名 where 表达式  #删除单个语句
drop database 库名字； #输出数据库
drop table 表名字;#输出数据表

改(更新)

update 表名 set 列名=值 [where 表达式]

 alter

alter table 旧表名 rename  to 新表名; #修改表名字
alter table 表名字 modify 列名 (新)数据类型; #修改数据类型 id char(10)---->id int
alter table 表名字 change 旧列名字 新列名字 新数据类型；#修改列名字  studentname-->name
alter table 表名 drop 列名;#删除某一列
alter table 表名 add 新列名 （新列名）数据类型;#添加一个列
alter table 表名 modify 列名1 数据类型 after 列名字2;#把列1插入到列2后面

查

select 要查询的列名1，列名2 from 表名1,表2 
where 表达式1 and/not/or 表达式2
order by 列名字 desc/asc #order by 排序 desc降序 asc升序
limit 数字 #限制查询的条数 limit（2, 5）第2条数据到第5条数据

练习

创建学生信息表。字段有：学生id（主键自增），学生姓名，性别，生日，电话（不为空），身份证号（唯一）
创建科目表：字段有：科目id（主键自增），科目名字
成绩表：成绩id（主键自增，成绩，科目id（外键），学生id（外键））
分别插入十条数据

---

#学生信息表
create TABLE studentInfo(
studentId int auto_increment primary key,
StudentName varchar(20),
#性别
Gender varchar(2),
Birthday date,
Age int(4),
BeginYear year,
Phone  varchar(11) not null,
idcard varchar(20) not null unique
);

#科目表
create TABLE subject(
subjectId int auto_increment primary key,
subjectname varchar(20)
);

#成绩表
create TABLE exam(
examId int auto_increment primary key,
exam float(4),
subjectid int,
constraint fk_subjectr_id foreign key(subjectid) references subject(subjectId),
studentid int,
constraint fk_student_id foreign key(studentid) references studentInfo(studentId)
);

•  上面我们讲述了，数据库的基本语法，下面我来看怎么用Python来写SQL语句

 Python与数据库连接

1.安装pymsql

pip install pymysql

列出软件包清单
    pip list
查看软件包信息
    pip show django

•  打开cmd运行窗口(要以管理员身份运行)
• 先运行 pip install pymsql 开始下载
• 下载完成后，可以运行 pip list 查看是否安装成功

2.连接mysql

连接方法

pymysql.connect(host,user, password,port,db,charset)
host：MySQL服务器所在的主机的ip；
user：用户名
password：密码
port：连接的MySQL主机的端口，默认是3306
db：连接的数据库名
charset：当读取数据出现中文会乱码的时候，需要我们设置一下编码；python3默认采用的utf8字符集

• 首先打开服务----->启动mysql

例如

import pymysql

db=pymysql.connect(host='localhost',
                   user='root',
                   password='123456',
                   port=3306,
                   db='temp',
                   charset='utf8')
#运行不报错就是可以了

3.cursor游标对象

它负责执行我们熟悉的SQL语句

开启游标

# 开启mysql的游标功能，创建一个游标对象；              
cursor = db.cursor()

常用方法

方法	功能
execute(query, args)	执行单条sql语句，接收的参数为sql语句本身和使用的参数列表，返回值为受影响的行数；
executemany(query, args)	执行单条sql语句，但是重复执行参数列表里的参数，返回值为受影响的行数

区别是：批量添加的时候executemany比较快

# 要执行的SQL语句，确保数据表中有相应的数据
sql = "select * from exam;"  
# 使用游标对象执行SQL语句；
cursor.execute(sql)

cursor用来查询数据的方法

我们执行完sql语句怎么查询返回的受影响的函数呢，有以下及格方法

方法	功能
fetchone()	返回一条结果行
fetchmany(size)	接收size条返回结果行。如果size的值大于返回的结果行的数量，则会返回cursor.arraysize条数据
fetchall()	接收全部的返回结果行

例如

import pymysql

db=pymysql.connect(host='localhost',
                   user='root',
                   password='123456',
                   port=3306,
                   db='temp',
                   charset='utf8')
#运行不报错就是可以了

# 开启mysql的游标功能，创建一个游标对象；              
cursor = db.cursor()

# 要执行的SQL语句，确保数据表中有相应的数据
sql = "select * from exam;"  
# 使用游标对象执行SQL语句；
cursor.execute(sql)
# 使用fetchone()方法，获取返回的结果，但是需要用变量保存返回结果；
#data = cursor.fetchone() #(1, '张三', 60)  单条数据
#多条数据
#data = cursor.fetchall() #((1, '张三', 60), (2, '李四', 60), (3, '王五', 80))
#两条数据
data = cursor.fetchmany(2) #((1, '张三', 60), (2, '李四', 60))
print(data)
# 断开数据库的连接，释放资源；
db.close()

• 运行的时候，什么都不输出就是正确的

游标类型

类型	意义
Cursor	默认，元组类型
DictCursor	字典类型
DictCursorMixin	支持自定义的游标类型，需先自定义才可使用
SSCursor	无缓冲元组类型
SSDictCursor	无缓冲字典类型

# 开启mysql的游标功能，创建一个游标对象；              
cursor = db.cursor(cursor=DictCursor)

4.才做数据库

创建表

import pymysql

db=pymysql.connect(host='localhost',
                   user='root',
                   password='123456',
                   port=3306,
                   db='temp',
                   charset='utf8')
#运行不报错就是可以了

# 开启mysql的游标功能，创建一个游标对象；              
cursor = db.cursor()

#创建表，字段是id，姓名，年龄，性别
sql = """
CREATE TABLE ceshi(
    id int,
    name varchar(10),
    age int,
    sex char(10)
) 

"""  
# 使用游标对象执行SQL语句；
cursor.execute(sql)
# 断开数据库的连接，释放资源；
db.close()

插入数据(插入单条数据)

import pymysql

db=pymysql.connect(host='localhost',
                   user='root',
                   password='123456',
                   port=3306,
                   db='temp',
                   charset='utf8')
#运行不报错就是可以了

# 开启mysql的游标功能，创建一个游标对象；              
cursor = db.cursor()

#插入数据
sql = """
insert into ceshi(id, name, age, sex)
values(%s,%s,%s,%s)
"""  
try:
      # 使用游标对象执行SQL语句；
	cursor.execute(sql,(2,"翠花",19,"男"))
        db.commit()
except Exception as ex:
        print('插入语句错误' + str(ex))
        db.rollback()
finally:
        db.close()

插入多条数据

import pymysql

db=pymysql.connect(host='localhost',
                   user='root',
                   password='123456',
                   port=3306,
                   db='temp',
                   charset='utf8')
#运行不报错就是可以了

# 开启mysql的游标功能，创建一个游标对象；              
cursor = db.cursor()

#插入数据
sql = """
insert into ceshi(id, name, age, sex)
values(%s,%s,%s,%s)
"""  
data = [
    (3,"王五",19,"男"),
    (4,"李四",19,"男"),
    (5,"赵六",19,"男")
]

try:
      # 使用游标对象执行SQL语句；
		cursor.executemany(sql,data)
        db.commit()
except Exception as ex:
        print('插入语句错误' + str(ex))
        db.rollback()
finally:
        db.close()

查询数据

import pymysql

db=pymysql.connect(host='localhost',
                   user='root',
                   password='123456',
                   port=3306,
                   db='temp',
                   charset='utf8')

# 开启mysql的游标功能，创建一个游标对象；              
cursor = db.cursor()
# 要执行的SQL语句，确保数据表中有相应的数据
sql = "select * from ceshi;"  
# 使用游标对象执行SQL语句；
cursor.execute(sql)
#多条数据
data = cursor.fetchall() 
print(data)
# 断开数据库的连接，释放资源；
db.close()

更新数据

import pymysql

db=pymysql.connect(host='localhost',
                   user='root',
                   password='123456',
                   port=3306,
                   db='temp',
                   charset='utf8')

# 开启mysql的游标功能，创建一个游标对象；              
cursor = db.cursor()
# 要执行的SQL语句，确保数据表中有相应的数据
sql = "update ceshi set age=%s, sex=%s where id=%s;"  
try:
       # 使用游标对象执行SQL语句；
		cursor.execute(sql,(50,"女",3)) 
        db.commit()
except Exception as ex:
        print('插入语句错误' + str(ex))
        db.rollback()
finally:
        db.close()

删除数据

import pymysql

db=pymysql.connect(host='localhost',
                   user='root',
                   password='123456',
                   port=3306,
                   db='temp',
                   charset='utf8')

# 开启mysql的游标功能，创建一个游标对象；              
cursor = db.cursor()
# 要执行的SQL语句，确保数据表中有相应的数据 
sql = 'delete from ceshi where id=%s'
try:
        cursor.execute(sql, 3)  
        db.commit()
except Exception as ex:
        print('插入语句错误' + str(ex))
        db.rollback()
finally:
        db.close()

总结:执行事物(复习)

事务机制可以确保数据一致性。

事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。

· 原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的各种操作要么都做，要么都不做。

· 一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

· 隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

· 持久性（durability）。持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

在pymysql模块是默认开启MySQL的事务功能的，MySQL不会自动提交所执行的相关SQL语句。因此，进行"增"、"删"、"改"的时候，一定要使用db.commit()提交事务，否则就看不见所插入的数据。

同时，进行"增"、"删"、"改"的时候，一定要使用try…except…语句，因为SQL语句一旦没有执行成功，我们可以使用db.rollback()回滚到操作之前的状态；当语句执行成功，我们就db.commit()提交事务。