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()
之后可以在此基础上根据需要进行封装。
感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!