current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I am writing a script that reads data from excel files and writes into a postgresql database. I have read data from the excel file and stored each row data as an array of strings.

I tried a couple of things, like 

  for x in range(len(sh.columns)):
        i = users.insert()
        i.execute({sh.columns[x] : sh.values[0][x]})

Here sh is the reference to my excel sheet and the table I'm editing in the postgreSQL database i referred to by users.
I also tried an

'INSERT INTO users VALUES %s'%(tuple(sh.values[0])

and a few more variations of that.

This is really urgent and I've spent over three days on this one task, unable to figure out how to go about it.

share|improve this question
1  
It would help if you post any error messages that come up. –  nom-mon-ir Jun 19 '13 at 22:02

2 Answers 2

up vote 0 down vote accepted

Here is my solution. This is what I used and it works like a charm! 

import pandas as pd
import sqlalchemy as sql
import sqlalchemy.orm as orm



sh = pd.ExcelFile("ExcelFileName").parse("SheetName")
db=sql.create_engine('DBtype://username:password@localhost:postnumber/dbname')
metadata = sql.schema.MetaData(bind=db,reflect=True)



table = sql.Table('TableinDB', metadata, autoload=True)

class Row(object):
 pass
rowmapper = orm.Mapper(Row,table)
print table.columns

Sess = orm.sessionmaker(bind = db)
session = Sess()

for x in range(len(sh.values)):
 row1 = Row() 
 row1.field1 = sh.values[x][0]
 row1.field2 = sh.values[x][1]
 #Add as many fields as you want
session.add(row1)
#forloop ends
session.commit()
share|improve this answer
up vote 0 down vote

To make sure you can write to the table, try in more raw way:

i.execute( "INSERT INTO table (field1, field2, field3) VALUES (%s, %s, %s) " % (data1, data2, data3) )

Modify the above to have appropriate table name, field names and data values.

Once this works, you can refine writing from sh.

share|improve this answer
    
Thanks Avid, That is an easy way to do it. But I have all data stored in an array and I'm trying to automate the script, so that way of doing will have me to hardcode all the column names into the insert line. I also have multiple sheets in the excel file. I did a little more research and was able to get the task done. I am doing it using sessions and tansactions using ORM. I read about it but was not able to use it correctly. I figured it out and it works like a charm now. Thanks anyways Avid. –  Sabareesh Kappagantu Jun 20 '13 at 22:47
    
great, can you post snippet of your solution as an answer? You can later accept your own answer. Plus others will be able to learn from your solution. –  nom-mon-ir Jun 21 '13 at 0:07
    
There you go! I posted the solution to my answer! –  Sabareesh Kappagantu Jun 22 '13 at 0:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.