mPPpSR9Z

1# Script to create the tables and schema for the TCF database from the raw data
2import psycopg2
3import os
4import sys
5
6
7# we need to know the directory that all of the input files live in
8directory = os.getcwd() 
9#connect to the TCF database
10try:
11    connection = psycopg2.connect("dbname='TCF' user='postgres' password='postgres'")
12    print "Connection established to PostgreSQL Server..."
13except:
14    print "Unable to connect to the database..."
15
16if connection:
17    cursor = connection.cursor()
18
19    directory_files = os.listdir(directory)
20
21    cursor.execute('SELECT tablename FROM pg_tables')
22    table_list = cursor.fetchall()
23    import pdb;pdb.set_trace()
24
25    for file_name in directory_files:
26        if '.txt' in file_name:
27            print "Found file: %s" % file_name
28            "parse name of data file for table name"
29            table_name = file_name.replace('.txt', '')
30            table_name = table_name.replace('TCF.dbo.', '')
31            "parse first row of input data file for column names"
32            file_object = open((directory + "/" + file_name))
33            column_names = file_object.readline()
34            "drop table if it exists"
35            if table_name in table_list:
36                cursor.execute('Drop Table %s' % table_name)
37                connection.commit()
38            "create table in database if it does not exist based on file name"
39            "create columns based on names if they do not yet exist"
40            cursor.execute('Create Table %s ()'% table_name)
41            connection.commit()
42            "import data from input file using COPY"
43            cursor.copy_from(file_object, table_name)
44            file_object.close()
45            connection.commit()
46            sys.stdout.flush()