【第4章】4.1读，写不同数据源数据

SQLAlchemy 连接 MySql 数据库

sqlalchemy 库

from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:981221@localhost/testdb?charset=utf8mb4')
print(engine)

Engine(mysql+pymysql://root:***@localhost/testdb?charset=utf8mb4)

pandas 库

• pandas 的3 个函数用于读取数据库数据
• read_sql_table ---------------读取都一个表格
• read_sql_query ---------------实现查询
• read_sql ------------------上面两者的综合

读 / 写数据库数据

• read_sql_table
• read_sql_query
• read_sql

import pandas as pd

formlist = pd.read_sql_query('show tables', con=engine)
print('testdb数据库表清单为:','\n',formlist)

testdb数据库表清单为: 
      Tables_in_testdb
0  meal_order_detail1
1  meal_order_detail2
2  meal_order_detail3
3               test1

detail1 = pd.read_sql_table('meal_order_detail1',con=engine)
print('使用read_sql_table 读取订单详情的长度为:',len(detail1))

使用read_sql_table 读取订单详情的长度为: 2779

detail2 = pd.read_sql('select * from meal_order_detail2',con=engine)
print('使用read_sql 函数 + SQL语句 读取的订单详情表长度为:',len(detail2))
detail3 = pd.read_sql('meal_order_detail3',con=engine)
print('使用read_sql 函数 + 表格名称 读取的订单详情表长度为:',len(detail3))

使用read_sql 函数 + SQL语句 读取的订单详情表长度为: 3647
使用read_sql 函数 + 表格名称 读取的订单详情表长度为: 3611

数据库数据存储

• to_sql

detail1.to_sql('test1', con = engine, index = False, if_exists = 'replace')
formlist1 = pd.read_sql_query('show tables', con=engine)
print('新增一个表格后，testdb数据库数据表清单为:','\n',formlist1)

新增一个表格后，testdb数据库数据表清单为: 
      Tables_in_testdb
0  meal_order_detail1
1  meal_order_detail2
2  meal_order_detail3
3               test1

读 / 写文本文件

• read_table
• read_csv

order = pd.read_table('D:/pandas/meal_order_info.csv',sep=',',encoding='gbk')
print('使用read_table读取的菜品订单信息表长度为：',len(order))

使用read_table读取的菜品订单信息表长度为： 945

order1 = pd.read_csv('D:/pandas/meal_order_info.csv',encoding='gbk')
print('使用read_csv读取菜品订单信息表的长度为为：',len(order1))

使用read_csv读取菜品订单信息表的长度为为： 945

更改参数读取菜品订单信息表

• header 用于指定猎命，默认infer-------自动识别，如果是None----------添加默认列名
• sep ------指定文本的分隔符，如果不符合----------读取结果是一片数据

order2 = pd.read_table('D:/pandas/meal_order_info.csv',sep=';',encoding='gbk')
print('分隔符是；时，菜品订单信息表为：','\n', order2)

分隔符是；时，菜品订单信息表为： 
     info_id,"emp_id","number_consumers","mode","dining_table_id","dining_table_name","expenditure","dishes_count","accounts_payable","use_start_time","check_closed","lock_time","cashier_id","pc_id","order_number","org_id","print_doc_bill_num","lock_table_info","order_status","phone","name"
0    417,1442,4,NA,1501,1022,165,5,165,"2016/8/1 11...                                                                                                                                                                                                                                            
1    301,1095,3,NA,1430,1031,321,6,321,"2016/8/1 11...                                                                                                                                                                                                                                            
2    413,1147,6,NA,1488,1009,854,15,854,"2016/8/1 1...                                                                                                                                                                                                                                            
3    415,1166,4,NA,1502,1023,466,10,466,"2016/8/1 1...                                                                                                                                                                                                                                            
4    392,1094,10,NA,1499,1020,704,24,704,"2016/8/1 ...                                                                                                                                                                                                                                            
..                                                 ...                                                                                                                                                                                                                                            
940  641,1095,8,NA,1492,1013,679,12,679,"2016/8/31 ...                                                                                                                                                                                                                                            
941  672,1089,6,NA,1489,1010,800,24,800,"2016/8/31 ...                                                                                                                                                                                                                                            
942  692,1155,8,NA,1492,1013,735,10,735,"2016/8/31 ...                                                                                                                                                                                                                                            
943  647,1094,4,NA,1485,1006,262,9,262,"2016/8/31 2...                                                                                                                                                                                                                                            
944  570,1113,8,NA,1517,1038,589,13,589,"2016/8/31 ...                                                                                                                                                                                                                                            

[945 rows x 1 columns]

order3 = pd.read_csv('D:/pandas/meal_order_info.csv',sep=',',header=None,encoding='gbk')
print('header为None时菜品订单信息表为：',order3)

header为None时菜品订单信息表为：           0       1                 2     3                4   \
0    info_id  emp_id  number_consumers  mode  dining_table_id   
1        417    1442                 4   NaN             1501   
2        301    1095                 3   NaN             1430   
3        413    1147                 6   NaN             1488   
4        415    1166                 4   NaN             1502   
..       ...     ...               ...   ...              ...   
941      641    1095                 8   NaN             1492   
942      672    1089                 6   NaN             1489   
943      692    1155                 8   NaN             1492   
944      647    1094                 4   NaN             1485   
945      570    1113                 8   NaN             1517   

                    5            6             7                 8   \
0    dining_table_name  expenditure  dishes_count  accounts_payable   
1                 1022          165             5               165   
2                 1031          321             6               321   
3                 1009          854            15               854   
4                 1023          466            10               466   
..                 ...          ...           ...               ...   
941               1013          679            12               679   
942               1010          800            24               800   
943               1013          735            10               735   
944               1006          262             9               262   
945               1038          589            13               589   

                     9   ...                  11          12     13  \
0        use_start_time  ...           lock_time  cashier_id  pc_id   
1     2016/8/1 11:05:36  ...   2016/8/1 11:11:46         NaN    NaN   
2     2016/8/1 11:15:57  ...   2016/8/1 11:31:55         NaN    NaN   
3     2016/8/1 12:42:52  ...   2016/8/1 12:54:37         NaN    NaN   
4     2016/8/1 12:51:38  ...   2016/8/1 13:08:20         NaN    NaN   
..                  ...  ...                 ...         ...    ...   
941  2016/8/31 21:23:48  ...  2016/8/31 21:31:48         NaN    NaN   
942  2016/8/31 21:24:12  ...  2016/8/31 21:56:12         NaN    NaN   
943  2016/8/31 21:25:18  ...  2016/8/31 21:33:34         NaN    NaN   
944  2016/8/31 21:37:39  ...  2016/8/31 21:55:39         NaN    NaN   
945  2016/8/31 21:41:56  ...  2016/8/31 21:32:56         NaN    NaN   

               14      15                  16               17            18  \
0    order_number  org_id  print_doc_bill_num  lock_table_info  order_status   
1             NaN     330                 NaN              NaN             1   
2             NaN     328                 NaN              NaN             1   
3             NaN     330                 NaN              NaN             1   
4             NaN     330                 NaN              NaN             1   
..            ...     ...                 ...              ...           ...   
941           NaN     330                 NaN              NaN             1   
942           NaN     330                 NaN              NaN             1   
943           NaN     330                 NaN              NaN             1   
944           NaN     330                 NaN              NaN             1   
945           NaN     330                 NaN              NaN             1   

              19    20  
0          phone  name  
1    18688880641   苗宇怡  
2    18688880174    赵颖  
3    18688880276   徐毅凡  
4    18688880231   张大鹏  
..           ...   ...  
941  18688880307    李靖  
942  18688880305    莫言  
943  18688880327   习一冰  
944  18688880207   章春华  
945  18688880313   唐雅嘉  

[946 rows x 21 columns]

文本文件存储

• to_csv ( path_or_buf=None, sep=’,’, index= True…)

import os
print('菜品订单信息表写入文本文件之前---------文件列表:','\n',os.listdir('D:/pandas/tmp'))
order.to_csv('D:/pandas/tmp/orderInfo.csv',sep=';',index=False)
print('菜品订单信息表写入文本文件之后---------文件列表:','\n',os.listdir('D:/pandas/tmp'))

菜品订单信息表写入文本文件之前---------文件列表: 
 ['orderInfo.csv']
菜品订单信息表写入文本文件之后---------文件列表: 
 ['orderInfo.csv']

读 / 写 Excel 文件

• read_excel ( io, sheetname=0, encoding='gbk…)
• 要需要 pandas库
• excel 文件转换需要安装 xlrd 库，下面的代码才可以运行

user = pd.read_excel('D:/pandas/users.xlsx') # 读取 users.xlsx 文件
print('客户信息表长度:',len(user))

客户信息表长度: 734

Excel 文件存储

• to_excel (excel_writer=None, sheetname=‘None’…)

print('客户信息表写入Excel文件前-----目录列表：','\n',os.listdir('D:/pandas/tmp'))
user.to_excel('D:/pandas/tmp/userInfro.xlsx')
print('客户信息表写入Excel文件后-----目录列表：','\n',os.listdir('D:/pandas/tmp'))

客户信息表写入Excel文件前-----目录列表： 
 ['orderInfo.csv']
客户信息表写入Excel文件后-----目录列表： 
 ['orderInfo.csv', 'userInfro.xlsx']