pythonå¯ä»¥å©ç¨pymysql模åæä½æ°æ®åºã
ä»ä¹æ¯ PyMySQLï¼
PyMySQL æ¯å¨ Python3.x çæ¬ä¸ç¨äºè¿æ¥ MySQL æå¡å¨çä¸ä¸ªåºï¼Python2ä¸å使ç¨mysqldbã
PyMySQL éµå¾ª Python æ°æ®åº API v2.0 è§èï¼å¹¶å å«äº pure-Python MySQL 客æ·ç«¯åºã
PyMySQL å®è£
å¨ä½¿ç¨ PyMySQL ä¹åï¼æ们éè¦ç¡®ä¿ PyMySQL å·²å®è£ ã
PyMySQL ä¸è½½å°åï¼https://github.com/PyMySQL/PyMySQLã
å¦æè¿æªå®è£ ï¼æ们å¯ä»¥ä½¿ç¨ä»¥ä¸å½ä»¤å®è£ ææ°çç PyMySQLï¼
$ pip3 install PyMySQL
å¦æä½ çç³»ç»ä¸æ¯æ pip å½ä»¤ï¼å¯ä»¥ä½¿ç¨ä»¥ä¸æ¹å¼å®è£ ï¼
1ãä½¿ç¨ git å½ä»¤ä¸è½½å®è£ å å®è£ (ä½ ä¹å¯ä»¥æå¨ä¸è½½)ï¼
$ git clone https://github.com/PyMySQL/PyMySQL$ cd PyMySQL/$ python3 setup.py install
2ãå¦æéè¦å¶å®çæ¬å·ï¼å¯ä»¥ä½¿ç¨ curl å½ä»¤æ¥å®è£ ï¼
$ # X.X 为 PyMySQL ççæ¬å·$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz$ cd PyMySQL*$ python3 setup.py install
$ # ç°å¨ä½ å¯ä»¥å é¤ PyMySQL* ç®å½
注æï¼è¯·ç¡®ä¿æ¨ærootæéæ¥å®è£ ä¸è¿°æ¨¡åã
å®è£ çè¿ç¨ä¸å¯è½ä¼åºç°"ImportError: No module named setuptools"çé误æ示ï¼æææ¯ä½ 没æå®è£ setuptoolsï¼ä½ å¯ä»¥è®¿é®https://pypi.python.org/pypi/setuptools æ¾å°å个系ç»çå®è£ æ¹æ³ã
Linux ç³»ç»å®è£ å®ä¾ï¼
$ wget https://bootstrap.pypa.io/ez_setup.py$ python3 ez_setup.py
æ°æ®åºè¿æ¥
è¿æ¥æ°æ®åºåï¼è¯·å 确认以ä¸äºé¡¹ï¼
æ¨å·²ç»å建äºæ°æ®åº TESTDB.
å¨TESTDBæ°æ®åºä¸æ¨å·²ç»å建äºè¡¨ EMPLOYEE
EMPLOYEE表å段为 FIRST_NAME, LAST_NAME, AGE, SEX å INCOMEã
è¿æ¥æ°æ®åºTESTDB使ç¨çç¨æ·å为 "testuser" ï¼å¯ç 为 "test123",ä½ å¯ä»¥å¯ä»¥èªå·±è®¾å®æè ç´æ¥ä½¿ç¨rootç¨æ·ååå ¶å¯ç ï¼Mysqlæ°æ®åºç¨æ·ææ请使ç¨Grantå½ä»¤ã
å¨ä½ çæºåä¸å·²ç»å®è£ äº Python MySQLdb 模åã
å¦ææ¨å¯¹sqlè¯å¥ä¸çæï¼å¯ä»¥è®¿é®æ们ç SQLåºç¡æç¨
å®ä¾ï¼
以ä¸å®ä¾é¾æ¥ Mysql ç TESTDB æ°æ®åºï¼
å®ä¾(Python 3.0+)
#!/usr/bin/python3
import pymysql
# æå¼æ°æ®åºè¿æ¥db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# ä½¿ç¨ cursor() æ¹æ³å建ä¸ä¸ªæ¸¸æ 对象 cursorcursor = db.cursor()
# ä½¿ç¨ execute() æ¹æ³æ§è¡ SQL æ¥è¯¢ cursor.execute("SELECT VERSION()")
# ä½¿ç¨ fetchone() æ¹æ³è·ååæ¡æ°æ®.data = cursor.fetchone()
print ("Database version : %s " % data)
# å
³éæ°æ®åºè¿æ¥db.close()
æ§è¡ä»¥ä¸èæ¬è¾åºç»æå¦ä¸ï¼
Database version : 5.5.20-logå建æ°æ®åºè¡¨
å¦ææ°æ®åºè¿æ¥åå¨æ们å¯ä»¥ä½¿ç¨execute()æ¹æ³æ¥ä¸ºæ°æ®åºå建表ï¼å¦ä¸æ示å建表EMPLOYEEï¼
å®ä¾(Python 3.0+)
#!/usr/bin/python3
import pymysql
# æå¼æ°æ®åºè¿æ¥db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# ä½¿ç¨ cursor() æ¹æ³å建ä¸ä¸ªæ¸¸æ 对象 cursorcursor = db.cursor()
# ä½¿ç¨ execute() æ¹æ³æ§è¡ SQLï¼å¦æ表åå¨åå é¤cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# 使ç¨é¢å¤çè¯å¥å建表sql = """CREATE TABLE EMPLOYEE (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT )"""
cursor.execute(sql)
# å
³éæ°æ®åºè¿æ¥db.close()
æ°æ®åºæå ¥æä½
以ä¸å®ä¾ä½¿ç¨æ§è¡ SQL INSERT è¯å¥å表 EMPLOYEE æå ¥è®°å½ï¼
å®ä¾(Python 3.0+)
#!/usr/bin/python3
import pymysql
# æå¼æ°æ®åºè¿æ¥db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 使ç¨cursor()æ¹æ³è·åæä½æ¸¸æ cursor = db.cursor()
# SQL æå
¥è¯å¥sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""try: # æ§è¡sqlè¯å¥
cursor.execute(sql)
# æ交å°æ°æ®åºæ§è¡
db.commit()except: # å¦æåçé误ååæ»
db.rollback()
# å
³éæ°æ®åºè¿æ¥db.close()
以ä¸ä¾åä¹å¯ä»¥åæå¦ä¸å½¢å¼ï¼
å®ä¾(Python 3.0+)
#!/usr/bin/python3
import pymysql
# æå¼æ°æ®åºè¿æ¥db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 使ç¨cursor()æ¹æ³è·åæä½æ¸¸æ cursor = db.cursor()
# SQL æå
¥è¯å¥sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
LAST_NAME, AGE, SEX, INCOME) \
VALUES ('%s', '%s', %s, '%s', %s)" % \ ('Mac', 'Mohan', 20, 'M', 2000)try: # æ§è¡sqlè¯å¥
cursor.execute(sql)
# æ§è¡sqlè¯å¥
db.commit()except: # åçé误æ¶åæ»
db.rollback()
# å
³éæ°æ®åºè¿æ¥db.close()
以ä¸ä»£ç 使ç¨åéåSQLè¯å¥ä¸ä¼ éåæ°:
..................................user_id = "test123"password = "password"con.execute('insert into Login values( %s, %s)' % \ (user_id, password))..................................æ°æ®åºæ¥è¯¢æä½
Pythonæ¥è¯¢Mysqlä½¿ç¨ fetchone() æ¹æ³è·ååæ¡æ°æ®, 使ç¨fetchall() æ¹æ³è·åå¤æ¡æ°æ®ã
fetchone(): 该æ¹æ³è·åä¸ä¸ä¸ªæ¥è¯¢ç»æéãç»æéæ¯ä¸ä¸ªå¯¹è±¡
fetchall(): æ¥æ¶å ¨é¨çè¿åç»æè¡.
rowcount: è¿æ¯ä¸ä¸ªåªè¯»å±æ§ï¼å¹¶è¿åæ§è¡execute()æ¹æ³åå½±åçè¡æ°ã
å®ä¾ï¼
æ¥è¯¢EMPLOYEE表ä¸salaryï¼å·¥èµï¼å段大äº1000çæææ°æ®ï¼
å®ä¾(Python 3.0+)
#!/usr/bin/python3
import pymysql
# æå¼æ°æ®åºè¿æ¥db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 使ç¨cursor()æ¹æ³è·åæä½æ¸¸æ cursor = db.cursor()
# SQL æ¥è¯¢è¯å¥sql = "SELECT * FROM EMPLOYEE \
WHERE INCOME > %s" % (1000)try: # æ§è¡SQLè¯å¥
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=%s,sex=%s,income=%s" % \ (fname, lname, age, sex, income ))except: print ("Error: unable to fetch data")
# å
³éæ°æ®åºè¿æ¥db.close()
以ä¸èæ¬æ§è¡ç»æå¦ä¸ï¼
fname=Mac, lname=Mohan, age=20, sex=M, income=2000æ°æ®åºæ´æ°æä½
æ´æ°æä½ç¨äºæ´æ°æ°æ®è¡¨ççæ°æ®ï¼ä»¥ä¸å®ä¾å° TESTDB è¡¨ä¸ SEX 为 'M' ç AGE å段éå¢ 1ï¼
å®ä¾(Python 3.0+)
#!/usr/bin/python3
import pymysql
# æå¼æ°æ®åºè¿æ¥db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 使ç¨cursor()æ¹æ³è·åæä½æ¸¸æ cursor = db.cursor()
# SQL æ´æ°è¯å¥sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')try: # æ§è¡SQLè¯å¥
cursor.execute(sql)
# æ交å°æ°æ®åºæ§è¡
db.commit()except: # åçé误æ¶åæ»
db.rollback()
# å
³éæ°æ®åºè¿æ¥db.close()
å é¤æä½
å é¤æä½ç¨äºå é¤æ°æ®è¡¨ä¸çæ°æ®ï¼ä»¥ä¸å®ä¾æ¼ç¤ºäºå é¤æ°æ®è¡¨ EMPLOYEE ä¸ AGE å¤§äº 20 çæææ°æ®ï¼
å®ä¾(Python 3.0+)
#!/usr/bin/python3
import pymysql
# æå¼æ°æ®åºè¿æ¥db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 使ç¨cursor()æ¹æ³è·åæä½æ¸¸æ cursor = db.cursor()
# SQL å é¤è¯å¥sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)try: # æ§è¡SQLè¯å¥
cursor.execute(sql)
# æ交修æ¹
db.commit()except: # åçé误æ¶åæ»
db.rollback()
# å
³éè¿æ¥db.close()
æ§è¡äºå¡
äºå¡æºå¶å¯ä»¥ç¡®ä¿æ°æ®ä¸è´æ§ã
äºå¡åºè¯¥å ·æ4个å±æ§ï¼ååæ§ãä¸è´æ§ãé离æ§ãæä¹ æ§ãè¿å个å±æ§é常称为ACIDç¹æ§ã
ååæ§ï¼atomicityï¼ãä¸ä¸ªäºå¡æ¯ä¸ä¸ªä¸å¯åå²çå·¥ä½åä½ï¼äºå¡ä¸å æ¬ç诸æä½è¦ä¹é½åï¼è¦ä¹é½ä¸åã
ä¸è´æ§ï¼consistencyï¼ãäºå¡å¿ é¡»æ¯ä½¿æ°æ®åºä»ä¸ä¸ªä¸è´æ§ç¶æåå°å¦ä¸ä¸ªä¸è´æ§ç¶æãä¸è´æ§ä¸ååæ§æ¯å¯åç¸å ³çã
é离æ§ï¼isolationï¼ãä¸ä¸ªäºå¡çæ§è¡ä¸è½è¢«å ¶ä»äºå¡å¹²æ°ãå³ä¸ä¸ªäºå¡å é¨çæä½å使ç¨çæ°æ®å¯¹å¹¶åçå ¶ä»äºå¡æ¯é离çï¼å¹¶åæ§è¡çå个äºå¡ä¹é´ä¸è½äºç¸å¹²æ°ã
æä¹ æ§ï¼durabilityï¼ãæç»æ§ä¹ç§°æ°¸ä¹ æ§ï¼permanenceï¼ï¼æä¸ä¸ªäºå¡ä¸æ¦æ交ï¼å®å¯¹æ°æ®åºä¸æ°æ®çæ¹åå°±åºè¯¥æ¯æ°¸ä¹ æ§çãæ¥ä¸æ¥çå ¶ä»æä½ææ éä¸åºè¯¥å¯¹å ¶æä»»ä½å½±åã
Python DB API 2.0 çäºå¡æä¾äºä¸¤ä¸ªæ¹æ³ commit æ rollbackã
å®ä¾
å®ä¾(Python 3.0+)
# SQLå é¤è®°å½è¯å¥sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)try: # æ§è¡SQLè¯å¥
cursor.execute(sql)
# åæ°æ®åºæ交
db.commit()except: # åçé误æ¶åæ»
db.rollback()
对äºæ¯æäºå¡çæ°æ®åºï¼ å¨Pythonæ°æ®åºç¼ç¨ä¸ï¼å½æ¸¸æ 建ç«ä¹æ¶ï¼å°±èªå¨å¼å§äºä¸ä¸ªéå½¢çæ°æ®åºäºå¡ã
commit()æ¹æ³æ¸¸æ çæææ´æ°æä½ï¼rollbackï¼ï¼æ¹æ³åæ»å½å游æ çæææä½ãæ¯ä¸ä¸ªæ¹æ³é½å¼å§äºä¸ä¸ªæ°çäºå¡ã
é误å¤ç
DB APIä¸å®ä¹äºä¸äºæ°æ®åºæä½çé误åå¼å¸¸ï¼ä¸è¡¨ååºäºè¿äºé误åå¼å¸¸:
å¼å¸¸
æè¿°
Warning å½æ严éè¦åæ¶è§¦åï¼ä¾å¦æå ¥æ°æ®æ¯è¢«æªæççãå¿ é¡»æ¯ StandardError çåç±»ã
Error è¦å以å¤ææå ¶ä»é误类ãå¿ é¡»æ¯ StandardError çåç±»ã
InterfaceError å½ææ°æ®åºæ¥å£æ¨¡åæ¬èº«çé误ï¼èä¸æ¯æ°æ®åºçé误ï¼åçæ¶è§¦åã å¿ é¡»æ¯Errorçåç±»ã
DatabaseError åæ°æ®åºæå ³çé误åçæ¶è§¦åã å¿ é¡»æ¯Errorçåç±»ã
DataError å½ææ°æ®å¤çæ¶çé误åçæ¶è§¦åï¼ä¾å¦ï¼é¤é¶é误ï¼æ°æ®è¶ èå´ççã å¿ é¡»æ¯DatabaseErrorçåç±»ã
OperationalError æéç¨æ·æ§å¶çï¼èæ¯æä½æ°æ®åºæ¶åççé误ãä¾å¦ï¼è¿æ¥æå¤æå¼ã æ°æ®åºåæªæ¾å°ãäºå¡å¤ç失败ãå ååé é误ççæä½æ°æ®åºæ¯åççé误ã å¿ é¡»æ¯DatabaseErrorçåç±»ã
IntegrityError å®æ´æ§ç¸å ³çé误ï¼ä¾å¦å¤é®æ£æ¥å¤±è´¥çãå¿ é¡»æ¯DatabaseErroråç±»ã
InternalError æ°æ®åºçå é¨é误ï¼ä¾å¦æ¸¸æ ï¼cursorï¼å¤±æäºãäºå¡åæ¥å¤±è´¥ççã å¿ é¡»æ¯DatabaseErroråç±»ã
ProgrammingError ç¨åºé误ï¼ä¾å¦æ°æ®è¡¨ï¼tableï¼æ²¡æ¾å°æå·²åå¨ãSQLè¯å¥è¯æ³é误ã åæ°æ°éé误ççãå¿ é¡»æ¯DatabaseErrorçåç±»ã
NotSupportedError ä¸æ¯æé误ï¼æ使ç¨äºæ°æ®åºä¸æ¯æçå½æ°æAPIçãä¾å¦å¨è¿æ¥å¯¹è±¡ä¸ 使ç¨.rollback()å½æ°ï¼ç¶èæ°æ®åºå¹¶ä¸æ¯æäºå¡æè äºå¡å·²å ³éã å¿ é¡»æ¯DatabaseErrorçåç±»ã