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數據庫的資料請關注好吧啦網其它相關文章！