连接MySQL数据库

In [1]:

# 第三方库PyMySQL通过conda命令来安装import pymysql        # 导入PyMySQL库conn = pymysql.connect(    host='localhost',     user='root',     passwd='123456',     db='mydb',    port=3306,     charset='utf8')       # 连接到本地的MySQL数据库cursor = conn.cursor()    # 创建游标，cursor为光标对象，用于操作MySQL数据库creat = '''CREATE TABLE ch4ex9 (    id int,    name char(8),    grade int)ENGINE INNODB DEFAULT CHARSET=utf8;'''cursor.execute(creat)   # 执行创建表命令conn.commit()     # 完成创建命令

In [2]:

cursor.execute("insert into ch4ex9 (id,name,grade) values(%s,%s,%s)",(1,'luchy',87))cursor.execute("insert into ch4ex9 (id,name,grade) values(%s,%s,%s)",(2,'peter',92))cursor.execute("insert into ch4ex9 (id,name,grade) values(%s,%s,%s)",(3,'lili',85))conn.commit()     # 完成插入数据命令cursor.close()   # 关闭游标conn.close()     #关闭连接

In [3]:

# 通过PyMySQL库读取数据库，然后传入到DataFrame构造器from pandas import DataFrameimport pymysqlconn = pymysql.connect(    host='localhost',     user='root',     passwd='123456',     db='mydb',    port=3306,     charset='utf8')cursor = conn.cursor()rows = cursor.execute('select * from ch4ex9')rows

Out[3]:

3

In [4]:

data = cursor.fetchall()    # 通过游标的fetchall方法，取得所有数据data

Out[4]:

((1, 'luchy', 87), (2, 'peter', 92), (3, 'lili', 85))

In [5]:

from pandas import DataFrameimport pandas as pddf = DataFrame(list(data))    # 元组列表化后传给DataFrame构造器df

Out[5]:

0

1

2

0

1

luchy

87

1

2

peter

92

2

3

lili

85

In [31]:

data

Out[31]:

((1, 'luchy', 87), (2, 'peter', 92), (3, 'lili', 85))

In [32]:

type(data)

Out[32]:

tuple

In [34]:

# 通过read_sql函数读取MySQL数据import pandas as pdimport pymysqlconn = pymysql.connect(    host='localhost',     user='root',     passwd='123456',     db='mydb',    port=3306,     charset='utf8')df = pd.read_sql('select * from ch4ex9',conn)df

Out[34]:

id

name

grade

0

1

luchy

87

1

2

peter

92

2

3

lili

85

存储MySQL数据库df.to_sql?

df.to_sql(name, con, flavor=None, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None) ·name参数为存储的表名； ·con参数为连接的数据库； ·if_exists参数用于判断是否有重复表名。 其中，fail表示如果有重复表名，就不保存； replace表示替换重复表名； append表示在该表中继续插入数据。 新版pandas中，con参数不能使用pymysql连接数据库。

In [37]:

df.to_sql(name='out6',con='mysql+pymysql://root:123456@localhost:3306/mydb? charset=utf8',          if_exists='replace',index=False)# con参数是固定写法，读者记住即可

F:\Anaconda\envs\data-analysis\lib\site-packages\pymysql\cursors.py:166: Warning: (1366, "Incorrect string value: '\\xD6\\xD0\\xB9\\xFA\\xB1\\xEA...' for column 'VARIABLE_VALUE' at row 480")  result = self._query(query)