æ¬æä¾ç¶æ¯å¨ Jupyter notebook ä¸è¿è¡è¿è¡çãé常好ç¨ç Python IDEæå¾å¤ï¼ä¾å¦PyCharmï¼Anacondaï¼Vimï¼PyDevï¼VisualStudioï¼VSCodeï¼jupyterï¼Wingçå¾ ï¼åæä¼å¿ï¼ä¸»è¦æ¯æ ¹æ®èªå·±çéæ±åç±å¥½è¿è¡éæ©ä½¿ç¨ãä¹åä¼ä»ç»ä¸ä¸æ使ç¨ç VSCodeï¼éæ©å®æ²¡å«çåå ï¼ä¸»è¦æ¯å®è£ å æ¯è¾å°ï¼åæ¶ä¹è¿æ¯è¾å¥½ç¨ï¼å¦ä¹ ææ¬ä¸é«ã
Python æ åçæ°æ®åºæ¥å£æ¯ Python DB-APIï¼Python DB-API为å¼å人åæä¾äºæ°æ®åºåºç¨ç¼ç¨æ¥å£ã
Python æ°æ®åº API æ¯æå¤ç§æ°æ®åºæå¡å¨ï¼
- GadFly
- mSQL
- MySQL
- PostgreSQL
- Microsoft SQL Server 2000
- Informix
- Interbase
- Oracle
- Sybase
- SQLite
è¿æ¯å¯ç¨ç Python æ°æ®åºæ¥å£å表 - Python æ°æ®åºæ¥å£å APIãå¿ é¡»ä¸ºæ¯ä¸ªéè¦è®¿é®çæ°æ®åºä¸è½½åç¬ç DB API 模åãä¾å¦ï¼å¦æéè¦è®¿é® Oracle æ°æ®åºå MySQL æ°æ®åºï¼åå¿ é¡»åæ¶ä¸è½½ Oracle å MySQL æ°æ®åºæ¨¡åã
DB API 为尽å¯è½ä½¿ç¨ Python ç»æåè¯æ³å¤çæ°æ®åºæä¾äºæä½æ åã该 API å æ¬ä»¥ä¸å 容ï¼
- å¯¼å ¥ API 模å
- è·åä¸æ°æ®åºçè¿æ¥
- ååº SQL è¯å¥ååå¨è¿ç¨
- å ³éè¿æ¥
Python é»è®¤å ç½®äºå¯¹ SQLite çæ¯æã
æ¬æå°ä½¿ç¨ PyMySQL 模åè¿è¡MySQLæ°æ®åºç访é®ã
PyMySQL ä»ç»
PyMySQL æ¯ä¸ä¸ªç¨äºä» Python è¿æ¥å° MySQL æ°æ®åºæå¡å¨çæ¥å£ãå®å®ç°äº Python æ°æ®åº API v2.0 并å å«ä¸ä¸ªçº¯ Python MySQL 客æ·ç«¯åºã PyMySQL çç®æ æ¯æ为 MySQLdb çç´æ¥æ¿ä»£åã
å®è£ PyMySQL
PyPI ä¸æä¾äºææ°ç稳å®PyMySQLçæ¬ï¼å¯ä»¥ä½¿ç¨ pip å®è£ ï¼
pip install pymysql
è¿æ¥æ°æ®åº
å¨è¿æ¥å° MySQL æ°æ®åºä¹åï¼è¯·ç¡®ä¿ä»¥ä¸å ç¹ï¼
- å·²ç»å建äºä¸ä¸ªæ°æ®åº TESTDB
- å·²ç»å¨ TESTDB ä¸å建äºä¸ä¸ªè¡¨ USER
- 表å å« FIRST_NAMEãLAST_NAMEãAGEãSEX å INCOME å段
- 设置ç¨æ·IDâtestâåå¯ç â12345â访é®TESTDBã
- PyMySQL 模å å·²æ£ç¡®å®è£ å¨æºå¨ä¸ï¼äºè§£å ¶åºæ¬ç¨æ³
- å·²ç»äºè§£ MySQL åºç¡ç¥è¯
é®é¢è§£å³
注æï¼
ä¸å®è¦æ³¨æï¼my.ini æ件ä¸mysqlçå®è£ è·¯å¾æ¯å¦æ£ç¡®ã
åºç°é误ï¼RuntimeError: âcryptographyâ package is required for sha256_password or caching_sha2_password auth methods
解å³ï¼
1.å®è£ cryptographyï¼ pip install cryptography
2.éå¯ MySQL
3.éå¯ jupyter notebook
ä¾å¦ï¼
import pymysql
# æå¼æ°æ®åºè¿æ¥
conn = pymysql.connect( # èµå¼ç» connè¿æ¥å¯¹è±¡
host='localhost', # æ¬å°å°å
port=3306, # é»è®¤ç«¯å£
user='test', # ç¨æ·å
password='12345', # å¯ç
database='TESTDB', # è¿æ¥æ°æ®åºå称
charset='utf8mb4' # ç¼ç
)
# 产ç游æ 对象
cursor = conn.cursor()
# æ§è¡SQLè¯å¥
cursor.execute("SELECT VERSION()")
# è·åä¸è¡æ°æ®
data = cursor.fetchone()
print ("Database version : %s " % data)
# å
³éæ°æ®åºè¿æ¥
conn.close()
#è¿è¡ç»æ
Database version : 8.0.32
å¦æä¸æ°æ®æºå»ºç«äºè¿æ¥ï¼åè¿åä¸ä¸ªè¿æ¥å¯¹è±¡å¹¶ä¿åå° conn ä¸ä»¥ä¾è¿ä¸æ¥ä½¿ç¨ï¼å¦å conn 设置为 Noneãæ¥ä¸æ¥ï¼conn 对象ç¨äºå建ä¸ä¸ªæ¸¸æ 对象ï¼è¯¥å¯¹è±¡åç¨äºæ§è¡ SQL æ¥è¯¢ãæåï¼å¨åºæ¥ä¹åï¼å®ç¡®ä¿å ³éæ°æ®åºè¿æ¥å¹¶éæ¾èµæºã
å¦æè¿æ®µä»£ç è½å¤æ£å¸¸è¿è¡ç»æãé£ä¹è¡¨ç¤º MySql å·²ç»å¯ä»¥æ£å¸¸ä½¿ç¨äºã
å建æ°æ®åºè¡¨
建ç«æ°æ®åºè¿æ¥åï¼å°±å¯ä»¥ä½¿ç¨å·²å建游æ çæ§è¡æ¹æ³å¨æ°æ®åºè¡¨ä¸å建表æè®°å½ã
ä¾å¦ï¼å建USER表
æ°æ®æå ¥æä½
æ°æ®æå ¥æä½ãå°è®°å½å建å°æ°æ®åºè¡¨ä¸æ¶ï¼éè¦ INSERT æä½ãä¾å¦æ§è¡ SQL INSERT è¯å¥ä»¥å¨ EMPLOYEE 表ä¸å建ä¸æ¡è®°å½ï¼
import pymysql
# æå¼é¾æ¥
conn = pymysql.connect(host="localhost",user="test",password="12345",database="TESTDB" )
cursor = conn.cursor()
# å¦æ该表已åå¨ï¼å
å é¤
cursor.execute("DROP TABLE IF EXISTS USER")
# sql è¯å¥ï¼å建æ°æ®åºè¡¨
sql = """CREATE TABLE USER (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT )"""
cursor.execute(sql)
#sql è¯å¥ï¼æå
¥ä¸è¡æ°æ®
sql = """INSERT INTO USER(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('John', 'Mac', 20, 'M', 2000)"""
try:
# æ§è¡sqlè¯å¥
cursor.execute(sql)
# å°ååæ交å°æ°æ®åº
conn.commit()
except:
# å¦æåçå¼å¸¸è¿è¡åæ»
conn.rollback()
# å
³éè¿æ¥
conn.close()
ä¹å¯ä»¥ç¨ç´æ¥ä¼ éåæ°çæ¹å¼ï¼æ¼åsqlè¯å¥
user_id = "test123"
password = "password"
con.execute('insert into Login values("%s", "%s")' % (user_id, password))
æ°æ®è¡¨è¯»æä½
建ç«æ°æ®åºè¿æ¥åï¼å°±å¯ä»¥æ¥è¯¢è¯¥æ°æ®åºäºãå¯ä»¥ä½¿ç¨ fetchone() æ¹æ³è·åå个记å½æä½¿ç¨ fetchall() æ¹æ³ä»æ°æ®åºè¡¨ä¸è·åå¤ä¸ªå¼ã
- fetchone() - å®è·åæ¥è¯¢ç»æéçä¸ä¸è¡ãç»æéæ¯ä½¿ç¨æ¸¸æ 对象æ¥è¯¢è¡¨æ¶è¿åç对象ã
- fetchall() - å®è·åç»æéä¸çææè¡ãå¦æå·²ç»ä»ç»æéä¸æåäºä¸äºè¡ï¼åå®ä¼ä»ç»æéä¸æ£ç´¢å©ä½çè¡ã
- rowcount -- è¿æ¯ä¸ä¸ªåªè¯»å±æ§ï¼è¿åå execute() æ¹æ³å½±åçè¡æ°ã
import pymysql
conn = pymysql.connect(host="localhost",user="test",password="12345",database="TESTDB" )
cursor = conn.cursor()
sql = "SELECT * FROM EMPLOYEE WHERE INCOME > '%d'" % (1000)
try:
cursor.execute(sql)
# è·åæææ°æ®
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4]
print ("fname = %s,lname = %s,age = %d,sex = %s,income = %d" % (fname, lname, age, sex, income ))
except:
print ("Error: unable to fetch data")
conn.close()
#è¿è¡ç»æ
fname = John,lname = Mac,age = 20,sex = M,income = 2000
å ³äºæ¸¸æ cursor
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # çæä¸ä¸ªæ¸¸æ 对象ï¼è®©æ°æ®èªå¨ç»ç»æåå ¸
cursor.scroll(1, 'relative') # ç¸å¯¹å½åä½ç½®ç§»å¨(ç¸å¯¹ç§»å¨)
cursor.scroll(0, 'absolute') # ç¸å¯¹æ°æ®å¼å¤´ä½ç½®ç§»å¨(ç»å¯¹ç§»å¨)
execute() è¿åå¼æ¯æ§è¡SQLè¯å¥ä¹ååå½±åçè¡æ°
fetchall() è·åææçç»æ
fetchone() è·åç»æé第ä¸ä¸ªç»æ
fetchmany(n) æ¬å·å å¯ä»¥æå®è·åå 个ç»æé
é²SQLæ³¨å ¥æ»å»
å½ SQL è¯å¥ç´æ¥è¿è¡æ¼æ¥æ¶ï¼å®¹æé æSQLæ³¨å ¥ï¼ä¸ç¨éªè¯ç¨æ·åæåå¯ç ï¼ä¾å¦
sql = "select * from userinfo where name='zhangsan' -- haha' and pwd='12345' "
sql = "select * from userinfo where name='zhangsan' or 1=1 -- heihei' and pwd='12345' "
è¿æ¯å©ç¨ä¸äºç¹æ®ç¬¦å·çç»å产çäºç¹æ®çå«ä¹ä»èéè±äºæ£å¸¸çä¸å¡é»è¾ã
解å³åæ³
é对ç¨æ·è¾å ¥çæ°æ®ä¸è¦èªå·±å¤ç 交ç»ä¸é¨çæ¹æ³èªå¨è¿æ»¤ãä¾å¦
sql = "select * from userinfo where name=%s and pwd=%s"
cursor.execute(sql, (username, password)) # èªå¨è¯å«%s 并èªå¨è¿æ»¤åç§ç¬¦å æåå并æ°æ®
executeæ¹æ³ï¼èªå¨å° ç¨æ·ååå¯ç æ¾å¨å¯¹åºç%så ï¼å¹¶ä¸æ¾ä¹åä¼èªå¨å¯¹ç¨æ·ååå¯ç åç¹æ®ç¬¦å·çæ ¡éªï¼ç¡®ä¿å®å ¨æ§ã
æ´æ°æ°æ®æä½
UPDATE 对任ä½æ°æ®åºçæä½æå³çæ´æ°ä¸æ¡æå¤æ¡è®°å½ã
import pymysql
conn = pymysql.connect(host="localhost",user="test",password="12345",database="TESTDB" )
cursor = conn.cursor()
sql = "UPDATE EMPLOYEE SET INCOME = INCOME + 500 WHERE SEX = '%c'" % ('M')
try:
cursor.execute(sql)
conn.commit()
except:
conn.rollback()
conn.close()
å é¤æ°æ®æä½
æ§è¡ DELETE æä½ï¼å¯ä»¥ä»æ°æ®åºä¸å é¤æäºè®°å½ãè¦æ§è¡ç sql è¯å¥ï¼
sql = "DELETE FROM EMPLOYEE WHERE AGE >= '%d'" % (20)
äºå¡æä½
å¡æ¯ä¸ç§ç¡®ä¿æ°æ®ä¸è´æ§çæºå¶ãå ·æ以ä¸å个å±æ§ï¼
- ååæ§ - 交æå®ææä»ä¹é½æ²¡æåçã
- ä¸è´æ§ - äºå¡å¿ 须以ä¸è´çç¶æå¼å§å¹¶ä½¿ç³»ç»ä¿æä¸è´çç¶æã
- é离 - 交æçä¸é´ç»æå¨å½å交æä¹å¤ä¸å¯è§ã
- æä¹ æ§ - ä¸æ¦æ交äºå¡ï¼å³ä½¿å¨ç³»ç»åºç°æ éåï¼ææä¹ä¼æä¹ ã
Python DB API 2.0 æä¾äºä¸¤ç§æ¹æ³æ¥æ交æåæ»äºå¡ãä¾å¦ï¼
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
# æ§è¡sqlè¯å¥
cursor.execute(sql)
# å°ååæ交å°æ°æ®åº
db.commit()
except:
# åºéæ¶è¿è¡åæ»
db.rollback()
æ交 Commit æä½
Commit æ¯ä¸ä¸ªæä½ï¼å®åæ°æ®åºååºç»¿è²ä¿¡å·ä»¥å®ææ´æ¹ï¼å¹¶ä¸å¨æ¤æä½ä¹åï¼æ æ³è¿åä»»ä½æ´æ¹ã
åæ»æä½
å¦æ对ä¸é¡¹æå¤é¡¹æ´æ¹ä¸æ»¡æ并ä¸æ³è¦å®å ¨è¿åè¿äºæ´æ¹ï¼æè æä¸é¨åæä½åºç°é误ï¼éè¦å ¨é¨è¿åå°åæ¥ç¶æï¼å¯ä»¥ä½¿ç¨ rollback() æ¹æ³ã
æå¼æ°æ®åºè¿æ¥
ä½¿ç¨ close() æ¹æ³æå¼æ°æ®åºè¿æ¥ï¼éæ¾èµæºã
é误å¤ç
é误çæ¥æºæå¾å¤ãä¾å¦æ§è¡ç SQL è¯å¥ä¸çè¯æ³é误ãè¿æ¥å¤±è´¥æ为已åæ¶æå·²å®æçè¯å¥å¥æè°ç¨ fetch æ¹æ³çã
DB API å®ä¹äºæ¯ä¸ªæ°æ®åºæ¨¡åä¸å¿ é¡»åå¨çä¸äºé误ã
- Warning è¦å ç¨äºéè´å½é®é¢ãå¿ é¡»ç»§æ¿ StandardErrorã
- Error é误 é误çåºç±»ãå¿ é¡»ç»§æ¿ StandardErrorã
- InterfaceError æ¥å£é误 ç¨äºæ°æ®åºæ¨¡åä¸çé误ï¼èä¸æ¯æ°æ®åºæ¬èº«ãå¿ é¡»åç±»é误ã
- DatabaseError æ°æ®åºé误 ç¨äºæ°æ®åºä¸çé误ãå¿ é¡»åç±»é误ã
- DataError æ°æ®é误 DatabaseError çåç±»ï¼æçæ¯æ°æ®ä¸çé误ã
- OperationalError æä½é误 DatabaseError çåç±»ï¼æçæ¯è¯¸å¦ä¸æ°æ®åºçè¿æ¥ä¸¢å¤±ä¹ç±»çé误ãè¿äºé误é常ä¸å¨ Python èæ¬ç¼åè çæ§å¶èå´å ã
- IntegrityError å®æ´æ§é误 DatabaseError çåç±»ï¼ç¨äºä¼ç ´åå ³ç³»å®æ´æ§çæ åµï¼ä¾å¦å¯ä¸æ§çº¦ææå¤é®ã
- InternalError å é¨é误 DatabaseError çåç±»ï¼æçæ¯æ°æ®åºæ¨¡åå é¨çé误ï¼ä¾å¦æ¸¸æ ä¸åå¤äºæ´»å¨ç¶æã
- ProgrammingError ç¼ç¨é误 DatabaseError çåç±»ï¼æçæ¯é误ç表ååå ¶ä»å 为ç¼ç¨åå åççé误ã
- NotSupportedError ä¸æ¯æé误 DatabaseError çåç±»ï¼æçæ¯å°è¯è°ç¨ä¸åæ¯æçåè½ã
注æï¼å¨ä½¿ç¨ Python ç¨åºå¤çè¿äºé误ä¹åï¼è¦ç¡®ä¿æå®è£ ç MySQL æ°æ®åº æ¯æè¿äºå¼å¸¸ã
å¼å ¥traceback模åï¼è·è¸ªé误
å¯ä»¥å¼å ¥pythonä¸çtraceback模åï¼æ¥è·è¸ªé误ãç¨åºéè¦ import tracebackãæä¸ç§æ¹å¼è·è¸ªé误ãä¾å¦ï¼
import pymysql
import traceback
import sys
conn = self.getConnect()
# 使ç¨cursor() æ¹æ³å建ä¸ä¸ªæ¸¸æ 对象 cursor
cursor = conn.cursor()
try:
# æ§è¡sqlè¯å¥
cursor.execute(sql)
result = cursor.fetchone()
except: #æ¹æ³ä¸ï¼éç¨traceback模åæ¥çå¼å¸¸
#è¾åºå¼å¸¸ä¿¡æ¯
traceback.print_exc()
# å¦æåçå¼å¸¸ï¼ååæ»
db.rollback()
try:
# æ§è¡sqlè¯å¥
cursor.execute(sql)
results = cursor.fetchall()
except: #æ¹æ³äºï¼éç¨sys模åå溯æåçå¼å¸¸
#è¾åºå¼å¸¸ä¿¡æ¯
info = sys.exc_info()
print( info[0], ":", info[1])
# å¦æåçå¼å¸¸ï¼ååæ»
db.rollback()
try:
# æ§è¡sqlè¯å¥
cursor.execute(sql)
db.commit()
except: #å¦ææ³æè¿äºå¼å¸¸ä¿åå°ä¸ä¸ªæ¥å¿æ件ä¸ï¼æ¥åæè¿äºå¼å¸¸
#å°é误æ¥å¿è¾å
¥å°ç®å½æ件ä¸
f = open("c:\\log.txt", 'a')
traceback.print_exc(file=f)
f.flush()
f.close()
# å¦æåçå¼å¸¸ï¼ååæ»
db.rollback()