這篇文章主要講解了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)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.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 conexecutemany() 反復執行一條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 con2. 數據庫連接池
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數據庫有進一步的了解，如果還想學習更多內容，歡迎關注億速云行業資訊頻道。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
