【Python之路Day13】网络篇之MySQL、ORM框架
MySQL
一. 概述
什么是数据库？
数据库可以理解成保存有组织数据的容器（通常是一个文件或一组文件），很多时候我们所说的数据库就是我们使用的数据库软件（专业点就是数据库管理系统DBMS），我们并不是直接访问数据库而是使用数据库软件，它为我们访问数据库。
什么是数据表?
数据表就是我们要存放数据的东西，就像一个图书馆里的书架一样，并不是随便堆到里面就完事了，有人要借书，你需要根据目录(数据库里为索引)很快的找到书在哪排那层哪个位置，从而快速的取出要借的书。
什么是RDBMS？
RDBMS即关系数据库管理系统(Relational Database Management System)，是将数据组织为相关的行和列的系统，而管理关系数据库的计算机软件就是关系数据库管理系统.
有哪些RDBMS？
- DB2， IBM公司的产品，起源于世界上第一个关系型数据库System R和System R*。站点：http://www.ibm.com/analytics/us/en/technology/db2/
- Oracle，米国甲骨文公司生产商用产品，它是在数据库领域一直处于领先地位的产品。站点：http://www.oracle.com/cn/database/overview/index.html
- PostgreSQL，Berkeley开发，完全由社区驱动的开源项目，由全世界超过1000名贡献者所维护。PostgreSQL标榜自己是世界上最先进的开源数据库。不过也确实很牛逼，选择什么主要看怎么取舍了。InfoQ这里有一片文章是MySQL和PostgreSQL的一个对比和分析，点我. PostgreSQL官方站点：https://www.postgresql.org/。
- MySQL，开源的一款RDBMS，现在米国甲骨文公司旗下，分社区版和企业版，一般我们使用的都是社区版。官方站点：http://www.mysql.com/
- SQL Server，Microsoft的一款商用RDBMS，官方站点：https://www.microsoft.com/zh-cn/server-cloud/products/sql-server/
- Percona：MySQL优化版，性能更强。官方站点：https://www.percona.com
- Sybase，Infomix，MariaDB等等...
RDBMS的相关术语？
- 数据库（Database）：数据库是带有相关数据的表的集合。
- 表（Table）：表是带有数据的矩阵。数据库中的表就像一种简单的电子表格。
- 列（Column）：每一列（数据元素）都包含着同种类型的数据，比如邮编。
- 行（Row）：行（又被称为元组、项或记录）是一组相关数据，比如有关订阅量的数据。
- 冗余（Redundancy）：存储两次数据，以便使系统更快速。
- 主键（Primary Key）：主键是唯一的。同一张表中不允许出现同样两个键值。一个键值只对应着一行。
- 外键（Foreign Key）：用于连接两张表。
- 复合键（Compound Key）：复合键（又称组合键）是一种由多列组成的键，因为一列并不足以确定唯一性。
- 索引（Index）：它在数据库中的作用就像书后的索引一样。
- 引用完整性（Referential Integrity）：用来确保外键一直指向已存在的一行。
什么是SQL?
SQL：Structure Query Language，结构化查询语言，是一种专门用来与数据库通信的语言。遵循的标准主要有：
- SQL-86
- SQL-89
- SQL-92
- SQL-03
- SQL-08
SQL语句分类
Data defination language（DDL）：
- CREATE --在数据库中创建对象
- ALTER ---修改数据库结构
- DROP ---删除对象
- RENAME --- 重命名对象
Data Manipulation language（DML）
- SELECT --- 获取数据
- INSERT --- 向表中插入数据
- UPDATE --- 更新表中已有数据
- DELETE --- 删除表中的数据
Data Control Language(DCL)
- GRANT --- 赋予一个用户对数据库或者数据表格等制定权限
- REVOKE --- 删除一个用户对数据库或者表哥的制定权限
Transaction Control Language（TCL）
- COMMIT --- 保存数据操作
- SAVEPOINT --- 为方便Roolback标记一个事务点
- ROOLBACK --- 从最后一次COMMIT中恢复到提交前的状态
二. 安装MySQL
安装MySQL有好多种方式：
- 使用平台包管理器安装， 如apt-get ，yum等
- 下载源码编译安装
- 下载通用二进制包安装
- ...
包管理器安装：
#debian/Ubuntu apt-get install mysql-server -y #RHEL yum install mysql-server -y ...
源码安装(RHEL平台，其他平台大同小异)，此处版本是5.5.28，可以下载最新的，大同小异。
cmake的重要特性之一是其独立于源码（out-of-source）的编译功能，即编译工作可以在另一个指定的目录中而非源码目录中进行，这可以保证源码目录不受任何一次编译的影响，因此在同一个源码树上可以进行多次不同的编译，如针对不同平台编译。 1.安装跨平台编译器,cmake： tar xv cmake-3.2.1.tar.gz cd cmake-3.2.1 ./bootstrap make make install 2.编译安装mysql-5.5.28 1.使用cmake编译mysql-5.5 cmake指定编译选项的方式不同于make，其实现方式对比如下： ./configure 相当于 cmake . ./configura --help 相当于 cmake . -LH or ccmake . 指定安装文件的安装路径时常用的选项： -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DSYSCONFDIR=/etc 默认编译的存储引擎包括：csv、myisam、myisammrg和heap。如要安装其他存储引擎，可以使用类似如下的选项： -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 若要明确指定不编译某存储引擎，可是使用类似如下的选项: -DWITHOUT_<ENGINE>_STORAGE_ENGINE=1 如： -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITHOUT_FEDERATED_STORAGE_ENGINE=1 如要编译其他功能，如SSL等，则可以使用如下选项来实现编译时使用某库或不使用某库： -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 其他常用的选项： -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DENABLED_LOCAL_INFILE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_DEBUG=0 -DENABLE_PROFILING=1 安装Mysql： 创建用户： groupadd -r mysql useradd -r -g mysql -M -s /sbin/nologin mysql 编译： cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ -DMYSQL_DATADIR=/mydata/data -DSYSCONFDIR=/etc \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_ARCHIVE_STORAGE_ENGINE=1 \ -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DWITH_READLINE=1 \ -DWITH_SSL=system \ -DWITH_ZLIB=system \ -DWITH_LIBWRAP=0 \ -DMYSQL_UNIX_ADDR=/tmp/mysql.sock \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci make make install 创建数据目录: mkdir -pv /mydata/data && chown mysql /mydata/data 初始化数据库: cd /usr/local/mysql chown mysql:mysql -R /usr/local/mysql scripts/mysql_install_db --user=mysql --datadir=/mydata/data 拷贝文件: cp support-files/my-huge.cnf /etc/my.cnf cp support-files/mysql.server /etc/init.d/mysqld 添加环境变量： vi /etc/profile.d/mysql.sh export PATH=$PATH:/usr/local/mysql/bin source /etc/profile.d/mysql.sh 添加man手册： vi /etc/man.config 添加： MANPATH /usr/local/mysql/man 添加库文件到系统： vi /etc/ld.so.conf.d/mysql.conf 添加： /usr/local/mysql/lib 执行： ldconfig -v /etc/ld.so.conf.d/mysql.conf 添加到自启动： chkconfig --add mysqld
通用二进制包安装：
===1、准备数据存放的文件系统=== 这里假设其数据盘的目录为/data/mysql，而后需要创建/data/mysql目录做为mysql数据的存放目录。 ===2、新建用户以安全方式运行进程：=== # groupadd -r mysql # useradd -g mysql -r -s /sbin/nologin -M mysql # mkdir /data/mysql # chown -R mysql:mysql /data/mysql ===3、安装并初始化mysql-5.6.26=== # cd /usr/local/src && tar xf mysql-5.6.26-linux-glibc2.5-x86_64.tar.gz -C /usr/local && cd /usr/local/ # mv mysql-5.6.26-linux-glibc2.5-x86_64 mysql && cd mysql # chown -R mysql:mysql . # yum install libaio-devel -y #安装依赖包 # scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql # chown -R root . ===4、为mysql提供主配置文件：=== # vi /etc/my.cnf #贴入下面内容： # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL. [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M #sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES basedir = /usr/local/mysql datadir = /data/mysql socket = /data/mysql/mysql.sock character-set-server=utf8mb4 collation-server=utf8mb4_general_ci back_log = 600 max_connections = 3000 max_connect_errors = 500 #table_cache = 614 # # #external-locking = FALSE max_allowed_packet = 32M # sort_buffer_size = 2M join_buffer_size = 2M # thread_cache_size = 300 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 4M #query_cache_min_res_unit = 2k default-storage-engine = InnoDB # thread_stack = 192K # transaction_isolation = READ-COMMITTED # tmp_table_size = 256M max_heap_table_size = 256M long_query_time = 1 slow_query_log = 1 slow_query_log_file=/data/mysql/slow-log.log #log-slow-queries=/data/mysql/slow-log.log binlog_cache_size = 4M max_binlog_cache_size = 8M max_binlog_size = 100M expire_logs_days = 10 key_buffer_size = 2048M read_buffer_size = 1M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M #myisam_sort_buffer_size = 128M #myisam_max_sort_file_size = 10G #myisam_max_extra_sort_file_size = 10G #myisam_repair_threads = 1 #myisam_recover skip-name-resolve lower_case_table_names = 1 server-id = 1 innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 2G innodb_file_io_threads = 4 innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 16M innodb_log_file_size = 128M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 1 [mysql] default-character-set = utf8mb4 [client] default-character-set = utf8mb4 socket = /data/mysql/mysql.sock ===5、为mysql提供sysv服务脚本：=== # cd /usr/local/mysql # cp support-files/mysql.server /etc/rc.d/init.d/mysqld ===6. 添加至服务列表：=== # chkconfig --add mysqld && chkconfig mysqld on 而后就可以启动服务测试使用了。 为了使用mysql的安装符合系统使用规范，并将其开发组件导出给系统使用，这里还需要进行如下步骤： ===7、输出mysql的man手册至man命令的查找路径：=== # yum install man -y # sed -i '48 s@^@MANPATH /usr/local/mysql/man @' /etc/man.config ===8、输出mysql的头文件至系统头文件路径/usr/include：=== 这可以通过简单的创建链接实现： # ln -sv /usr/local/mysql/include /usr/include/mysql ===9、输出mysql的库文件给系统库查找路径：=== # echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf 而后让系统重新载入系统库： # ldconfig -v ===10、修改PATH环境变量，让系统可以直接使用mysql的相关命令。具体实现过程这里不再给出。=== # vim /etc/profile.d/mysql.sh export PATH=/usr/local/mysql/bin:$PATH # . /etc/profile.d/mysql.sh ===11. 启动MySQL服务：=== # service mysqld start
想要使用MySQL来存储并操作数据，则需要做几件事情：
a. 安装MySQL服务端
b. 安装MySQL客户端 (安装完服务器端，默认都带有客户端工具，安装过程略...)
b. 【客户端】连接【服务端】
c. 【客户端】发送命令给【服务端MySQL】服务的接受命令并执行相应操作(增删改查等)
三. 数据库操作：
1. 显示所有数据库：
SHOW DATABASES; #默认数据库： # mysql - 用户权限相关数据 # test - 用于用户测试数据 # information_schema - MySQL本身架构相关数据
2. 使用/进入数据库
USE db_name;
3. 显示库的的所有表
SHOW TABLES;
4. 授权管理
用户管理：
创建用户 create user '用户名'@'IP地址' identified by '密码'; 删除用户 drop user '用户名'@'IP地址'; 修改用户 rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';; 修改密码 set password for '用户名'@'IP地址' = Password('新密码') PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）
授权管理：
show grants for '用户'@'IP地址' -- 查看权限 grant 权限 on 数据库.表 to '用户'@'IP地址' -- 授权 revoke 权限 on 数据库.表 from '用户'@'IP地址' -- 取消权限
all privileges 除grant外的所有权限 select 仅查权限 select,insert 查和插入权限 ... usage 无访问权限 alter 使用alter table alter routine 使用alter procedure和drop procedure create 使用create table create routine 使用create procedure create temporary tables 使用create temporary tables create user 使用create user、drop user、rename user和revoke all privileges create view 使用create view delete 使用delete drop 使用drop table execute 使用call和存储过程 file 使用select into outfile 和 load data infile grant option 使用grant 和 revoke index 使用index insert 使用insert lock tables 使用lock table process 使用show full processlist select 使用select show databases 使用show databases show view 使用show view update 使用update reload 使用flush shutdown 使用mysqladmin shutdown(关闭MySQL) super 使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆 replication client 服务器位置的访问 replication slave 由复制从属使用
对于目标数据库以及内部其他： 数据库名.* 数据库中的所有 数据库名.表 指定数据库中的某张表 数据库名.存储过程 指定数据库中的存储过程 *.* 所有数据库
用户名@IP地址 用户只能在改IP下才能访问 用户名@192.168.1.% 用户只能在改IP段下才能访问(通配符%表示任意) 用户名@% 用户可以再任意IP下访问(默认IP地址为%)
grant all privileges on db1.tb1 TO '用户名'@'IP' grant select on db1.* TO '用户名'@'IP' grant select,insert on *.* TO '用户名'@'IP' revoke select on db1.tb1 from '用户名'@'IP'
四. 表操作
1、创建表
create table 表名(
列名 类型 是否可以为空，
列名 类型 是否可以为空
)
是否可空，null表示空，非字符串 not null - 不可空 null - 可空
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值 create table tb1( nid int not null defalut 2, num int not null )
自增，如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列） create table tb1( nid int not null auto_increment primary key, num int null ) 或 create table tb1( nid int not null auto_increment, num int null, index(nid) ) #注意：1、对于自增列，必须是索引（含主键）。 2、对于自增可以设置步长和起始值 show session variables like 'auto_inc%'; set session auto_increment_increment=2; set session auto_increment_offset=10; shwo global variables like 'auto_inc%'; set global auto_increment_increment=2; set global auto_increment_offset=10;
主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。 create table tb1( nid int not null auto_increment primary key, num int null ) 或 create table tb1( nid int not null, num int not null, primary key(nid,num) )
外键，一个特殊的索引，只能是指定内容 creat table color( nid int not null primary key, name char(16) not null ) create table fruit( nid int not null primary key, smt char(32) null , color_id int not null, constraint fk_cc foreign key (color_id) references color(nid) )
2、删除表
drop table tb_name;
3、清空表
delete from tb_name; truncate table tb_name; #清空表推荐使用truncate
4、修改表
#添加列： ALTER TABLE 表名 ADD 列名 类型; #删除列： ALTER TABLE 表名 DROP COLUMN 列名; #修改列： ALTER TABLE 表名 MODIFY COLUMN 列名 类型; -- 类型 ALTER TABLE 表名 CHANGE 原列名 新列名 类型; -- 列名，类型 #添加主键： ALTER TABLE 表名 ADD PRIMARY KEY(列名); #删除主键： ALTER TABLE 表名 DROP PRIMARY KEY; ALTER TABLE 表名 MODIFY 列名 INT, DRIP PRIMARY KEY; #添加外键： ALTER TABLE 从表 ADD CONSTRAINT 外键名称（形如：FK_从表_主表） FOREIGN KEY 从表(外键字段) REFERENCES 主表(主键字段); #删除外键： ALTER TABLE 表名 DROP FOREIGN KEY 外键名称; #修改默认值： ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; #删除默认值： ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
5、基本数据类型
数值型； 精确数值： tinyint a very small integer 1byte smallint a small integer 2byte mediumint a medium-sized integer 3byte int a standard integer 4byte bigint a large integer 8byte decimal a fixed-point number 近似数值： float a single-precision floating-point number 4byte double a double-precision floating-point number 8byte bit a bit field real auto_increment：自增，设定之后必须满足以下条件: 非空 无符号 一定要创建索引 通过查看LAST_INSERT_ID内置函数查看上一次自增的值；mysql> SELECT LAST_INSERT_ID(); 前提： 无符号整型 必须是primary key或者 Unique key 必须是Not Null unsigned：无符号 字符型； 定长字符 char(#) 最大255个字符 binary(#)（区分大小写的定长字符） 变长字符 varchar(#)，如varchar(3)：最多只能存储3个字符，但是占位4个字符，因为多一个字符串的结束符； 每一个2**8需要一个结束符，也就是如果在255个以内需要1个结束符，如果超过需要2个；最大可以存储65535个字符 tinytext 255 text 65535，上面索引不能索引整个字段，不区分大小写 mediumtext 16777215 longtext 4294967295个 以上五种都不区分大小写； varbinary(#)（区分大小写的可变长字符） blob（binary large object ）文本大对象，区分大小写 Mysql内置数值类型: enum（枚举型）, set(集合) 修饰符： NOT NULL NULL DEFAULT CHARACTER SET 字符集 COLLATION 日期型； date, time, datetime, timestamp（从linux元年开始经过的秒数）, year
五. 基本操作
这里主要就是一些DML语句
1、增(INSERT)
insert into 表 (列名,列名...) values (值,值,值...); insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...); insert into 表 (列名,列名...) select (列名,列名...) from 表;
2. 删(DELETE)
delete from 表 delete from 表 where id＝1 and name＝'sam'
3. 改(UPDATE)
update 表 set name ＝ 'SAM' where id>1
4. 查(SELECT)
select * from 表; select * from 表 where id > 1; select nid,name,gender as gg from 表 where id > 1;
5、其他
1、条件 select * from 表 where id > 1 and name != 'jerry' and num = 12; select * from 表 where id between 5 and 16; select * from 表 where id in (11,22,33) select * from 表 where id not in (11,22,33) select * from 表 where id in (select nid from 表) 2、通配符 select * from 表 where name like 'jerry%' - jerry开头的所有（多个字符串） select * from 表 where name like 'jerr_' - jerr开头的所有（一个字符） 3、限制 select * from 表 limit 5; - 前5行 select * from 表 limit 4,5; - 从第4行开始的5行 select * from 表 limit 5 offset 4 - 从第4行开始的5行 4、排序 select * from 表 order by 列 asc - 根据 “列” 从小到大排列 select * from 表 order by 列 desc - 根据 “列” 从大到小排列 select * from 表 order by 列1 desc,列2 asc - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序 5、分组 select num from 表 group by num select num,nid from 表 group by num,nid select num,nid from 表 where nid > 10 group by num,nid order nid desc select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid select num from 表 group by num having max(id) > 10 特别的：group by 必须在where之后，order by之前 6、连表 无对应关系则不显示 select A.num, A.name, B.name from A,B Where A.nid = B.nid 无对应关系则不显示 select A.num, A.name, B.name from A inner join B on A.nid = B.nid A表所有显示，如果B中无对应关系，则值为null select A.num, A.name, B.name from A left join B on A.nid = B.nid B表所有显示，如果B中无对应关系，则值为null select A.num, A.name, B.name from A right join B on A.nid = B.nid 7、组合 组合，自动处理重合 select nickname from A union select name from B 组合，不处理重合 select nickname from A union all select name from B
FROM子句：表示要查询的关系 表、多个表、其他SELECT语句； WHERE子句：布尔关系表达式 = 、> 、< 、>= 、<= 逻辑关系： AND OR NOT BETWEEN ... AND ... 指定一个运算符要搜索的闭区间 LIKE '' %:任意长度任意字符 _：任意单个字符 #: 0~9之间的单一数字 . [字符列表] 在字符列表中的任一值 . [! 字符列表] 不在字符列表中的任一值 . - ：指定字符范围，两边的值分别为其上限； RLIKE或者REGXP 支持正则表达式匹配 IN 离散取值的时候指定一个IN，在其中选值； 用于匹配列表中的任何一个值，IN子句可以代替用OR子句连接的一连串的条件。 IN () IS NULL IS NOT NULL ORDER BY field_name {ASC|DESC} 查询结果排序 但是数据量太大的话，非常消耗系统资源，因此应该将数据存入磁盘的时候就排好序存放； AS 取个别名可以给字段起个别名，也可以对表起个别名： SELECT name AS New_name FROM tb; LIMIT子句： LIMIT [offset,]Count Offset：偏移几个 count：取几个 如果不指定count，取值offset个数 如果指定count，偏移offset，取值count个
pymysql
pymysql是Python中操作MySQL的模块，使用方法和MySQLDB几乎一样。
一. 安装
pip3 install pymysql #源码安装 https://github.com/PyMySQL/PyMySQL python3 setup.py install
二. 使用
1. 执行SQL语句
#!/usr/bin/env python3 # -*- coding: utf-8 -*- # Author: DBQ(Du Baoqiang) import pymysql #导入模块 #创建连接 conn = pymysql.connect( host='172.16.30.162', #主机IP port=3306, #端口 user='tom', #连接数据库用户 password='tom123', #连接密码 db='db1' #连接的数据库名称 ) #创建游标 cursor = conn.cursor() #执行SQL,并返回受影响的行数 effect_row = cursor.execute("UPDATE tb1 SET host='1.1.1.1'") # 执行SQL，并返回受影响行数 #effect_row = cursor.execute("UPDATE tb1 SET host='1.1.1.2' WHERE id > %s",(1,)) # 执行SQL，并返回受影响行数 #effect_row = cursor.executemany("INSERT INTO tb1(host) VALUES(%s),(%s)", [("1.1.1.11",1),("1.1.1.11",1)]) print(effect_row) # 提交，不然无法保存新建或者修改的数据 conn.commit() # 关闭游标 cursor.close() # 关闭连接 conn.close()
2、获取新创建数据自增ID
#!/usr/bin/env python3 # -*- coding: utf-8 -*- # Author: DBQ(Du Baoqiang) import pymysql #导入模块 #创建连接 conn = pymysql.connect( host='172.16.30.162', #主机IP port=3306, #端口 user='tom', #连接数据库用户 password='tom123', #连接密码 db='db1' #连接的数据库名称 ) # #创建游标 cursor = conn.cursor() cursor.executemany("INSERT INTO tb1(host,id) VALUES(%s,%s)", [("1.1.1.30",12),("1.1.1.30",13)]) conn.commit() cursor.close() conn.close() new_id = cursor.lastrowid print(new_id)
3、获取查询数据
#!/usr/bin/env python3 # -*- coding: utf-8 -*- # Author: DBQ(Du Baoqiang) import pymysql #导入模块 #创建连接 conn = pymysql.connect( host='172.16.30.162', #主机IP port=3306, #端口 user='tom', #连接数据库用户 password='tom123', #连接密码 db='db1' #连接的数据库名称 ) # #创建游标 cursor = conn.cursor() cursor.execute("SELECT * FROM tb1") #获取第一行数据 row_1 = cursor.fetchone() #获取前N行数据 row_2 = cursor.fetchmany(3) #获取所有数据 row_3 = cursor.fetchall() conn.commit() cursor.close() conn.close() print(row_1) #游标的位置会变的，获取了第一行之后，游标就到第二行位置了 print(row_2) #因此打印前三行的时候，是打印的2，3，4 print(row_3) #同理，打印所有的，实际上是当前游标到最后的位置 #代码执行结果： (1, '1.1.1.1') ((2, '1.1.1.2'), (3, '1.1.1.2'), (4, '1.1.1.11')) ((5, '3'), (6, '1.1.1.11'), (7, '3'), (8, '1.1.1.11'), (9, '1'), (10, '1.1.1.11'), (11, '1'), (12, '1.1.1.30'), (13, '1.1.1.30'))
PS: 在fetch数据时按照顺序进行，可以使用cursor.scoll(num,mode) 来移动游标位置，如下：
- cursor.scroll(1,mode='relative') # 相对当前位置移动
- cursor.scroll(2,mode='absolute') # 相对绝对位置移动
#获取第一行数据 row_1 = cursor.fetchone() #获取前N行数据 row_2 = cursor.fetchmany(3) #获取所有数据 #先移动游标 cursor.scroll(0,mode='absolute') #绝对位置移动 row_3 = cursor.fetchall() print(row_1) print(row_2) print(row_3) #再次执行的结果： (1, '1.1.1.1') ((2, '1.1.1.2'), (3, '1.1.1.2'), (4, '1.1.1.11')) ((1, '1.1.1.1'), (2, '1.1.1.2'), (3, '1.1.1.2'), (4, '1.1.1.11'), (5, '3'), (6, '1.1.1.11'), (7, '3'), (8, '1.1.1.11'), (9, '1'), (10, '1.1.1.11'), (11, '1'), (12, '1.1.1.30'), (13, '1.1.1.30'))
4、fetch数据类型
关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：
#!/usr/bin/env python3 # -*- coding: utf-8 -*- # Author: DBQ(Du Baoqiang) import pymysql #导入模块 #创建连接 conn = pymysql.connect( host='172.16.30.162', #主机IP port=3306, #端口 user='tom', #连接数据库用户 password='tom123', #连接密码 db='db1' #连接的数据库名称 ) # #创建游标, 并设置为字典类型 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) cursor.execute("select * from tb1") result = cursor.fetchone() print(result) conn.commit() cursor.close() conn.close() #执行结果： {'host': '1.1.1.1', 'id': 1}
SQLAlchemy
SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射来操作数据库。简而言之，就是将对象转换为SQL语句，然后使用数据API执行SQL并获取执行结果。
对象关系映射（Object Relational Mapping，简称ORM，或O/RM，或O/R mapping），是一种程序技术，用于实现面向对象编程语言里不同类型系统的数据之间的转换。从效果上说，它其实是创建了一个可在编程语言里使用的“虚拟对象数据库”。
Python其他的ORM框架，推荐博文：http://www.oschina.net/translate/sqlalchemy-vs-orms
既然ORM是一个框架或者说是中间件，无论怎样，肯定是会有消耗的，所以，用还是不用？推荐这个博文：https://www.pureweber.com/article/orm/
SQLAlchemy本身无法操作数据库，其必须以pymysql等第三方插件。
Dialect用于和数据API交互，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如下代码：
MySQL-Python mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> pymysql mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] MySQL-Connector mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname> cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...] 更多：http://docs.sqlalchemy.org/en/latest/dialects/index.html
一. 底层处理
使用Engine/ConnectionPooling/Dialect进行数据库操作，Engine使用ConnectionPooling连接数据库，而后再通过Dialect执行SQL语句。
#!/usr/bin/env python3 # -*- coding: utf-8 -*- # Author: DBQ(Du Baoqiang) from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://tom:tom123@172.16.30.162:3306/db1",max_overflow=5) #执行SQL cur = engine.execute( "INSERT INTO tb1(host) VALUES ('172.16.30.253')" #插入一条数据 ) print('ID,',cur.lastrowid) #打印新插入行的自增ID #执行SQL cur1 = engine.execute( "INSERT INTO tb1(host) VALUES(%s),(%s)",[('172.16.0.1'),('172.16.0.2')] #一下插入多条记录, 使用%s方式 ) print('ID,',cur1.lastrowid) #执行SQL方式3 cur2 = engine.execute( "INSERT INTO tb1(host) VALUES(%(host)s),(%(host1)s)",host='172.16.1.1',host1='172.16.1.2' #插入多条记录,使用占位符的形式 ) print('ID,',cur2.lastrowid) #执行SQL cur_select = engine.execute("SELECT * FROM tb1") #获取第一行数据 res = cur_select.fetchone() #也存在指针的问题 print(res) #前三行 res1 = cur_select.fetchmany(3) print(res1) #获取所有 res2 = cur_select.fetchall() print(res2)
二. ORM功能的使用
使用ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect所有的组件对数据进行操作。根据类创建对象，将对象转换成SQL，而后执行SQL。
1. 创建表
#!/usr/bin/env python3 # -*- coding: utf-8 -*- # Author: DBQ(Du Baoqiang) from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer,String, ForeignKey,UniqueConstraint,Index from sqlalchemy.orm import sessionmaker,relationships from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://tom:tom123@172.16.30.162:3306/db1",max_overflow=5) Base = declarative_base() #创建一个单表 class Users(Base): ''' 一定要继承Base ''' __tablename__ = 'users' #表名为users id = Column(Integer,primary_key=True) #id列, 整数数据类型, 主键 name = Column(String(32)) #name列, 字符串类型, 长度32 extra = Column(String(20)) #extra列,字符串类型,长度20 __table_args__ = ( UniqueConstraint('id','name',name='unx_id_name'), Index('ix_id_name','name','extra') ) #一对多 class Favor(Base): __tablename__ = 'favor' nid = Column(Integer,primary_key=True) caption = Column(String(50),default='red',unique=True) class Person(Base): ''' 通过外键关联favor表的nid实现一对多 ''' __tablename__ = 'person' nid = Column(Integer,primary_key=True) name = Column(String(32),index=True,nullable=True) favor_id = Column(Integer, ForeignKey('favor.nid')) #外键,关联favor表的nid #多对多 class Group(Base): __tablename__ = 'group' id = Column(Integer,primary_key=True) name = Column(String(64),unique=True,nullable=True) port = Column(Integer,default=22) class Server(Base): __tablename__ = 'server' id = Column(Integer,primary_key=True,autoincrement=True) hostname = Column(String(64),unique=True,nullable=False) class ServerToGroup(Base): ''' servertogroup这个表存放上述两个表的对应关系,可以多对多 ''' __tablename__ = 'servertogroup' nid = Column(Integer,primary_key=True,autoincrement=True) server_id = Column(Integer,ForeignKey('server.id')) group_id = Column(Integer,ForeignKey('group.id')) def init_db(): Base.metadata.create_all(engine) def drop_db(): Base.metadata.drop_all(engine) #init_db() #创建所有表 #drop_db() #删除所有表
2. 操作表
#!/usr/bin/env python3 # -*- coding: utf-8 -*- # Author: DBQ(Du Baoqiang) from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer,String, ForeignKey,UniqueConstraint,Index from sqlalchemy.orm import sessionmaker,relationships from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://tom:tom123@172.16.30.162:3306/db1",max_overflow=5) Base = declarative_base() #创建一个单表 class Users(Base): ''' 一定要继承Base ''' __tablename__ = 'users' #表名为users id = Column(Integer,primary_key=True) #id列, 整数数据类型, 主键 name = Column(String(32)) #name列, 字符串类型, 长度32 extra = Column(String(20)) #extra列,字符串类型,长度20 __table_args__ = ( UniqueConstraint('id','name',name='unx_id_name'), Index('ix_id_name','name','extra') ) def __repr__(self): return "%s-%s"%(self.id, self.name) #一对多 class Favor(Base): __tablename__ = 'favor' nid = Column(Integer,primary_key=True) caption = Column(String(50),default='red',unique=True) def __repr__(self): return "%s-%s" %(self.nid, self.caption) class Person(Base): ''' 通过外键关联favor表的nid实现一对多 ''' __tablename__ = 'person' nid = Column(Integer,primary_key=True) name = Column(String(32),index=True,nullable=True) favor_id = Column(Integer, ForeignKey('favor.nid')) #外键,关联favor表的nid #与生成表结构无关, 仅用于查询方便 #favor = relationships('Favor',backref = 'pers') #多对多 class Group(Base): __tablename__ = 'group' id = Column(Integer,primary_key=True) name = Column(String(64),unique=True,nullable=True) port = Column(Integer,default=22) #group = relationships('Group',secondary=ServerToGroup,backref='host_list') class Server(Base): __tablename__ = 'server' id = Column(Integer,primary_key=True,autoincrement=True) hostname = Column(String(64),unique=True,nullable=False) class ServerToGroup(Base): ''' servertogroup这个表存放上述两个表的对应关系,可以多对多 ''' __tablename__ = 'servertogroup' nid = Column(Integer,primary_key=True,autoincrement=True) server_id = Column(Integer,ForeignKey('server.id')) group_id = Column(Integer,ForeignKey('group.id')) #group = relationships('Group',backref='s2g') #server = relationships('Server',backref='s2g') def init_db(): Base.metadata.create_all(engine) def drop_db(): Base.metadata.drop_all(engine) Session = sessionmaker(bind=engine) session = Session()
- 增
-
View Code
obj = Users(name='Tom',extra='mouse') session.add(obj) session.add_all([ Users(name='Jerry',extra='Cat'), Users(name='Sam',extra='Human') ]) session.commit()
- 删
-
View Code
session.query(Users).filter(Users.id > 4).delete() #删除id>4的用户 session.commit()
- 改
-
View Code
session.query(Users).filter(Users.id > 3).update({"name":"sb"}) #先将id>3的用户名字改为sb session.query(Users).filter(Users.id > 3).update({Users.name:Users.name + '123'},synchronize_session = False) #而后在sb后拼接一个123 session.query(Users).filter(Users.id > 3).update({"id": Users.id+1},synchronize_session = "evaluate") #最后把大于3的id号+1 session.commit() - 查
-
View Code
res = session.query(Users).all() #返回一个列表 print(res) res1 = session.query(Users.name, Users.extra).all() #返回一个列表,中包含元组 print(res1) res2 = session.query(Users).filter_by(name='Jerry').all() #返回一个列表 print(res2) res3 = session.query(Users).filter_by(name='Jerry').first() print(res3) session.commit()
- 其他
-
View Code
#条件 ret = session.query(Users).filter_by(name='Jerry').all() ret = session.query(Users).filter(Users.id > 1, Users.name == 'Tom').all() #并 ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'Sam').all() #id在1-3之间,并且名字是Sam ret = session.query(Users).filter(Users.id.in_([1,3,4])).all() #id在1,3,4里的 ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all() #取反 ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='Sam'))).all() #子查询 from sqlalchemy import and_, or_ ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'Tom')).all() #并 ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'Tom')).all() #或 ret = session.query(Users).filter( or_( Users.id < 2, and_(Users.name == 'Sam', Users.id > 3), Users.extra != "" )).all() #id<2 或者 用户名是Sam并大于3的 # 通配符 ret = session.query(Users).filter(Users.name.like('J%')).all() #J开头后续任意字符 ret = session.query(Users).filter(~Users.name.like('J%')).all() #取反 # 限制 ret = session.query(Users)[0:5] #显示多少个值,个人感觉有点类似于列表的切片一样 # 排序 ret = session.query(Users).order_by(Users.id.desc()).all() #降序 ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all() #升序 # 分组 from sqlalchemy.sql import func ret = session.query(Users).group_by(Users.extra).all() ret = session.query( func.max(Users.id), func.sum(Users.id), func.min(Users.id)).group_by(Users.name).all() #结果:[(2, Decimal('2'), 2), (3, Decimal('3'), 3), (5, Decimal('5'), 5), (1, Decimal('1'), 1)] ret = session.query( func.max(Users.id), func.sum(Users.id), func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all() #添加条件最小id大于2 # 连表 ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all() #连表查询, 条件 usersid 等于 Favor.nid ret = session.query(Person).join(Favor).all() #左连接, 如果要用右连接,可以把两个表的位置换下 # ret = session.query(Person).join(Favor, isouter=True).all() # 组合 q1 = session.query(Users.name).filter(Users.id > 2) q2 = session.query(Favor.caption).filter(Favor.nid < 2) ret = q1.union(q2).all() q1 = session.query(Users.name).filter(Users.id > 2) q2 = session.query(Favor.caption).filter(Favor.nid < 2) ret = q1.union_all(q2).all() print(ret) session.commit()
#!/usr/bin/env python3 # -*- coding: utf-8 -*- # Author: DBQ(Du Baoqiang) from sqlalchemy import create_engine,and_,or_,func,Table from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String,ForeignKey,UniqueConstraint,DateTime from sqlalchemy.orm import sessionmaker,relationship Base = declarative_base() #生成一个SqlORM 基类 # 服务器账号和组 # HostUser2Group = Table('hostuser_2_group',Base.metadata, # Column('hostuser_id',ForeignKey('host_user.id'),primary_key=True), # Column('group_id',ForeignKey('group.id'),primary_key=True), # ) # 用户和组关系表，用户可以属于多个组，一个组可以有多个人 UserProfile2Group = Table('userprofile_2_group',Base.metadata, Column('userprofile_id',ForeignKey('user_profile.id'),primary_key=True), Column('group_id',ForeignKey('group.id'),primary_key=True), ) # 程序登陆用户和服务器账户，一个人可以有多个服务器账号，一个服务器账号可以给多个人用 UserProfile2HostUser= Table('userprofile_2_hostuser',Base.metadata, Column('userprofile_id',ForeignKey('user_profile.id'),primary_key=True), Column('hostuser_id',ForeignKey('host_user.id'),primary_key=True), ) class Host(Base): __tablename__='host' id = Column(Integer,primary_key=True,autoincrement=True) hostname = Column(String(64),unique=True,nullable=False) ip_addr = Column(String(128),unique=True,nullable=False) port = Column(Integer,default=22) def __repr__(self): return "<id=%s,hostname=%s, ip_addr=%s>" %(self.id, self.hostname, self.ip_addr) class HostUser(Base): __tablename__ = 'host_user' id = Column(Integer,primary_key=True) AuthTypes = [ (u'ssh-passwd',u'SSH/Password'), (u'ssh-key',u'SSH/KEY'), ] # auth_type = Column(ChoiceType(AuthTypes)) auth_type = Column(String(64)) username = Column(String(64),unique=True,nullable=False) password = Column(String(255)) host_id = Column(Integer,ForeignKey('host.id')) # groups = relationship('Group', # secondary=HostUser2Group, # backref='host_list') __table_args__ = (UniqueConstraint('host_id','username', name='_host_username_uc'),) def __repr__(self): return "<id=%s,name=%s>" %(self.id,self.username) class Group(Base): __tablename__ = 'group' id = Column(Integer,primary_key=True) name = Column(String(64),unique=True,nullable=False) def __repr__(self): return "<id=%s,name=%s>" %(self.id,self.name) class UserProfile(Base): __tablename__ = 'user_profile' id = Column(Integer,primary_key=True) username = Column(String(64),unique=True,nullable=False) password = Column(String(255),nullable=False) # host_list = relationship('HostUser', # secondary=UserProfile2HostUser, # backref='userprofiles') # groups = relationship('Group', # secondary=UserProfile2Group, # backref='userprofiles') def __repr__(self): return "<id=%s,name=%s>" %(self.id,self.username) class AuditLog(Base): __tablename__ = 'audit_log' id = Column(Integer,primary_key=True) userprofile_id = Column(Integer,ForeignKey('user_profile.id')) hostuser_id = Column(Integer,ForeignKey('host_user.id')) action_choices2 = [ (u'cmd',u'CMD'), (u'login',u'Login'), (u'logout',u'Logout'), ] action_type = Column(ChoiceType(action_choices2)) #action_type = Column(String(64)) cmd = Column(String(255)) date = Column(DateTime) # user_profile = relationship("UserProfile") #bind_host = relationship("BindHost") engine = create_engine("mysql+pymsql://root:123@localhost:3306/stupid_jumpserver",echo=False) Base.metadata.create_all(engine) #创建所有表结构
参考：
- http://www.cnblogs.com/wupeiqi/articles/5699254.html
