我有以下問題。我想將 df 發送到 MySQL 資料庫。看我的代碼：
data = {'book_url': ['Tom'],
'book_id': [20],
'book_name': ['foo'],
'book_author': ['foo'],
'book_rating': ['foo'],
'book_reviews': ['foo'],
'book_year': ['foo'],
'book_description': ['foo'],
'book_genre': ['foo']
}
# Create DataFrame
my_df = pd.DataFrame(data)
my_df.to_sql("KNIHY", self.connection, if_exists="append", index=False)
self.connection.commit()
我的表KNIHY是使用以下方法創建的：
query = """
CREATE TABLE KNIHY (book_url TEXT, book_id TEXT, book_name TEXT, book_author
TEXT, book_rating TEXT, book_reviews TEXT,
book_year TEXT, book_description TEXT, book_genre TEXT)
"""
self.cursor.execute(query)
但我收到了這個錯誤：
Traceback (most recent call last):
File "/home/vojtam/Desktop/greads_scrape/venv/lib/python3.8/site-packages/pandas/io/sql.py", line 2056, in execute
cur.execute(*args, **kwargs)
File "/home/vojtam/Desktop/greads_scrape/venv/lib/python3.8/site-packages/pymysql/cursors.py", line 146, in execute
query = self.mogrify(query, args)
File "/home/vojtam/Desktop/greads_scrape/venv/lib/python3.8/site-packages/pymysql/cursors.py", line 125, in mogrify
query = query % self._escape_args(args, conn)
TypeError: not all arguments converted during string formatting
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/vojtam/Desktop/greads_scrape_prod/scrape_prehled.py", line 268, in <module>
main()
File "/home/vojtam/Desktop/greads_scrape_prod/scrape_prehled.py", line 237, in main
db.vlozit_df(df_vysledek)
File "/home/vojtam/Desktop/greads_scrape_prod/db.py", line 81, in vlozit_df
my_df.to_sql("KNIHY", self.connection, if_exists="append", index=False)
File "/home/vojtam/Desktop/greads_scrape/venv/lib/python3.8/site-packages/pandas/core/generic.py", line 2872, in to_sql
sql.to_sql(
File "/home/vojtam/Desktop/greads_scrape/venv/lib/python3.8/site-packages/pandas/io/sql.py", line 717, in to_sql
pandas_sql.to_sql(
File "/home/vojtam/Desktop/greads_scrape/venv/lib/python3.8/site-packages/pandas/io/sql.py", line 2225, in to_sql
table.create()
File "/home/vojtam/Desktop/greads_scrape/venv/lib/python3.8/site-packages/pandas/io/sql.py", line 856, in create
if self.exists():
File "/home/vojtam/Desktop/greads_scrape/venv/lib/python3.8/site-packages/pandas/io/sql.py", line 840, in exists
return self.pd_sql.has_table(self.name, self.schema)
File "/home/vojtam/Desktop/greads_scrape/venv/lib/python3.8/site-packages/pandas/io/sql.py", line 2236, in has_table
return len(self.execute(query, [name]).fetchall()) > 0
File "/home/vojtam/Desktop/greads_scrape/venv/lib/python3.8/site-packages/pandas/io/sql.py", line 2068, in execute
raise ex from exc
pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during string formatting
Process finished with exit code 1
我試圖將所有列轉換為字串，但沒有幫助：
for column in my_df:
my_df[column] = my_df[column].astype("string")
我使用以下方法連接到資料庫：
from configparser import ConfigParser
import pymysql as mysql
config = ConfigParser()
config.read("config_knihy.ini")
self.connection = mysql.connect(
user=config["knihy"]["user"],
passwd=config["knihy"]["password"],
host=config["knihy"]["host"],
db=config["knihy"]["database"],
)
self.cursor = self.connection.cursor()
query = """
CREATE TABLE KNIHY (book_url TEXT, book_id TEXT, book_name
TEXT, book_author TEXT, book_rating TEXT, book_reviews TEXT,
book_year TEXT, book_description TEXT, book_genre TEXT)
"""
self.cursor.execute(query)
請問這是什么意思？
uj5u.com熱心網友回復：
Pandas .to_sql 旨在與 SQLAlchemy 一起使用，SQLAlchemy 是一個資料庫介面。
engine = create_engine("mysql pymysql://user:[email protected]:3306/mydb")
my_df.to_sql("KNIHY", engine, if_exists="append", index=False)
插入是自動提交的。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/363700.html
標籤：Python sql-server 熊猫