1. Python and MySQL connection and operation, directly on the code, simple and direct efficiency:
Import MySQLdbTry: Conn= MySQLdb.connect (host='localhost', user='Root', passwd='xxxxx', db='Test', charset='UTF8') cur=conn.cursor () Cur.execute ('CREATE TABLE User (id int,name varchar )') Value= [1,'Jkmiao'] Cur.execute ("INSERT into user values (%s,%s)", value) users= [] forIinchRange -): Users.append ((i,"User"+str (i))) Cur.executemany ("INSERT into user values (%s,%s)", users) Cur.execute ("Update user Set name="Test"where id=2") Res=Cur.fetchone () Print res res= Cur.fetchmany (Ten) Print res print cur.fetchall () conn.commit () Cur.close () Conn.close () Cur.execute ('SELECT * from user') Cur.close () conn.close () except Mysqldb.error,e:print"Mysql Error%d:%s"% (e.args[0], e.args[1])
2. Pandas connection Operation MySQL
ImportPandas as PDImportMysqldbconn= MySQLdb.connect (host="Localhot", user="Root", passwd="*****", db="Test", charset="UTF8")#Readsql ="SELECT * from user limit 3"DF= Pd.read_sql (sql,conn,index_col="ID")PrintDF#WriteCur =conn.cursor () Cur.execute ("drop table if exists user")
Cur.execute (' CREATE table user (ID int,name varchar (20)) ')
pd.io.sql.write_frame (DF,"User", conn)
Python&pandas connection to MySQL