Postgres

Download Postgres: PostgreSQL: Downloads

Set up database credentials

postgres=# \password new_name
postgres=# CREATE DATABASE db_name;

Connection

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)

Insert

insert = """
    INSERT INTO 
    table (name, date)
    VALUES (%s, %s)
"""
cursor.execute(insert, ("Hello", "2014-09-03"))

Update

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;