CODEWITHSUNDEEP
pythonpandaspostgresqltypeerrorpsycopg2
user13802383
user13802383

Reputation:

psycopg2 typeerror: TypeError: not all arguments converted during string formatting

looked at this resource already with confusion still:

TypeError: not all arguments converted during string formatting in psycopg2

I am having this error in regards to adding a pandas dataframe being added to a database

user_df = [df.userId, df.firstName,df.lastName,df.gender,df.level]
user_df = pd.DataFrame(user_df)

here is what the userId firstName etc looks like

useId = 73 (may be an int or string unknown)
firstName = Jacob
LastName = Klein
gender = M
level = paid


user_df = [df.userId, df.firstName,df.lastName,df.gender,df.level]
user_df = pd.DataFrame(user_df)
for i, row in user_df.iterrows():
    cur.execute(user_table_insert, row)
    conn.commit()

with user_table_insert looking like this:

user_table_insert = ("""

INSERT INTO users(userId, firstName, lastName, gender, level)
VALUES(%s,%s,%s,%s,%s);

""")

with the user table create statement looking like this:

createuserstable = "CREATE TABLE IF NOT EXISTS users(userId VARCHAR, firstName VARCHAR, lastName VARCHAR, gender VARCHAR, level VARCHAR);"

and this is the error when my code attempts to do the insert into users model

Traceback (most recent call last):
  File "etl.py", line 111, in <module>
    main()
  File "etl.py", line 105, in main
    process_data(cur, conn, filepath='data/log_data', func=process_log_file)
  File "etl.py", line 94, in process_data
    func(cur, datafile)
  File "etl.py", line 61, in process_log_file
    cur.execute(user_table_insert, row)
TypeError: not all arguments converted during string formatting

not sure why I a getting this. Looking at the psycopg2 docs maybe one of you have ad a similar issue?

Upvotes: 0

Views: 215

Answers (1)

Adrian Klaver
Adrian Klaver

Reputation: 19742

Well if you do:

userId = 73
firstName = 'Jacob'
lastName = 'Klein'
gender = 'M'
level = 'paid'

user_df = [userId, firstName, lastName, gender, level] 
user_df = pd.DataFrame(user_df) 

for i, row in user_df.iterrows(): 
         print(row) 
                                                                                                                                                                               
0    73
Name: 0, dtype: object
0    Jacob
Name: 1, dtype: object
0    Klein
Name: 2, dtype: object
0    M
Name: 3, dtype: object
0    paid
Name: 4, dtype: object

You see that the row is made of Pandas objects. psycopg2 does not know how to adapt those, so you get the error. Besides which you are iterating over a single row and getting the elements in the row.

Not sure why you want to convert to a DataFrame anyway. Just use the list:

user_row = [userId, firstName, lastName, gender, level] 
cur.execute(user_table_insert, user_row)

Upvotes: 1

Related Questions