inserting data to Postgres using SQL alchemy in flask

Question

I am passing from my local machine, a person id and person name to an ubuntu server. then this information will be read and passed to the database. I am using the below code, but somehow I am not able to get it done. I am new to all these, so not sure what I am missing

@app.route("/personInformation", methods=['POST'])
def parsingData():

        data = request.get_json(force=True)
        personID = data['person_id']
        personName = data['person_name']

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

        db=sql.create_engine(connectionString)            
        metadata = sql.schema.MetaData(bind=db,reflect=True)
        table = sql.Table('patient_all_info', metadata, autoload=True)

        class Row(object):
            pass
        rowmapper = orm.Mapper(Row,table)
        Sess = orm.sessionmaker(bind = db)
        session = Sess()
        row1 = Row() 
        row1.person_id = personID
        row1.person_name = personName
        session.add(row1)
        session.commit()
        return "sent, no error"

First of all, get rid of that all-eating except:, which is hiding the error, and the rerun your code and post the error with the traceback. — Ilja Everilä
– Ilja Everilä, Commented Jan 30, 2019 at 8:11
I followed this link: stackoverflow.com/questions/17181575/… — Cathy
– Cathy, Commented Jan 30, 2019 at 8:13
Ah my bad, I missed your db=sql.create_engine(connectionString). So it is not "very wrong" at all. But still, if you're using Flask-SQLAlchemy, do follow its tutorials :). The link you provided handles mapping the "old way" among other things, instead of using Declarative. Flask-SQLAlchemy also provides integration to Flask request life cycle out of the box. — Ilja Everilä
– Ilja Everilä, Commented Jan 30, 2019 at 8:15

kerwei · Accepted Answer · 2019-01-30 08:43:13Z

0

could you give the construct below a try? I adapted it from one of my projects.

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData

Base = declarative_base()
engine = create_engine(connstring)
Base.metadata.bind = engine
DBSession = sessionmaker(bind=engine)
session = DBSession()    
conn = engine.connect()    
metadata = MetaData()

SomeDataTable = Table('TableName', metadata,
    Column('Column1', Integer, primary_key=True),
    Column('Column2', String(250), nullable=False),
    schema = 'schema_name'
)

stmt = SomeDataTable.insert().values([{"Column1": 1},{"Column2": "test"}])
conn.execute(stmt)

answered Jan 30, 2019 at 8:43

kerwei

1,8521 gold badge13 silver badges25 bronze badges

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

2 Comments

Cathy Over a year ago

I want to do it in flask

kerwei Over a year ago

Yes, you can do this in flask. Ideally, you would separate the Table class from your function but basically what your data parsing function needs to do is just to accept the payload, construct the statement and then execute i.e. the last 2 lines in my answer. Flask is just your web microframework and is independent of your db operations. I use flask for my project too.

Collectives™ on Stack Overflow

inserting data to Postgres using SQL alchemy in flask

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest