Python操作MySQL数据库的示例代码
作者:吃着东西不想停 时间:2024-01-29 03:55:09
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)
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:
<class 'mysql.connector.cursor_cext.CMySQLCursor'>
(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
解决方法——预编译(也可以提高速度)
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 con
executemany() 反复执行一条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 con
2. 数据库连接池
数据库的连接是昂贵的,一个连接要经过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
来源:https://www.cnblogs.com/zwhy8/archive/2020/07/13/13293335.html
标签:Python,操作,MySQL,数据库
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
一文轻松掌握python语言命名规范规则
2023-03-23 02:11:43
Git原理和常用操作
2023-02-01 23:55:55
![](https://img.aspxhome.com/file/2023/7/123737_0s.png)
MySQL环境下导入数据时是否需要禁用索引
2009-01-04 12:42:00
利用Python获取赶集网招聘信息前篇
2021-09-23 12:53:36
python中图像通道分离与合并实例
2021-04-02 00:09:48
wordpress安装过程中遇到中文乱码的处理方法
2023-11-15 12:24:52
js实现ajax分页完整实例
2024-05-21 10:12:49
django基础学习之send_mail功能
2023-04-01 00:16:53
php中json 序列化为 [] 的弊端
2023-05-25 00:14:30
SQL Server连接失败错误及解决
2008-01-28 21:09:00
![](https://img.aspxhome.com/file/UploadPic/20081/28/2008128211959685s.jpg)
详解利用Python scipy.signal.filtfilt() 实现信号滤波
2022-09-23 21:23:03
Python 爬虫学习笔记之正则表达式
2021-07-02 01:50:55
![](https://img.aspxhome.com/file/2023/7/108037_0s.png)
SQL2000中的默认sa帐号的修改与删除方法
2024-01-22 22:54:59
python tensorflow基于cnn实现手写数字识别
2023-05-09 06:22:06
Python中for循环和while循环的基本使用方法
2021-02-21 01:29:48
ASP四级连动下拉列表程序段
2009-07-03 15:33:00
判断 iframe 是否加载完成的完美方法
2009-09-24 13:35:00
python将Dataframe格式的数据写入opengauss数据库并查询
2024-01-12 19:35:28
![](https://img.aspxhome.com/file/2023/7/102727_0s.png)
JavaScript定时器实现的原理分析
2023-08-23 10:24:16
![](https://img.aspxhome.com/file/2023/4/56094_0s.jpg)
Django项目中表的查询的操作
2023-04-23 10:22:23