dokucama

## don't modify anything below this line (except for experimenting)

import sys
import os
import psycopg2
import openpyxl

DSN = "dbname=pnegron"

print "Opening connection using dsn:", DSN
conn = psycopg2.connect(DSN)
print "Encoding for this connection is", conn.encoding

curs = conn.cursor()
try:
    curs.execute("CREATE TABLE cisco_devices (device text, Serial_No text)")
except:
    conn.rollback()
    curs.execute("DROP TABLE cisco_devices")
    curs.execute("CREATE TABLE cisco_devices (device text, Serial_No text)")
conn.commit()

# demostrate copy_to functionality
data = [('R1', '12345', ),
        ('R2', '34567')]
query = "INSERT INTO cisco_devices VALUES (%s,%s)"
curs.executemany(query, data)
conn.commit()

# copy_to text doc using custom separator
io = open('/Users/pnegron/Desktop/TestConfig.txt', 'w')
curs.copy_to(io, 'cisco_devices', ':')
print "2) Copied %d records into file object using sep = :" % len(data)
io.close()

# open an EXCEL spreadsheet and go to the First Sheet
wb2 = openpyxl.load_workbook('/Users/pnegron/Desktop/example.xlsx')
sheet = wb2.get_sheet_by_name('First Sheet')

# Dump the data from the tuple to the sheet postions
sheet['A2'] = data[0][0]
sheet['B2'] = data[0][1]

# Assign one of the cells of a sheet to a variable and print it. Save the Doc!
x = sheet['A2']
print x.value
wb2.save('/Users/pnegron/Desktop/example.xlsx')

#curs.execute("DROP TABLE cisco_devices")
#os.unlink('/Users/pnegron/Desktop/TestConfig.txt')
#conn.commit()