Python数据库与DataFrame

Python数据库与DataFrame

安装必须的包：

pip install sqlalchemy
pip install pandas
pip install pymysql

用交互式 python：

>>> import pandas as pd
>>> from sqlalchemy import create_engine
>>>
>>> db_info = {'user':'robotuser',
...            'password':'robotuser@123',
...            'host':'rm-wz9f3mhz6y24t5822.mysql.rds.aliyuncs.com',
...            'database':'robotdb'
...           }
>>> engine = create_engine('mysql+pymysql://%(user)s:%(password)s@%(host)s/%(database)s?charset=utf8' % db_info,encoding='utf-8')
>>>
>>>
>>> sql = "SELECT `create_time` , `robot_id`,`keyword`  FROM `stat_message_keyword` where `create_time` BETWEEN '2018-01-01' and '2018-01-08'"
>>> df = pd.read_sql(sql, engine)
>>> df
    create_time         robot_id   keyword
0    2018-01-01  WX-93iuoqio92kd      专用发票
1    2018-01-01  WX-93iuoqio92kd        材料
2    2018-01-01  WX-93iuoqio92kd        认证
3    2018-01-01  WX-93iuoqio92kd        办理
..          ...              ...       ...
236  2018-01-06  WX-93iuoqio92kd        办理
237  2018-01-06  WX-93iuoqio92kd        预缴
238  2018-01-06  WX-93iuoqio92kd       增值税