Adding dataframe values as parameters to sql query

Question

I am trying to iterate through a dataframe and fetching values from indivdidual column to use as my parameters in sql query.

for index,frame in df1.iterrows():

      sql = "select * from issuers where column_1 = %s;"
      cur.execute(sql, frame['column_1'])
      row = cur.fetchone()
      id = row[0]
      print id

But I am getting the following error

"TypeError: not all arguments converted during string formatting"

How can I solve this? In case I need to add multiple parameters, how can I do that?

jeanggi90 · Accepted Answer · 2018-09-20 11:01:35Z

1

Instead of this:

cur.execute(sql, frame['column_1'])

Try this:

cur.execute(sql, [frame['column_1']])

The second parameter of execute is a list containing all values to be inseted into sql.

In order to insert multiple values use something as follows:

sql = "select * from issuers where column_1 = %s and column_2 = %s;"
cur.execute(sql, ["val1", "val2"])

For more information please refere to the documentation

EDIT

Here an example for INSERT INTO in SQL.

sql = "INSERT INTO user (firstname, lastname) VALUES (%s, %s)"
cur.execute(sql, ["John", "Doe"])

edited Sep 20, 2018 at 11:01

answered Sep 20, 2018 at 9:32

jeanggi90

7619 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jeanggi90 Over a year ago

@themaster I have added an example in my answer above. I hope that helps

Collectives™ on Stack Overflow

Adding dataframe values as parameters to sql query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related