python & pandas链接mysql数据库

Python&pandas与mysql连接

1、python 与mysql 连接及操作，直接上代码，简单直接高效：
 

import MySQLdb

try:

    conn = MySQLdb.connect(host='localhost',user='root',passwd='×××××',db='test',charset='utf8')

    cur = conn.cursor()

    cur.execute('create table user(id int,name varchar(20))' )

   

    value = [1,'jkmiao']

    cur.execute("insert into user values(%s,%s)",value)

 

    users = []

 

    for i in range(20):

        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(10)

    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 连接操作mysql：

import pandas as pd

import MySQLdb

 

conn = MySQLdb.connect(host="localhot",user="root",passwd="*****",db="test",charset="utf8")

 

# read

sql = "select * from user limit 3"

df = pd.read_sql(sql,conn,index_col="id")

print df

 

# write

cur = 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)