Python中怎样操作MySQL数据库，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。
创新互联公司是一家企业级云计算解决方案提供商,超15年IDC数据中心运营经验。主营GPU显卡服务器，站群服务器，服务器托管雅安，海外高防服务器，服务器机柜，动态拨号VPS，海外云手机，海外云服务器，海外服务器租用托管等。
1. MySQL Connector
1.1 创建连接
import mysql.connector
config={
"host":"localhost","port":"3306",
"user":"root","password":"password",
"database":"demo"
}
con=mysql.connector.connect(**config) import mysql.connector
config={
"host":"localhost","port":"3306",
"user":"root","password":"password",
"database":"demo"
}
con=mysql.connector.connect(**config)点击加群即可免费获取Python资料
资料就在群文件里等你来拿
1.2 Cursor
import mysql.connector
con=mysql.connector.connect(
host="localhost",port="3306",
user="root",password="password",
database="demo"
)
cursor=con.cursor()
sql="SELECT empno,job,sal FROM t_bonus;"
cursor.execute(sql)
print(type(cursor))
for i in cursor:
print(i)
con.close()
Result:
(7369, 'CLERK', Decimal('8000.00'))
(7499, 'SALESMAN', Decimal('1600.00'))
(7521, 'SALESMAN', Decimal('1250.00'))
(7566, 'MANAGER', Decimal('2975.00'))
(7654, 'SALESMAN', Decimal('1250.00'))
(7698, 'MANAGER', Decimal('2850.00'))
(7782, 'MANAGER', Decimal('2450.00'))
(7788, 'ANALYST', Decimal('3000.00'))
(7839, 'PRESIDENT', Decimal('5000.00'))
(7844, 'SALESMAN', Decimal('1500.00'))
(7900, 'CLERK', Decimal('950.00'))
(7902, 'ANALYST', Decimal('3000.00'))
(7934, 'CLERK', Decimal('1300.00'))
1.3 SQL注入攻击
username=1 OR 1=1 password=1 OR 1=1
在使用字符串直接拼接时OR之前不管对错，与OR结合都为true
解决方法——预编译（也可以提高速度）
username="1 OR 1=1"
password="1 OR 1=1"
sql="SELECT COUNT(*) FROM t_user WHERE username=%s AND AES_DECRYPT(UNHEX(password),'helloWorld')=%s"
cursor.execute(sql,(username,password))
print(cursor.fetchone()[0])
1.4 事务管理和异常处理
sql连接和使用异常处理异常
import mysql.connector
try:
con=mysql.connector.connect(
host="localhost",port="3306",
user="root",password="password",
database="demo"
)
con.start_transaction()
cursor=con.cursor()
sql="INSERT INTO t_dept(deptno,dname,loc) VALUES(%s,%s,%s);"
cursor.execute(sql,(60,"SALES","HUBAI"))
con.commit()
except Exception as e:
if "con" in dir():
con.rollback()
print(e)
finally:
if "con" in dir():
con.close()
1.5 删除数据
import mysql.connector,mysql.connector.pooling
config={
"host": "localhost", "port": "3306",
"user": "root", "password": "password",
"database": "demo"
}
try:
pool=mysql.connector.pooling.MySQLConnectionPool(**config,pool_size=5)
con=pool.get_connection()
con.start_transaction()
cursor = con.cursor()
sql = "DELETE FROM t_dept WHERE deptno=%s"
cursor.execute(sql, (70,))
con.commit()
except Exception as e:
if "con" in dir():
con.rollback()
print(e)
# do not need to close conexecutemany() 反复执行一条SQL语句
import mysql.connector,mysql.connector.pooling
config={
"host": "localhost", "port": "3306",
"user": "root", "password": "password",
"database": "demo"
}
try:
pool=mysql.connector.pooling.MySQLConnectionPool(**config,pool_size=5)
con=pool.get_connection()
con.start_transaction()
cursor = con.cursor()
sql = "INSERT INTO t_dept(deptno,dname,loc) VALUES(%s,%s,%s);"
date=[[70,"SALES","BEIJING"],[80,"ACTOR","SHANGHAI"]]
cursor.executemany(sql, date)
con.commit()
except Exception as e:
if "con" in dir():
con.rollback()
print(e)
# do not need to close con2. 数据库连接池
数据库的连接是昂贵的，一个连接要经过TCP三次握手，四次挥手，而且一台计算机的最大线程数也是有限的
数据库连接池技术就是先创建好连接，再直接拿出来使用
import mysql.connector,mysql.connector.pooling
config={
"host": "localhost", "port": "3306",
"user": "root", "password": "password",
"database": "demo"
}
try:
pool=mysql.connector.pooling.MySQLConnectionPool(**config,pool_size=5)
con=pool.get_connection()
con.start_transaction()
cursor = con.cursor()
sql = "INSERT INTO t_dept(deptno,dname,loc) VALUES(%s,%s,%s);"
cursor.execute(sql, (70, "SALES", "HUBAI"))
con.commit()
except Exception as e:
if "con" in dir():
con.rollback()
print(e)
# do not need to close con看完上述内容，你们掌握Python中怎样操作MySQL数据库的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注创新互联行业资讯频道，感谢各位的阅读！
网站栏目：Python中怎样操作MySQL数据库
URL网址：
http://bjjierui.cn/article/jijhep.html
