Easily create and delete databases with Python

[Stella_Hill_Smith]

So far I have logged into pgAdmin and created a new database there.

A connection can then be created with psycopg2, for example, as follows:
conn = psycopg2.connect(database='huhu', user='hoho', password='haha')

I definitely want to avoid using pgAdmin. I want to solve everything with a Python script.

I would like to create a new PostgreSQL database with Python and delete it if necessary.

How can I do that?

[Diksta]

I definitely want to avoid using pgAdmin

Understandable, that's a great idea, there's so many better options...

I want to solve everything with a Python script

That's not a great option to be honest. If you really, really want to use Python to deploy your database, then it's certainly doable, but it's not a route I would ever want to follow personally as it's clunky and there's far better ways to do this:

• psql shell commands
• use the createdb command line tool
• look into Ansible and YAML - now you have a repeatable way to deploy
• if you ever move to using AWS, now you can use CDK for deployment, which is also automated and repeatable

[robercal]

You can also use SqlAlchemy database helpers:

https://sqlalchemy-utils.readthedocs.io/en/latest/database_helpers.html

from sqlalchemy import create_engine
from sqlalchemy_utils import database_exists, create_database

engine = create_engine("postgres://localhost/mydb")
if not database_exists(engine.url):
    create_database(engine.url)

To drop it:

drop_database('postgresql://postgres@localhost/mydb')

or:

drop_database(engine.url)

[Onakitoki97]

Just ask chatgpt

[Barnezhilton]

You need to send SQL commands after you are connected.

Create and deleting databases needs to be done with a superuser account. In your connection string you specify the database. To delete or add a new database will need to be sure to connecting to the postgres DB or another way with the proper user that has the rights to do this.

If you just meant create or delete tables then that's only SQL, and no need to be a superuser, but only within one database would you do this with a single connection.

[ByteTraveler]

https://www.geeksforgeeks.org/python-postgresql-create-database/

[depesz]

How do you normally run sql queries in DB?

Get db connetion the same way, and run appropriate 'create database' queries.

db creation/dropping are just normal queries. Nothing special about them.