In [1]:

    

import json
import psycopg2

_db='fred'
_user='fred'
_psw='763160'
_host='localhost'
_port=5432


    

In [ ]:

    

def connect():
    con = None
    try:
        con = psycopg2.connect(database=_db,
                               user=_user,
                               password=_psw,
                               host=_host,
                               port=_port)

    except psycopg2.DatabaseError, e:
        print('Error %s' % e)

    return con


    

In [ ]:

    

def delete_table(table):
    con = connect()
    if con :
        try:
            cur = con.cursor()
            cur.execute('DROP TABLE ' + table)
            con.commit()
        except Exception as e:
            return False

        con.close()
        return True
    return False


    

In [ ]:

    

def create_table(table):
    con = connect()
    if con :
        try:
            cur = con.cursor()
            cur.execute('CREATE TABLE ' + table + '(ID INT PRIMARY KEY NOT NULL, data TEXT NOT NULL)')
            con.commit()
        except Exception as e:
            return False

        con.close()
        return True
    return False

print(create_table('test'))
print(create_table('test'))
print(delete_table('test'))


    

In [ ]:

    

def table_exist(table):
    con = connect()
    if con :
        try:
            cur = con.cursor()
            cur.execute("SELECT * FROM information_schema.tables WHERE table_name='" + table + "';")
        except Exception as e:
            return False

        con.close()
        return bool(cur.rowcount)
    return False

create_table('test')
print(table_exist('toto'))
print(table_exist('test'))
delete_table('test')


    

In [ ]:

    

def create_row(table, id, data):
    con = connect()
    if con :
        try:
            cur = con.cursor()
            cur.execute("INSERT INTO " + table + "(ID,data) " + "VALUES("+ str(id) + ", '"+ json.dumps(data) + "')" )
            con.commit()
        except Exception as e:
            print('row',e)
            return False

        con.close()
        return True
    return False

create_table('test')
print(create_row('test', 1, 'toto'))
print(create_row('test', 1, 'test'))
delete_table('test')


    

In [ ]:

    

def row_exist(table, id):
    con = connect()
    if con :
        try:
            cur = con.cursor()
            cur.execute("SELECT * FROM " + table + " WHERE ID='" + str(id) + "';")
        except Exception as e:
            return False

        con.close()
        return bool(cur.rowcount)
    return False


    

In [ ]:

    

def store(table, id, data):
    if not table_exist(table):
        create_table(table)
    if not row_exist(table, id):
        create_row(table, id, data)

    con = connect()
    if con :
        try:
            cur = con.cursor()
            cur.execute('UPDATE ' + table + ' SET data=\''+ json.dumps(data) +'\' WHERE ID=' + str(id))
            con.commit()
        except Exception as e:
            print('store', e)
            return False

        con.close()
        return True
    return False

print(store('test', 1, 'toto'))
print(store('test', 1, 'test'))
delete_table('test')


    

In [ ]:

    

def restore(table, id):
    if not table_exist(table) or not row_exist(table, id):
        return ''

    con = connect()
    if con :
        try:
            cur = con.cursor()
            cur.execute('SELECT data FROM ' + table + ' WHERE ID=' + str(id))
            records = cur.fetchall()
            data = json.loads(records[0][0])
        except Exception as e:
            return None

        con.close()
        return data
    return None
store('test', 1, 'toto')
print(restore('test2', 1))
print(restore('test', 2))
print(restore('test', 1))
delete_table('test')


    

In [ ]:

    

req_tables = "SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema = 'public' ORDER BY table_schema,table_name;"
con = connect()
data={}
if con :
    try:
        cur = con.cursor()
        cur.execute(req_tables)
        records = cur.fetchall()
        for record in records:
            table = record[0]
            data[table] = {}
            data_req = 'SELECT * FROM {table};'.format(table=table)
            cur.execute(data_req)
            rows = cur.fetchall()
            for row in rows:
                data[table][row[0]] = json.loads(row[1])
    except Exception as e:
        pass

    con.close()
print(data)


    

In [ ]:

    

req_tables = "SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema = 'public' ORDER BY table_schema,table_name;"
con = connect()
if con :
    try:
        cur = con.cursor()
        cur.execute(req_tables)
        records = cur.fetchall()
        for record in records:
            table = record[0]
            drop_req = 'DROP TABLE {table};'.format(table=table)
            cur.execute(drop_req)
        con.commit()
        
        for table in list(data.items()):
            table_name = table[0]
            print(table_name)
            for row in list(table[1].items()):
                id = row[0]
                print(store(table_name, id, row[1]))
    except Exception as e:
        pass

    con.close()


    

In [2]:

    

from postgres_store import postgres_store


    

In [3]:

    

db = postgres_store('fred', 'fred', '763160', 'localhost', 5432)
db_prod = postgres_store('dc7m5co1u7n7ka', 'vfumyroepkgfsd', 'AsRCUy1JTkf500s_2pfXZK9qwR', 'ec2-107-22-246-250.compute-1.amazonaws.com', 5432)


    

In [ ]:

    

db.store('users', 1, [{'name': 'test'}])
db.restore('users', 1)


    

In [4]:

    

data = db_prod.backup()
db.restore_backup(data)


    

In [ ]:

    

for table in list(data.items()):
    print(table[0])
    table_name = table[0]
    for row in list(table[1].items()):
        #print(json.dumps(row[1]))
        id = row[0]
        print(id)
        print(store(table_name, id, row[1]))


    

In [ ]:

    

with open('html/data.txt', 'r') as infile:
    data = json.load(infile)


    

In [ ]:

    

data


    

In [ ]: