Connect to an existing PostgreSQL cluster as a PostgreSQL SUPERUSER and execute SQL commands.
psycopg2 has a great API for interacting with PostgreSQL, once you provide it with the connection parameters for a given database.
However, what if your desired database and database user do not yet exist?
In order to create them, you will need to connect to PostgreSQL as a SUPERUSER.
- autodetects postgres setup, tested on
- Ubuntu 20.04 & PostgreSQL installed via
apt - Ubuntu 16.04 & PostgreSQL installed via
apt - Ubuntu 18.04 & PostgreSQL docker container
- MacOS 12 and PostgreSQL installed via
conda - Windows Server 2019 and PostgreSQL installed via
conda
- Ubuntu 20.04 & PostgreSQL installed via
- uses psycopg2 to connect if possible
- can use
sudoto become thepostgresUNIX user if necessary/possible (default Ubuntu PostgreSQL setups)
from pgsu import PGSU
pgsu = PGSU() # On Ubuntu, this may prompt for sudo password
pgsu.execute("CREATE USER newuser WITH PASSWORD 'newpassword'")
users = pgsu.execute("SELECT usename FROM pg_user WHERE usename='newuser'")
print(users)While the main point of the package is to guess how to connect as a postgres superuser, you can also provide partial or all information abut the setup using the dsn parameter.
These are the default settings:
from pgsu import PGSU
pgsu = PGSU(dsn={
'host': None,
'port': 5432,
'user': 'postgres',
'password': None,
'database': 'template1', # Note: you cannot drop databases you are connected to
})The package also comes with a very basic pgsu command line tool that allows users to execute PostgreSQL commands as the superuser:
$ pgsu "SELECT usename FROM pg_user"
Trying to connect to PostgreSQL...
Executing query: SELECT usename FROM pg_user
[('aiida_qs_leopold',),
('postgres',)]
Run the tests as follows:
pip install -e .[testing]
pytest
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent