3

I am trying to create a database with pg8000 driver of postgressql , but unable to create. Creating a db manually and then connecting to it works fine with me, but i need to create db with my code. I am getting error "sqlalchemy.exc.ProgrammingError: (pg8000.ProgrammingError)". I have tried below code for creating a db.

from sqlalchemy import create_engine
dburl = "postgresql+pg8000://user:pswd@myip:5432/postgres/"
engine = create_engine(dburl)
conn = engine.connect()
conn.execute("COMMIT")
conn.execute("CREATE DATABASE qux")

I also tried with below -

from sqlalchemy import create_engine
from sqlalchemy.engine import url
settings ={"drivername" : "postgresql+pg8000", "host" : "myip","port" : 5432,"username" : "user","password" : "pswd","database" : "MyTestDB"}
db=create_engine(url.URL(**settings))
db.execute("commit")

This is the exact Error i am getting """sqlalchemy.exc.ProgrammingError: (pg8000.ProgrammingError) ('ERROR', '25001', 'CREATE DATABASE cannot run inside a transaction block') [SQL: 'create database workDB']""""

Please suggest as to how i can create this db...

Improve this question
3
  • please, add full error message into post Commented Dec 19, 2018 at 12:20
  • edited with exact error Commented Dec 19, 2018 at 13:28
  • @tec_abhi Ever get an answer to this? Commented Sep 30, 2019 at 15:33

2 Answers 2

Reset to default
2

Here's a solution:

from sqlalchemy import create_engine
dburl = "postgresql+pg8000://user:pswd@myip:5432/postgres/"
engine = create_engine(dburl)
conn = engine.connect()

con.rollback()  # Make sure we're not in a transaction
con.autocommit = True  # Turn on autocommit

conn.execute("CREATE DATABASE qux")
 
con.autocommit = False  # Turn autocommit back off again

The docs talk about this problem of executing commands that can't be run in a transaction. The thing is that pg8000 automatically executes a begin transaction before any execute() if there isn't already a transaction in progress. This is fine until you come to execute a command that can't be executed inside a transaction. In that case you have to enter autocommit mode, which implicitly starts a transaction before the statement and commits it afterwards, but automatically avoids doing this if (like CREATE DATABASE) the statement can't be executed within a transaction.

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

Comments

0

A better solution is turning on autocommit at creation of engine.

engine = create_engine(dburl, isolation_level="AUTOCOMMIT")

to avoid the confusing and potentially dangerous rollback() before turning autocommit on.

SQLAlchemy does a lot of probes under the hood with the transactions before handing over a connection to user. The rollback might break some of them, especially not well-tested databases that choose to use the postgres wire protocols.

Please see my detailed answer at postgres pg8000 create database

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.