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)

发表评论

邮箱地址不会被公开。 必填项已用*标注