python&pandas 与mysql 连接
时间：2021-07-01 10:21:17
帮助过：3人阅读
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)
python&pandas 与mysql 连接
标签：
