Home » Beitrag verschlagwortet mit 'PostgresSQL'

Schlagwort-Archive: PostgresSQL

PostgresSQL mit Python (psycopg2)

Setup

Administering PostgresSQL

Using PSQL commandline tool of 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.
Start/Stop PostgresSQL Server.

!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:

CommandDescription
sudo -u <username> -ilog in as username. Default installed user is called postgres
createdb <dbname>
dropdb <dbname>
select * from tableA;


PSQL

CommandDescription
psql <dbname> [<username>]Initial login to the db starting the PSQL interactive tool.
\llist all databases on the server, owners, access levels
\c <dbname>connect to a DB
\dtshow DB tables
\d <tablename>describe table
\qquit 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()