import sqlalchemy
from sqlalchemy import Table, Column, Integer, String, ForeignKey


def connect(user, password, db, host='localhost', port=5432):
    '''Returns a connection and a metadata object'''
    # We connect with the help of the PostgreSQL URL
    # postgresql://federer:grandestslam@localhost:5432/tennis
    url = 'postgresql://{}:{}@{}:{}/{}'
    url = url.format(user, password, host, port, db)

    # The return value of create_engine() is our connection object
    connection = sqlalchemy.create_engine(url, client_encoding='utf8')

    # We then bind the connection to MetaData()
    meta = sqlalchemy.MetaData(bind=connection, reflect=True)

    return connection, meta



def create_tables(connection, meta):

    test_table = Table('zmeyka_test', meta,  
        Column('id', Integer, primary_key=True),
        Column('text', String)
    )
    meta.create_all(connection)

def truncate_table(table_name, connection, meta):
    table_to_truncate = Table(table_name, meta, autoload=True, autoload_with=connection)
    clause = table_to_truncate.delete()
    connection.execute(clause)


def insert_values(table_name, connection, meta, dict_insert = [1,'text1']):
    table_to_insert = Table(table_name, meta, autoload=True, autoload_with=connection)
    clause = table_to_insert.insert().values(dict_insert)
    connection.execute(clause)

def select_all(table_name, connection, meta):
    table_to_select = Table(table_name, meta, autoload=True, autoload_with=connection)
    clause = table_to_select.select()
    select_results = connection.execute(clause)
    rows_list = list()
    for row in select_results:
        rows_list.append(row)
    return rows_list

def get_table_raw_data(table_name = 'zmeyka_test'):
    connection, meta = connect('zmeyka_user', 'zmeyka1!', 'zmeyka_db')
    if table_name in meta.tables:
        print ('table exists')
    else:
        print ('creating table')
        create_tables(connection, meta)

    truncate_table (table_name, connection, meta)
    for x in range(100): 
        insert_values(table_name, connection, meta, [x, 'text{}'.format(str(x))])

    select_results = select_all(table_name, connection, meta)
    #print(select_results)
    return select_results

##################### MAIN ####################     

if __name__ == '__main__': 
    connection, meta = connect('zmeyka_user', 'zmeyka1!', 'zmeyka_db')

    if 'zmeyka_test' in meta.tables:
        print ('table exists')
    else:
        print ('creating table')
        create_tables(connection, meta)

    truncate_table ('zmeyka_test', connection, meta)
    for x in range(100): 
        insert_values('zmeyka_test', connection, meta, [x, 'text{}'.format(str(x))])


    select_results = select_all('zmeyka_test', connection, meta)
    print(select_results)

    #test_table = Table('zmeyka_test', meta, autoload=True, autoload_with=connection)

