14 . Python3之MysSQL
数据库概念
数据库: 按照数据结构来组织、存储、管理数据的仓库`
诞生
计算机的发明是为了做科学计算的，而科学计算需要大量的输入和输出. 早期，可以使用打孔卡片的孔、灯泡的亮灭表示数据输入，输出. 后来，数据可以存储在磁带上，顺序的读取、写入磁带. 1956年IBM发明了磁盘驱动器这个革命性产品，支持随机访问. 随着信息化时代的到来，有了硬件存储技术的发展，有大量的数据需要存储和管理，数据库管理.
分类
# 按照数据模型分类:
# 网状数据库
# 层次数据库
# 关系型数据库
层次数据库
以树型结构表示实体及其之间的关系，关系支持一对多，代表数据库IMM IMS，不能交叉,在有些地方很适合使用。
网状数据库
通用电气早在1964年开发出网状数据库IDS，只能运行在GE自家的主机上.
节点描述数据，结点的联系就是数据的关系. 能够直接描述客观世界，可以表示实体间多种复杂关系，而这是层次数据模型无法做到的，比如，一个节点可以有多个父节点，节点之间支持多对多关联.
关系数据库
使用行、列组成的二维表来组织数据和关系、表中行(记录)既可以描述数据实体，也可以描述实体间关系. 关系模型比网状模型、层次模型更简单，不需要关系数存储的物理洗劫，专心于数据的逻辑构建，而且关系模型有论文的严格的数据理论基础支撑. 1970年，IBM的研究员E.F.Codd发表了名为'A Relational Model of Data for Large Shared Data Banks'的论文，提出了关系模型的概念，奠定了关系模型的理论基础. 关系模型，有严格的数据基础，抽象级别较高，简单清晰，便于理解和使用. 经过几十年的发展，关系数据库百花齐放，技术日渐成熟和完善. 基于关系模型构建的数据库系统成为RDBMS（Relational DataBase Sytem）. IBM DB2,Oracle的Oracle和Mysql、微软的MS SQL，以前的Infomix,Sybase等.
Oracle的发展
拉里·埃里森(Larry EMison)仔细阅读了IBM的关系数据库的论文，敏感意识到在这个研究基础上可以开发商用软件系统，他们决定开发通用商用数据库系统Oracle，这个名字来源于他们曾给中央情报局做过的项目名，几个月后，他们就开发了Oracle1.0,Oracle快速的被推销，但是很不稳定，直到1992年的时候，Oracle7才逐渐稳定下来，并取得巨大成功，2001年的9i版本被广泛应用.
2009年4月20日，甲骨文公司宣布以每股9.50美元，总计74亿美金收购sun（计算机系统）公司，2010年1月成功收购. 2013年,甲骨文超过IBM，成为继微软之后的全球第二大软件公司.
Mysql发展
1985年几个瑞典人为大型零售商的项目设计了一种利用索引顺序存取数据的软件，他就是MyISAM的前身,1996年，MySQL1.0发布，随后发布了3.11.1版本，并开始往其他平台移植，2000年MySQL采用GPL协议开元，MySQL4.0采用GPL协议开源，MySQL4.0开始支持MyISAM、InnoDB. 2005年10月,MySQL5.0成为里程碑版本. 2008年1月被Sun公司收购 2009年1月,在Oracle收购Mysql前，Monty Widenius担心收购，就从Mysql Server5.5开始一条新的GPL分支，起名MariaDB. MySQL的引擎的是插件化的，可以支持很多种引擎: MyISASM,不支持事务，插入，查询速度快. InnoDB,支持事务，行级锁，Mysql5.5起的默认引擎.
去IOE
他是阿里巴巴造出的概念，其本意是，在阿里巴巴的IT架构中，去掉IBM的小型机、Oracle数据库、EMC存储设备，代之以自己在开源软件基础上开发的系统，传统上，一个高端大气的数据中心，IBM小型机、Oracle数据库、EMC存储设备，可以说缺一不可，而使用这些架构的企业，不但维护成本极高，核心架构还掌握在他人手里.
对于阿里巴巴这样大规模的互联网应用，采用开源、开放的系统架构，这并不是阿里巴巴发明的，国外的谷歌、Facebook、亚马逊等早已为之，只不过他们几乎一开始就没有采用IT商业公司的架构，所以他们也不用"去IOE"
去IOE，转而使用廉价的架构，稳定性一定下降，需要较高的运维水平解决.
NoSQL
NoSQL是非SQL，非传统关系数据库的统称. NoSQL一词诞生于1998年，2009年这个词汇被再次提出指非关系型，分布式，不提供ACID的数据库设计模式.
随之互联网时代的到来，数据爆发式增长，数据库技术发展日新月异，要适应新的业务需求，随着移动互联网，物联网的到来，大数据的技术中NoSQL也同样重要.
什么是数据库？
什么是数据?
数据(data)是事实或观察的结果，是对客观事物的逻辑归纳，是用于表示客观事物的未经加工的原始素材，数据是信息的表现形式和载体，可以是符号，文字，数字，语音，图像，视频等，数据和信息是不可分离的，数据是信息的表达，信息是数据的内涵，数据本身没有任何意义，数据只有对实体行为产生影响才成为信息。在计算机系统中，数据以二进制信息单元0,1形式表示.
数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，
每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。
所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
RDBMS即关系数据库管理系统(Relational Database Management System)的特点：
# 1.数据以表格的形式出现
# 2.每行为各种记录名称
# 3.每列为记录名称所对应的数据域
# 4.许多的行和列组成一张表单
# 5.若干的表单组成database
RDBMS术语
# 数据库: 数据库是一些关联表的集合。.
# 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
# 列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
# 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
# 冗余：存储两倍数据，冗余可以使系统速度更快。(表的规范化程度越高，表与表之间的关系就越多；查询时可能经常需要在多个表之间进行连接查询；而进行连接操作会降低查询速度。例如，学生的信息存储在student表中，院系信息存储在department表中。通过student表中的dept_id字段与department表建立关联关系。如果要查询一个学生所在系的名称，必须从student表中查找学生所在院系的编号（dept_id），然后根据这个编号去department查找系的名称。如果经常需要进行这个操作时，连接查询会浪费很多的时间。因此可以在student表中增加一个冗余字段dept_name，该字段用来存储学生所在院系的名称。这样就不用每次都进行连接操作了。)
# 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
# 外键： 外键用于关联两个表。
# 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
# 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
# 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
数据库分类?
关系型数据库: 库中有表，几个表之间有关联的，拥有共同的列，称之为关系型数据库。
Oracle<1521>,DB2<5000>,Mysql,Mariadb<3306>,SQLServer<1433>
> 非关系型数据库: 通常是以Key-value形式存储的，不支持SQL语句，没有表结构，配置简单，低廉学习成本，能很好作为Mysql中间层:
```python
# 1. 键值存储: Redis<6379> Memcached<11211>,因为相比其他数据存储没有数据结构，又工作在内存中，所以性能很高.
# 2. 列式存储: HBase:
# 3. 文档存储: Documentation , MongoDB<27017>
分布式数据库: 通过分片机制进行数据分布，每个节点都能接收客户端请求<去中心化>，并且持有全局元数据的一部分数据.
# Hadoop(HDFS): 适用于大文件存储，Apache公司的产品，java程序编写
# FastDFS(开源软件): 适用于小文件存储(网盘，短视频，images)，对于高并发有很好的支持.
Mysql是一种关系型数据库管理软件、支持网络访问，默认服务端口3306.
MySQL通信使用mysql协议.因为数据库要保证数据安全，完整，一般使用TCP.
Mysql是最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
# Mysql是开源的，所以你不需要支付额外的费用。
# Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
# MySQL使用标准的SQL数据语言形式。
# Mysql可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
# Mysql对PHP有很好的支持，PHP是目前最流行的Web开发语言。
# MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。
# Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。
安装Mysql5.7
安装rpm包的mysql
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
# 建议用mysql5.7.23版本,以下采用5.7.23版本
yum -y install perl libaio expect
tar xvf mysql-5.7.23-1.el7.x86_64.rpm-bundle.tar
rpm -ivh mysql-community-server-5.7.23-1.el7.x86_64.rpm \
mysql-community-client-5.7.23-1.el7.x86_64.rpm \
mysql-community-common-5.7.23-1.el7.x86_64.rpm \
mysql-community-libs-5.7.23-1.el7.x86_64.rpm
systemctl start mysqld
changepass() {
sed -i '/\[mysqld]/ a skip-grant-tables' /etc/my.cnf
systemctl restart mysqld
mysql <<EOF
update mysql.user set authentication_string='' where user='root' and Host='localhost';
flush privileges;
EOF
sed -i '/skip-grant/d' /etc/my.cnf
systemctl restart mysqld
yum -y install expect ntp
expect <<-EOF
spawn mysqladmin -uroot -p password "ZHOUjian.20"
expect {
"password" { send "\r" }
}
expect eof
EOF
systemctl restart mysqld
}
changepass
# 授权远程登录
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'ZHOUjian.21' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Mysql.tar包方式安装安装
安装源码Mysql
#!/usr/bin/env bash
# Author: ZhouJian
# Mail: 18621048481@163.com
# Time: 2019-9-3
# Describe: CentOS 7 Install Mysql.tar Script # 此处为编译好的tar包，具体编译维护中
Deplay(){
rpm -e mariadb-libs --nodeps
setenforce 0
systemctl stop firewalld
systemctl enable firewalld
sed -i '/^SELINUX=/ s/enforcing/disabled' /etc/ssh/sshd_config
sed -i '/^GSSAPIAu/ s/yes/no/' /etc/ssh/sshd_config
sed -i '/^#UseDNS/ {s/^#//;s/yes/no}' /etc/ssh/sshd_config
id mysql > /dev/null
if [ $? -eq 0 ];then
echo "mysql user exist"
else
groupadd mysql
useradd -M -s /sbin/nologin mysql -g mysql
fi
if [ ! -d /usr/local/mysqld ];then
tar xf mysql-5.7.26-bin.tar.xz -C /usr/local/
chown mysql.mysql /usr/local/mysqld/ -R
fi
echo "export PATH=$PATH:/usr/local/mysqld/mysql/bin" >> /etc/profile
source /etc/profile
cat > /etc/my.cnf <<EOF
[mysqld]
basedir = /usr/local/mysqld/mysql
datadir = /usr/local/mysqld/data
tmpdir = /usr/local/mysqld/tmp
socket = /usr/local/mysqld/tmp/mysql.sock
pid_file = /usr/local/mysqld/tmp/mysqld.pid
log_error = /usr/local/mysqld/log/mysql_error.log
slow_query_log_file = /usr/local/mysqld/log/slow_warn.log
user = mysql
port = 3306
bind-address = 0.0.0.0
character-set-server = utf8
default_storage_engine = InnoDB
EOF
ln -s /usr/local/mysqld/mysql/support-files/mysql.server /usr/bin/mysqldctl
mysqldctl start
ln -s /usr/local/mysqld/tmp/mysql.sock /tmp/mysql.sock
mysqldctl restart
sed -i '/\[mysqld]/ a skip-grant-tables' /etc/my.cnf
mysqldctl restart
mysql <<EOF
update mysql.user set authentication_string='' where user='root' and Host='localhost';
flush privileges;
EOF
sed -i '/skip-grant/d' /etc/my.cnf
mysqldctl restart
yum -y install expect ntp
cat > /etc/ntp.conf << EOF
restrict default nomodify
server 127.127.1.0
fudge 127.127.1.0 stratum 10
EOF
systemctl start ntpd ; systemctl enable ntpd
expect <<-EOF
spawn mysqladmin -uroot -p password "ZHOUjian.20"
expect {
"password" { send "\r" }
}
expect eof
EOF
mysqldctl restart
}
Deplay
安装Yum的mysql
# 使用wget下载yum源安装
wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
# 安装yum源
rpm -ivh mysql57-community-release-el7-8.noarch.rpm
# 安装mysql
yum -y install mysql-server
修改Mysql密码
# 修改Mysql密码下面有三种办法
# 1.刚安装好的mysql，可以从/var/log/mysqld.log获取临时密码
grep "password" /var/log/mysqld.log
[root@mysql ~]# mysql -uroot -p
Enter password:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'ZHOUjian.22';
# 2.mysqladmin -uroot -p password "Baidu.123.com"
Enter password:
# 3.实验环境不知道root密码操作方法如下
sed -i '/\[mysqld]/ a skip-grant-tables' /etc/my.cnf
systemctl restart mysqld
mysql <<EOF
update mysql.user set authentication_string='' where user='root' and Host='localhost';
flush privileges;
EOF
sed -i '/skip-grant/d' /etc/my.cnf
systemctl restart mysqld
mysqladmin -uroot -p password "ZHOUjian.20"
Enter password: # 此处回车一下即可
# 4.mariadb修改密码
use mysql
UPDATE user SET password=password('ZHOUjian.20') WHERE user='root';
MariaDB [mysql]> flush privileges;
# 如果嫌登陆Mysql输入密码麻烦，可以使用以下办法，只需要mysql就可以进入数据库
vim /etc/my.cnf
[client]
password=admin
user=root
systemctl restart mysqld Or mariadb
记得做下面实验之前给Mysql授权一个远程访问用户喔
grant all privileges on *.* to admin@"%" identified by 'ZHOUjian.21' withth grant option;
flush privileges;
SQL介绍:
# SQL语言主要用于存取数据，查询数据，更新数据和管理关系数据库系统，由IBM开发，分为四种类型
# DDL语句 数据库定义语言(Create,Alter,Drop,Declare)
# 用于定义或改变表的结构，数据类型，表之间的连接和约束等初始化工作上，他们大多在建表时使用.
# DML语句 数据库操作语言（Select,Delete,Update,Insert） # 用来对数据库里的数据进行操作的语言.
# DCL语句 数据库控制语言(GRANT,REVOKE,COMMIT,ROLLBACK)
# 用来设置或更改数据库或角色权限的语句，只有sysadmin,dbcreator,db_owner等人员才能执行.
# DQL语句 数据库查询语言（select）
想知道mysql更多用法请看我写的mysql专栏
https://www.cnblogs.com/you-men/tag/Mysql/
Mysql数据类型
Mysql中定义数据字段的类型对你数据库的优化是非常重要的.
Mysql支持多种类型，大致可以分为三类: 数值、日期/时间和字符串类型
数值类型
Mysql支持所有标准SQL数值数据类型
这些类型包括严格数值类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值类型(FLOAT、REAL和DOUBLE PRECISION),关键字INT是INTEGER的同义字，关键字DEC是DECIMAL的同义词.
BLT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BOB表.
作为SQL标准的扩展,Mysql也支持整数类型TINYINT、MEDIUMINT和BIGINT,下面的表显示了需要的每个整数类型的存储和范围
日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性，将在后面描述。
字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求.
mysql-connector库
我们可以使用pip命令来安装mysql-connector
python -m pip install mysql-connector
使用以下代码测试mysql-connector是否安装成功:
import mysql.connector
# 执行以上代码，如果没有报错，表明安装成功
# 注意: 如果你的 MySQL 是 8.0 版本，密码插件验证方式发生了变化，早期版本为 mysql_native_password，
# 8.0 版本为 caching_sha2_password，所以需要做些改变：
# 先修改 my.ini 配置：
[mysqld]
default_authentication_plugin=mysql_native_password
# 然后在mysql下执行以下命令来修改密码:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
创建数据库连接
# 我们可以使用以下代码连接数据库
import mysql.connector
mydb = mysql.connector.connect(
host="121.36.43.123", # 数据库主机地址
user="admin", # 数据库用户名
passwd="ZHOUjian.21" # 数据库密码
# database = "youmen_db" # 此处可以直接连接指定数据库，如果数据库不存在，会输出错误信息
)
print(mydb)
创建数据库
创建数据库使用"create database" 语句，以下创建一个名为youmen_db的数据库:
import mysql.connector
mydb = mysql.connector.connect(
host="116.196.83.113",
user="root",
passwd="ZHOUjian.21"
)
mycursor = mydb.cursor()
mycursor.execute("create database youmen_db")
输出所有数据库列表
import mysql.connector
mydb = mysql.connector.connect(
host="116.196.83.113",
user="root",
passwd="ZHOUjian.21"
)
mycursor = mydb.cursor()
mycursor.execute("show databases")
for x in mycursor:
print(x)
('information_schema',)
('mysql',)
('performance_schema',)
('sys',)
('youmen_db',)
Mysql常用操作
创建数据表
语法
create table table_name(column_name column_type);
创建数据表使用"create table" 语句，创建数据表前，确保数据库已存在,以下创建一个名为sites的数据表:
import mysql.connector
mydb = mysql.connector.connect(
host="116.196.83.113",
user="root",
passwd="ZHOUjian.21",
database="youmen_db"
)
mycursor = mydb.cursor()
mycursor.execute("create table sites(name VARCHAR(255),url VARCHAR(255))")
Example1
#!/usr/bin/env python3
#-*- coding:utf-8 -*-
# 2020/2/12 20:24
import mysql.connector
mydb = mysql.connector.connect(
host='116.196.83.113',
user='root',
passwd='ZHOUjian.20',
database='youmen_db'
)
mycursor=mydb.cursor()
mycursor.execute("create table student(stu_id INT NOT NULL AUTO_INCREMENT,name CHAR(32) NOT NULL ,age INT NOT NULL,register_date DATE,PRIMARY KEY(stu_id))")
如果你不想字段为NULL,可以设置字段的属性为NOT NULL，在操作数据时如果输入该字段的数据为NULL，就会报错.
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加一.
PRIMAPY KEY关键字用于定义列为主键，他可以使用多列来定义主键，列间以逗号分隔.
主键设置
创建表的时候我们一般都会设置一个主键（PRIMARY KEY），我们可以使用 "INT AUTO_INCREMENT PRIMARY KEY" 语句来创建一个主键，主键起始值为 1，逐步递增.
如果我们的表已经创建，我们需要使用 ALTER TABLE 来给表添加主键
# 给sites表增加主键
import mysql.connector
mydb = mysql.connector.connect(
host="116.196.83.113",
user="root",
passwd="ZHOUjian.21",
database="youmen_db"
)
mycursor = mydb.cursor()
mycursor.execute("ALTER TABLE sites ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")
# 如果还未创建sites表，可以直接使用以下代码创建
mycursor.execute("CREATE TABLE sites (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), url VARCHAR(255))")
插入数据
插入数据使用"INSERT INTO"语句:
# 插入数据使用INSERT INTO语句
# 向sites表插入一条记录
import mysql.connector
mydb = mysql.connector.connect(
host="116.196.83.113",
user="root",
passwd="ZHOUjian.21",
database="youmen_db"
)
mycursor = mydb.cursor()
sql = "INSERT INTO sites (name,url) VALUES (%s,%s)"
val = ("youmen_db","https:/www.youmen.com")
mycursor.execute(sql,val)
mydb.commit() # 数据表内容有更新，必须使用到该语句
print(mycursor.rowcount,"记录插入成功.")
1 记录插入成功.
批量插入
# 批量插入使用executemany()方法，该方法的第二个参数是一个元组列表，包含了我们要插入的数据
import mysql.connector
mydb = mysql.connector.connect(
host="116.196.83.113",
user="root",
passwd="ZHOUjian.21",
database="youmen_db"
)
mycursor = mydb.cursor()
sql = "INSERT INTO sites (name,url) VALUES (%s,%s)"
val = [
("youmen_db","https://www.youmen.com"),
("GitHub","https://www.github.com"),
("Taobao","https://www.taobao.com"),
]
mycursor.executemany(sql,val)
mydb.commit() # 数据表内容有更新，必须使用到该语句
print(mycursor.rowcount,"记录插入成功.")
3 记录插入成功.
# 如果想要数据记录插入后，获取该记录的ID,可以使用以下代码:
# print(mycursor.rowcount,"记录插入成功.",mycursor.lastrowid)
# 3 记录插入成功. 5
查询数据
# 查询数据使用SELECT语句
import mysql.connector
mydb = mysql.connector.connect(
host="116.196.83.113",
user="root",
passwd="ZHOUjian.21",
database="youmen_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites")
myresult = mycursor.fetchall() # fetchall()获取所有记录
for i in myresult:
print(i)
('youmen_db', 'https:/www.youmen.com', 1)
('youmen_db', 'https://www.youmen.com', 2)
('GitHub', 'https://www.github.com', 3)
('Taobao', 'https://www.taobao.com', 4)
# 也可以只读取指定字段数据
# 将mycursor.execute("SELECT * FROM sites")当中的* 换成你需要查看的字段即可.如果有多个字段,','隔开
如果我们只想读取一条数据,可以使用fetchone()方法:
# 只查询一条数据
import mysql.connector
mydb = mysql.connector.connect(
host="116.196.83.113",
user="root",
passwd="ZHOUjian.21",
database="youmen_db"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM sites")
myresult = mycursor.fetchone() # fetchall()获取所有记录
print(myresult)
('youmen_db', 'https:/www.youmen.com', 1)
where条件语句
如果我们要读取指定条件的数据，可以使用where语句:
# 读取name字段为youmen_db的记录：
import mysql.connector
mydb = mysql.connector.connect(
host="116.196.83.113",
user="root",
passwd="ZHOUjian.21",
database="youmen_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites WHERE name = 'youmen_db'"
# 此处条件查找可以使用通配符
# `sql = "SELECT * FROM sites WHERE url LIKE '%men%'"`
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
('youmen_db', 'https:/www.youmen.com', 1)
('youmen_db', 'https://www.youmen.com', 2)
('youmen_db', 'https://www.youmen.com', 5)
为了防止数据库查询发生SQL注入的攻击，我们可以使用%s占位符来转义查询的条件:
import mysql.connector
mydb = mysql.connector.connect(
host="116.196.83.113",
user="root",
passwd="ZHOUjian.21",
database="youmen_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites WHERE name = %s"
na = ("youmen_db",)
mycursor.execute(sql,na)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
('youmen_db', 'https:/www.youmen.com', 1)
('youmen_db', 'https://www.youmen.com', 2)
('youmen_db', 'https://www.youmen.com', 5)
排序
查询结果排序可以使用ORDER BY语句，默认的排序方式是升序，关键字是ASC,如果要设置降序排序，可以设置关键字DESC.
# 按name字段字母的升序排序
import mysql.connector
mydb = mysql.connector.connect(
host="116.196.83.113",
user="root",
passwd="ZHOUjian.21",
database="youmen_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites ORDER BY name"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for i in myresult:
print(i)
('GitHub', 'https://www.github.com', 3)
('GitHub', 'https://www.github.com', 6)
('Taobao', 'https://www.taobao.com', 4)
('Taobao', 'https://www.taobao.com', 7)
('youmen_db', 'https:/www.youmen.com', 1)
降序排序
# 按name字段字母的升序排序
import mysql.connector
mydb = mysql.connector.connect(
host="116.196.83.113",
user="root",
passwd="ZHOUjian.21",
database="youmen_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites ORDER BY name DESC"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for i in myresult:
print(i)
('youmen_db', 'https:/www.youmen.com', 1)
('youmen_db', 'https://www.youmen.com', 2)
('youmen_db', 'https://www.youmen.com', 5)
('Taobao', 'https://www.taobao.com', 4)
('Taobao', 'https://www.taobao.com', 7)
('GitHub', 'https://www.github.com', 3)
Limit
我们要设置查询的数据量，可以通过"LIMIT"语句来指定
import mysql.connector
mydb = mysql.connector.connect(
host="116.196.83.113",
user="root",
passwd="ZHOUjian.21",
database="youmen_db"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM sites LIMIT 3"
# 从第二条开始读取前3条记录
# sql = "SELECT * FROM sites LIMIT 3 OFFSET 1"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for i in myresult:
print(i)
('youmen_db', 'https:/www.youmen.com', 1)
('youmen_db', 'https://www.youmen.com', 2)
('GitHub', 'https://www.github.com', 3)
删除记录
删除记录使用"DELETE FROM"语句
# 删除name为youmen_db的记录
import mysql.connector
mydb = mysql.connector.connect(
host="116.196.83.113",
user="root",
passwd="ZHOUjian.21",
database="youmen_db"
)
mycursor = mydb.cursor()
sql = "DELETE FROM sites WHERE name = 'youmen_db'"
# 为了防止数据库查询发生SQL注入的攻击，我们可以使用%s占位符来转义删除语句的条件.
# sql = "DELETE FROM sites WHERE name = %s"
# na = ("youmen_db",)
# mycursor.execute(sql,na)
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount,"条记录删除")
3 条记录删除
# 慎重使用删除语句，删除语句要确保指定了WHERE条件语句，否则会导致整表数据被删除.
更新表数据
数据库更新使用"UPDATE"语句
import mysql.connector
mydb = mysql.connector.connect(
host="116.196.83.113",
user="root",
passwd="ZHOUjian.21",
database="youmen_db"
)
mycursor = mydb.cursor()
sql = "UPDATE sites SET name = '%ao%' WHERE name = 'ZHOU'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, " 条记录被修改")
# UPDATE语句确保了指定WHERE条件语句，否则会导致整表数据被更新
# 为了防止数据库查询发生SQL注入情况，我们可以使用%s占位符来转义更新语句的条件:
import mysql.connector
mydb = mysql.connector.connect(
host="116.196.83.113",
user="root",
passwd="ZHOUjian.21",
database="youmen_db"
)
mycursor = mydb.cursor()
sql = "UPDATE sites SET name = %s WHERE name = %s"
val = ("GitHub","ZHOU")
mycursor.execute(sql,val)
mydb.commit()
print(mycursor.rowcount, " 条记录被修改")
6.11 删除表
删除表使用 "DROP TABLE" 语句， IF EXISTS 关键字是用于判断表是否存在，只有在存在的情况才删除
import mysql.connector
mydb = mysql.connector.connect(
host="116.196.83.113",
user="root",
passwd="ZHOUjian.21",
database="youmen_db"
)
mycursor = mydb.cursor()
sql = 'DROP TABLE IF EXISTS sites' # 删除数据表sites
mycursor.execute(sql)
Python3之PyMySQL
什么是PyMySQL?
PyMySQL是在Python3.x版本中用于连接MySQL服务的一个库，Python2则使用mysqldb.
PyMySQL遵循Python数据库API v2.0规范，并包含了pure-Python Mysql客户端库
PyMySQL安装
在使用PyMySQL之前，我们需要确保 PyMySQL 已安装。
PyMySQL 下载地址：https://github.com/PyMySQL/PyMySQL。
如果还未安装，我们可以使用以下命令安装最新版的 PyMySQL：
pip3 install PyMySQL
如果你的系统不支持pip命令，可以使用以下方式安装:
- 使用git命令下载安装包安装（也可以手动下载）
$ git clone https://github.com/PyMySQL/PyMySQL
$ cd PyMySQL/
$ python3 setup.py install
- 如果需要指定版本号，可以使用curl命令来安装**
$ # X.X 为 PyMySQL 的版本号
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python3 setup.py install
$ # 现在你可以删除 PyMySQL* 目录
# 安装的过程中可能会出现"ImportError: No module named setuptools"的错误提示，
# 意思是你没有安装setuptools，你可以访问
# [https://pypi.python.org/pypi/setuptools]找到各个系统的安装方法。
# Linux系统安装实例:
$ wget https://bootstrap.pypa.io/ez_setup.py
$ python3 ez_setup.py
数据库连接
# 连接数据库前，请先确认以下事项:
# 在你的机上安装了Python MySQLdb模块:
Example1: 连接数据库
import pymysql
# 打开数据库连接
db = pymysql.connect("116.196.83.113","root","ZHOUjian.21")
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
# 使用execute() 方法执行SQLc查询
cursor.execute("SELECT VERSION()")
# 使用fetchone()方法获取单条数据.
data = cursor.fetchone()
print("Database version: %s" % data)
# 关闭数据库连接
db.close()
Database version: 5.7.23
2.1 创建数据库表
如果数据库连接存在我们可以使用excute()方法为数据库创建库，表.
创建数据库youmen_db
import pymysql
# 打开数据库连接
db = pymysql.connect("116.196.83.113","root","ZHOUjian.21")
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
# 使用execute() 方法执行SQLc查询
cursor.execute("CREATE DATABASE youmen_db")
创建数据库表
import pymysql
# 打开数据库连接
db = pymysql.connect("116.196.83.113","root","ZHOUjian.21","youmen_db")
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
# 使用execute() 方法执行SQL,如果存在则删除
cursor.execute("DROP TABLE IF EXISTS NOTES")
# 使用预处理语句创建表
sql = """CREATE TABLE NOTES (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT )"""
cursor.execute(sql)
# 关闭数据库连接
db.close()
2.2 数据库插入操作
以下实例使用执行SQL INSERT语句向表NOTES插入记录:
import pymysql
# 打开数据库连接
db = pymysql.connect("116.196.83.113","root","ZHOUjian.21","youmen_db")
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL 插入语句
sql = """INSERT INTO NOTES(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('Mac', 'DELL', 20, 'M', 2000)"""
try:
# 执行sql语句
cursor.execute(sql)
# 提交到数据库执行
db.commit()
except:
# 如果发生错误则回滚
db.rollback()
# 关闭数据库连接
db.close()
Example2
以上例子可以写成如下格式:
import pymysql
# 打开数据库连接
db = pymysql.connect("116.196.83.113","root","ZHOUjian.21","youmen_db")
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL 插入语句
sql = "INSERT INTO NOTES(FIRST_NAME,\
LAST_NAME,AGE,SEX,INCOME) \
VALUES ('%s','%s',%s,'%s',%s)" % \
('air','level',21,'M',1999)
try:
# 执行SQL语句
cursor.execute(sql)
# 执行sql语句
db.commit()
except:
# 发生错误时回滚
db.rollback()
# 关闭数据库连接
db.close()
以下代码使用变量向SQL中传递参数
..................................
user_id = "test123"
password = "password"
con.execute('insert into Login values( %s, %s)' % \
(user_id, password))
..................................
2.3 数据库查询操作
Python查询MySQL使用fetchone()方法获取单条数据，使用fetchall()方法获取多条数据.
- fetchone(): 该方法获取下一个查询结果集，结果集是一个对象.
- fetchall(): 接受全部的返回结果行.
- rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。
Example3:
查询NOTES表中salary(工资)字段大于1000的所有数据
import pymysql
# 打开数据库连接
db = pymysql.connect("116.196.83.113","root","ZHOUjian.21","youmen_db")
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE \
WHERE INCOME > %s" % (1000)
try:
# 执行SQL语句
cursor.execute(sql)
# 获取所有记录列表
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4]
# 打印结果
print ("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \
(fname, lname, age, sex, income ))
except:
print ("Error: unable to fetch data")
# 关闭数据库连接
db.close()
# 脚本执行结果如下:
fname=Mac, lname=Mohan, age=20, sex=M, income=2000
2.4 数据库更新操作
更新操作用于更新数据表的数据，以下实例将NOTES表中SEX为M的字段递增1.
Example4
import pymysql
# 打开数据库连接
db = pymysql.connect("116.196.83.113","root","ZHOUjian.21","youmen_db")
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL 更新语句
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
# 执行SQL语句
cursor.execute(sql)
# 提交到数据库执行
db.commit()
except:
# 发生错误时回滚
db.rollback()
# 关闭数据库连接
db.close()
2.5 删除操作
删除操作用于删除数据表中的数据，以下实例演示了删除数据表 EMPLOYEE 中 AGE 大于 20 的所有数据
import pymysql
# 打开数据库连接
db = pymysql.connect("116.196.83.113","root","ZHOUjian.21","youmen_db")
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL 删除语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
# 执行SQL语句
cursor.execute(sql)
# 提交修改
db.commit()
except:
# 发生错误时回滚
db.rollback()
# 关闭连接
db.close()
2.6 执行事务
事务机制可以确保数据一致性
事务应该具有4个属性: 原子性，一致性，隔离性，持久性，这四个属性通常称为ACID特性
**d
原子性(atomicity) 一个特性是一个不可分割的工作单位,事务中包括的诸操作要么都做，要么都不做
一致性(consistency) 事务必须使数据库从一个一致性状态到另一个一致性状态，一致性与原子性是密切相关的
隔离性(isotation) 一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰.
持久性(durability). 持续性也称永久性(permanence),指一个事务一旦提交，他对数据库中数据的改变就应该是永久性的.接下来的其他操作或故障不应该对其有任何影响.
Python DB API2.0的事务提供了两个方法commit或rollback。
import pymysql
# 打开数据库连接
db = pymysql.connect("116.196.83.113","root","ZHOUjian.21","youmen_db")
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL删除记录语句
sql = "DELETE FROM NOTES WHERE AGE > %s" % (20)
try:
# 执行SQL语句
cursor.execute(sql)
# 向数据库提交
db.commit()
except:
# 发生错误时回滚
db.rollback()
对于支持事务的数据库， 在Python数据库编程中，当游标建立之时，就自动开始了一个隐形的数据库事务。
commit()方法游标的所有更新操作，rollback（）方法回滚当前游标的所有操作。每一个方法都开始了一个新的事务。
2.7 错误处理
DB API中定义了一些数据库操作的错误及异常，下面列出了这些错误和异常
| 异常 | 描述 |
|---|---|
| Warning | 当有严重警告时触发，例如插入数据是被截断等等。必须是 StandardError 的子类。 |
| Error | 警告以外所有其他错误类。必须是 StandardError 的子类。 |
| InterfaceError | 当有数据库接口模块本身的错误（而不是数据库的错误）发生时触发。 必须是Error的子类。 |
| DatabaseError | 和数据库有关的错误发生时触发。 必须是Error的子类。 |
| DataError | 当有数据处理时的错误发生时触发，例如：除零错误，数据超范围等等。 必须是DatabaseError的子类。 |
| OperationalError | 指非用户控制的，而是操作数据库时发生的错误。例如：连接意外断开、 数据库名未找到、事务处理失败、内存分配错误等等操作数据库是发生的错误。 必须是DatabaseError的子类。 |
| IntegrityError | 完整性相关的错误，例如外键检查失败等。必须是DatabaseError子类。 |
| InternalError | 数据库的内部错误，例如游标（cursor）失效了、事务同步失败等等。 必须是DatabaseError子类。 |
| ProgrammingError | 程序错误，例如数据表（table）没找到或已存在、SQL语句语法错误、 参数数量错误等等。必须是DatabaseError的子类。 |
| NotSupportedError | 不支持错误，指使用了数据库不支持的函数或API等。例如在连接对象上 使用.rollback()函数，然而数据库并不支持事务或者事务已关闭。 必须是DatabaseError的子类。 |
2.8 with简化数据库操作
import pymysql
class DB():
def __init__(self, host='localhost', port=3306, db='', user='root', passwd='root', charset='utf8'):
# 建立连接
self.conn = pymysql.connect(host=host, port=port, db=db, user=user, passwd=passwd, charset=charset)
# 创建游标，操作设置为字典类型
self.cur = self.conn.cursor(cursor = pymysql.cursors.DictCursor)
def __enter__(self):
# 返回游标
return self.cur
def __exit__(self, exc_type, exc_val, exc_tb):
# 提交数据库并执行
self.conn.commit()
# 关闭游标
self.cur.close()
# 关闭数据库连接
self.conn.close()
if __name__ == '__main__':
with DB(host='192.168.68.129',user='root',passwd='zhumoran',db='text3') as db:
db.execute('select * from course')
print(db)
for i in db:
print(i)
1
