å
è±ç¹æ¶é´æ¥è¯´è¯´ä¸ä¸ªç¨åºæä¹åæ°æ®åºè¿è¡äº¤äº
1.åæ°æ®åºå»ºç«è¿æ¥
2.æ§è¡sqlè¯å¥,æ¥æ¶è¿åå¼
3.å
³éæ°æ®åºè¿æ¥
使ç¨MySQLdbä¹è¦éµå¾ªä¸é¢çå æ¥.让æ们ä¸æ¥æ¥çè¿è¡.
1ãMySQLæ°æ®åºè¦ç¨MySQLdb模å,ä½Pythonç¨æ¥é¾æ¥MySQLç第ä¸æ¹åºMySQLdbä¸æ¯æPython3.x
ç¹å«è¯´æï¼æå¨æççµèä¸å®éªæ¶ï¼æçpythonæ¯2.7.2çæ¬ï¼å®è£
对åºçæ¬çMySQLdbä¹åç´æ¥å¯ä»¥è¿è¡ï¼å¹¶ä¸æ°æ®åºè¿æ¥æåï¼æ以å¦æ大家
ä¹åæä¸æ ·é¡ºå©çè¯ï¼ä¸é¢çå°±ä¸éè¦çäºï¼ç´æ¥è·³è¿ï¼ç第2ç¹å¦ä½æ§è¡sqlè¯å¥å³å¯ï¼å¦æå®è£
ä¹ååºç°å¼å¸¸ï¼å¯ä»¥åèä¸ä¸ä¸é¢ç解å³åæ³ã
è¿æ¥çå
³é®æ¯å®è£
MySQLdb模åè¦ä¸è½½ä¸Pythonç¸å¯¹åºççæ¬ï¼
ä¸è½½å¥½åå®è£
ï¼å®ä¼èªå¨æ£æµå°è®¡ç®æºPythonçå®è£
è·¯å¾ï¼å¹¶èªå¨å¡«å模å解åè·¯å¾ï¼æçæ¯ï¼D:\ProgramFiles\ActivePython 2.6.6.17\Lib\site-packages\ï¼ã
ä½è§£åå®æå并ä¸è½ä½¿ç¨ï¼è¿è¦ä¿®æ¹MySQLdb模åä¸çä¸äºæ件ï¼
â .å¨MySQLdbç®å½ä¸ï¼æçæ¯ï¼D:\ProgramFiles\ActivePython 2.6.6.17\Lib\site-packages\MySQLdbï¼æ¾å°__init__.pyï¼
注é第34ã35è¡çfrom setsimport ImmutableSetãclass DBAPISet(ImmutableSet):ï¼å¨åé¢æ·»å class DBAPISet(frozenset):
# from sets import ImmutableSet
# class DBAPISet(ImmutableSet):
class DBAPISet(frozenset):
â¡.æå¼converters.pyï¼
注é第37è¡çfrom sets import BaseSet, Setï¼å°ç¬¬45è¡çreturn Set([ i for i in
s.split(',') ifi ])ä¸çSetæ¹ä¸ºsetï¼åæ ·å°ç¬¬129è¡çSet:
Set2Str,ä¸çSetæ¹ä¸ºsetï¼ä¸è¦ä¿®æ¹Set2Strï¼ï¼å°è¿é就修æ¹å®æ¯äº
2.建ç«æ°æ®åºè¿æ¥
import MySQLdb
conn=MySQLdb.connect(host="localhost",user="root",passwd="sa",db="mytable")
ã
æ¯è¾å¸¸ç¨çåæ°å
æ¬
host: è¿æ¥çæ°æ®åºæå¡å¨ä¸»æºåï¼é»è®¤ä¸ºæ¬å°ä¸»æº(localhost)ã
user:æ°æ®åºç»éå.é»è®¤æ¯å½åç¨æ·.
passwd:æ°æ®åºç»éçç§å¯.é»è®¤ä¸ºç©º.
db:è¦ä½¿ç¨çæ°æ®åºå.没æé»è®¤å¼.
port:MySQLæå¡ä½¿ç¨çTCP端å£.é»è®¤æ¯3306.
connè¿æ¥æ两个éè¦çæ¹æ³commitãæ交æ°å¢åä¿®æ¹ã,rollbackãæ¤éæ°å¢æä¿®æ¹ã
3ãæ§è¡æ°æ®åºæä½
n=cursor.execute(sql,param)
æ们è¦ä½¿ç¨è¿æ¥å¯¹è±¡è·å¾ä¸ä¸ªcursor对象,æ¥ä¸æ¥,æ们ä¼ä½¿ç¨cursoræä¾çæ¹æ³æ¥è¿è¡å·¥ä½.
è¿äºæ¹æ³å
æ¬ä¸¤å¤§ç±»:1.æ§è¡å½ä»¤,2.æ¥æ¶è¿åå¼
cursorç¨æ¥æ§è¡å½ä»¤çæ¹æ³:
ã callproc(self, procname, args):ç¨æ¥æ§è¡åå¨è¿ç¨,æ¥æ¶çåæ°ä¸ºåå¨è¿ç¨åååæ°å表,è¿åå¼ä¸ºåå½±åçè¡æ°
ã execute(self, query, args):æ§è¡åæ¡sqlè¯å¥,æ¥æ¶çåæ°ä¸ºsqlè¯å¥æ¬èº«å使ç¨çåæ°å表,è¿åå¼ä¸ºåå½±åçè¡æ°
ã executemany(self, query, args):æ§è¡åæsqlè¯å¥,ä½æ¯éå¤æ§è¡åæ°å表éçåæ°,è¿åå¼ä¸ºåå½±åçè¡æ°
ã nextset(self):移å¨å°ä¸ä¸ä¸ªç»æé
ã cursorç¨æ¥æ¥æ¶è¿åå¼çæ¹æ³:
ã fetchall(self):æ¥æ¶å
¨é¨çè¿åç»æè¡.
ã fetchmany(self, size=None):æ¥æ¶sizeæ¡è¿åç»æè¡.å¦æsizeçå¼å¤§äºè¿åçç»æè¡çæ°é,åä¼è¿åcursor.arraysizeæ¡æ°æ®.
ã fetchone(self):è¿åä¸æ¡ç»æè¡.
ã scroll(self, value, mode='relative'):移å¨æéå°æä¸è¡.å¦æmode='relative',å表示ä»å½åæå¨è¡ç§»å¨valueæ¡,å¦æmode='absolute',å表示ä»ç»æéç第ä¸è¡ç§»å¨valueæ¡.
ä¸é¢ç代ç æ¯ä¸ä¸ªå®æ´çä¾å.
#使ç¨sqlè¯å¥,è¿éè¦æ¥æ¶çåæ°é½ç¨%så ä½ç¬¦.è¦æ³¨æçæ¯,æ è®ºä½ è¦æå
¥çæ°æ®æ¯ä»ä¹ç±»å,å ä½ç¬¦æ°¸è¿é½è¦ç¨%s
sql="insert into cdinfo values(%s,%s,%s,%s,%s)"
#paramåºè¯¥ä¸ºtupleæè
list
param=(title,singer,imgurl,url,alpha)
#æ§è¡,å¦ææå,nçå¼ä¸º1
n=cursor.execute(sql,param)
#åæ¥æ§è¡ä¸ä¸ªæ¥è¯¢çæä½
cursor.execute("select * from cdinfo")
#æ们使ç¨äºfetchallè¿ä¸ªæ¹æ³.è¿æ ·,cdséä¿åçå°ä¼æ¯æ¥è¯¢è¿åçå
¨é¨ç»æ.æ¯æ¡ç»æé½æ¯ä¸ä¸ªtupleç±»åçæ°æ®,è¿äºtupleç»æäºä¸ä¸ªtuple
cds=cursor.fetchall()
#å 为æ¯tuple,æ以å¯ä»¥è¿æ ·ä½¿ç¨ç»æé
print cds[0][3]
#æè
ç´æ¥æ¾ç¤ºåºæ¥,ççç»æéççå®æ ·å
print cds
#å¦æéè¦æ¹éçæå
¥æ°æ®,å°±è¿æ ·å
sql="insert into cdinfo values(0,%s,%s,%s,%s,%s)"
#æ¯ä¸ªå¼çéå为ä¸ä¸ªtuple,æ´ä¸ªåæ°éç»æä¸ä¸ªtuple,æè
list
param=((title,singer,imgurl,url,alpha),(title2,singer2,imgurl2,url2,alpha2))
#使ç¨executemanyæ¹æ³æ¥æ¹éçæå
¥æ°æ®.è¿çæ¯ä¸ä¸ªå¾é
·çæ¹æ³!
n=cursor.executemany(sql,param)
éè¦æ³¨æçæ¯(æè
说æ¯ææå°å¥æªçæ¯),å¨æ§è¡å®æå
¥æå é¤æä¿®æ¹æä½å,éè¦è°ç¨ä¸ä¸
conn.commit()æ¹æ³è¿è¡æ交.è¿æ ·,æ°æ®æä¼çæ£ä¿åå¨æ°æ®åºä¸.æä¸æ¸
æ¥æ¯å¦æ¯æçmysql设置é®é¢,æ»ä¹,ä»å¤©æå¨ä¸å¼å§ä½¿ç¨çæ¶å,
å¦æä¸ç¨commit,é£æ°æ®å°±ä¸ä¼ä¿çå¨æ°æ®åºä¸,ä½æ¯,æ°æ®ç¡®å®å¨æ°æ®åºåè¿.å 为èªå¨ç¼å·è¿è¡äºç´¯ç§¯,èä¸è¿åçåå½±åçè¡æ°å¹¶ä¸ä¸º0.
4ãå
³éæ°æ®åºè¿æ¥
éè¦åå«çå
³éæé对象åè¿æ¥å¯¹è±¡.ä»ä»¬æååç¸åçæ¹æ³
cursor.close()
conn.close()
5ã
5 ç¼ç ï¼é²æ¢ä¹±ç ï¼
éè¦æ³¨æçç¹ï¼
1 Pythonæ件设置ç¼ç utf-8 ï¼æ件åé¢å ä¸ #encoding=utf-8)
2 MySQLæ°æ®åºcharset=utf-8
3 Pythonè¿æ¥MySQLæ¯å ä¸åæ° charset=utf8
4 设置Pythonçé»è®¤ç¼ç 为 utf-8 (sys.setdefaultencoding(utf-8)
#encoding=utf-8 import sys import MySQLdb reload(sys) sys.setdefaultencoding('utf-8') db=MySQLdb.connect(user='root',charset='utf8')
注ï¼MySQLçé
ç½®æ件设置ä¹å¿
é¡»é
ç½®æutf8
6.模ååè½æ¼ç¤º
#!/usr/bin/python
import MySQLdb
Con= MySQLdb.connect(host='localhost',user='root',passwd='root',db='abc')
cursor =con.cursor()
sql ="select * from myt"
cursor.execute(sql)
row=cursor.fetchone()
print row
cursor.close()
con.close()
æ§è¡ä»¥ä¸SQLè¯å¥è·åè¿åå¼ï¼
//è·åè¿æ¥ç游æ
cursor=conn.cursor()
//æ¥è¯¢
sql = "select * from ãtableã"
//æ°å¢
sql = "insert into ãtableã(å段,å段) values(å¼ï¼å¼)"
//ä¿®æ¹
sql = "update ãtableã set å段 =âå¼âwhere æ¡ä»¶ "
//å é¤
sql = "delete from ãtableãwhere æ¡ä»¶"
cursor.execute(sql)
è¿åå¼
cur.execute('select * from tables')
å
¶è¿åå¼ä¸ºSQLè¯å¥å¾å°çè¡æ°ï¼å¦ï¼2Lï¼è¡¨ç¤º2è¡ã
ç¶åï¼å¯ä»¥ä»è¯¥å¯¹è±¡çfetchoneæfetchallæ¹æ³å¾å°è¡ä¿¡æ¯ã
è·åè¡ä¿¡æ¯
æé对象çfetchone()æ¹æ³ï¼æ¯æ¯æ¬¡å¾å°ä¸è¡çtupleè¿åå¼ï¼
å¼ç¨
>>> row=cur.fetchone()
>>> print row
('user1', '52c69e3a57331081823331c4e69d3f2e', 1000L, 1000L, '/home/FTP/user1','')
æé对象çfetchall()æ¹æ³ï¼å¯ååºæéç»æéä¸çææè¡ï¼è¿åçç»æéä¸ä¸ªå
ç»(tuples)ï¼
å¼ç¨
>>> cur.scroll(0,'absolute')
>>> row=cur.fetchall()
>>> print row
(('user1', '52c69e3a57331081823331c4e69d3f2e', 1000L, 1000L,
'/home/FTP/user1',''), ('user2', '7e58d63b60197ceb55a1c487989a3720',
1000L, 1000L,'/home/FTP/user2', None))
移å¨æé
å½ä½¿ç¨fetchone()æ¹æ³æ¯ï¼æéæ¯ä¼åç移å¨çãæ以ï¼è¥ä¸éç½®æéï¼é£ä¹ä½¿ç¨fetchallçä¿¡æ¯å°åªä¼å
å«æéåé¢çè¡å
容ã
æå¨ç§»å¨æé使ç¨ï¼
cur.scroll(int,parm)
å«ä¹ä¸ºï¼
å¼ç¨
intï¼ç§»å¨çè¡æ°ï¼æ´æ°ï¼å¨ç¸å¯¹æ¨¡å¼ä¸ï¼æ£æ°åä¸ç§»å¨ï¼è´å¼è¡¨ç¤ºåä¸ç§»å¨ã
parmï¼ç§»å¨ç模å¼ï¼é»è®¤æ¯relativeï¼ç¸å¯¹æ¨¡å¼ï¼å¯æ¥åabsoulteï¼ç»å¯¹æ¨¡å¼ã
ä¿®æ¹æ°æ®
ä¿®æ¹æ°æ®ï¼å
æ¬æå
¥ãæ´æ°ãå é¤ãå®ä»¬é½æ¯ä½¿ç¨æé对象çexecute()æ¹æ³æ§è¡ï¼
cur.execute("insert into table (row1, row2) values ('111', '222')")
cur.execute("update table set row1 = 'test' where row2 = 'row2' ")
cur.execute("delete from table where row1 = 'row1' ")
å åå¼å·â'âç¨äºSQLè¯å¥ä¸çæ è¯ï¼æ以ï¼pythonä¸çå符串é使ç¨åå¼å·æ¬ä½ã
æ¤å¤ï¼ä¹å¯ä»¥ä½¿ç¨pythonçâæ ¼å¼åå符串âåæ³ï¼ç®åå½ä»¤ï¼ä¾å¦ï¼
cur.execute("update table set row1 = '%s' where row2 = '%s' "%('value1','value2'))
â»è¯·æ³¨æï¼'%s'çåå¼å·æ¯SQLè¯å¥çé´é符ï¼'value1'çåå¼å·æ¯pythonçå符串é´é符ï¼å
¶å«ä¹æ¯ä¸åçãæ¯å¦éè¦é´é符ï¼ä»¥å使ç¨åå¼å·è¿æ¯åå¼å·ä½ä¸ºé´éï¼éæ ¹æ®å
¶å«ä¹å³å®ãä¾å¦ï¼è¿æï¼
cur.execute("update FTPUSERS set passwd=%s where userid='%s' "%("md5('123')",'user2'))
è¿éï¼paswd=%sæ¯å SQLçmd5()å½æ°æ¯ä¸éè¦åå¼å·é´éçï¼"md5('123')"æ¯pythonçå符串ä¸å«æåå¼å·ï¼æ以ç¨åå¼å·æ¬ä½ã
æ交修æ¹
ä¸è¬æ
åµä¸ï¼MySQLdb模åä¼èªå¨æ交修æ¹ãä½æ们å¨æ´æ°æ°æ®åï¼æå¨è¿è¡ä¸æ¬¡ï¼
conn.commit()
å
³éæ°æ®åºè¿æ¥
éè¦åå«çå
³éæé对象åè¿æ¥å¯¹è±¡.ä»ä»¬æååç¸åçæ¹æ³
cursor.close()
conn.close()
温馨提示:答案为网友推荐,仅供参考