Inserting tuple into postegresql with python - Select statment-CodePudding

Hello I am trying to insert to a pgadmin table using python, I used execute and it worked, but for my second aprt i need to use a fucntion, I got everything working except the inserts with select, it tells my syntax error, or forgot comma, literally everything. Im new, so help would be apprecitated .

def insrtDirector(q,w,e,r,t,y,u):
        sql1 = (q,w,e,r,t,y,u)
        insrt = """INSERT INTO "Director" VALUES (%s,%s,%s,%s,%s,%s,%s)"""
        cur.execute(insrt,sql1)
    insrtDirector(uuid.uuid4(), (SELECT "UName" from University WHERE "UName" = 'University College London') , (SELECT "DName" from Department WHERE "DName" ='English') , 'Christopher (3)', 'Nolan (3)', 1970, 'Westminster, London, United Kingdom' )

Error

 insrtDirector(uuid.uuid4(), (SELECT "UName" from University WHERE "UName" = 'University College London') , (SELECT "DName" from Department WHERE "DName" ='English') , 'Christopher (3)', 'Nolan (3)', 1970, 'Westminster, London, United Kingdom' )
                                        ^^^^^^^
SyntaxError: invalid syntax

CodePudding user response：

You need to properly use subqueries (google it!). Try something like this, it might work (and please fix your variable names, qwertyu is not good, should be descriptive like unique_id, uname, dname, first_name, etc.)

def insrtDirector(q,w,e,r,t,y,u):
    # Assume w and e are always subqueries with one result
    # Also assume no outside source can supply w and e (must be sanitized to not be subject to sql injection)
    insrt = "INSERT INTO Director VALUES (%s,{uname_subquery},{dname_subquery},%s,%s,%s,%s)".format(
        uname_subquery=w,
        dmane_subquery=e,
    )
    # This print command is just temporary so you can see what it looks like
    print("insert command:", insrt)
    sql1 = (q,r,t,y,u)
    cur.execute(insrt, sql1)

insrtDirector(
    uuid.uuid4(),
    "(SELECT UName from University WHERE UName = 'University College London')",
    "(SELECT DName from Department WHERE DName = 'English')",
    'Christopher (3)',
    'Nolan (3)',
    1970,
    'Westminster, London, United Kingdom',
)