psycopg2 cannot execute multiple queries

Question

I'm having issues with executing multiple queries on my psql db using psycopg2. Example:

#!/usr/bin/env python
#-*- coding: utf-8 -*-

import psycopg2
from psycopg2.extras import RealDictCursor

def CreateUser(user, mySchema):

    conn = psycopg2.connect("dbname='postgres' user='root' password='somePassword' host='localhost'")
    cur = conn.cursor()
    cur.execute("""create user %s""" % (user)) 
    conn.commit()
    cur.close()
    conn.close()
    CreateSchema(user, mySchema)


def CreateSchema(user, mySchema):
    conn = psycopg2.connect("dbname='postgres' user='root' password='somePassword' host='localhost'")
    cur = conn.cursor()
    cur.execute("""create schema %s authorization %s """ % (user,mySchema))
    conn.commit()
    cur.close()
    conn.close()

def FetchUserInput():
    userInput = raw_input("UserName")
    mySchema = raw_input("SchemaName")
    CreateUser(userInput, mySchema)


FetchUserInput()

In this case, the second query fails with an error that the user created previously does not exist! If I only execute the CreateUser function, it works fine. If I execute it manually within psql, it works fine.

As If the first commit isnt executed on the database when I open up a second connection within the CreateSchema function, which makes no sense.

What am I doing wrong?

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Looks like you've just inverted the 2 parameters in the second query :

cur.execute("""CREATE SCHEMA %s AUTHORIZATION %s """ % (mySchema, user))

Some help from the doc:

CREATE SCHEMA schema_name [ AUTHORIZATION user_name ] [ schema_element [ ... ] ]

CREATE SCHEMA AUTHORIZATION user_name [ schema_element [ ... ] ]

CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION user_name ]

CREATE SCHEMA IF NOT EXISTS AUTHORIZATION user_name

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 10, 2017 at 21:17

PRMoureu

13.4k6 gold badges46 silver badges52 bronze badges

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

Collectives™ on Stack Overflow

psycopg2 cannot execute multiple queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related