python如何将dataframe数据插入mysql数据库

一、读写文本格式的数据

1、读取文本文件

pandas提供了一些用于将表格型数据读取为DataFrame对象的函数：

其中read_csv和read_table用得较多；

这些函数的选项可以划分为以下几个大类：

1）索引：将一个或多个列当做返回的DataFrame处理，以及是否从文件、用户获取列名。

2）类型推断和数据转换：包括用户定义值的转换、和自定义的缺失值标记列表等。

3）日期解析：包括组合功能，比如将分散在多个列中的日期时间信息组合成结果中的单个列。

4）迭代：支持对大文件进行逐块迭代。

5）不规整数据问题：跳过一些行、页脚、注释或其他一些不重要的东西（比如由成千上万个逗号隔开的数值数据）。

import numpy as np
import pandas as pd
from pandas import Series, DataFrame

# 一、读写文本格式的数据
# 1、读取文本文件
# 以逗号分隔的（CSV）文本文件
!cat examples/ex1.csv

# 由于该文件以逗号分隔，所以我们可以使用read_csv将其读入一个DataFrame：
df = pd.read_csv('examples/ex1.csv')
df

# 还可以使用read_table，并指定分隔符：
pd.read_table('examples/ex1.csv', sep=',')

# 并不是所有文件都有标题行。
!cat examples/ex2.csv

# 读入该文件的办法有两个。你可以让pandas为其分配默认的列名，也可以自己定义列名：
pd.read_csv('examples/ex2.csv', header=None)

pd.read_csv('examples/ex2.csv', names=['a', 'b', 'c', 'd', 'message'])

# 假设你希望将message列做成DataFrame的索引。
# 可以明确表示要将该列放到索引4的位置上，也可以通过index_col参数指定"message"：
names = ['a', 'b', 'c', 'd', 'message']
pd.read_csv('examples/ex2.csv', names=names, index_col='message')

# 如果希望将多个列做成一个层次化索引，只需传入由列编号或列名组成的列表即可：
!cat examples/csv_mindex.csv

parsed = pd.read_csv('examples/csv_mindex.csv', 
                     index_col=['key1', 'key2'])
parsed

# 有些情况下，有些表格可能不是用固定的分隔符去分隔字段的（比如空白符或其它模式）。
list(open('examples/ex3.txt'))

# 这种情况下，可以传递一个正则表达式作为read_table的分隔符。
# 可以用正则表达式表达为s+;
result = pd.read_table('examples/ex3.txt', sep='s+')
result
# 这里，由于列名比数据行的数量少，所以read_table推断第一列应该是DataFrame的索引。

# 用skiprows跳过文件的第一行、第三行和第四行：
!cat examples/ex4.csv

pd.read_csv('examples/ex4.csv', skiprows=[0, 2, 3])

# 缺失值处理是文件解析任务中的一个重要组成部分。
# 缺失数据经常是要么没有（空字符串），要么用某个标记值表示。
# 默认情况下，pandas会用一组经常出现的标记值进行识别，比如NA及NULL：
!cat examples/ex5.csv

result = pd.read_csv('examples/ex5.csv')
result

pd.isnull(result)

# na_values可以用一个列表或集合的字符串表示缺失值：
result = pd.read_csv('examples/ex5.csv', na_values=['NULL'])
result

# 字典的各列可以使用不同的NA标记值：
sentinels = {'message': ['foo', 'NA'], 'something': ['two']}
pd.read_csv('examples/ex5.csv', na_values=sentinels)

下表列出了pandas.read_csv和pandas.read_table常用的选项：

2、逐块读取文本文件

在处理很大的文件时，或找出大文件中的参数集以便于后续处理时，你可能只想读取文件的一小部分或逐块对文件进行迭代。

# 2、逐块读取文本文件
# 在看大文件之前，我们先设置pandas显示地更紧些：
pd.options.display.max_rows = 10

result = pd.read_csv('examples/ex6.csv')
result

# 如果只想读取几行（避免读取整个文件），通过nrows进行指定即可：
pd.read_csv('examples/ex6.csv', nrows=5)

# 要逐块读取文件，可以指定chunksize（行数）：
chunker = pd.read_csv('examples/ex6.csv', chunksize=1000)
chunker

# read_csv所返回的这个TextParser对象使你可以根据chunksize对文件进行逐块迭代。
# 比如说，我们可以迭代处理ex6.csv，将值计数聚合到"key"列中，
chunker = pd.read_csv('examples/ex6.csv', chunksize=1000)
tot = pd.Series([])
for piece in chunker:
    tot = tot.add(piece['key'].value_counts(), fill_value=0)
tot = tot.sort_values(ascending=False)
tot[:10]
# TextParser还有一个get_chunk方法，它使你可以读取任意大小的块。

3、将数据写出到文本格式

# 3、将数据写出到文本格式
# 数据也可以被输出为分隔符格式的文本
data = pd.read_csv('examples/ex5.csv')
data

# 利用DataFrame的to_csv方法，我们可以将数据写到一个以逗号分隔的文件中：
data.to_csv('examples/out1.csv')
!cat examples/out1.csv

# 还可以使用其他分隔符（由于这里直接写出到sys.stdout，所以仅仅是打印出文本结果而已）：
import sys
data.to_csv(sys.stdout, sep='|')

# 缺失值在输出结果中会被表示为空字符串。你可能希望将其表示为别的标记值：
data.to_csv(sys.stdout, na_rep='NULL')

# 如果没有设置其他选项，则会写出行和列的标签。它们也都可以被禁用：
data.to_csv(sys.stdout, index=False, header=False)

# 你还可以只写出一部分的列，并以你指定的顺序排列：
data.to_csv(sys.stdout, index=False, columns=['a', 'b', 'c'])

# Series也有一个to_csv方法：
dates = pd.date_range('1/1/2000', periods=7)
ts = pd.Series(np.arange(7), index=dates)
ts.to_csv('examples/series1.csv')
!cat examples/series1.csv

4、处理分隔符格式

# 4、处理分隔符格式
!cat examples/ex7.csv

# 对于任何单字符分隔符文件，可以直接使用Python内置的csv模块。
# 将任意已打开的文件或文件型的对象传给csv.reader：
import csv
f = open('examples/ex7.csv')
print(f)
reader = csv.reader(f)
print(reader)
# 对这个reader进行迭代将会为每行产生一个元组（并移除了所有的引号）：
for line in reader:
    print(line)

# 首先，读取文件到一个多行的列表中：
with open('examples/ex7.csv') as f:
    lines = list(csv.reader(f))
# 然后，将这些行分为标题行和数据行：
header, values = lines[0], lines[1:]
# 接着用字典构造式和zip(*values)，后者将行转置为列，创建数据列的字典：
data_dict = {h: v for h, v in zip(header, zip(*values))}
data_dict

# 要手工输出分隔符文件，你可以使用csv.writer。
# 它接受一个已打开且可写的文件对象以及跟csv.reader相同的那些语支和格式化选项：
with open('examples/mydata.csv', 'w') as f:
    writer = csv.writer(f, dialect=my_dialect)
    writer.writerow(('one', 'two', 'three'))
    writer.writerow(('1', '2', '3'))
    writer.writerow(('4', '5', '6'))
    writer.writerow(('7', '8', '9'))

下表列出了可用的选项（csv.Dialect的属性）及其功能：

5、JSON数据

# 5、JSON数据
# 通过json.loads即可将JSON字符串转换成Python形式：
obj = """
{"name": "Wes",
"places_lived": ["United States", "Spain", "Germany"],
"pet": null,
"siblings": [{"name": "Scott", "age": 30, "pets": ["Zeus", "Zuko"]},
            {"name": "Katie", "age": 38, "pets": ["Sixes", "Stache", "Cisco"]}]
} """
import json
result = json.loads(obj)
result

# json.dumps则将Python对象转换成JSON格式：
asjson = json.dumps(result)
# 将（一个或一组）JSON对象转换为DataFrame或其他便于分析的数据结构：
# 最简单方便的方式是：
# 向DataFrame构造器传入一个字典的列表（就是原先的JSON对象），并选取数据字段的子集：
siblings = pd.DataFrame(result['siblings'], columns=['name', 'age'])
siblings

!cat examples/example.json

# pandas.read_json可以自动将特别格式的JSON数据集转换为Series或DataFrame。
# pandas.read_json的默认选项假设JSON数组中的每个对象是表格中的一行：
data = pd.read_json('examples/example.json')
data

# 将数据从pandas输出到JSON，可以使用to_json方法：
print(data.to_json())

print(data.to_json(orient='records'))

二、二进制数据格式

1、使用HDF5格式

HDF5是一种存储大规模科学数组数据的非常好的文件格式；HDF5中的HDF指的是层次型数据格式（hierarchical data format）。

每个HDF5文件都含有一个文件系统式的节点结构，它使你能够存储多个数据集并支持元数据。与其他简单格式相比，HDF5支持多种压缩器的即时压缩，还能更高效地存储重复模式数据。对于那些非常大的无法直接放入内存的数据集，HDF5就是不错的选择，因为它可以高效地分块读写。

# 二、二进制数据格式
# 1、使用HDF5格式
# 虽然可以用PyTables或h5py库直接访问HDF5文件，
# pandas提供了更为高级的接口，可以简化存储Series和DataFrame对象。
# HDFStore类可以像字典一样，处理低级的细节：
frame = pd.DataFrame({'a': np.random.randn(100)})
store = pd.HDFStore('examples/mydata.h5')
store['obj1'] = frame
store['obj1_col'] = frame['a']
store

# HDF5文件中的对象可以通过与字典一样的API进行获取：
store['obj1']

# HDFStore支持两种存储模式，'fixed'和'table'。
# 'table'通常会更慢，但是支持使用特殊语法进行查询操作：
store.put('obj2', frame, format='table')
store.select('obj2', where=['index >= 10 and index <= 15'])

store.close()

# put是store['obj2'] = frame方法的显示版本，允许我们设置其它的选项，
# pandas.read_hdf函数可以快捷使用这些工具：
frame.to_hdf('examples/mydata2.h5', 'obj3', format='table')
pd.read_hdf('examples/mydata2.h5', 'obj3', where=['index < 5'])

# 由于许多数据分析问题都是IO密集型（而不是CPU密集型），利用HDF5这样的工具能显著提升应用程序的效率。
# 注意：HDF5不是数据库。它最适合用作“一次写多次读”的数据集。虽然数据可以在任何时候被添加到文件中，
# 但如果同时发生多个写操作，文件就可能会被破坏。

2、读取Microsoft Excel文件

pandas的ExcelFile类或pandas.read_excel函数支持读取存储在Excel 2003（或更高版本）中的表格型数据。这两个工具分别使用扩展包xlrd和openpyxl读取XLS和XLSX文件。

# 2、读取Microsoft Excel文件
# 要使用ExcelFile，通过传递xls或xlsx路径创建一个实例：
xlsx = pd.ExcelFile('examples/ex1.xlsx')
pd.read_excel(xlsx, 'Sheet1')

# 要读取一个文件中的多个表单，创建ExcelFile会更快，
# 但你也可以将文件名传递到pandas.read_excel：
frame = pd.read_excel('examples/ex1.xlsx', 'Sheet1')
frame

# 如果要将pandas数据写入为Excel格式，你必须首先创建一个ExcelWriter，
# 然后使用pandas对象的to_excel方法将数据写入到其中：
writer = pd.ExcelWriter('examples/ex2.xlsx')
frame.to_excel(writer, 'Sheet1')
writer.save()

# 还可以不使用ExcelWriter，而是传递文件的路径到to_excel：
frame.to_excel('examples/ex2_1.xlsx')

三、数据库交互

将数据从SQL加载到DataFrame的过程很简单，此外pandas还有一些能够简化该过程的函数；

# 三、数据库交互
1、sqlite
import sqlite3
query = """ 
        CREATE TABLE test 
        (a VARCHAR(20), b VARCHAR(20), c REAL, d INTEGER);
        """
con = sqlite3.connect('mydata.sqlite')
con.execute(query)

con.commit()

# 然后插入几行数据：
data = [('Atlanta', 'Georgia', 1.25, 6),
        ('Tallahassee', 'Florida', 2.6, 3),
        ('Sacramento', 'California', 1.7, 5)]
stmt = "INSERT INTO test VALUES(?, ?, ?, ?)"
con.executemany(stmt, data)

# 从表中选取数据时，大部分Python SQL驱动器
# （PyODBC、psycopg2、MySQLdb、pymssql等）都会返回一个元组列表：
cursor = con.execute('select * from test')
rows = cursor.fetchall()
rows

# 将这个元组列表传给DataFrame构造器，但还需要列名（位于光标的description属性中）：
cursor.description

pd.DataFrame(rows, columns=[x[0] for x in cursor.description])

import sqlalchemy as sqla
db = sqla.create_engine('sqlite:///mydata.sqlite')
pd.read_sql('select * from test', db)

# 2、mysql
# python连接mysql，有2种方式，分别是利用sqlalchemy、pymysql这2个包连接；
# sqlalchemy
import pandas as pd
from sqlalchemy import create_engine
# 创建数据库连接
engine = create_engine('mysql+pymysql://root:passwd@@localhost:3306/test')

# DataFrame写入MySQL
# 新建DataFrame
df_write = pd.DataFrame(np.arange(16).reshape((4, 4)),
                    index=['Ohio', 'Colorado', 'Utah','New York'],
                    columns=['one', 'two', 'three', 'four'])
# 将df储存为MySQL中的表，不储存index列
df_write.to_sql('test_01', engine, index=False)
# mysql查询语句
sql_query = 'select * from test_01;'
# 使用pandas的read_sql_query函数执行SQL语句，并存入DataFrame
df_read_01 = pd.read_sql(sql_query, engine)
print(df_read_01)

# pymysql
import pymysql
connect = pymysql.connect(
            host = '127.0.0.1',
            db = 'test',
            user = 'root',
            passwd = 'passwd',
            charset = 'utf8mb4',
            use_unicode = True
                        )
cursor = connect.cursor()
sql_02 = "select * from test_01;"
df_read_02 = pd.read_sql(sql_02, con=connect)
print(df_read_02)