Iteratively insert rows into a postgreSQL database using SQLAlchemy in python

Question

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 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.

It would help if you post any error messages that come up.

nom-mon-ir
– nom-mon-ir

2013-06-19 22:02:42 +00:00
Commented Jun 19, 2013 at 22:02 — nom-mon-ir
– nom-mon-ir, Commented Jun 19, 2013 at 22:02

nom-mon-ir · Accepted Answer · 2013-06-19 21:58:38Z

1

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.

answered Jun 19, 2013 at 21:58

nom-mon-ir

3,9288 gold badges29 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sabareesh Kappagantu Over a year ago

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.

nom-mon-ir Over a year ago

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.

Sabareesh Kappagantu Over a year ago

There you go! I posted the solution to my answer!

Sabareesh Kappagantu · Accepted Answer · 2013-06-22 00:59:21Z

1

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()

answered Jun 22, 2013 at 0:59

Sabareesh Kappagantu

2,6073 gold badges20 silver badges15 bronze badges

Collectives™ on Stack Overflow

Iteratively insert rows into a postgreSQL database using SQLAlchemy in python

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related