Syntax Question When Attempting To Use A Python Variable in a PostgresSQL SELECT

Question

I am new to Python. While this is similar to a previous question I asked - I am now looking for some information specifically on syntax. I set up the following test below to attempt to take a number from a query and feed it into another query. The specific problem I am having is with the line "cursor.execute("SELECT text FROM feed WHERE id=" + str(rows))". I receive syntax errors.

When I print the query in Python, the record shows up in parenthesis with a comma after it, which I believe is what is causing the error (as these characters are unexpected for the next query).

Being new to this, my question is, should I use "Replace" to strip the result down to a number with no other characters, or, is there something different I should be doing. What is the proper way to pass this number in?

import psycopg2
try:
    connect_str = "dbname='mydatabase' user='myuser' host='localhost' " + \
                  "password='mypassword'"
    # establish a connection
    conn = psycopg2.connect(connect_str)
    # cursor to execute queries
    cursor = conn.cursor()
    # run am initial SELECT statement
    cursor.execute("SELECT result FROM mytable WHERE id=1")
    # assign fetched data to a variable named rows
    rows = cursor.fetchone()
    # for testing purposes, print the variable
    print(rows)
    # for testing purposes, use it in a sentence
    print("the following is your next row",rows)
    # feed the result stored in rows into another query
    cursor.execute("SELECT result FROM mytable WHERE id=" + str(rows))
    morerows = cursor.fetchone()
except Exception as e:
    print("cant connect")
    print(e)

Note: I also tried the following alternative to this line and received an error stating "not all arguments converted during string formatting" cursor.execute("SELECT text FROM feed WHERE id=" % (rows)) — ByRequest
– ByRequest, Commented Oct 23, 2018 at 21:31

klin · Accepted Answer · 2023-01-07 00:27:50Z

The cursor class has the very helpful method mogrify() which can be used to see how a query is constructed. You can try a query with it and next replace mogrify with execute to run a query.

You should carefully read Passing parameters to SQL queries in the documentation.

A few examples:

# pass an integer constant:
print(cursor.mogrify("SELECT result FROM mytable WHERE id= %s", (111, )))

# pass two variables:
a_number = 222
a_string = 'string'
print(cursor.mogrify("SELECT result FROM mytable WHERE id= %s or str = %s", (a_number, a_string)))

#pass a tuple:
args = (333, 'another_string')
print(cursor.mogrify("SELECT result FROM mytable WHERE id= %s or str = %s", args))

The above code gives the output:

b'SELECT result FROM mytable WHERE id= 111'
b"SELECT result FROM mytable WHERE id= 222 or str = 'string'"
b"SELECT result FROM mytable WHERE id= 333 or str = 'another_string'"

Collectives™ on Stack Overflow

Syntax Question When Attempting To Use A Python Variable in a PostgresSQL SELECT

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related