0

When using this basic read_csv and to_sql script I get the following error code. Can somebody please explain what my system is doing wrong. I used to work but after a while I got these error messages. I have also looked at my MySQL database but it can also upload varchar(256).

import sqlalchemy
import mysql.connector
import pandas as pd


engine = sqlalchemy.create_engine('mysql+pymysql://boaz:............./Boaz', pool_recycle=3600, echo=False)
connection_alchemy = engine.connect()

filename = r'C:\Users\Bkuijer\Documents\contact details.csv'

df = pd.read_csv(filename, delimiter=';', encoding='utf-8')

print(df)

df.to_sql('Proef', con=engine, index=False, if_exists='append')

C:\Users\Bkuijer\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\cursors.py:170: Warning: (1287, "'@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead")
  result = self._query(query)
   Name      Adres   Nation
0  Boaz  Amsterdam  Holland
Traceback (most recent call last):
  File "c:/Users/Bkuijer/Documents/python_work/EPCOR/DMM database maken 2300/Mysql_upload_test.py", line 17, in <module>
    df.to_sql('Proef', con=engine, index=False, if_exists='append')
  File "C:\Users\Bkuijer\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\core\generic.py", line 2663, in to_sql
    method=method,
  File "C:\Users\Bkuijer\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\io\sql.py", line 521, in to_sql
    method=method,
  File "C:\Users\Bkuijer\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\io\sql.py", line 1317, in to_sql
    table.insert(chunksize, method=method)
  File "C:\Users\Bkuijer\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\io\sql.py", line 755, in insert
    exec_insert(conn, keys, chunk_iter)
  File "C:\Users\Bkuijer\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pandas\io\sql.py", line 669, in _execute_insert
    conn.execute(self.table.insert(), data)
  File "C:\Users\Bkuijer\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\base.py", line 1449, in execute
    params)
  File "C:\Users\Bkuijer\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\base.py", line 1584, in _execute_clauseelement
    compiled_sql, distilled_params
  File "C:\Users\Bkuijer\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\base.py", line 1691, in _execute_context   
    context)
  File "C:\Users\Bkuijer\AppData\Local\Programs\Python\Python37-32\lib\site-packages\sqlalchemy\engine\default.py", line 333, in do_execute       
    cursor.execute(statement, parameters)
  File "C:\Users\Bkuijer\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\cursors.py", line 168, in execute
    query = self.mogrify(query, args)
  File "C:\Users\Bkuijer\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymysql\cursors.py", line 147, in mogrify
    query = query % self._escape_args(args, conn)
TypeError: %b requires a bytes-like object, or an object that implements __bytes__, not 'str'
sjakobi
  • 3,546
  • 1
  • 25
  • 43
Boaz
  • 21
  • 3
  • Can you add the output of printing `df`? – mabergerx Jul 17 '20 at 08:17
  • Its in there: ``` Name Adres Nation 0 Boaz Amsterdam Holland – Boaz Jul 17 '20 at 09:22
  • I am unable to reproduce your issue using pandas 1.0.5, SQLAlchemy 1.3.18, and PyMySQL 0.9.3 under Python38-32. – Gord Thompson Jul 17 '20 at 13:27
  • Answer can be found on a similair question i asked. https://stackoverflow.com/questions/62912357/i-get-typeerror-cannot-use-a-string-pattern-on-a-bytes-like-object-when-using-t – Boaz Jul 31 '20 at 10:53
  • Does this answer your question? [I Get TypeError: cannot use a string pattern on a bytes-like object when using to\_sql on dataframe python 3](https://stackoverflow.com/questions/62912357/i-get-typeerror-cannot-use-a-string-pattern-on-a-bytes-like-object-when-using-t) – Gord Thompson Jul 31 '20 at 12:16

0 Answers0