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 res 40 41 42 43 res = cur.fetchmany(10) 44 45 print res 46 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 # read 12 13 sql = "select * from user limit 3" 14 15 df = pd.read_sql(sql,conn,index_col="id") 16 17 print df 18 19 20 21 # write 22 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)
