CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
user2497586
user2497586

Reputation:

PostgreSQL TypeError: not all arguments converted during string formatting

I am executing a query in psycopg2 linked up to a PostgreSQL database. Here is the code in question: 

with open('dataFile.txt', 'r') as f:
    lines = f.readlines()
    newLines = [line[:-1] for line in lines]
    curr=conn.cursor()
    lineString = ','.join(newLines)
    curr.execute("SELECT fields.fieldkey FROM fields LEFT JOIN zone ON zone.fieldkey=fields.fieldkey WHERE zone.zonekey = %s;", (newLines[0]))
    rows = curr.fetchall()

There's no issue connecting to the DB, and the type of lines[0] is definitely string, I checked that. Is there something wrong in the syntax of my string formatting?

The error I get, to clarify is this:

TypeError: not all arguments converted during string formatting

Upvotes: 10

Views: 18087

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125524

There must be a comma after lines[0] to make that a tuple.

curr.execute("""
    SELECT fields.fieldkey
    FROM fields
    LEFT JOIN zone ON zone.fieldkey=fields.fieldkey
    WHERE zone.zonekey = %s;
""", (lines[0],))

Since the execute method is expecting a sequence (or a mapping) it iterates over the string you provided surrounded by parenthesis. So it is necessary to explicitly make that a tuple. The same result, with clearer code, can be had using the tuple function:

(tuple(lines[0]))

Upvotes: 26

Related Questions