在python中使用SQLAlchemy将行迭代地插入到postgreSQL数据库中 [英] Iteratively insert rows into a postgreSQL database using SQLAlchemy in python
问题描述
我正在编写一个脚本,该脚本从excel文件中读取数据并写入PostgreSQL数据库。我已经从excel文件中读取了数据,并将每行数据存储为字符串数组。
我尝试了几件事,例如
对于x范围(len(sh.columns)):
i = users.insert()
i.execute({sh。 column [x]:sh.values [0] [x]})
此处是对我的Excel工作表和我正在用户引用的postgreSQL数据库中编辑的表。
我还尝试了
'向用户插入值VALUEs%s'%(元组(sh.values [0])
这是我的解决方案,这是我使用的方法,它就像一个魅力
将熊猫作为pd
导入sqlalchemy作为sql
导入sqlalchemy.orm作为orm
sh = pd.ExcelFile( ExcelFileName)。parse( SheetName)
db = sql.create_engine('DBtype:// username:password @ localhost :postnumber / dbname')
元数据= sql.schema.MetaData(bind = db,reflect = True)
table = sql.Table('TableinDB',metadata,autoload = True)
class Row(object):
pass
rowmapper = orm.Mapper(Row,table)
打印表格。列
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]
#添加任意数量的字段
session.add(row1)
#forloop结束
session.commit()
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.
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()
这篇关于在python中使用SQLAlchemy将行迭代地插入到postgreSQL数据库中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!