Python MySQL进行数据库表变更和查询
Python连接MySQL，进行数据库表变更和查询：
pythonmysqlinsertdeletequery：
#!/usr/bin/python
importMySQLdb
defdoInsert(cursor,db):
#insert
#PrepareSQLquerytoINSERTarecordintothedatabase.
sql="UPDATEEMPLOYEESETAGE=AGE+1WHERESEX='%c'"%('M')
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
defdo_query(cursor,db):
sql="SELECT*FROMEMPLOYEE\
WHEREINCOME>'%d'"%(1000)
try:
#ExecutetheSQLcommand
cursor.execute(sql)
#Fetchalltherowsinalistoflists.
results=cursor.fetchall()
print'resuts',cursor.rowcount
forrowinresults:
fname=row[0]
lname=row[1]
age=row[2]
sex=row[3]
income=row[4]
#Nowprintfetchedresult
print"fname=%s,lname=%s,age=%d,sex=%s,income=%d"%\
(fname,lname,age,sex,income)
except:
print"Error:unabletofecthdata"
defdo_delete(cursor,db):
sql='DELETEFROMEMPLOYEEWHEREAGE>{}'.format(20)
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
defdo_insert(cursor,db,firstname,lastname,age,sex,income):
sql="INSERTINTOEMPLOYEE(FIRST_NAME,\
LAST_NAME,AGE,SEX,INCOME)\
VALUES('%s','%s','%d','%c','%d')"%\
(firstname,lastname,age,sex,income)
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
#Opendatabaseconnection
#changethistoyourmysqlaccount
#connect(server,username,password,db_name)
db=MySQLdb.connect("localhost","root","root","pydb")
#prepareacursorobjectusingcursor()method
cursor=db.cursor()
do_query(cursor,db)
doInsert(cursor,db)
do_query(cursor,db)
do_delete(cursor,db)
do_query(cursor,db)
do_insert(cursor,db,'hunter','xue',22,'M',2000)
do_insert(cursor,db,'mary','yang',22,'f',5555)
do_insert(cursor,db,'zhang','xue',32,'M',5000)
do_insert(cursor,db,'hunter','xue',22,'M',333)
do_query(cursor,db)
#disconnectfromserver
db.close()
之后可以在此基础上根据需要进行封装。
感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！