python & pandas連結mysql資料庫

標籤：

Python&pandas與mysql串連

1、python 與mysql 串連及操作，直接上代碼，簡單直接高效：
 

 1 import MySQLdb 2  3 try: 4  5     conn = MySQLdb.connect(host=‘localhost‘,user=‘root‘,passwd=‘××××ב,db=‘test‘,charset=‘utf8‘) 6  7     cur = conn.cursor() 8  9     cur.execute(‘create table user(id int,name varchar(20))‘ )10 11    12 13     value = [1,‘jkmiao‘]14 15     cur.execute("insert into user values(%s,%s)",value)16 17  18 19     users = []20 21  22 23     for i in range(20):24 25         users.append((i,"user"+str(i)))26 27  28 29     cur.executemany("insert into user values(%s,%s)",users)30 31  32 33     cur.execute("update user set name="test" where id=2")34 35   36 37     res = cur.fetchone()38 39     print res40 41  42 43     res = cur.fetchmany(10)44 45     print res46 47  48 49     print cur.fetchall()50 51    52 53     conn.commit()54 55     cur.close()56 57     conn.close()   58 59     cur.execute(‘select * from user‘)60 61     cur.close()62 63     conn.close()64 65 except MySQLdb.Error,e:66 67      print "Mysql Error %d: %s" % (e.args[0], e.args[1])  

 

 

2、pandas 串連操作mysql：

 1 import pandas as pd 2  3 import MySQLdb 4  5   6  7 conn = MySQLdb.connect(host="localhot",user="root",passwd="*****",db="test",charset="utf8") 8  9  10 11 # read12 13 sql = "select * from user limit 3"14 15 df = pd.read_sql(sql,conn,index_col="id")16 17 print df18 19  20 21 # write22 23 cur = conn.cursor()24 25 cur.execute("drop table if exists user")26 cur.execute(‘create table user(id int,name varchar(20))‘ )27 pd.io.sql.write_frame(df,"user",conn)

 

 

python & pandas連結mysql資料庫