Python SQL framework

gistfile1.md

Basic processing

import psycopg2
from prettytable import PrettyTable

conn = psycopg2.connect("dbname=databasename user=postgres")
cur = conn.cursor()
conn.autocommit = True

def clean():

    sql = '''
    ALTER TABLE my_table ADD COLUMN new_column INT;
    UPDATE my_table SET new_column=10000*(some_column/10000)::INT;
    '''
    cur.execute(sql)


def segment_X():
    sql = '''

    SELECT new_column,
           COUNT(*),
           SUM(CASE WHEN another_column=2 THEN 1 ELSE 0 END)
    FROM
        my_table
    GROUP BY
        new_column
    ORDER BY
        new_column;
    '''
    cur.execute(sql)

    data = [[ 'New Column', '#', "XX" ]]

    table = PrettyTable(['New Column', '#', "XX" ])
    for row in cur.fetchall():
        table.add_row(row)

    print table

if __name__ == "__main__":
    clean()
    segment_X()