def copy_data(file_name, target_table, truncate):
# ------------------------------------------------------------------------------
try:
if truncate:
con = db.db_get_connection()
cur = con.cursor()
cmd_truncate = "truncate table {0}".format(target_table)
print cmd_truncate
cur.execute(cmd_truncate)
con.commit()
con.close()
print "Truncated"
db.copy_file_to_table(file_name, target_table)
except psycopg2.DatabaseError, e:
# except mysql.connector.Error, e:
if con:
con.rollback()
print "Error %s" % e
sys.exit(1)
def transform_data(delete_command, insert_command):
# ------------------------------------------------------------------------------
try:
con = db.db_get_connection()
cur = con.cursor()
if delete_command:
print delete_command
cur.execute(delete_command)
print "Deleted"
if insert_command:
print insert_command
cur.execute(insert_command)
con.commit()
print "Inserted"
except psycopg2.DatabaseError, e:
# except mysql.connector.Error, e:
if con:
con.rollback()
print "Error %s" % e
sys.exit(1)
def load_data(file_pattern, delete_command, insert_command):
# ------------------------------------------------------------------------------
try:
# get latest file for the pattern
file_name = max(glob.iglob(file_pattern), key=os.path.getctime)
print file_name
with db.db_get_connection() as con:
cur = con.cursor()
if delete_command:
print delete_command
cur.execute(delete_command)
print "Truncated"
with open(file_name, "r") as f:
lines = f.read().splitlines()
for line in lines:
data = line.split(",")
print data[0]
if data[0] != "Date" and data[0] != '"No."':
print insert_command
print data
# cur.mogrify(insert_command, data)
cur.execute(insert_command, data)
con.commit()
except psycopg2.DatabaseError, e:
# except mysql.connector.Error, e:
if con:
con.rollback()
print "Error %s" % e
sys.exit(1)