Setup

• Installation: Postgres Download page
• Installation psycopg2: install instructions found here
• Python

Administering PostgresSQL

Using PSQL commandline tool or PgAdmin GUI (included with the Windows installer PostgreSQL Database Download)
Der Pfad muss noch gesetzt werden um es aus dem Dos-Prompt heraus ausführen zu können.

PostgresSQL DB starten:

pg_ctl -D „C:\Program Files\PostgreSQL\13\data“ start

!template1 und postgres DBs sind nicht zum Verändern gedacht (ausser man will die DB Vorlage für neue DBs verändern)! Template1 DB ist – wie der Name schon sagt – ein Template für neu zu erstellendes DBs.

Postgres default port: 5432

postgres commands issued at the OS command line:

Command	Description
sudo -u <username> -i	log in as username. Default installed user is called postgres
createdb <dbname>
dropdb <dbname>
select * from tableA;

PSQL

Command	Description
psql <dbname> [<username>]	Initial login to the db starting the PSQL interactive tool.
\l	list all databases on the server, owners, access levels
\c <dbname>	connect to a DB
\dt	show DB tables
\d <tablename>	describe table
\q	quit PSQL tool

Python access to PostgresSQL using psycopg2

import psycopg2

conn = psycopg2.connect('dbname=testDB user=postgres password=*****')
cursor = conn.cursor()

# Open a cursor to perform database operations
cur = conn.cursor()

# drop any existing todos table
cur.execute("DROP TABLE IF EXISTS todos;")

# (re)create the todos table
# (note: triple quotes allow multiline text in python)
cur.execute("""
  CREATE TABLE todos (
    id serial PRIMARY KEY,
    description VARCHAR NOT NULL
  );
""")

todos = ["Abwaschen", "Putzen", "Aufgabenhilfe", "Ferienplanung"]

for tup in enumerate(todos):
    cur.execute('insert into todos (id, description) values (%s, %s)', tup)

# Alternative: Parameter resolution with named parameters by providing an parameter object:
for tup in enumerate(todos, 10):
    cur.execute('insert into todos (id, description) values (%(key)s, %(value)s)', { 'key' : tup[0], 'value' : tup[1] })

cur.execute('select * from todos')
results = cur.fetchall()

print('Read from DB: ')
for res2 in results:
    print(res2)

# commit, so it does the executions on the db and persists in the db
conn.commit()

cur.close()
conn.close()