How to use a string variable in psycopg2 select query?

Question

Error while fetching data from PostgreSQL column "u62194" does not exist

sql = """select userid from myusers WHERE userid='u62194' """ Of
course the hardcoded value works as it should.

import psycopg2

def select_tables():
    someuserid = 'u62194'
    print(someuserid)
    """ Run a select using a variable that is a string """
    sql = """select userid from myusers WHERE userid=%s """ %someuserid
    conn = None
    try:
        conn = psycopg2.connect(user = "postgres",
              password = "xxxxxx",
              host = "127.0.0.1",
              port = "5432",
              database = "mydb")
        cur = conn.cursor()
        cur.execute(sql, (select_tables,))
        print(cur.fetchone())
        cur.close()

    """ "myusers" table: id     userid 1      u51884 2      u62194 3      u26922  """

I should get: u62194

Answer 1

I think you're using the query parameters incorrectly. Pass a list instance as a parameter to the execute function to pass your query parameters. From memory the psycopg2 manual explicitly discourages doing this in the manner you were trying. Try something closer to this:

import psycopg2

someuserid = "u62194"

conn = psycopg2.connect(
    user = "postgres",
    password = "xxxxxx",
    host = "127.0.0.1",
    port = "5432",
    database = "mydb"
)

cur = conn.cursor()

# Use a list here to insert query parameters into the query string.
cur.execute(
    """
    SELECT userid 
    FROM myusers u
    WHERE u.userid = %s;
    """,
    [someuserid,]
)

result = cur.fetchone()

print(result)

cur.close()