pandas.io.sql.DatabaseError: ': not all arguments converted during string formatting Python pan-CodePudding

I have a following issue. I would like to send df to MySQL database. See my code:

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

My table KNIHY is created using:

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)

But I got this Error:

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

I tried to convert all column to string, but it did not help:

for column in my_df:
   my_df[column] = my_df[column].astype("string")

I connect to the db using:

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)

What does that mean please?

CodePudding user response：

Pandas .to_sql is meant to be used with SQLAlchemy, which is a DB interface.

engine = create_engine("mysql pymysql://user:[email protected]:3306/mydb")

my_df.to_sql("KNIHY", engine, if_exists="append", index=False)

The inserts are commited automatically.