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
以上就是Python操作MySQL数据库的示例代码的详细内容,更多关于Python操作MySQL数据库的资料请关注其它相关文章!
华山资源网 Design By www.eoogi.com
广告合作:本站广告合作请联系QQ:858582 申请时备注:广告合作(否则不回)
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
华山资源网 Design By www.eoogi.com
暂无评论...
更新日志
2024年09月23日
2024年09月23日
- 华少翌.2005-华少翌【中唱艺能】【WAV+CUE】
- 群星《十二女声Ⅱ》[WAV/CUE/分轨][527MB]
- 大自然音乐系列《漫步在山裡》[WAV/CUE/分轨][502.8MB]
- 大自然音乐系列《星光夜语》[WAV/CUE/分轨][453.3MB]
- 史依弘《史依弘交响乐京剧演唱专辑HQ》头版限量编号[WAV+CUE]
- 曼丽2020《女人三十风继续吹》HQCD[WAV+CUE]
- 曼丽2021《女人三十2雾之恋》HQCD[WAV+CUE]
- 隔壁老樊.2019-我曾【好听音乐】【FLAC分轨】
- 高林生.1993-牵挂你的人是我【白天鹅】【WAV+CUE】
- 洪卓立.2012-找个懂我的女孩(国专)【英皇娱乐】【WAV+CUE】
- 曼丽2022《女人三十3晚秋》HQCD[WAV+CUE]
- 曼丽2018《女人三十4冬天》HQCD[WAV+CUE]
- 柏菲-《好歌30年壹》限量开盘母带ORMCD[低速原抓WAV+CUE]
- 大自然音乐系列《水舞》[WAV/CUE/分轨][424.3MB]
- 大自然音乐系列《星光夜语》[WAV/CUE/分轨][453.3MB]