Python操作MySQL数据库详解

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Python操作MySQL数据库详解，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3908字，纯文字阅读大概需要6分钟。

内容图文

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

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数据库详解

标签：tab   type   not   tran   manager   nec   nal   操作   into   

本文系统来源：https://www.cnblogs.com/zwhy8/p/13293335.html

内容总结

以上是互联网集市为您收集整理的Python操作MySQL数据库详解全部内容，希望文章能够帮你解决Python操作MySQL数据库详解所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端