ä¸ãå®è£ å¼å ¥æ¨¡å
â å®è£ mysql模å
sudo apt-get install pymysql
â¡å¨æ件ä¸å¼å ¥æ¨¡å
import pymysql
Connection对象
ç¨äºå»ºç«ä¸æ°æ®åºçè¿æ¥
å建对象ï¼è°ç¨connect()æ¹æ³
conn=pymysql.connect(åæ°å表)
åæ°hostï¼è¿æ¥çmysql主æºï¼å¦ææ¬æºæ¯âlocalhostâ
åæ°portï¼è¿æ¥çmysql主æºç端å£ï¼é»è®¤æ¯3306
åæ°dbï¼æ°æ®åºçå称
åæ°userï¼è¿æ¥çç¨æ·å
åæ°passwdï¼è¿æ¥çå¯ç
åæ°charsetï¼éä¿¡éç¨çç¼ç æ¹å¼ï¼é»è®¤æ¯âgb2312âï¼è¦æ±ä¸æ°æ®åºå建æ¶æå®çç¼ç ä¸è´ï¼å¦åä¸æä¼ä¹±ç
对象çæ¹æ³
close()å ³éè¿æ¥
commit()äºå¡ï¼æ以éè¦æ交æä¼çæ
rollback()äºå¡ï¼æ¾å¼ä¹åçæä½
cursor()è¿åCursor对象ï¼ç¨äºæ§è¡sqlè¯å¥å¹¶è·å¾ç»æ
Cursor对象
æ§è¡sqlè¯å¥
å建对象ï¼è°ç¨Connection对象çcursor()æ¹æ³
cursor1=conn.cursor()
对象çæ¹æ³
close()å ³é
execute(operation [, parameters ])æ§è¡è¯å¥ï¼è¿ååå½±åçè¡æ°
fetchone()æ§è¡æ¥è¯¢è¯å¥æ¶ï¼è·åæ¥è¯¢ç»æéç第ä¸ä¸ªè¡æ°æ®ï¼è¿åä¸ä¸ªå ç»
next()æ§è¡æ¥è¯¢è¯å¥æ¶ï¼è·åå½åè¡çä¸ä¸è¡
fetchall()æ§è¡æ¥è¯¢æ¶ï¼è·åç»æéçææè¡ï¼ä¸è¡ææä¸ä¸ªå ç»ï¼åå°è¿äºå ç»è£ å ¥ä¸ä¸ªå ç»è¿å
scroll(value[,mode])å°è¡æé移å¨å°æ个ä½ç½®
mode表示移å¨çæ¹å¼
modeçé»è®¤å¼ä¸ºrelativeï¼è¡¨ç¤ºåºäºå½åè¡ç§»å¨å°valueï¼value为æ£ååä¸ç§»å¨ï¼value为è´ååä¸ç§»å¨
modeçå¼ä¸ºabsoluteï¼è¡¨ç¤ºåºäºç¬¬ä¸æ¡æ°æ®çä½ç½®ï¼ç¬¬ä¸æ¡æ°æ®çä½ç½®ä¸º0
对象çå±æ§
rowcountåªè¯»å±æ§ï¼è¡¨ç¤ºæè¿ä¸æ¬¡execute()æ§è¡ååå½±åçè¡æ°
connectionè·å¾å½åè¿æ¥å¯¹è±¡
å®ä¾
â mysqlcli=pymysql.connect(host=â127.0.0.1â,user=ârootâ,passwd=âå¯ç â,db=âæ°æ®åºåâ,port=3306,charset=âutf8â)
â¡cursor = mysqlcli.cursor()
â¢cursor.execute(âINSERT INTO zhenaiwang_nv(nickname,personalized_signature, abstract, introduction, interest, mate_selection_require, source,source_url, time, spidername,gender) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)â,[item[ânicknameâ], item[âpersonalized_signatureâ], item[âabstractâ], item[âintroductionâ], item[âinterestâ], item[âmate_selection_requireâ], item[âsourceâ],item[âsource_urlâ], item[âtimeâ], item[âspidernameâ],item[âgenderâ]])
â£mysqlcli.commit()
â¤cursor.close()
å¢å
å建testInsert.pyæ件ï¼åå¦ç表ä¸æå ¥ä¸æ¡æ°æ®
#encoding=utf-8
import pymysql
try:
conn=pymysql.connect(host=âlocalhostâ,port=3306,db=âtest1â,user=ârootâ,passwd=âmysqlâ,charset=âutf8â)
cs1=conn.cursor()
count=cs1.execute(âinsert into students(sname) values(âå¼ è¯â)â)
print count
conn.commit()
cs1.close()
conn.close()
except Exception,e:
print e.message
ä¿®æ¹
å建testUpdate.pyæ件ï¼ä¿®æ¹å¦ç表çä¸æ¡æ°æ®
#encoding=utf-8
import pymysql
try:
conn=pymysql.connect(host=âlocalhostâ,port=3306,db=âtest1â,user=ârootâ,passwd=âmysqlâ,charset=âutf8â)
cs1=conn.cursor()
count=cs1.execute(âupdate students set sname=âåé¦â where id=6â)
print count
conn.commit()
cs1.close()
conn.close()
except Exception,e:
print e.message
å é¤
å建testDelete.pyæ件ï¼å é¤å¦ç表çä¸æ¡æ°æ®
#encoding=utf-8
import pymysql
try:
conn=pymysql.connect(host=âlocalhostâ,port=3306,db=âtest1â,user=ârootâ,passwd=âmysqlâ,charset=âutf8â)
cs1=conn.cursor()
count=cs1.execute(âdelete from students where id=6â)
print count
conn.commit()
cs1.close()
conn.close()
except Exception,e:
print e.message
sqlè¯å¥åæ°å
å建testInsertParam.pyæ件ï¼åå¦ç表ä¸æå ¥ä¸æ¡æ°æ®
#encoding=utf-8
import pymysql
try:
conn=pymysql.connect(host=âlocalhostâ,port=3306,db=âtest1â,user=ârootâ,passwd=âmysqlâ,charset=âutf8â)
cs1=conn.cursor()
sname=raw_input(â请è¾å ¥å¦çå§åï¼â)
params=[sname]
count=cs1.execute(âinsert into students(sname) values(%s)â,params)
print count
conn.commit()
cs1.close()
conn.close()
except Exception,e:
print e.message
å ¶å®è¯å¥
cursor对象çexecute()æ¹æ³ï¼ä¹å¯ä»¥ç¨äºæ§è¡create tableçè¯å¥
建议å¨å¼åä¹åï¼å°±å建好æ°æ®åºè¡¨ç»æï¼ä¸è¦å¨è¿éæ§è¡
æ¥è¯¢ä¸è¡æ°æ®
å建testSelectOne.pyæ件ï¼æ¥è¯¢ä¸æ¡å¦çä¿¡æ¯
#encoding=utf8
import pymysql
try:
conn=pymysql.connect(host=âlocalhostâ,port=3306,db=âtest1â,user=ârootâ,passwd=âmysqlâ,charset=âutf8â)
cur=conn.cursor()
cur.execute(âselect * from students where id=7â)
result=cur.fetchone()
print result
cur.close()
conn.close()
except Exception,e:
print e.message
æ¥è¯¢å¤è¡æ°æ®
å建testSelectMany.pyæ件ï¼æ¥è¯¢ä¸æ¡å¦çä¿¡æ¯
#encoding=utf8
import pymysql
try:
conn=pymysql.connect(host=âlocalhostâ,port=3306,db=âtest1â,user=ârootâ,passwd=âmysqlâ,charset=âutf8â)
cur=conn.cursor()
cur.execute(âselect * from studentsâ)
result=cur.fetchall()
print result
cur.close()
conn.close()
except Exception,e:
print e.message
å°è£
è§å¯åé¢çæ件åç°ï¼é¤äºsqlè¯å¥ååæ°ä¸åï¼å ¶å®è¯å¥é½æ¯ä¸æ ·ç
å建MysqlHelper.pyæ件ï¼å®ä¹ç±»
#encoding=utf8
import pymysql
import MySQLdb
class MysqlHelper():
def init(self,host,port,db,user,passwd,charset=âutf8â):
self.host=host
self.port=port
self.db=db
self.user=user
self.passwd=passwd
self.charset=charset
def connect(self):
self.conn=MySQLdb.connect(host=self.host,port=self.port,db=self.db,user=self.user,passwd=self.passwd,charset=self.charset)
self.cursor=self.conn.cursor()
def close(self):
self.cursor.close()
self.conn.close()
def get_one(self,sql,params=()):
result=None
try:
self.connect()
self.cursor.execute(sql, params)
result = self.cursor.fetchone()
self.close()
except Exception, e:
print e.message
return result
def get_all(self,sql,params=()):
list=()
try:
self.connect()
self.cursor.execute(sql,params)
list=self.cursor.fetchall()
self.close()
except Exception,e:
print e.message
return list
def insert(self,sql,params=()):
return self.__edit(sql,params)
def update(self, sql, params=()):
return self.__edit(sql, params)
def delete(self, sql, params=()):
return self.__edit(sql, params)
def __edit(self,sql,params):
count=0
try:
self.connect()
count=self.cursor.execute(sql,params)
self.conn.commit()
self.close()
except Exception,e:
print e.message
return count
æ·»å
å建testInsertWrap.pyæ件ï¼ä½¿ç¨å°è£ 好ç帮å©ç±»å®ææå ¥æä½
#encoding=utf8
from MysqlHelper import *
sql=âinsert into students(sname,gender) values(%s,%s)â
sname=raw_input(â请è¾å ¥ç¨æ·åï¼â)
gender=raw_input(â请è¾å ¥æ§å«ï¼1为ç·ï¼0为女â)
params=[sname,bool(gender)]
mysqlHelper=MysqlHelper(âlocalhostâ,3306,âtest1â,ârootâ,âmysqlâ)
count=mysqlHelper.insert(sql,params)
if count==1:
print âokâ
else:
print âerrorâ
æ¥è¯¢ä¸ä¸ª
å建testGetOneWrap.pyæ件ï¼ä½¿ç¨å°è£ 好ç帮å©ç±»å®ææ¥è¯¢ææ°ä¸è¡æ°æ®æä½
#encoding=utf8
from MysqlHelper import *
sql=âselect sname,gender from students order by id descâ
helper=MysqlHelper(âlocalhostâ,3306,âtest1â,ârootâ,âmysqlâ)
one=helper.get_one(sql)
print one
å®ä¾ï¼ç¨æ·ç»å½
å建ç¨æ·è¡¨userinfos
表ç»æå¦ä¸
id
uname
upwd
isdelete
注æï¼éè¦å¯¹å¯ç è¿è¡å å¯
å¦æ使ç¨md5å å¯ï¼åå¯ç å å«32个å符
å¦æ使ç¨sha1å å¯ï¼åå¯ç å å«40个å符ï¼æ¨è使ç¨è¿ç§æ¹å¼
create table userinfos(
id int primary key auto_increment,
uname varchar(20),
upwd char(40),
isdelete bit default 0
);
å å ¥æµè¯æ°æ®
æå ¥å¦ä¸æ°æ®ï¼ç¨æ·å为123,å¯ç 为123,è¿æ¯sha1å å¯åçå¼
insert into userinfos values(0,â123â,â40bd001563085fc35165329ea1ff5c5ecbdbbeefâ,0);
æ¥æ¶è¾å ¥å¹¶éªè¯
å建testLogin.pyæ件ï¼å¼å ¥hashlib模åãMysqlHelper模å
æ¥æ¶è¾å ¥
æ ¹æ®ç¨æ·åæ¥è¯¢ï¼å¦ææªæ¥å°åæ示ç¨æ·åä¸åå¨
å¦ææ¥å°åå¹é å¯ç æ¯å¦ç¸çï¼å¦æç¸çåæ示ç»å½æå
å¦æä¸ç¸çåæ示å¯ç é误
#encoding=utf-8
from MysqlHelper import MysqlHelper
from hashlib import sha1
sname=raw_input(â请è¾å ¥ç¨æ·åï¼â)
spwd=raw_input(â请è¾å ¥å¯ç :â)
#sha1å å¯ã40ä½å符
s1=sha1()
s1.update(spwd)
spwdSha1=s1.hexdigest()
sql=âselect upwd from userinfos where uname=%sâ
params=[sname]
sqlhelper=MysqlHelper(âlocalhostâ,3306,âtest1â,ârootâ,âmysqlâ)
userinfo=sqlhelper.get_one(sql,params)
if userinfo==None:
print âç¨æ·åé误â
elif userinfo[0]==spwdSha1:
print âç»å½æåâ
else:
print âå¯ç é误â