Python中怎样操作MySQL数据库

Python中怎样操作MySQL数据库，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

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注入攻击

1. username=1 OR 1=1 password=1 OR 1=1

2. 在使用字符串直接拼接时OR之前不管对错，与OR结合都为true

3. 解决方法--预编译（也可以提高速度）

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 事务管理和异常处理

1. 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. 数据库连接池

1. 数据库的连接是昂贵的，一个连接要经过TCP三次握手，四次挥手，而且一台计算机的最大线程数也是有限的

2. 数据库连接池技术就是先创建好连接，再直接拿出来使用

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数据库的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注行业资讯频道，感谢各位的阅读！