Python对MySQL操作

星哥玩云

发布于 2022-09-08 20:48:05

1.4K0

文章被收录于专栏：开源部署开源部署

一、连接MySQL数据库

作用：对数据库进行操作（SQL语句）

说明：pymysql是纯用Python操作MySQL的模块，其使用方法和MySQLdb几乎相同

安装：pip install pymysql

<span class="hljs-comment"># -*- coding:utf-8 -*-</span>
<span class="hljs-comment"># 引入pymysql</span>
<span class="hljs-keyword">import</span> pymysql

<span class="hljs-comment"># 连接数据库</span>
<span class="hljs-comment"># 参数1：mysql服务器IP</span>
<span class="hljs-comment"># 参数2：用户名</span>
<span class="hljs-comment"># 参数3：用户密码</span>
<span class="hljs-comment"># 参数4：要连接的数据库名</span>
db = pymysql.connect(<span class="hljs-string">"8.8.8.8"</span>, <span class="hljs-string">"zutuanxue_com"</span>, <span class="hljs-string">"zutuanxue_com2000"</span>, <span class="hljs-string">"db1"</span>, charset=<span class="hljs-string">"utf8"</span>)

<span class="hljs-comment"># 创建cursor对象</span>
cursor = db.cursor()

<span class="hljs-comment"># 执行SQL语句</span>
cursor.execute(<span class="hljs-string">"select version()"</span>)

<span class="hljs-comment"># 获取返回信息</span>
data = cursor.fetchone()
print(data)

<span class="hljs-comment"># 断开数据库连接</span>
db.close()

二、执行SQL语句

建表语句

<span class="hljs-comment"># -*- coding:utf-8 -*-</span>
<span class="hljs-keyword">import</span> pymysql

db = pymysql.connect(<span class="hljs-string">"8.8.8.8"</span>, <span class="hljs-string">"zutuanxue_com"</span>, <span class="hljs-string">"zutuanxue_com2000"</span>, <span class="hljs-string">"db1"</span>, charset=<span class="hljs-string">"utf8"</span>)
cursor = db.cursor()

<span class="hljs-comment"># 建表之前首先判断表是否存在，存在则删除</span>
sql1 = <span class="hljs-string">"drop table if exists students;"</span>
sql2 = <span class="hljs-string">"create table students(id int not null auto_increment primary key,name char(20),passwd char(20)) engine=innodb default charset=utf8;"</span>

cursor.execute(sql1)
cursor.execute(sql2)

cursor.close()
db.close()

新增语句

<span class="hljs-comment"># -*- coding:utf-8 -*-</span>
<span class="hljs-keyword">import</span> pymysql

db = pymysql.connect(<span class="hljs-string">"8.8.8.8"</span>, <span class="hljs-string">"zutuanxue_com"</span>, <span class="hljs-string">"zutuanxue_com2000"</span>, <span class="hljs-string">"db1"</span>, charset=<span class="hljs-string">"utf8"</span>)
cursor = db.cursor()

<span class="hljs-keyword">try</span>:
    <span class="hljs-comment"># 待执行的SQL语句</span>
    sql = <span class="hljs-string">"insert into students values(0, 'lilei', '111');"</span>
    cursor.execute(sql)
    <span class="hljs-comment"># 提交事物，真正写入数据库</span>
    db.commit()
<span class="hljs-keyword">except</span>:
    <span class="hljs-comment"># 如果提交失败，回滚到上次提交的数据</span>
    db.rollback()

cursor.close()
db.close()

修改语句

<span class="hljs-comment"># -*- coding:utf-8 -*-</span>
<span class="hljs-keyword">import</span> pymysql

db = pymysql.connect(<span class="hljs-string">"8.8.8.8"</span>, <span class="hljs-string">"zutuanxue_com"</span>, <span class="hljs-string">"zutuanxue_com2000"</span>, <span class="hljs-string">"db1"</span>, charset=<span class="hljs-string">"utf8"</span>)
cursor = db.cursor()

<span class="hljs-keyword">try</span>:
    sql = <span class="hljs-string">"update students set name='li' where id=1"</span>
    cursor.execute(sql)
    db.commit()
    print(<span class="hljs-string">"------------"</span>, cursor.rowcount)
<span class="hljs-keyword">except</span>:
    db.rollback()

cursor.close()
db.close()

删除语句

<span class="hljs-comment"># -*- coding:utf-8 -*-</span>
<span class="hljs-keyword">import</span> pymysql

db = pymysql.connect(<span class="hljs-string">"8.8.8.8"</span>, <span class="hljs-string">"zutuanxue_com"</span>, <span class="hljs-string">"zutuanxue_com2000"</span>, <span class="hljs-string">"db1"</span>, charset=<span class="hljs-string">"utf8"</span>)
cursor = db.cursor()

<span class="hljs-keyword">try</span>:
    sql = <span class="hljs-string">"delete from students where id=1;"</span>
    cursor.execute(sql)
    db.commit()
<span class="hljs-keyword">except</span>:
    db.rollback()

cursor.close()
db.close()

查询语句

方法与属性	说明
fetchone()	获取下一个查询结果集，结果集是一个对象
fetchall()	接收全部的返回结果
rowcount	是一个只读属性，返回执行execute()方法后影响的行数

<span class="hljs-comment"># -*- coding:utf-8 -*-</span>
<span class="hljs-keyword">import</span> pymysql

db = pymysql.connect(<span class="hljs-string">"8.8.8.8"</span>, <span class="hljs-string">"zutuanxue_com"</span>, <span class="hljs-string">"zutuanxue_com2000"</span>, <span class="hljs-string">"db1"</span>, charset=<span class="hljs-string">"utf8"</span>)
<span class="hljs-comment"># cursor = db.cursor()</span>
<span class="hljs-comment"># 以字典形式显示</span>
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)

<span class="hljs-keyword">try</span>:
    sql = <span class="hljs-string">"select * from students where id&gt;=4;"</span>
    cursor.execute(sql)

    <span class="hljs-comment"># 获取所有数据列表</span>
    <span class="hljs-comment"># reslist = cursor.fetchall()</span>
    <span class="hljs-comment"># print(reslist)</span>
    <span class="hljs-comment"># for row in reslist:</span>
    <span class="hljs-comment">#     print(row, type(row))</span>

    <span class="hljs-comment"># print(cursor.fetchone())</span>
    <span class="hljs-comment"># print(cursor.fetchone())</span>
    <span class="hljs-comment"># print(cursor.fetchone())</span>

    <span class="hljs-keyword">for</span> i <span class="hljs-keyword">in</span> range(cursor.rowcount):
        res = cursor.fetchone()
        print(res)
<span class="hljs-keyword">except</span>:
    print(<span class="hljs-string">"查询有误"</span>)

cursor.close()
db.close()

三、防止SQL注入

SQL注入是一种注入攻击，可以执行恶意SQL语句。它通过将任意SQL代码插入数据库查询，使攻击者能够完全控制Web应用程序后面的数据库服务器。攻击者可以使用SQL注入漏洞绕过应用程序安全措施；可以绕过网页或Web应用程序的身份验证和授权，并检索整个SQL数据库的内容；还可以使用SQL注入来添加，修改和删除数据库中的记录

SQL注入漏洞可能会影响使用SQL数据库（如MySQL，Oracle，SQL Server或其他）的任何网站或Web应用程序。犯罪分子可能会利用它来未经授权访问用户的敏感数据：客户信息，个人数据，商业机密，知识产权等。SQL注入攻击是最古老，最流行，最危险的Web应用程序漏洞之一

如何防止SQL注入攻击？

不要使用动态SQL 避免将用户提供的输入直接放入SQL语句中；最好使用准备好的语句和参数化查询，这样更安全
不要将敏感数据保留在纯文本中加密存储在数据库中的私有/机密数据；这样可以提供了另一级保护，以防攻击者成功地排出敏感数据
限制数据库权限和特权将数据库用户的功能设置为最低要求；这将限制攻击者在设法获取访问权限时可以执行的操作
避免直接向用户显示数据库错误攻击者可以使用这些错误消息来获取有关数据库的信息
对访问数据库的Web应用程序使用Web应用程序防火墙（WAF）这为面向Web的应用程序提供了保护，它可以帮助识别SQL注入尝试；根据设置，它还可以帮助防止SQL注入尝试到达应用程序（以及数据库）
定期测试与数据库交互的Web应用程序这样做可以帮助捕获可能允许SQL注入的新错误或回归
将数据库更新为最新的可用修补程序这可以防止攻击者利用旧版本中存在的已知弱点/错误

<span class="hljs-comment"># -*- coding:utf-8 -*-</span>
<span class="hljs-keyword">import</span> pymysql

name = input(<span class="hljs-string">"账号："</span>)
passwd = input(<span class="hljs-string">"密码："</span>)

db = pymysql.connect(<span class="hljs-string">"8.8.8.8"</span>, <span class="hljs-string">"zutuanxue_com"</span>, <span class="hljs-string">"zutuanxue_com2000"</span>, <span class="hljs-string">"db1"</span>, charset=<span class="hljs-string">"utf8"</span>)
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)

<span class="hljs-keyword">try</span>:
    sql = <span class="hljs-string">"select * from students where name='%s' and passwd='%s';"</span> % (name, passwd)
    print(<span class="hljs-string">"sql语句："</span>, sql)

    cursor.execute(sql)
    res = cursor.fetchall()
    <span class="hljs-keyword">if</span> res:
        print(<span class="hljs-string">"登陆成功"</span>, res)
    <span class="hljs-keyword">else</span>:
        print(<span class="hljs-string">"登陆失败"</span>)
<span class="hljs-keyword">except</span>:
    print(<span class="hljs-string">"查询有误"</span>)

cursor.close()
db.close()

SQL注入代码	注意	说明
li' --	--后有一个空格	用户存在，但是密码错误也查找成功
abc' or 1=1 --	--后有一个空格	用户不存在，但是能查找出所有用户

select * from students where name='li' -- ' and passwd='qwe';

select * from students where name='abc' or 1=1 -- ' and passwd='sfeg';

防注入代码书写

方式1

<span class="hljs-comment"># -*- coding:utf-8 -*-</span>
<span class="hljs-keyword">import</span> pymysql

name = input(<span class="hljs-string">"账号："</span>)
passwd = input(<span class="hljs-string">"密码："</span>)

db = pymysql.connect(<span class="hljs-string">"8.8.8.8"</span>, <span class="hljs-string">"zutuanxue_com"</span>, <span class="hljs-string">"zutuanxue_com2000"</span>, <span class="hljs-string">"db1"</span>, charset=<span class="hljs-string">"utf8"</span>)
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)

<span class="hljs-keyword">try</span>:
    sql = <span class="hljs-string">"select * from students where name=%s and passwd=%s;"</span>

    cursor.execute(sql, [name, passwd])
    res = cursor.fetchall()
    <span class="hljs-keyword">if</span> res:
        print(<span class="hljs-string">"登陆成功"</span>, res)
    <span class="hljs-keyword">else</span>:
        print(<span class="hljs-string">"登陆失败"</span>)
<span class="hljs-keyword">except</span>:
    print(<span class="hljs-string">"查询有误"</span>)

cursor.close()
db.close()

方式2

<span class="hljs-comment"># -*- coding:utf-8 -*-</span>
<span class="hljs-keyword">import</span> pymysql

name = input(<span class="hljs-string">"账号："</span>)
passwd = input(<span class="hljs-string">"密码："</span>)

db = pymysql.connect(<span class="hljs-string">"8.8.8.8"</span>, <span class="hljs-string">"zutuanxue_com"</span>, <span class="hljs-string">"zutuanxue_com2000"</span>, <span class="hljs-string">"db1"</span>, charset=<span class="hljs-string">"utf8"</span>)
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)

<span class="hljs-keyword">try</span>:
    sql = <span class="hljs-string">"select * from students where name=%(name)s and passwd=%(passwd)s;"</span>

    cursor.execute(sql, {<span class="hljs-string">"name"</span>:name, <span class="hljs-string">"passwd"</span>:passwd})
    res = cursor.fetchall()
    <span class="hljs-keyword">if</span> res:
        print(<span class="hljs-string">"登陆成功"</span>, res)
    <span class="hljs-keyword">else</span>:
        print(<span class="hljs-string">"登陆失败"</span>)
<span class="hljs-keyword">except</span>:
    print(<span class="hljs-string">"查询有误"</span>)

cursor.close()
db.close()

四、增加多条数据

<span class="hljs-comment"># -*- coding:utf-8 -*-</span>
<span class="hljs-keyword">import</span> pymysql

db = pymysql.connect(<span class="hljs-string">"8.8.8.8"</span>, <span class="hljs-string">"zutuanxue_com"</span>, <span class="hljs-string">"zutuanxue_com2000"</span>, <span class="hljs-string">"db1"</span>, charset=<span class="hljs-string">"utf8"</span>)
cursor = db.cursor()

<span class="hljs-keyword">try</span>:
    <span class="hljs-comment"># 待执行的SQL语句</span>
    sql = <span class="hljs-string">"insert into students(name,passwd) values(%s,%s);"</span>
    cursor.executemany(sql, [(<span class="hljs-string">"aaa"</span>,<span class="hljs-string">"a"</span>),(<span class="hljs-string">"bbb"</span>,<span class="hljs-string">"b"</span>),(<span class="hljs-string">"ccc"</span>,<span class="hljs-string">"c"</span>)])
    db.commit()
<span class="hljs-keyword">except</span>:
    db.rollback()

cursor.close()
db.close()

五、新插入数据的自增ID

需求：在插入一篇文章后需要给文章插入它需要的图片到媒体表，那么在此时就需要这篇文章的id值作为外键来使用，需要提取出刚刚插入的文章的自增id值

<span class="hljs-comment"># -*- coding:utf-8 -*-</span>
<span class="hljs-keyword">import</span> pymysql

db = pymysql.connect(<span class="hljs-string">"8.8.8.8"</span>, <span class="hljs-string">"zutuanxue_com"</span>, <span class="hljs-string">"zutuanxue_com2000"</span>, <span class="hljs-string">"db1"</span>, charset=<span class="hljs-string">"utf8"</span>)
cursor = db.cursor()

<span class="hljs-keyword">try</span>:
    <span class="hljs-comment"># 待执行的SQL语句</span>
    sql = <span class="hljs-string">"insert into articles(title) values(%s);"</span>
    cursor.executemany(sql, [<span class="hljs-string">"zutuanxue_com"</span>])
    <span class="hljs-comment"># 插入对应的媒体需要得到刚才插入的文章的id号</span>
    print(<span class="hljs-string">"------"</span>, cursor.lastrowid)
    db.commit()
<span class="hljs-keyword">except</span>:
    db.rollback()

cursor.close()
db.close()

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

网站