爬虫python链接mysql数据库使用to_sql写入数据老报错的问题

网上有很多使用爬虫技术抓取数据并写入cvs的教学案例

抓下来也没大的问题，但是如果自己本地建立了mysql数据库，使用很多公开的教程，反倒实现不了功能，经常报这样那样的错误，比如常见的UserWarning: pandas only support SQLAlchemy connectable(engine/connection)错误！

经过不少时间的折腾终于找到了解决问题的办法，是因为to_sql办法网上大多数教程都是老版本的链接数据库的办法，新的版本的pandas只支持SQLAlchemy的办法链接

老的链接数据库的办法一般是这样的

import pandas as pd

import pymysql

# 创建数据库连接

conn = pymysql.connect(host='localhost', port=3306, user='root', password='password', db='database_name')

# 创建游标对象 cursor = conn.cursor()

# 创建一个DataFrame示例

data = {'Name': ['John', 'Mike', 'Bob'], 'Age': [25, 30, 35]}

df = pd.DataFrame(data)

# 将DataFrame插入MySQL表

# 如果该表不存在，可以使用CREATE TABLE语句来创建它

df.to_sql(name='table_name', con=conn, if_exists='replace', index=False)

# 关闭游标和连接

cursor.close()

conn.close()

这样的话直接报错！

那么就需要用新的办法链接

from sqlalchemy import create_engine

MYSQL_HOST = 'localhost'

MYSQL_PORT = '3306'

MYSQL_USER = 'root'

MYSQL_PASSWORD = '12345678'

MYSQL_DB = 'stocks'

### 使用pandas to_sql写入数据

engine = create_engine('mysql+pymysql://%s:%s@%s:%s/%s?charset=utf8'% (MYSQL_USER, MYSQL_PASSWORD, MYSQL_HOST, MYSQL_PORT, MYSQL_DB))

#engine = create_engine(conn)

df = pd.DataFrame(all_products,columns=keys)

#replce 清除并重写，append 叠加数据

df.to_sql('test_ifeng',engine, if_exists='replace', index=False)

#raise SystemExit

OK，这样我们就能看到mysql数据库存入了相关的数据

这是昨天爬虫入库的完整代码，有三种写入数据库的办法

import requests

from bs4 import BeautifulSoup

import csv

import pandas as pd

from sqlalchemy import create_engine

url = 'http://www.ifeng.com'

page = requests.get(url)

soup = BeautifulSoup(page.content, 'html.parser')

# 设置headers

headers = {"User-Agent":"Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/64.0.3282.186 Safari/537.36",

}

# 设置cookies

cookies = {}

cookies['mall'] = '1638237165.7.1148.523814'

cookies['JSESSIONID'] = '7D7F08E6CAC6989FDE82EBDEBBF9CB21'

# 发起请求

res = requests.get(url, cookies=cookies, headers=headers, timeout=5)

#res = requests.get(url)

#title = soup.title.text

all_products = []

#print(soup)

products = soup.select('p.index_news_list_p_5zOEF')

#raise SystemExit

for product in products:

title = product.select('a')[0].text

url = product.select('a')[0].attrs['href']

all_products.append({

"biaoti":title,

"link": url

})

#csv写入办法

keys = all_products[0].keys()

with open('ifeng新闻.csv', 'w', newline='', encoding='utf-8-sig') as output_file:

dict_writer = csv.DictWriter(output_file, keys)

dict_writer.writeheader()

dict_writer.writerows(all_products)

MYSQL_HOST = 'localhost'

MYSQL_PORT = '3306'

MYSQL_USER = 'root'

MYSQL_PASSWORD = '12345678'

MYSQL_DB = 'stocks'

### 使用pandas to_sql写入数据

engine = create_engine('mysql+pymysql://%s:%s@%s:%s/%s?charset=utf8'% (MYSQL_USER, MYSQL_PASSWORD, MYSQL_HOST, MYSQL_PORT, MYSQL_DB))

#engine = create_engine(conn)

df = pd.DataFrame(all_products,columns=keys)

#replce 清除并重写，append 叠加数据

df.to_sql('test_ifeng',engine, if_exists='replace', index=False)

#raise SystemExit

### 使用pandas to_csv写入数据

pd.DataFrame(all_products,columns=keys).to_csv('ifeng新闻1.csv', encoding='utf-8-sig')