from psycopg2 import sql
conn = psycopg2.connect(
user = "new_name",
password="password",
host="127.0.0.1",
port="port_number",
database="db_name"
)
cursor = conn.cursor()
Commit Changes
conn.commit()
Create Table
table = (
"""
CREATE TABLE IF NOT EXISTS
table (
id SERIAL PRIMARY KEY,
name TEXT,
date DATE,
status BOOLEAN,
num INTEGER,
UNIQUE (num)
);
"""
)
cursor.execute(table)
update = """
UPDATE table
SET name = %s,
date = %s
WHERE num = %s
"""
cursor.execute(update, ("Hi", "2019-03-20"))
Select
cursor.execute("""
SELECT num FROM table
WHERE num = %s;
""", (2, ))
Get value in row
cursor.fetchone()[column_num]
Rollbacks
cursor.execute('SAVEPOINT sp2')
try:
cursor.execute("""
SELECT num FROM table
WHERE num = %s;
""", (2, ))
except psycopg2.IntegrityError:
cursor.execute('ROLLBACK TO SAVEPOINT sp2')
Convert Postgres Database to CSV
COPY table_name/query TO '/Users/laura/...path_to/file.csv' DELIMITER ',' CSV HEADER;