Python使用mysql-connector连接数据库

Pyhton版本mysql-connector的使用

巴啦啦拉粑粑

9063人浏览 · 2022-05-11 02:33:44

巴啦啦拉粑粑 · 2022-05-11 02:33:44 发布

1.mysql-connector和MySQLdb的区别

mysql-connector和MySQLdb都是python连接数据库的包，二者区别如下：

mysql-connector是一个Python模块，它在Python中重新实现MySQL协议，它比较慢，但不需要C库，因此更便携。

MySQLdb是一个C模块，它使用MySQL客户端库中的MySQL协议实现相链接，它更快，但是需要C库才能工作。

这里选择使用mysql-connector，官方文档连接：https://dev.mysql.com/doc/connector-python/en/

2. 安装

pip i mysql-connector

3. 连接数据库connnection

import mysql.connector
# 获取连接
connection=mysql.connector.connect(
	host='127.0.0.1',
	user='root',
	passwd='123456',
	db=databases_name,
	charset='utf8'
	#autocommit=True 开启自动提交后，增删改操作无需手动调用connection.commit()
)

4. 获取游标cur

语句的执行都得依靠游标cur,可以连接数据库后得到的connection获取游标

# 获取连接
connection=mysql.connector.connect(
	host='127.0.0.1',
	user='root',
	passwd='123456',
	db=databases_name,
	charset='utf8'
)
# 获取游标
cur=connection.cursor()

5. 使用元素传参，进行增删改查

游标cur有一个方法execute(SQL语句，元祖/字典)，使用占位符%s防止SQL注入，把SQL语句和所需的参数（WHERE条件/判断条件）以元祖/字典的形式传进去，元祖的每个元素与%s的顺序和个数一一对应。增删改操作后需要进行调用connection.commit()

5.1 查询SELECT

import mysql.connector
# 获取连接
connection=mysql.connector.connect(
	host='127.0.0.1',
	user='root',
	passwd='123456',
	db=databases_name,
	charset='utf8'
)
# 获取游标
cur=connection.cursor()
# 执行查询语句，无需查询参数
cur.execute("SELECT * FROM table_name")
# 执行查询语句，使用占位符,注意参数是元祖形式
id=1
cur.execute("SELECT * FROM table1 WHERE id=%s",(id,))
# 获取查询结果集
result1=cur.fetchone() #获取一条 (2, bytearray(b'Mike'), 20,datetime.datetime(2022, 5, 11, 0, 53, 57)) 
# 将字节数组转为String
name=result1[1].decode()
result2=cur.fetchall() #获取全部

值得注意的是fetchone()返回的结果是元祖类型，fetchall()则是列表元祖，即列表中每个元素都是元祖。另外与SQL类型对应，varchar返回的是bytearray，需要进行调用decode()转为String，number正常返回是number，datetime返回datetime.datetime，如打印输出显示datetime.datetime(2022, 5, 11, 0, 53, 57)

5.2 更新UPDATE/删除DELETE

import mysql.connector
# 获取连接
connection=mysql.connector.connect(
	host='127.0.0.1',
	user='root',
	passwd='123456',
	db=databases_name,
	charset='utf8'
)
# 获取游标
cur=connection.cursor()
# 执行更新语句，使用占位符,注意参数是元祖形式
id=1
cur.execute("UPDATE FROM table1 SET name='Mike' WHERE id=%s",(id,))
cur.execute("DELETE FROM table1 WHERE id=%s",(id,))
# 提交修改
connection.commit()

5.3 插入INSERT

import mysql.connector
# 获取连接
connection=mysql.connector.connect(
	host='127.0.0.1',
	user='root',
	passwd='123456',
	db=databases_name,
	charset='utf8'
)
# 获取游标
cur=connection.cursor()
# 执行更新语句，使用占位符,注意参数是元祖形式
cur.execute("INSERT INTO table1 (name,age,birthday) VALUES (%s,%s,%s)",("Mike",20,datetime.datetime(2022, 5, 11, 0, 53, 57)))
# 提交修改
connection.commit()

6. 使用字典传参，进行增删改查

网上大多数教程都只教用元祖代替%s，导致经常要将字典转为元祖再执行语句。其实官方文档这一章节关于cur.execute()的API具体描述中，提到传递字典来代替%s

多数程序前面处理的结果是一个字典，比如描述一个人的信息，如果要使用元祖传参，那么就要调用people.items()获取字典所有的value值的列表，然后再转元祖，再执行语句

peple={
	"name":"Mike",
	"age":20,
	"birthday":datetime.date(2020, 3, 23)
}

可以直接使用字典传参，就免去了多余的转换。格式是%(key)s，即%s换为%(key)s，key是字典中对应的key值。如对应上述字典，则为%(name)s、%(age)s

import mysql.connector
import datetime

peple={
	"name":"Mike",
	"age":20,
	"birthday":datetime.date(2020, 3, 23)
}

# 获取连接
connection=mysql.connector.connect(
	host='127.0.0.1',
	user='root',
	passwd='123456',
	db=databases_name,
	charset='utf8'
)
# 获取游标
cur=connection.cursor()
# 执行查询语句，传入字典，注意格式是%(key)s
cur.execute("SELECT * FROM table1 WHERE name = %(name)s",peple))
# 执行插入语句，传入字典，注意格式是%(key)s
cur.execute("INSERT INTO table1 (name,age,birthday) VALUES (%(name)s,%(age)s,%(birthday)s)",
peple))
# 提交修改
connection.commit()

7. 将同一条语句执行多次

如果要将一个pople的列表插入数据库，那么就需要遍历这个列表，然后一次次地执行cur.execute()效率非常低。使用cur.executemany(SQL,列表元祖)可以一次性执行

data = [
  ('Jane', date(2005, 2, 12)),
  ('Joe', date(2006, 5, 23)),
  ('John', date(2010, 10, 3)),
]
stmt = "INSERT INTO employees (first_name, hire_date) VALUES (%s, %s)"
cursor.executemany(stmt, data)

8. 使用buffered游标

在创建connection时使用buffered=True，则以后创建的游标cur都是缓冲模式的。

# 获取连接
connection=mysql.connector.connect(
	host='127.0.0.1',
	user='root',
	passwd='123456',
	db=databases_name,
	charset='utf8'，
	# 开启缓冲模式
	buffered=True
)
# 获取游标
cur=connection.cursor()

什么是缓冲模式？

其实每次执行语句，游标都会下移，比如fetchall()游标一下子就到最后了，fetchone()则是一步步往下移动，直到末尾位置。同样，执行增/改/删语句则会直接移动到末尾。·

缓冲模式：在每次执行查询(SELECT语句)后，结果集会缓存在内存中，不受游标移动的影响。

假设有这样一个场景，在遍历结果集的同时，根据这条数据的结果，进行UPDATE操作

# 获取连接
connection=mysql.connector.connect(
	host='127.0.0.1',
	user='root',
	passwd='123456',
	db=databases_name,
	charset='utf8'，
	buffered=True
)
# 获取两个buffered模式游标
cur1=connection.cursor()
cur2=connection.cursor()

cur1.execute("SELECT * FROM table1 ")
# 遍历cur1查询结果集
for item in sql.cur1:
    print(item)
    # 如果名字是Mike
    if item[1].decode()=="Mike":
    	#使用cur2根据结果集数据，进行操作
    	cur2.execute("UPDATE table1 SET age=%s",(30,))
        sql.connection.commit()

上述代码有两点注意：

第一，必须开启buffered=True，否则会报mysql.connector.errors.InternalError: Unread result found，因为不是缓冲模式的话，两个游标的操作会相互影响，那么当cur2执行UPDATE后，两个游标直接到最后了，但是SELECT结果集还有数据没遍历完呢，所以直接报Unread

第二，即使开启了buffered=True，也必须是两个游标配合操作。如果从始至终都是cur1查询和更新，那么同样的当执行到UPDATE，游标直接到最后，又是报Unread