数据清洗之 数据库文件读写

import pandas as pd
import pymysql
from sqlalchemy import create_engine

conn = create_engine('mysql+pymysql://root:root@localhost:3306/data_test')

# 读取数据库内容
sql = 'select * from qidianreader'
df1 = pd.read_sql(sql, con=conn)

df1.head(5)

.dataframe tbody tr th:only-of-type {

    vertical-align: middle;

}

.dataframe tbody tr th {

    vertical-align: top;

}

.dataframe thead th {

    text-align: right;

}

<tr style="text-align: right;">

  <th></th>

  <th>id</th>

  <th>title</th>

  <th>href</th>

  <th>author</th>

  <th>infos</th>

</tr>

<tr>

  <th>0</th>

  <td>1</td>

  <td>庆帝番外</td>

  <td>https://book.qidian.com/info/1017372661</td>

  <td>祝家大郎</td>

  <td>醒掌天下权，醉卧美人膝，五千年风华烟雨，是非成败转头空！</td>

</tr>

<tr>

  <th>1</th>

  <td>2</td>

  <td>宋北云</td>

  <td>https://book.qidian.com/info/1018027097</td>

  <td>伴读小牧童</td>

  <td>醒掌天下权，醉卧美人膝，五千年风华烟雨，是非成败转头空！</td>

</tr>

<tr>

  <th>2</th>

  <td>3</td>

  <td>美漫之超人</td>

  <td>https://book.qidian.com/info/1018978566</td>

  <td>白蘸糖</td>

  <td>星海漫游，时空穿梭，机械科技，目标是未知的星辰大海！</td>

</tr>

<tr>

  <th>3</th>

  <td>4</td>

  <td>李朝万古一逆贼</td>

  <td>https://book.qidian.com/info/1015407245</td>

  <td>秽多非人</td>

  <td>醒掌天下权，醉卧美人膝，五千年风华烟雨，是非成败转头空！</td>

</tr>

<tr>

  <th>4</th>

  <td>5</td>

  <td>废土修真的日常</td>

  <td>https://book.qidian.com/info/1016234812</td>

  <td>枯玄</td>

  <td>热血的少年，为打破次元壁一往无前！</td>

</tr>

def query(table):
    host = 'localhost'
    user = 'root'
    password = 'root'
    database = 'db_qidian'
    port = 3306
    conn = create_engine('mysql+pymysql://{}:{}@{}:{}/{}'.format(user, password, host, port, database))
    sql = 'select * from ' + table
    result = pd.read_sql(sql, con=conn)
    return result

df2 = query('qidianreader')

df2.head(10)

.dataframe tbody tr th:only-of-type {

    vertical-align: middle;

}

.dataframe tbody tr th {

    vertical-align: top;

}

.dataframe thead th {

    text-align: right;

}

<tr style="text-align: right;">

  <th></th>

  <th>id</th>

  <th>title</th>

  <th>href</th>

  <th>author</th>

  <th>infos</th>

</tr>

<tr>

  <th>0</th>

  <td>1</td>

  <td>庆帝番外</td>

  <td>https://book.qidian.com/info/1017372661</td>

  <td>祝家大郎</td>

  <td>醒掌天下权，醉卧美人膝，五千年风华烟雨，是非成败转头空！</td>

</tr>

<tr>

  <th>1</th>

  <td>2</td>

  <td>宋北云</td>

  <td>https://book.qidian.com/info/1018027097</td>

  <td>伴读小牧童</td>

  <td>醒掌天下权，醉卧美人膝，五千年风华烟雨，是非成败转头空！</td>

</tr>

<tr>

  <th>2</th>

  <td>3</td>

  <td>美漫之超人</td>

  <td>https://book.qidian.com/info/1018978566</td>

  <td>白蘸糖</td>

  <td>星海漫游，时空穿梭，机械科技，目标是未知的星辰大海！</td>

</tr>

<tr>

  <th>3</th>

  <td>4</td>

  <td>李朝万古一逆贼</td>

  <td>https://book.qidian.com/info/1015407245</td>

  <td>秽多非人</td>

  <td>醒掌天下权，醉卧美人膝，五千年风华烟雨，是非成败转头空！</td>

</tr>

<tr>

  <th>4</th>

  <td>5</td>

  <td>废土修真的日常</td>

  <td>https://book.qidian.com/info/1016234812</td>

  <td>枯玄</td>

  <td>热血的少年，为打破次元壁一往无前！</td>

</tr>

<tr>

  <th>5</th>

  <td>6</td>

  <td>重生写推理小说</td>

  <td>https://book.qidian.com/info/1016350338</td>

  <td>别人家的小猫咪</td>

  <td>重生过去、畅想未来、梦幻现实，再塑传奇人生！</td>

</tr>

<tr>

  <th>6</th>

  <td>7</td>

  <td>日本战国走一遭</td>

  <td>https://book.qidian.com/info/1012757932</td>

  <td>秽多非人</td>

  <td>醒掌天下权，醉卧美人膝，五千年风华烟雨，是非成败转头空！</td>

</tr>

<tr>

  <th>7</th>

  <td>8</td>

  <td>西游里会穿越的猴</td>

  <td>https://book.qidian.com/info/1018166616</td>

  <td>妖怪的影子</td>

  <td>修仙觅长生，热血任逍遥，踏莲曳波涤剑骨，凭虚御风塑圣魂！</td>

</tr>

<tr>

  <th>8</th>

  <td>9</td>

  <td>我一不小心就僵了</td>

  <td>https://book.qidian.com/info/1018205919</td>

  <td>巫九</td>

  <td>考古探险、鉴宝收藏，侦探推理、诡秘分析，戏说传承古今中外的民间悬疑文化！</td>

</tr>

<tr>

  <th>9</th>

  <td>10</td>

  <td>我真是大昏君</td>

  <td>https://book.qidian.com/info/1017587444</td>

  <td>样样稀松</td>

  <td>醒掌天下权，醉卧美人膝，五千年风华烟雨，是非成败转头空！</td>

</tr>

import os
os.getcwd()

'D:\\Jupyter\\notebook\\Python数据清洗实战\\数据清洗之文件读写'

os.chdir('D:\\Jupyter\\notebook\\Python数据清洗实战\\数据')

df = pd.read_csv('baby_trade_history.csv')

try:
    df.to_sql('testdf', con=conn, index=False, if_exists='replace')
except:
    print('error')