Error when converting tuple to string in Python

Question

I have a tuple like this which is basically a DB record

record = ('R70:Congestion Ratio', 'pm_201204151900_KPI1.csv', 'pmexport_04_15_2012_20_00.tar.gz')

I am trying to convert it to tuple and then a string to get the query

"INSERT INTO PM_NEW_COUNTERS (COUNTER_NAME, CSV_FILE_NAME, TAR_FILE_NAME) VALUES %s" %(tuple(record))

However I am getting TypeError: not all arguments converted during string formatting What am I doing wrong ? Is there a better to achieve what I am trying to achieve ?

user647772 · Accepted Answer · 2012-10-11 13:00:16Z

5

Don't build SQL like this. Use the proper form of your database engine.

In SQLite, it works like this:

cur.execute("""INSERT INTO PM_NEW_COUNTERS
  (COUNTER_NAME, CSV_FILE_NAME, TAR_FILE_NAME)
  VALUES (?, ?, ?)""", (a, b, c))

For details see the Python Database API Specification v2.0.

answered Oct 11, 2012 at 13:00

user647772

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

Comments

Inbar Rose · Accepted Answer · 2012-10-11 13:04:32Z

1

first of all, you shouldent be trying to make queries like this. as stated before.

secondly, to answer your question about string formatting.

record = ('R70:Congestion Ratio', 'pm_201204151900_KPI1.csv', 'pmexport_04_15_2012_20_00.tar.gz')

record is already a tuple.

secondly, you are trying to put the tuple which has 3 values, into only 1 location in your string.

so instead do:

"INSERT INTO PM_NEW_COUNTERS (COUNTER_NAME, CSV_FILE_NAME, TAR_FILE_NAME) VALUES %s" % " ".join(record))

or if you want more control:

"INSERT INTO PM_NEW_COUNTERS (COUNTER_NAME, CSV_FILE_NAME, TAR_FILE_NAME) VALUES %s %s %s" % (record[0], record[1], record[2])

answered Oct 11, 2012 at 13:04

Inbar Rose

43.8k24 gold badges91 silver badges137 bronze badges

3 Comments

user647772 Over a year ago

Don't give bad advise. Building SQL like this opens the door for nasty attacks.

Inbar Rose Over a year ago

@Tichodroma you are correct. the OP never said anything about SQL though. im just helping with the specific problem of string formatting he is asking about.

user647772 Over a year ago

Well, if the question doesn't contain SQL, I don't know how SQL looks.

Cemre Mengü · Accepted Answer · 2012-10-11 14:09:29Z

0

You want more %s there:

"INSERT INTO PM_NEW_COUNTERS (COUNTER_NAME, CSV_FILE_NAME, TAR_FILE_NAME) VALUES ('%s', '%s', '%s')" %(tuple(record))

edited Oct 11, 2012 at 14:09

Cemre Mengü

18.9k31 gold badges118 silver badges185 bronze badges

answered Oct 11, 2012 at 13:02

Bogdan

8,3166 gold badges51 silver badges66 bronze badges

2 Comments

user647772 Over a year ago

No, he wants to use the Python Database API Specification v2.0.

Bogdan Over a year ago

I agree that is the way to go, but this was the error he was getting.

Shubhanshu Mishra · Accepted Answer · 2012-10-11 13:25:30Z

0

Use the following, you need to add a comma and remove the % charecter.

>>> str = "INSERT INTO PM_NEW_COUNTERS (COUNTER_NAME, CSV_FILE_NAME, TAR_FILE_NAME) VALUES ('%s')" %"', '".join(record)
>>> str
"INSERT INTO PM_NEW_COUNTERS (COUNTER_NAME, CSV_FILE_NAME, TAR_FILE_NAME) VALUES ('R70:Congestion Ratio', 'pm_201204151900_KPI1.csv', 'pmexport_04 _15_2012_20_00.tar.gz')"
>>>

Sorry about the last answer. Here is the updated code.

I would highly suggest using the built in db driver for your query however if you do want to build a string using the tuple my new code will produce the desired output.

edited Oct 11, 2012 at 13:25

answered Oct 11, 2012 at 12:58

Shubhanshu Mishra

6,7506 gold badges25 silver badges24 bronze badges

2 Comments

poke Over a year ago

OP obviously wants to use the string formatting operator % to insert the values…

Shubhanshu Mishra Over a year ago

I am sorry for the error, I have updated to code to just build the query string using the tuple. Its not the right way to interact with DB but for creating the string query my code works.

Collectives™ on Stack Overflow

Error when converting tuple to string in Python

4 Answers 4

Comments

3 Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related