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

r囧r小猫 2023-07-24 13:26 22阅读 0赞

# 数据库文件读写 #

*  使用sqlalchemy建立连接
 *  需要知道数据库的相关参数，如数据库IP地址、用户名和密码等
 *  通过pandas种read\_sql 函数读入，读取完以后是dataframe格式
 *  通过dataframe的to\_sql方法保存

## 数据库连接 ##

*  conn = create\_engine(‘mysql+pymysql://user:password@IP:3306/db’)
    
     *  user: 用户名
     *  password: 密码
     *  IP: 服务器IP，本地电脑用localhost
     *  3306: 默认端口号
     *  db: 数据库名称

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)

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)

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>id</th> 
   <th>title</th> 
   <th>href</th> 
   <th>author</th> 
   <th>infos</th> 
  </tr> 
 </thead> 
 <tbody> 
  <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> 
 </tbody> 
</table>

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')