Re: [sqlalchemy] Changing primary key's value in a multi-to-multi relation

it to use passive deletes. Do I also need the cascade="all, delete-orphan" option like in the docs? Zsolt On 15 February 2017 at 17:25, mike bayer <mike...@zzzcomputing.com> wrote: > > > On 02/15/2017 09:45 AM, Zsolt Ero wrote: >> >> 4. An interesti

[sqlalchemy] Changing primary key's value in a multi-to-multi relation

I would like to change a primary key's value, to be deterministic, based on a multi-to-multi relation. Thus I'm populating the tables with a temporary ids (just random strings), then calculating the right, unique id, and changing it afterwards. I have the following models: class

Re: [sqlalchemy] Changing primary key's value in a multi-to-multi relation

='CASCADE'), > primary_key=True, ), > Column('image_id', ForeignKey('images.id'), primary_key=True)) > > > > > On 02/15/2017 09:45 AM, Zsolt Ero wrote: >> >> Thanks Mike for looking into this. >> >> I've created a minimal program which reproduces my erro

Re: [sqlalchemy] Changing primary key's value in a multi-to-multi relation

y levels; data wise, > isolation/concurrency-wise, etc. Only "ON UPDATE CASCADE" configured on the > server level can accommodate the flow as an UPDATE on the target table, > where Postgresql internally handles the cascading of the primary key change > to all dependent tables

Re: [sqlalchemy] Changing primary key's value in a multi-to-multi relation

in the logs. Zsolt On 15 February 2017 at 04:17, mike bayer <mike...@zzzcomputing.com> wrote: > > > On 02/14/2017 08:15 PM, Zsolt Ero wrote: >> >> I would like to change a primary key's value, to be deterministic, based >> on a multi-to-multi relation. Thus I'm populati

Re: [sqlalchemy] Changing primary key's value in a multi-to-multi relation

Can anyone help with these questions? Nothing urgent, I'm just interested in understanding more about SQLAlchemy. On 16 February 2017 at 04:07, Zsolt Ero <zsolt@gmail.com> wrote: > I'm starting to understand it! Just a few very quick questions: > > 1. Is it a good strateg

Re: [sqlalchemy] Changing primary key's value in a multi-to-multi relation

Thanks for the detailed explanation! On 28 February 2017 at 16:22, mike bayer <mike...@zzzcomputing.com> wrote: > sorry, lost track on this one. > > On 02/15/2017 10:07 PM, Zsolt Ero wrote: >> >> I'm starting to understand it! Just a few very quick questions: >

[sqlalchemy] refresh's lockmode and with_for_update

Right now, the documentation for session.refresh() mentions: lockmode – Passed to the Query as used by with_lockmode()

Re: [sqlalchemy] jsonb_set in PostgreSQL 9.5+

. It is the same behaviour both with 'fetch' and False. Zsolt On 15 May 2017 at 16:33, mike bayer <mike...@zzzcomputing.com> wrote: > > > On 05/15/2017 10:31 AM, Zsolt Ero wrote: >> >> I'm trying to run your example, but it doesn't work: >> >> from sqlalchemy import

Re: [sqlalchemy] jsonb_set in PostgreSQL 9.5+

row, I was thinking a simple JSONB insertion would probably be better, since I can avoid locking the row. Zsolt On 15 May 2017 at 15:58, mike bayer <mike...@zzzcomputing.com> wrote: > > > On 05/15/2017 09:32 AM, Zsolt Ero wrote: >> >> In PostgreSQL 9.5+ it is finally

Re: [sqlalchemy] jsonb_set in PostgreSQL 9.5+

aps_id': 3, 'jsonb_set_3': '"value"', 'jsonb_set_2': '{key}', 'jsonb_set_1': {u'small': u'2ad139ee69cdcd9e.jpg', u'full': u'68b3f51491ff1501.jpg'}} On 15 May 2017 at 16:18, Zsolt Ero <zsolt@gmail.com> wrote: > Thanks for the answer. My use case is the following: > > I have an obje

[sqlalchemy] jsonb_set in PostgreSQL 9.5+

In PostgreSQL 9.5+ it is finally possible to modify a single key inside a JSONB column. Usage is something like this: update maps set screenshots=jsonb_set(screenshots, '{key}', '"value"') where id = 10688 Is it possible to write this query using the ORM somehow? If not, please take it as a

Re: [sqlalchemy] jsonb_set in PostgreSQL 9.5+

x for a one line SQL UPDATE query. Even the triggered SELECT statement is just querying for a single .id, which we have anyway. Zsolt On 15 May 2017 at 17:29, mike bayer <mike...@zzzcomputing.com> wrote: > > > On 05/15/2017 10:54 AM, Zsolt Ero wrote: >> >> Thanks,

Re: [sqlalchemy] jsonb_set in PostgreSQL 9.5+

Thanks a lot for the explanation, it's all clear now! On 15 May 2017 at 18:56, mike bayer <mike...@zzzcomputing.com> wrote: > > > On 05/15/2017 11:56 AM, Zsolt Ero wrote: >> >> I might not be understanding something, but for me there are two >> diffe

[sqlalchemy] SQLAlchemy + async

After reading Mike's great blog post: http://techspot.zzzeek.org/2015/02/15/asynchronous-python-and-databases/ and SO answer: https://stackoverflow.com/a/16503103/518169 I wanted to use gevent / sqlalchemy / psycopg2 / gunicorn in a new application. However I have immediately backtracked

[sqlalchemy] Connection pool slow performance

I thought I finally got to understand what does it mean to use a connection pool / QueuePool, but this performance problem puzzles me. If I run: for i in range(100): with pg_engine.connect() as conn: conn.execute('select 1').fetchall() it takes 47 seconds. If I run with

Re: [sqlalchemy] Connection pool slow performance

ot; % (total, )) go() On Fri, 18 Jan 2019 at 22:54, Mike Bayer wrote: > > On Fri, Jan 18, 2019 at 4:49 PM Zsolt Ero wrote: > > > > I know I'm far away from a remote server, as the server is a Google > > Cloud SQL instance, and I'm in a residential cable connection. But &

Re: [sqlalchemy] Connection pool slow performance

I know I'm far away from a remote server, as the server is a Google Cloud SQL instance, and I'm in a residential cable connection. But ping times are only 145 ms, nothing extreme I'd say. If you'd like I can quickly setup a sandbox instance on GCP for trying this out. Still, I don't even

Re: [sqlalchemy] Connection pool slow performance

tion, or 0.10, 0.17, 0.10 within the same datacenter. Zsolt On Fri, 18 Jan 2019 at 23:01, Zsolt Ero wrote: > > But why is it doing any kind of network activity on a .connect(), if > the previous connection was closed in the pool? > > OK, I created a sandbox server, it'll probably b

Re: [sqlalchemy] explain analyze on statement

Thanks it works perfectly, even with datetimes! Can I do something similar to make stmt.compile(dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True}) compatible with datetime? Or maybe not this, but I'm looking for a way to print a statement which I could copy and paste into

[sqlalchemy] explain analyze on statement

Hi, I'm using the following snippet to run explain analyize on raw sql (PostgreSQL, psycopg2). def print_explain(engine, raw_sql, values): sql_text = text('EXPLAIN (ANALYZE, BUFFERS) ' + raw_sql) q = engine.execute(sql_text, values) lines = q.fetchall() print('--- EXPLAIN ANALYZE

Re: [sqlalchemy] explain analyze on statement

21 Jan 2019 at 19:22, Mike Bayer wrote: > > On Mon, Jan 21, 2019 at 12:23 PM Zsolt Ero wrote: > > > > Thanks it works perfectly, even with datetimes! > > > > Can I do something similar to make > > > > stmt.compile(dialect=postgresql.dialect

Re: [sqlalchemy] explain analyze on statement

wrote: > > On Thu, Jan 24, 2019 at 9:07 PM Zsolt Ero wrote: > > > > Do I understand it right that there is no approach which could print a > > query without the need to modify it, if it contains datetimes? > > > > If so, how can I turn a where statement into so

[sqlalchemy] How to get output from PostgreSQL's VERBOSE functions

Hi, I'd like to run a CLUSTER VERBOSE command and save the lines which would normally be displayed in psql's command line. How can I do so with SQLAlchemy + psychopg2? -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code,

Re: [sqlalchemy] How to get output from PostgreSQL's VERBOSE functions

I figured out the keyword is NOTICES. However this section of the docs doesn't work / didn't had any effect: https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#notice-logging What worked was to manually clear and print conn.connection.connection.notices like this: if notices:

Re: [sqlalchemy] How to get output from PostgreSQL's VERBOSE functions

Thanks with logging.basicConfig() it indeed works. On Sun, 9 Jun 2019 at 16:19, Mike Bayer wrote: > > > On Sun, Jun 9, 2019, at 1:33 AM, Zsolt Ero wrote: > > I figured out the keyword is NOTICES. > > However this section of the docs doesn't work / didn't ha

Re: [sqlalchemy] explain analyze on statement

Fri, Jan 25, 2019 at 7:58 PM Mike Bayer wrote: > > > > On Fri, Jan 25, 2019 at 6:37 PM Zsolt Ero wrote: > > > > > > I understand. I define my tables like > > > > > > Table('tablename', metadata, Column('time', DateTime(timezone=True), > > > nullable=F

[sqlalchemy] How to check for table's existance without a separate query

Hi, I'm looking for a way to check for a table's existance. I know I can do it in 2 queries, once for checking the table and one for running the query, but I'd prefer to do it in one query with a try-except block. What is the best way to do this? I'm using psycopg2 and I see that the raised

[sqlalchemy] Re: How to check for table's existance without a separate query

My best idea so far is if 'psycopg2.errors.UndefinedTable' in str(err) Just to note, I'm looking especially for psycopg2.errors.UndefinedTable, I'm not using any other DB. On Wednesday, 4 December 2019 18:37:02 UTC+1, Zsolt Ero wrote: > > Hi, > > I'm looking for a way to check f

Re: [sqlalchemy] Re: How to check for table's existance without a separate query

Thanks. So is the following code correct for psycopg2 specific scenario? try: res = conn.execute(stmt) except sa.exc.ProgrammingError as err: if isinstance(err.orig, psycopg2.errors.UndefinedTable): print('Table does not exist') else: raise err -- SQLAlchemy - The

Re: [sqlalchemy] Adding alias to join() makes a wildly different query

ful, directing me in the right direction. Zsolt On Fri, 29 Nov 2019 at 04:50, Mike Bayer wrote: > > > > On Thu, Nov 28, 2019, at 4:31 PM, Zsolt Ero wrote: > > I have the following query: > > min_values = ( > sa.select( > [ >

[sqlalchemy] How to disable the integer col + pk = serial behaviour?

Hi, I've split a table into two tables, for performance reasons. I'd like to insert into both tables using the same sequence. I'm inserting using executemany_mode='values'. My idea is to call nextval() on the sequence before insert and fill in the values client side, before inserting. select

Re: [sqlalchemy] How to disable the integer col + pk = serial behaviour?

primary_key=True and issue an ALTER TABLE ... ADD PRIMARY KEY (...) command manually. On Wed, 5 Aug 2020 at 15:20, Mike Bayer wrote: > > > > On Wed, Aug 5, 2020, at 8:59 AM, Zsolt Ero wrote: > > Hi, > > I've split a table into two tables, for performance reasons. I'd lik

[sqlalchemy] Re: How to disable the integer col + pk = serial behaviour?

is make the Integer back into the Serial, which I'm trying to avoid. On Wednesday, 5 August 2020 14:59:03 UTC+2, Zsolt Ero wrote: > > Hi, > > I've split a table into two tables, for performance reasons. I'd like to > insert into both tables using the same sequence. I'm

[sqlalchemy] scalars() in 1.4+

Hi, I'm a bit confused about the scalars() in 1.4+. The "ORM Querying Guide" introduces it in the very first example, but it says: "When selecting a list of single-column ORM entities, it is typical to skip the generation of Row objects and instead receive ORM entities directly, which is achieved

Re: [sqlalchemy] low-level commands in 2.0

Thanks, so far my new project works well in 2.0. My question is that I'm trying to migrate to the 2.0 style form using this cheetsheet: https://docs.sqlalchemy.org/en/20/changelog/migration_20.html#migration-orm-usage So far I couldn't figure out how to turn this query in to 2.0 style.