Sqlalchemy + Postgres - multiple values update

Question

I neet to make a statement similar to this answer, but in Python Sqlalchemy. This is the code I'm trying to write:

def _update(
            self,
            table: sqlalchemy.Table,
            df: pd.DataFrame,
            autocommit: bool = False):
            """
            df = pd.DataFrame(columns = ['id', 'values'])
            """

        # with new_values(id, values) as (
        #     values
        #     ('id1', '{0.1,0.0}'::real[]),
        #     ('id2', '{0.0,0.1}'::real[])
        # )
        # update schema.table as t set
        #     values = new_values.values
        #     from new_values
        # where t.id = new_values.id

        stmt = above_comments_to_sqlalchemy(df)

        response = self.session.execute(stmt)
        if autocommit:
            self.session.commit()
        return response

You'd have to combine https://stackoverflow.com/questions/42543223/how-do-you-express-a-multi-table-update-update-from-in-sqlalchemy-orm or https://stackoverflow.com/questions/28274133/update-a-joined-table-with-sqlalchemy-core (depending on using ORM or Core) with https://stackoverflow.com/questions/18858291/values-clause-in-sqlalchemy. — Ilja Everilä, Nov 28 '19 at 20:36

score -1 · Answer 1 · answered Nov 28 '19 at 16:45

-1

Following the docs: https://docs.sqlalchemy.org/en/13/dialects/postgresql.html

1) Create an engine with create_engine()

2) Open a connection with connect()

3) execute a query using execute()

For example:

engine = create_engine();
connection = engine.connect();

queryString = 'UPDATE <TableName> SET...'

connection.execute(queryString);

answered Nov 28 '19 at 16:45

Naor Levi

1,713
1
13
27

Thanks, but my point is on doing the same statements with the sqlalchemy interface, without having to write my query string, such as: df = pandas.DataFrame(...) table.update().where(table.c.id == df[id]).values(df.to_dict()) – Felipe Chamas Nov 28 '19 at 16:50
You understand that in order to do so you need to open a connection to the database right? – Naor Levi Nov 28 '19 at 16:52
You could use the ORM of sql alchemy to do so. see: https://docs.sqlalchemy.org/en/13/orm/tutorial.html#connecting – Naor Levi Nov 28 '19 at 16:57

Sqlalchemy + Postgres - multiple values update

1 Answers1