<div id="cnblogs_post_body" class="blogpost-body"><a name="_labelTop"></a><div id="navCategory"><p style="font-size:25px"><b>阅读目录</b></p><ul><li><a href="#_label1" style="color: darkseagreen;text-decoration:none;font-size:18px">一 IDE工具介绍</a></li><li><a href="#_label2" style="color: darkseagreen;text-decoration:none;font-size:18px">二 MySQL数据备份</a></li><li><a href="#_label3" style="color: darkseagreen;text-decoration:none;font-size:18px">三 pymysql模块</a></li></ul></div><div style="text-align: right"><a href="#_labelTop"></a><a name="_label1"></a></div><h3>一 IDE工具介绍</h3>
<p>生产环境还是推荐使用mysql命令行,但为了方便我们测试,可以使用IDE工具</p>
<p><a href="https://pan.baidu.com/s/1bpo5mqj" target="_blank">下载链接:https://pan.baidu.com/s/1bpo5mqj</a></p>
<div class="cnblogs_code"><div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a href="javascript:void(0);" οnclick="copyCnblogsCode(this)" title="复制代码"><img src="//common.cnblogs.com/images/copycode.gif" alt="复制代码"></a></span></div>
<pre><span style="color: #000000;">掌握:
</span><span style="color: #008000;">#</span><span style="color: #008000;">1. 测试+链接数据库</span><span style="color: #008000;">
#</span><span style="color: #008000;">2. 新建库</span><span style="color: #008000;">
#</span><span style="color: #008000;">3. 新建表,新增字段+类型+约束</span><span style="color: #008000;">
#</span><span style="color: #008000;">4. 设计表:外键</span><span style="color: #008000;">
#</span><span style="color: #008000;">5. 新建查询</span><span style="color: #008000;">
#</span><span style="color: #008000;">6. 备份库/表</span>
<span style="color: #008000;">#</span><span style="color: #008000;">注意:</span>
批量加注释:ctrl+<span style="color: #000000;">?键
批量去注释:ctrl</span>+shift+?键</pre>
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a href="javascript:void(0);" οnclick="copyCnblogsCode(this)" title="复制代码"><img src="//common.cnblogs.com/images/copycode.gif" alt="复制代码"></a></span></div></div>
<div style="text-align: right"><a href="#_labelTop"></a><a name="_label2"></a></div><h3>二 MySQL数据备份</h3>
<div class="cnblogs_code">
<pre><span style="color: #008000;">#</span><span style="color: #008000;">1. 物理备份: 直接复制数据库文件,适用于大型数据库环境。但不能恢复到异构系统中如Windows。</span><span style="color: #008000;">
#</span><span style="color: #008000;">2. 逻辑备份: 备份的是建表、建库、插入等操作所执行SQL语句,适用于中小型数据库,效率相对较低。</span><span style="color: #008000;">
#</span><span style="color: #008000;">3. 导出表: 将表导入到文本文件中。</span> </pre>
</div>
<p><span style="font-size: 18px;"><strong>一、使用mysqldump实现逻辑备份</strong></span></p>
<div class="cnblogs_code"><div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a href="javascript:void(0);" οnclick="copyCnblogsCode(this)" title="复制代码"><img src="//common.cnblogs.com/images/copycode.gif" alt="复制代码"></a></span></div>
<pre><span style="color: #008000;">#</span><span style="color: #008000;">语法:</span><span style="color: #008000;">
#</span><span style="color: #008000;"> mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql</span>
<span style="color: #008000;">#</span><span style="color: #008000;">示例:</span><span style="color: #008000;">
#</span><span style="color: #008000;">单库备份</span>
mysqldump -uroot -p123 db1 ><span style="color: #000000;"> db1.sql
mysqldump </span>-uroot -p123 db1 table1 table2 > db1-table1-<span style="color: #000000;">table2.sql
</span><span style="color: #008000;">#</span><span style="color: #008000;">多库备份</span>
mysqldump -uroot -p123 --databases db1 db2 mysql db3 ><span style="color: #000000;"> db1_db2_mysql_db3.sql
</span><span style="color: #008000;">#</span><span style="color: #008000;">备份所有库</span>
mysqldump -uroot -p123 --all-databases > all.sql </pre>
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a href="javascript:void(0);" οnclick="copyCnblogsCode(this)" title="复制代码"><img src="//common.cnblogs.com/images/copycode.gif" alt="复制代码"></a></span></div></div>
<p><span style="font-size: 18px;"><strong><strong>二、恢复逻辑备份</strong></strong></span></p>
<div class="cnblogs_code"><div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a href="javascript:void(0);" οnclick="copyCnblogsCode(this)" title="复制代码"><img src="//common.cnblogs.com/images/copycode.gif" alt="复制代码"></a></span></div>
<pre><span style="color: #008000;">#</span><span style="color: #008000;">方法一:</span>
[root@egon backup]<span style="color: #008000;">#</span><span style="color: #008000;"> mysql -uroot -p123 < /backup/all.sql</span>
<span style="color: #008000;">#</span><span style="color: #008000;">方法二:</span>
mysql><span style="color: #000000;"> use db1;
mysql</span>> SET SQL_LOG_BIN=<span style="color: #000000;">0;
mysql</span>> source /root/<span style="color: #000000;">db1.sql
</span><span style="color: #008000;">#</span><span style="color: #008000;">注:如果备份/恢复单个库时,可以修改sql文件</span>
DROP database <span style="color: #0000ff;">if</span><span style="color: #000000;"> exists school;
create database school;
use school;</span> </pre>
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a href="javascript:void(0);" οnclick="copyCnblogsCode(this)" title="复制代码"><img src="//common.cnblogs.com/images/copycode.gif" alt="复制代码"></a></span></div></div>
<p><span style="font-size: 18px;"><strong>三、备份/恢复案例</strong></span></p>
<div class="cnblogs_code" οnclick="cnblogs_code_show('5b4cb974-74cf-4727-972c-ea5e88956d30')"><img id="code_img_closed_5b4cb974-74cf-4727-972c-ea5e88956d30" class="code_img_closed" src="https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif" alt=""><img id="code_img_opened_5b4cb974-74cf-4727-972c-ea5e88956d30" class="code_img_opened" style="display: none;" οnclick="cnblogs_code_hide('5b4cb974-74cf-4727-972c-ea5e88956d30',event)" src="https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif" alt="">
<div id="cnblogs_code_open_5b4cb974-74cf-4727-972c-ea5e88956d30" class="cnblogs_code_hide">
<pre><span style="color: #008000;">#</span><span style="color: #008000;">数据库备份/恢复实验一:数据库损坏</span>
<span style="color: #000000;">备份:
</span>1. <span style="color: #008000;">#</span><span style="color: #008000;"> mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql</span>
2. <span style="color: #008000;">#</span><span style="color: #008000;"> mysql -uroot -p123 -e 'flush logs' //截断并产生新的binlog</span>
3. 插入数据 //<span style="color: #000000;">模拟服务器正常运行
</span>4. mysql> set sql_log_bin=0; //<span style="color: #000000;">模拟服务器损坏
mysql</span>><span style="color: #000000;"> drop database db;
恢复:
</span>1. <span style="color: #008000;">#</span><span style="color: #008000;"> mysqlbinlog 最后一个binlog > /backup/last_bin.log</span>
2. mysql> set sql_log_bin=<span style="color: #000000;">0;
mysql</span>> source /backup/2014-02-13_all.sql //<span style="color: #000000;">恢复最近一次完全备份
mysql</span>> source /backup/last_bin.log //<span style="color: #000000;">恢复最后个binlog文件
</span><span style="color: #008000;">#</span><span style="color: #008000;">数据库备份/恢复实验二:如果有误删除</span>
<span style="color: #000000;">备份:
</span>1. mysqldump -uroot -p123 --all-databases > /backup/`date +%<span style="color: #000000;">F`_all.sql
</span>2. mysql -uroot -p123 -e <span style="color: #800000;">'</span><span style="color: #800000;">flush logs</span><span style="color: #800000;">'</span> //<span style="color: #000000;">截断并产生新的binlog
</span>3. 插入数据 //<span style="color: #000000;">模拟服务器正常运行
</span>4. drop table db1.t1 //<span style="color: #000000;">模拟误删除
</span>5. 插入数据 //<span style="color: #000000;">模拟服务器正常运行
恢复:
</span>1. <span style="color: #008000;">#</span><span style="color: #008000;"> mysqlbinlog 最后一个binlog --stop-position=260 > /tmp/1.sql </span><span style="color: #008000;">
#</span><span style="color: #008000;"> mysqlbinlog 最后一个binlog --start-position=900 > /tmp/2.sql </span>
2. mysql> set sql_log_bin=<span style="color: #000000;">0;
mysql</span>> source /backup/2014-02-13_all.sql //<span style="color: #000000;">恢复最近一次完全备份
mysql</span>> source /tmp/1.log //<span style="color: #000000;">恢复最后个binlog文件
mysql</span>> source /tmp/2.log //<span style="color: #000000;">恢复最后个binlog文件
注意事项:
</span>1<span style="color: #000000;">. 完全恢复到一个干净的环境(例如新的数据库或删除原有的数据库)
</span>2. 恢复期间所有SQL语句不应该记录到binlog中</pre>
</div>
<span class="cnblogs_code_collapse">View Code</span></div>
<p><span style="font-size: 18px;"><strong><strong>四、实现自动化备份</strong></strong></span></p>
<div class="cnblogs_code" οnclick="cnblogs_code_show('f451285c-0744-403d-8073-c5f29cb9044a')"><img id="code_img_closed_f451285c-0744-403d-8073-c5f29cb9044a" class="code_img_closed" src="https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif" alt=""><img id="code_img_opened_f451285c-0744-403d-8073-c5f29cb9044a" class="code_img_opened" style="display: none;" οnclick="cnblogs_code_hide('f451285c-0744-403d-8073-c5f29cb9044a',event)" src="https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif" alt="">
<div id="cnblogs_code_open_f451285c-0744-403d-8073-c5f29cb9044a" class="cnblogs_code_hide">
<pre><span style="color: #000000;">备份计划:
</span>1. 什么时间 2:00
2<span style="color: #000000;">. 对哪些数据库备份
</span>3<span style="color: #000000;">. 备份文件放的位置
备份脚本:
[root@egon </span>~]<span style="color: #008000;">#</span><span style="color: #008000;"> vim /mysql_back.sql</span><span style="color: #008000;">
#</span><span style="color: #008000;">!/bin/bash</span>
back_dir=/<span style="color: #000000;">backup
back_file</span>=`date +%<span style="color: #000000;">F`_all.sql
user</span>=<span style="color: #000000;">root
</span><span style="color: #0000ff;">pass</span>=123
<span style="color: #0000ff;">if</span> [ ! -d /<span style="color: #000000;">backup ];then
mkdir </span>-p /<span style="color: #000000;">backup
fi
</span><span style="color: #008000;">#</span><span style="color: #008000;"> 备份并截断日志</span>
mysqldump -u${user} -p${<span style="color: #0000ff;">pass</span>} --events --all-databases > ${back_dir}/<span style="color: #000000;">${back_file}
mysql </span>-u${user} -p${<span style="color: #0000ff;">pass</span>} -e <span style="color: #800000;">'</span><span style="color: #800000;">flush logs</span><span style="color: #800000;">'</span>
<span style="color: #008000;">#</span><span style="color: #008000;"> 只保留最近一周的备份</span>
<span style="color: #000000;">cd $back_dir
find . </span>-mtime +7 -<span style="color: #0000ff;">exec</span> rm -<span style="color: #000000;">rf {} \;
手动测试:
[root@egon </span>~]<span style="color: #008000;">#</span><span style="color: #008000;"> chmod a+x /mysql_back.sql </span>
[root@egon ~]<span style="color: #008000;">#</span><span style="color: #008000;"> chattr +i /mysql_back.sql</span>
[root@egon ~]<span style="color: #008000;">#</span><span style="color: #008000;"> /mysql_back.sql</span>
<span style="color: #000000;">
配置cron:
[root@egon </span>~]<span style="color: #008000;">#</span><span style="color: #008000;"> crontab -l</span>
2 * * * /mysql_back.sql</pre>
</div>
<span class="cnblogs_code_collapse">View Code</span></div>
<p><span style="font-size: 18px;"><strong>五、表的导出和导入</strong></span></p>
<div class="cnblogs_code" οnclick="cnblogs_code_show('0f09fea7-3001-4315-969a-5e7678dac9b5')"><img id="code_img_closed_0f09fea7-3001-4315-969a-5e7678dac9b5" class="code_img_closed" src="https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif" alt=""><img id="code_img_opened_0f09fea7-3001-4315-969a-5e7678dac9b5" class="code_img_opened" style="display: none;" οnclick="cnblogs_code_hide('0f09fea7-3001-4315-969a-5e7678dac9b5',event)" src="https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif" alt="">
<div id="cnblogs_code_open_0f09fea7-3001-4315-969a-5e7678dac9b5" class="cnblogs_code_hide">
<pre><span style="color: #000000;">SELECT... INTO OUTFILE 导出文本文件
示例:
mysql</span>> SELECT *<span style="color: #000000;"> FROM school.student1
INTO OUTFILE </span><span style="color: #800000;">'</span><span style="color: #800000;">student1.txt</span><span style="color: #800000;">'</span><span style="color: #000000;">
FIELDS TERMINATED BY </span><span style="color: #800000;">'</span><span style="color: #800000;">,</span><span style="color: #800000;">'</span> //<span style="color: #000000;">定义字段分隔符
OPTIONALLY ENCLOSED BY </span><span style="color: #800000;">'</span><span style="color: #800000;">”</span><span style="color: #800000;">'</span> //<span style="color: #000000;">定义字符串使用什么符号括起来
LINES TERMINATED BY </span><span style="color: #800000;">'</span><span style="color: #800000;">\n</span><span style="color: #800000;">'</span> ; //<span style="color: #000000;">定义换行符
mysql 命令导出文本文件
示例:
</span><span style="color: #008000;">#</span><span style="color: #008000;"> mysql -u root -p123 -e 'select * from student1.school' > /tmp/student1.txt</span><span style="color: #008000;">
#</span><span style="color: #008000;"> mysql -u root -p123 --xml -e 'select * from student1.school' > /tmp/student1.xml</span><span style="color: #008000;">
#</span><span style="color: #008000;"> mysql -u root -p123 --html -e 'select * from student1.school' > /tmp/student1.html</span>
<span style="color: #000000;">
LOAD DATA INFILE 导入文本文件
mysql</span>><span style="color: #000000;"> DELETE FROM student1;
mysql</span>> LOAD DATA INFILE <span style="color: #800000;">'</span><span style="color: #800000;">/tmp/student1.txt</span><span style="color: #800000;">'</span><span style="color: #000000;">
INTO TABLE school.student1
FIELDS TERMINATED BY </span><span style="color: #800000;">'</span><span style="color: #800000;">,</span><span style="color: #800000;">'</span><span style="color: #000000;">
OPTIONALLY ENCLOSED BY </span><span style="color: #800000;">'</span><span style="color: #800000;">”</span><span style="color: #800000;">'</span><span style="color: #000000;">
LINES TERMINATED BY </span><span style="color: #800000;">'</span><span style="color: #800000;">\n</span><span style="color: #800000;">'</span>;</pre>
</div>
<span class="cnblogs_code_collapse">View Code</span></div>
<div class="cnblogs_code" οnclick="cnblogs_code_show('b99f6256-cf1f-43b7-a7bc-2789b62b4480')"><img id="code_img_closed_b99f6256-cf1f-43b7-a7bc-2789b62b4480" class="code_img_closed" src="https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif" alt=""><img id="code_img_opened_b99f6256-cf1f-43b7-a7bc-2789b62b4480" class="code_img_opened" style="display: none;" οnclick="cnblogs_code_hide('b99f6256-cf1f-43b7-a7bc-2789b62b4480',event)" src="https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif" alt="">
<div id="cnblogs_code_open_b99f6256-cf1f-43b7-a7bc-2789b62b4480" class="cnblogs_code_hide">
<pre><span style="color: #008000;">#</span><span style="color: #008000;">可能会报错</span>
mysql> select * <span style="color: #0000ff;">from</span> db1.emp into outfile <span style="color: #800000;">'</span><span style="color: #800000;">C:\\db1.emp.txt</span><span style="color: #800000;">'</span> fields terminated by <span style="color: #800000;">'</span><span style="color: #800000;">,</span><span style="color: #800000;">'</span> lines terminated by <span style="color: #800000;">'</span><span style="color: #800000;">\r\n</span><span style="color: #800000;">'</span><span style="color: #000000;">;
ERROR </span>1238 (HY000): Variable <span style="color: #800000;">'</span><span style="color: #800000;">secure_file_priv</span><span style="color: #800000;">'</span> <span style="color: #0000ff;">is</span><span style="color: #000000;"> a read only variable
</span><span style="color: #008000;">#</span><span style="color: #008000;">数据库最关键的是数据,一旦数据库权限泄露,那么通过上述语句就可以轻松将数据导出到文件中然后下载拿走,因而mysql对此作了限制,只能将文件导出到指定目录</span>
<span style="color: #000000;">在配置文件中
[mysqld]
secure_file_priv</span>=<span style="color: #800000;">'</span><span style="color: #800000;">C:\\</span><span style="color: #800000;">'</span> <span style="color: #008000;">#</span><span style="color: #008000;">只能将数据导出到C:\\下</span>
<span style="color: #000000;">
重启mysql
重新执行上述语句</span></pre>
</div>
<span class="cnblogs_code_collapse">报错:Variable 'secure_file_priv' is a read only</span></div>
<p><span style="font-size: 18px;"><strong><strong>六、数据库迁移</strong></strong></span></p>
<div class="cnblogs_code">
<pre><span style="color: #000000;">务必保证在相同版本之间迁移
</span><span style="color: #008000;">#</span><span style="color: #008000;"> mysqldump -h 源IP -uroot -p123 --databases db1 | mysql -h 目标IP -uroot -p456</span></pre>
</div>
<div style="text-align: right"><a href="#_labelTop"></a><a name="_label3"></a></div><h3>三 pymysql模块</h3>
<div class="cnblogs_code">
<pre><span style="color: #008000;">#</span><span style="color: #008000;">安装</span>
pip3 install pymysql</pre>
</div>
<p><span style="font-size: 18px;"><strong>一 链接、执行sql、关闭(游标)</strong></span></p>
<p><img src="https://images2017.cnblogs.com/blog/1036857/201709/1036857-20170915114556719-2064840662.png" alt=""></p>
<div class="cnblogs_code"><div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a href="javascript:void(0);" οnclick="copyCnblogsCode(this)" title="复制代码"><img src="//common.cnblogs.com/images/copycode.gif" alt="复制代码"></a></span></div>
<pre><span style="color: #0000ff;">import</span><span style="color: #000000;"> pymysql
user</span>=input(<span style="color: #800000;">'</span><span style="color: #800000;">用户名: </span><span style="color: #800000;">'</span><span style="color: #000000;">).strip()
pwd</span>=input(<span style="color: #800000;">'</span><span style="color: #800000;">密码: </span><span style="color: #800000;">'</span><span style="color: #000000;">).strip()
</span><span style="color: #008000;">#</span><span style="color: #008000;">链接</span>
conn=pymysql.connect(host=<span style="color: #800000;">'</span><span style="color: #800000;">localhost</span><span style="color: #800000;">'</span>,user=<span style="color: #800000;">'</span><span style="color: #800000;">root</span><span style="color: #800000;">'</span>,password=<span style="color: #800000;">'</span><span style="color: #800000;">123</span><span style="color: #800000;">'</span>,database=<span style="color: #800000;">'</span><span style="color: #800000;">egon</span><span style="color: #800000;">'</span>,charset=<span style="color: #800000;">'</span><span style="color: #800000;">utf8</span><span style="color: #800000;">'</span><span style="color: #000000;">)
</span><span style="color: #008000;">#</span><span style="color: #008000;">游标</span>
cursor=conn.cursor() <span style="color: #008000;">#</span><span style="color: #008000;">执行完毕返回的结果集默认以元组显示</span><span style="color: #008000;">
#</span><span style="color: #008000;">cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)</span>
<span style="color: #008000;">#</span><span style="color: #008000;">执行sql语句</span>
sql=<span style="color: #800000;">'</span><span style="color: #800000;">select * from userinfo where name="%s" and password="%s"</span><span style="color: #800000;">'</span> %(user,pwd) <span style="color: #008000;">#</span><span style="color: #008000;">注意%s需要加引号</span>
<span style="color: #0000ff;">print</span><span style="color: #000000;">(sql)
res</span>=cursor.execute(sql) <span style="color: #008000;">#</span><span style="color: #008000;">执行sql语句,返回sql查询成功的记录数目</span>
<span style="color: #0000ff;">print</span><span style="color: #000000;">(res)
cursor.close()
conn.close()
</span><span style="color: #0000ff;">if</span><span style="color: #000000;"> res:
</span><span style="color: #0000ff;">print</span>(<span style="color: #800000;">'</span><span style="color: #800000;">登录成功</span><span style="color: #800000;">'</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">else</span><span style="color: #000000;">:
</span><span style="color: #0000ff;">print</span>(<span style="color: #800000;">'</span><span style="color: #800000;">登录失败</span><span style="color: #800000;">'</span>)</pre>
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a href="javascript:void(0);" οnclick="copyCnblogsCode(this)" title="复制代码"><img src="//common.cnblogs.com/images/copycode.gif" alt="复制代码"></a></span></div></div>
<p><span style="font-size: 18px;"><strong>二 execute()之sql注入</strong></span></p>
<p>注意:符号--会注释掉它之后的sql,正确的语法:--后至少有一个任意字符</p>
<p>根本原理:就根据程序的字符串拼接name='%s',我们输入一个<span style="color: #ff0000;"><em><strong>xxx' -- haha</strong></em></span>,用我们输入的xxx加'在程序中拼接成一个判断条件name='<span style="color: #ff0000;"><em><strong>xxx' -- haha</strong></em></span>'</p>
<div class="cnblogs_code"><div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a href="javascript:void(0);" οnclick="copyCnblogsCode(this)" title="复制代码"><img src="//common.cnblogs.com/images/copycode.gif" alt="复制代码"></a></span></div>
<pre>最后那一个空格,在一条sql语句中如果遇到select * <span style="color: #0000ff;">from</span> t1 where id > 3 -- <span style="color: #0000ff;">and</span> name=<span style="color: #800000;">'</span><span style="color: #800000;">egon</span><span style="color: #800000;">'</span>;则--<span style="color: #000000;">之后的条件被注释掉了
</span><span style="color: #008000;">#</span><span style="color: #008000;">1、sql注入之:用户存在,绕过密码</span>
egon<span style="color: #800000;">'</span><span style="color: #800000;"> -- 任意字符</span>
<span style="color: #008000;">#</span><span style="color: #008000;">2、sql注入之:用户不存在,绕过用户与密码</span>
xxx<span style="color: #800000;">'</span><span style="color: #800000;"> or 1=1 -- 任意字符</span></pre>
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a href="javascript:void(0);" οnclick="copyCnblogsCode(this)" title="复制代码"><img src="//common.cnblogs.com/images/copycode.gif" alt="复制代码"></a></span></div></div>
<p><img src="https://images2017.cnblogs.com/blog/1036857/201709/1036857-20170914080611375-1304472294.png" alt="" width="663" height="197"></p>
<p><img src="https://images2017.cnblogs.com/blog/1036857/201709/1036857-20170914073716813-2024667165.png" alt="" width="662" height="161"></p>
<p><img src="https://images2017.cnblogs.com/blog/1036857/201709/1036857-20170914073927438-1525371660.png" alt="" width="662" height="173"> </p>
<p>解决方法:</p>
<div class="cnblogs_code"><div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a href="javascript:void(0);" οnclick="copyCnblogsCode(this)" title="复制代码"><img src="//common.cnblogs.com/images/copycode.gif" alt="复制代码"></a></span></div>
<pre><span style="color: #008000;">#</span><span style="color: #008000;"> 原来是我们对sql进行字符串拼接</span><span style="color: #008000;">
#</span><span style="color: #008000;"> sql="select * from userinfo where name='%s' and password='%s'" %(user,pwd)</span><span style="color: #008000;">
#</span><span style="color: #008000;"> print(sql)</span><span style="color: #008000;">
#</span><span style="color: #008000;"> res=cursor.execute(sql)</span>
<span style="color: #008000;">#</span><span style="color: #008000;">改写为(execute帮我们做字符串拼接,我们无需且一定不能再为%s加引号了)</span>
sql=<span style="color: #800000;">"</span><span style="color: #800000;">select * from userinfo where name=%s and password=%s</span><span style="color: #800000;">"</span> <span style="color: #008000;">#</span><span style="color: #008000;">!!!注意%s需要去掉引号,因为pymysql会自动为我们加上</span>
res=cursor.execute(sql,[user,pwd]) <span style="color: #008000;">#</span><span style="color: #008000;">pymysql模块自动帮我们解决sql注入的问题,只要我们按照pymysql的规矩来。</span></pre>
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a href="javascript:void(0);" οnclick="copyCnblogsCode(this)" title="复制代码"><img src="//common.cnblogs.com/images/copycode.gif" alt="复制代码"></a></span></div></div>
<p><strong><span style="font-size: 18px;">三 增、删、改:conn.commit()</span></strong></p>
<div class="cnblogs_code" οnclick="cnblogs_code_show('2ffcf73c-8ec6-4e08-8173-01d4243cf6bd')"><img id="code_img_closed_2ffcf73c-8ec6-4e08-8173-01d4243cf6bd" class="code_img_closed" src="https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif" alt=""><img id="code_img_opened_2ffcf73c-8ec6-4e08-8173-01d4243cf6bd" class="code_img_opened" style="display: none;" οnclick="cnblogs_code_hide('2ffcf73c-8ec6-4e08-8173-01d4243cf6bd',event)" src="https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif" alt="">
<div id="cnblogs_code_open_2ffcf73c-8ec6-4e08-8173-01d4243cf6bd" class="cnblogs_code_hide">
<pre><span style="color: #0000ff;">import</span><span style="color: #000000;"> pymysql
</span><span style="color: #008000;">#</span><span style="color: #008000;">链接</span>
conn=pymysql.connect(host=<span style="color: #800000;">'</span><span style="color: #800000;">localhost</span><span style="color: #800000;">'</span>,user=<span style="color: #800000;">'</span><span style="color: #800000;">root</span><span style="color: #800000;">'</span>,password=<span style="color: #800000;">'</span><span style="color: #800000;">123</span><span style="color: #800000;">'</span>,database=<span style="color: #800000;">'</span><span style="color: #800000;">egon</span><span style="color: #800000;">'</span><span style="color: #000000;">)
</span><span style="color: #008000;">#</span><span style="color: #008000;">游标</span>
cursor=<span style="color: #000000;">conn.cursor()
</span><span style="color: #008000;">#</span><span style="color: #008000;">执行sql语句</span><span style="color: #008000;">
#</span><span style="color: #008000;">part1</span><span style="color: #008000;">
#</span><span style="color: #008000;"> sql='insert into userinfo(name,password) values("root","123456");'</span><span style="color: #008000;">
#</span><span style="color: #008000;"> res=cursor.execute(sql) #执行sql语句,返回sql影响成功的行数</span><span style="color: #008000;">
#</span><span style="color: #008000;"> print(res)</span>
<span style="color: #008000;">#</span><span style="color: #008000;">part2</span><span style="color: #008000;">
#</span><span style="color: #008000;"> sql='insert into userinfo(name,password) values(%s,%s);'</span><span style="color: #008000;">
#</span><span style="color: #008000;"> res=cursor.execute(sql,("root","123456")) #执行sql语句,返回sql影响成功的行数</span><span style="color: #008000;">
#</span><span style="color: #008000;"> print(res)</span>
<span style="color: #008000;">#</span><span style="color: #008000;">part3</span>
sql=<span style="color: #800000;">'</span><span style="color: #800000;">insert into userinfo(name,password) values(%s,%s);</span><span style="color: #800000;">'</span><span style="color: #000000;">
res</span>=cursor.executemany(sql,[(<span style="color: #800000;">"</span><span style="color: #800000;">root</span><span style="color: #800000;">"</span>,<span style="color: #800000;">"</span><span style="color: #800000;">123456</span><span style="color: #800000;">"</span>),(<span style="color: #800000;">"</span><span style="color: #800000;">lhf</span><span style="color: #800000;">"</span>,<span style="color: #800000;">"</span><span style="color: #800000;">12356</span><span style="color: #800000;">"</span>),(<span style="color: #800000;">"</span><span style="color: #800000;">eee</span><span style="color: #800000;">"</span>,<span style="color: #800000;">"</span><span style="color: #800000;">156</span><span style="color: #800000;">"</span>)]) <span style="color: #008000;">#</span><span style="color: #008000;">执行sql语句,返回sql影响成功的行数</span>
<span style="color: #0000ff;">print</span><span style="color: #000000;">(res)
conn.commit() </span><span style="color: #008000;">#</span><span style="color: #008000;">提交后才发现表中插入记录成功</span>
<span style="color: #000000;">cursor.close()
conn.close()</span></pre>
</div>
<span class="cnblogs_code_collapse">View Code</span></div>
<p><strong><span style="font-size: 18px;">四 查:fetchone,fetchmany,fetchall</span></strong></p>
<p><img src="https://images2017.cnblogs.com/blog/1036857/201709/1036857-20170914075702438-239735784.png" alt=""></p>
<div class="cnblogs_code" οnclick="cnblogs_code_show('eaca94cb-c582-43eb-b0f1-7cb682603c4e')"><img id="code_img_closed_eaca94cb-c582-43eb-b0f1-7cb682603c4e" class="code_img_closed" src="https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif" alt=""><img id="code_img_opened_eaca94cb-c582-43eb-b0f1-7cb682603c4e" class="code_img_opened" style="display: none;" οnclick="cnblogs_code_hide('eaca94cb-c582-43eb-b0f1-7cb682603c4e',event)" src="https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif" alt="">
<div id="cnblogs_code_open_eaca94cb-c582-43eb-b0f1-7cb682603c4e" class="cnblogs_code_hide">
<pre><span style="color: #0000ff;">import</span><span style="color: #000000;"> pymysql
</span><span style="color: #008000;">#</span><span style="color: #008000;">链接</span>
conn=pymysql.connect(host=<span style="color: #800000;">'</span><span style="color: #800000;">localhost</span><span style="color: #800000;">'</span>,user=<span style="color: #800000;">'</span><span style="color: #800000;">root</span><span style="color: #800000;">'</span>,password=<span style="color: #800000;">'</span><span style="color: #800000;">123</span><span style="color: #800000;">'</span>,database=<span style="color: #800000;">'</span><span style="color: #800000;">egon</span><span style="color: #800000;">'</span><span style="color: #000000;">)
</span><span style="color: #008000;">#</span><span style="color: #008000;">游标</span>
cursor=<span style="color: #000000;">conn.cursor()
</span><span style="color: #008000;">#</span><span style="color: #008000;">执行sql语句</span>
sql=<span style="color: #800000;">'</span><span style="color: #800000;">select * from userinfo;</span><span style="color: #800000;">'</span><span style="color: #000000;">
rows</span>=cursor.execute(sql) <span style="color: #008000;">#</span><span style="color: #008000;">执行sql语句,返回sql影响成功的行数rows,将结果放入一个集合,等待被查询</span>
<span style="color: #008000;">#</span><span style="color: #008000;"> cursor.scroll(3,mode='absolute') # 相对绝对位置移动</span><span style="color: #008000;">
#</span><span style="color: #008000;"> cursor.scroll(3,mode='relative') # 相对当前位置移动</span>
res1=<span style="color: #000000;">cursor.fetchone()
res2</span>=<span style="color: #000000;">cursor.fetchone()
res3</span>=<span style="color: #000000;">cursor.fetchone()
res4</span>=cursor.fetchmany(2<span style="color: #000000;">)
res5</span>=<span style="color: #000000;">cursor.fetchall()
</span><span style="color: #0000ff;">print</span><span style="color: #000000;">(res1)
</span><span style="color: #0000ff;">print</span><span style="color: #000000;">(res2)
</span><span style="color: #0000ff;">print</span><span style="color: #000000;">(res3)
</span><span style="color: #0000ff;">print</span><span style="color: #000000;">(res4)
</span><span style="color: #0000ff;">print</span><span style="color: #000000;">(res5)
</span><span style="color: #0000ff;">print</span>(<span style="color: #800000;">'</span><span style="color: #800000;">%s rows in set (0.00 sec)</span><span style="color: #800000;">'</span> %<span style="color: #000000;">rows)
conn.commit() </span><span style="color: #008000;">#</span><span style="color: #008000;">提交后才发现表中插入记录成功</span>
<span style="color: #000000;">cursor.close()
conn.close()
</span><span style="color: #800000;">'''</span><span style="color: #800000;">
(1, 'root', '123456')
(2, 'root', '123456')
(3, 'root', '123456')
((4, 'root', '123456'), (5, 'root', '123456'))
((6, 'root', '123456'), (7, 'lhf', '12356'), (8, 'eee', '156'))
rows in set (0.00 sec)
</span><span style="color: #800000;">'''</span></pre>
</div>
<span class="cnblogs_code_collapse">View Code</span></div>
<p><strong><span style="font-size: 18px;">五 获取插入的最后一条数据的自增ID</span></strong></p>
<div class="cnblogs_code" οnclick="cnblogs_code_show('c633ac75-1e62-416a-92fc-7c3784f11f9f')"><img id="code_img_closed_c633ac75-1e62-416a-92fc-7c3784f11f9f" class="code_img_closed" src="https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif" alt=""><img id="code_img_opened_c633ac75-1e62-416a-92fc-7c3784f11f9f" class="code_img_opened" style="display: none;" οnclick="cnblogs_code_hide('c633ac75-1e62-416a-92fc-7c3784f11f9f',event)" src="https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif" alt="">
<div id="cnblogs_code_open_c633ac75-1e62-416a-92fc-7c3784f11f9f" class="cnblogs_code_hide">
<pre><span style="color: #0000ff;">import</span><span style="color: #000000;"> pymysql
conn</span>=pymysql.connect(host=<span style="color: #800000;">'</span><span style="color: #800000;">localhost</span><span style="color: #800000;">'</span>,user=<span style="color: #800000;">'</span><span style="color: #800000;">root</span><span style="color: #800000;">'</span>,password=<span style="color: #800000;">'</span><span style="color: #800000;">123</span><span style="color: #800000;">'</span>,database=<span style="color: #800000;">'</span><span style="color: #800000;">egon</span><span style="color: #800000;">'</span><span style="color: #000000;">)
cursor</span>=<span style="color: #000000;">conn.cursor()
sql</span>=<span style="color: #800000;">'</span><span style="color: #800000;">insert into userinfo(name,password) values("xxx","123");</span><span style="color: #800000;">'</span><span style="color: #000000;">
rows</span>=<span style="color: #000000;">cursor.execute(sql)
</span><span style="color: #0000ff;">print</span>(cursor.lastrowid) <span style="color: #008000;">#</span><span style="color: #008000;">在插入语句后查看</span>
<span style="color: #000000;">
conn.commit()
cursor.close()
conn.close()</span></pre>
</div>
<span class="cnblogs_code_collapse">View Code</span></div>
<p> </p></div>