Re: [sqlalchemy] association_proxy

Mike- where do messages like these come from? The approval queue? The originals are often missing. I looked in the admin and didn't see them pending either. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide

Re: [sqlalchemy] session.add with insert-or-update

FWIW: If your application is high-traffic/high-concurrency, depending on how your transactions are scoped within the code you may want to do the getcreate or create step that calls `.flush` within an exception block or savepoint, to catch duplicate inserts. I've only had to do this on 2 (out of

Re: [sqlalchemy] Re: Help with configuring connect_timeout setting for when we a high available postgres database drops

I would also throw a few debug lines into psycopg2 and ensure that library is creating a connection with the params you intend. On Wednesday, February 26, 2020 at 11:19:28 AM UTC-5, Karim Gillani wrote: > > Thanks. I don't believe this is a Red Hat issue as much as a > configuration issue. T

[sqlalchemy] Re: SQLAlchemy Memory Leak

Memory leaks caused by SqlAlchemy are extremely rare. The code is in heavy production with tens of thousands of users. If there is a memory leak, it is likely caused by another library, or your code in how you are using SqlAlchemy. Your next step should be creating a "Short, Self Contained, Cor

Re: [sqlalchemy] debugging sqlite/thread errors

> I'm assuming this is an error you see in the logs but isn't actually making anyone's request fail, otherwise it's something different. Yes, that is what is happening. AH HA, I GOT IT! The application startup routine had this block in it: dbEngine = ... dbSession = ... ... setup ...

Re: [sqlalchemy] debugging sqlite/thread errors

On Monday, February 24, 2020 at 6:23:32 PM UTC-5, Mike Bayer wrote: > > it looks like garbage collection. SQLAlchemy 2.0 won't do that, it will > just warn that you left a connection open somewhere. we would hope that > the SQLite gc routine doesn't raise like that. > Thanks Mike, this hel

[sqlalchemy] debugging sqlite/thread errors

I am working on a rewrite of a package to support ACME2 and Python3, and somehow introduced a thread unsafe error. This is happening in a web application, and I have absolutely no clue where I introduced the bug or what is causing it. Does anyone know of a good way to bugtest for this sort of s

[sqlalchemy] Re: Retaining plain old python instance member values betwen Flask-SQLAlchemy requests.

Session.merge allows you to attach a detached object https://docs.sqlalchemy.org/en/13/orm/session_state_management.html#merging But What you are describing to me looks like an anti-pattern, as it will have wildly unpredictable results if you deploy your application with more than one worker (

Re: [sqlalchemy] extending the relationships api to ensure the intended uselist and back_populates match up

On Wednesday, February 5, 2020 at 5:58:30 PM UTC-5, Mike Bayer wrote: > > OK so you are explicitly worrying about a one-to-many relationship that > should be one-to-one, or vice versa, that is, you aren't worried about FKs > or remote_side setting up the entirely wrong M2O / O2M for a relations

Re: [sqlalchemy] extending the relationships api to ensure the intended uselist and back_populates match up

On Wednesday, February 5, 2020 at 4:50:36 PM UTC-5, Mike Bayer wrote: > > what conditions would cause the "uselist" to be incorrect? > If `Foo.bars` declares: uselist=True, back_populates_uselist=False but `Bar.foo` declares: uselist=True, back_populates_uselist=True I want t

[sqlalchemy] extending the relationships api to ensure the intended uselist and back_populates match up

Does anyone know if there is an easy way to extend the relationships system so that I can specify the uselist I expect for back_populates relationships? One of my projects has a complex model with a mix of one-to-one and one-to-many relationships that declare `uselist`. I'd like to ensure I de

[sqlalchemy] Re: Best practices for cleaning up SQLAlchemy engine and sessionmaker instances?

There are some sections in the docs that may help you understand things a bit more. 1. Connections- Disposal https://docs.sqlalchemy.org/en/13/core/connections.html#engine-disposal 2. Connections- Threadlocal https://docs.sqlalchemy.org/en/13/core/connections.html#using-the-threadlocal-executio

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

Personally, I would handle the check like this: ERRORS_UNDEFINED_TABLE = (psycopg2.errors.UndefinedTable, ) try: res = conn.execute(stmt) except sa.exc.ProgrammingError as err: if isinstance(err.orig, ERRORS_UNDEFINED_TABLE): print('Table does not exist') raise This wou

Re: [sqlalchemy] Large RAM usage in bulk_insert_mappings

On Friday, November 15, 2019 at 4:03:44 AM UTC-5, Elmer de Looff wrote: > > That said, I've run into the same problem with a little toy project, which > works around this with a 'bulk save' interface. > FWIW on something related... In my experience, if you need to focus on speed for bulk ins

Re: [sqlalchemy] duplicating a relationship ?

On Wednesday, November 6, 2019 at 4:49:29 AM UTC-5, Simon King wrote: > > > Isn't this what synonyms are for? > > https://docs.sqlalchemy.org/en/13/orm/mapped_attributes.html#synonyms > Thank you, SImon! That is exactly what I needed! -- SQLAlchemy - The Python SQL Toolkit and Object Relat

[sqlalchemy] duplicating a relationship ?

I'm not sure if something broke during an upgrade, or if I just had bad tests and this was always an issue (I am assuming the latter!) I need to duplicate a relationship on a sqlalchemy ORM declarative object. I was hoping i could just do this (or at one point i could!) class Foo(DeclaredTable

[sqlalchemy] Re: joining three tables using single query

Whoops, my `==` got erased, it should read: > db.session.query(Images)\ > .join(VideoClip, > Images.video_clip_id == VideoClip.id > )\ > > >> I am explicitly providing the on-clause arguments, but that is not necessary for configured relationships ( see https://docs.sql

[sqlalchemy] Re: joining three tables using single query

You might do something like this... db.session.query(Images)\ .join(VideoClip, Images.video_clip_id VideoClip.id )\ .join(RecordingSession, RecordingSession.recording_id == VideoClip.id )\ .filter(Images.video_clip_id = 'vcid',

[sqlalchemy] Re: Slow entities pickling for caching purposes

> > 1) What strategy for caching I should consider while using SQLAlchemy? > Currently, the only option I see is to have a duplicated declaration of > entities in a form of simple classes and use it when I don't need > modification. Needles to say, it's a lot of code duplication. I cache SqlA

[sqlalchemy] Re: boud parameter to NCHAR column in Oracle

I'm sure Mike will have a better answer, but I immediately thought "events!" and that led me to some documentation in the Oracle driver for dealing with another issue, but it looks like you could use that same hook... https://docs.sqlalchemy.org/en/13/core/events.html#sqlalchemy.events.DialectE

Re: [sqlalchemy] misc questions about finding `association_proxy` "columns"

On Thursday, September 26, 2019 at 1:07:29 PM UTC-4, Simon King wrote: > That sounds like it would have to be application-specific. I don't > know how SQLAlchemy can tell that you've finished adding tables and > mappers. Some of that is application specific, but there must be an event that can

Re: [sqlalchemy] misc questions about finding `association_proxy` "columns"

Thanks Mike! this is exactly what i needed. Follow-up question: is there a good sqlalchemy event or hook were I can inspect all my tables once per application startup ? I've tried several orm.events, but they did not work as i had hoped. -- SQLAlchemy - The Python SQL Toolkit and Object Re

[sqlalchemy] misc questions about finding `association_proxy` "columns"

I recently shifted some of a database schema around, and thanks to `association_proxy`, I've almost-eliminated the need to adjust my code (thanks, Mike!). I'm left with a situation on my caching layer that I can't seem to work out. I can't seem to to find out the following information (or othe

Re: [sqlalchemy] Complex Constraints in Many to Many relationships

You should (probably) also add the "person" to "team_tournament" for the added benefit that a person may switch teams between tournaments (or not play in one), so tracking participation with a triplet is a chunk of useful data. Mike's approach with indexes is definitely the straightforward bes

[sqlalchemy] Re: Track a "column FOO does not exist" bug.

On Thursday, September 5, 2019 at 8:58:16 AM UTC-4, Riccardo Cagnasso wrote: > > I don't think so. Wouldn't postgres prevented me deleting the column if it > were? > PostgreSQL will prevent you from deleting the column if it were a CONSTRAINT, but would not necessarily notice it on a trigger

Re: [sqlalchemy] Resource closed error when trying to insert the record in table randomly

some situations that may be screwing up the connection pool like this include (and are not limited to): * your application may not be properly closing, resetting or returning a connection when there is an exception; * multiple threads/processes are using the same connection because of how it w

Re: [sqlalchemy] Percentile calculation

On Monday, August 5, 2019 at 3:21:40 PM UTC-4, Michael wrote: > > Thanks a lot! Indeed SQLite seems to be the problem and the code works > fine with PostgreSQL. > > Unfortunately, a full fledged database server is not an option. Therefore > I probably have to work around the problem in Python.

[sqlalchemy] Re: How to catch exceptions (the best practice)

On Saturday, July 27, 2019 at 4:53:06 PM UTC-4, Nestor Diaz wrote: > > it then raise an exception, however I can not > catch the exception, even if I set up a try-catch block, however if I > add a DBSession.flush() inside the try, the sql sentence is executed and > therefore I can catch the ex

Re: [sqlalchemy] possible bug on sqlite regarding automatic constraints when using alembic recommended naming conventions

That's great, Mike. I will help out on this and handle the below. I'll do a Github ticket for tracking, and you can assign it to me. On Thursday, July 25, 2019 at 9:44:10 PM UTC-4, Mike Bayer wrote: > > > > On Mon, Jul 22, 2019, at 10:51 PM, Jonathan Vanasco wrote: &

Re: [sqlalchemy] sqlalchemy.engine question

Are you writing any code that queries this model or are you just letting the framework handle that? We'd need to see the actual code you are using to query the database. If you are not writing code that looks like 'session.query(model.AccessKeys)` yourself, then SqlAlchemy queries are being g

Re: [sqlalchemy] sqlalchemy.engine question

what does your sqlalchemy query code look like? -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full descripti

Re: [sqlalchemy] possible bug on sqlite regarding automatic constraints when using alembic recommended naming conventions

On Monday, July 22, 2019 at 9:36:10 PM UTC-4, Mike Bayer wrote: > > likely , the "create_constraint" flag should default to False for booleans > and enums. I think it was a mistake to default these to true, I think > people usually don't care. > Everything you said was apparent to me, but I

[sqlalchemy] Re: use sqlalchemy model to generate avro schema

On Monday, July 22, 2019 at 7:47:51 PM UTC-4, Isaac Martin wrote: > > or straight-forward way of generating avro schema from a sqlalchemy model? > You can iterate the columns and relations of a SqlAlchemy model / table. Search terms like "iterate sqlachemy model columns" and you'll see many

[sqlalchemy] possible bug on sqlite regarding automatic constraints when using alembic recommended naming conventions

Mike- I'm not sure if this is a bug or docs incompatibility issue, and I know metadata may be going away soon... but I'm leaning towards a bug. I was setting up a test suite for a Pyramid plugin, and used the Alembic naming conventions (https://alembic.sqlalchemy.org/en/latest/naming.html) as

[sqlalchemy] Re: disable notifications in terminal

SqlAlchemy uses Python logging. SqlAlchemy Docs: https://docs.sqlalchemy.org/en/13/core/engines.html#dbengine-logging Python Logging Docs: https://docs.python.org/3/library/logging.html -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post e

Re: [sqlalchemy] How to inspect filters

On Wednesday, June 26, 2019 at 2:43:44 PM UTC-4, Andrew Martin wrote: > > That's very interesting, Jonathan. Could you show me a quick example of > that approach? I'm not sure I *need* to do that, but I think I would learn > about SQLAlchemy from such an example and trying to understand it. >

Re: [sqlalchemy] Re: Limiting Relationship Depths

On Wednesday, June 26, 2019 at 4:01:31 PM UTC-4, Michael P. McDonnell wrote: > > Awesome, thank you Jonathan. > > I know I've read that at least 3-4 times, but I think I've been staring at > the screen too much these days to actually read. I'll give it a go and let > you know how it goes! >

Re: [sqlalchemy] How to inspect filters

FWIW, I found a better approach to a similar problem was to create a dict/object I used to log metadata about the query I wanted... then build the query or analyze it based on that metadata. All the information is in the sqlalchemy query, but the execution performance a development time was mu

[sqlalchemy] Re: Limiting Relationship Depths

This section of the narrative dogs will help you configure the relationship as you want. https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html In terms of API docs, https://docs.sqlalche

Re: [sqlalchemy] Primary keys mandatory sortability

I am not sure if this helps or not, but the Primary key in SqlAlchemy doesn't need to be the same as the Primary key in the database. You may be able to work around the constraints of your system by divorcing the two concepts from one another (I have, but not in an example I can easily adapt t

[sqlalchemy] Re: Masking SAWarning: Flushing object ... with incompatible polymorphic identity

On Thursday, June 20, 2019 at 3:14:06 AM UTC-4, Chris Withers wrote: > > > How can I indicate in my code that this is intentional and no warning > should be omitted? > Personal option: I would not mask these. I would let them persist and probably add a unittest to ensure they are invoked i

[sqlalchemy] Re: Composite key; one field is also foreign key

adding `primary_key=True` to each of the `Column` constructors should do it. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.co

Re: [sqlalchemy] Apply a function to every query

On Friday, June 14, 2019 at 9:33:49 AM UTC-4, Mike Bayer wrote: > > docs for this general idea are at > https://docs.sqlalchemy.org/en/13/core/custom_types.html#redefining-and-creating-new-operators > > but this is likely a good example to add > Mike- This is actually a great example to add,

Re: [sqlalchemy] advice/feedback sought on testing idea

On Monday, June 3, 2019 at 6:41:48 PM UTC-4, Mike Bayer wrote: > > > It's probably better to use TypeDecorator since that keeps the two roles > separate. you could also put bind/result handlers on them to assert things > if you wanted... > > That's a great idea; thank you!. I'll change thing

[sqlalchemy] advice/feedback sought on testing idea

We have a fairly complex SqlAlchemy model that spans several integrated applications (300+ tables). Our text columns are all of the `sqlalchemy.UnicodeText` class, however they all have widely different 'cleaning' or 'sanitization' schemes. This typically needs to be preprocessed, as we need t

Re: [sqlalchemy] Re: TypeDecorators don't know which database session to use

On Tuesday, May 28, 2019 at 4:35:32 PM UTC-4, Mike Bayer wrote: > > > additionally, running SQL inside of a TypeDecorator is not the intended > usage, as well as using ORM features inside of a TypeDecorator is also not > the intended usage. > thanks, mike. I was 99.99% sure that was the case,

[sqlalchemy] Re: TypeDecorators don't know which database session to use

On Tuesday, May 28, 2019 at 9:10:19 AM UTC-4, Chris Wilson wrote: > > During initial load one can use a global session object, > You should not do that. Global sessions are widely considered an anti-pattern. I have discovered a limitation of TypeDecorators (custom column types): > any one

Re: [sqlalchemy] Left join on one-to-many relationship with filters

I agree with Simon, and I think I'm very much -1 on the usage of contains_eager above (and I think that ticket you linked to, Mike). I find that sort of stuff causes a lot of bugs in the long run. I am looking at it from this perspective, which is the same as Simon's but some stronger language

[sqlalchemy] Re: Looking to consume query as relationship (multiple column return)

FWIW, I often make queries that map to a 'logical object' like this one (e.g. "leaderboard") that have a lot of reads as a View in the database, then create a separate SqlAlchemy ORM object for it and have every relation to/from that marked as view_only. This has nothing to do with writing the

Re: [sqlalchemy] Re: Is it good practice to copy all sqlalchemy models (tables) for every API I create?

i have a few projects families with a model like this: /shared_model/setup.py /shared_model/shared_model/ /shared_model/shared_model/sqlalchemy_api/.. /shared_model/shared_model/sqlalchemy_model/.. /shared_model/shared_model/utils/... /shared_model/tests_unit the sqlalchemy_model directory jus

Re: [sqlalchemy] Re: Query last relation created and attributes

On Tuesday, May 14, 2019 at 10:29:58 AM UTC-4, Scheck David wrote: > > I'm near the result of sql: > > https://dpaste.de/1XYa# > >> >>> Do you want this to be an attribute/relationship on the class or to run this as a separate query? -- SQLAlchemy - The Python SQL Toolkit and Object Relation

[sqlalchemy] Re: Is it good practice to copy all sqlalchemy models (tables) for every API I create?

Are all of these APIs for the same company/organization (e.g. in-house services), or are you developing something for different companies (you are an agency with clients)? If everything is for the same company, I typically define and maintain a single model in a dedicated separate package. The

Re: [sqlalchemy] why MySQL DOUBLE is asdecimal=True by default

FWIW, that PR would have been during MySQL5.0 - not 5.5. 5.0 was released in October 2005; 5.1 was not released until November 2008 assuming the 5.0 docs... > > https://docs.oracle.com/cd/E19078-01/mysql/mysql-refman-5.0/data-types.html#numeric-types looking deeper at their docs, this bit may

Re: [sqlalchemy] Commands out of sync; you can't run this command now

On Wednesday, April 17, 2019 at 4:36:30 AM UTC-4, tonthon wrote: > > May the scoped_session factory, used in both services (web and celery), > may cause such a problem ? > Apologies for my earlier reply, that would only affect exceptions in Celery. I do suggest checking your Celery code to ma

Re: [sqlalchemy] Commands out of sync; you can't run this command now

On Tuesday, April 16, 2019 at 9:41:39 AM UTC-4, Mike Bayer wrote: > > On Tue, Apr 16, 2019 at 4:49 AM tonthon wrote: > > > > Celery tasks are using the same scoped_session factory, could it cause > the errors we're facing here ? > Is Celery running as threads or multiprocessing? IIRC, it u

[sqlalchemy] Re: 100 records are inserted while inserting 10 records raw_connection

You have two for loops On Tuesday, April 16, 2019 at 6:47:34 AM UTC-4, Suresh Chinta wrote: > > > for rows in range(0,df.shape[0]): > ... > for row in range(0,df.shape[0]): > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/

[sqlalchemy] Re: If your message did not appear on this list, please post again.

the 2 messages were caught for moderation in the spam filter. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a

[sqlalchemy] If your message did not appear on this list, please post again.

I approved 2 messages earlier today from people who have not posted before, but they haven't appeared yet. There seems to be some oddity with Google Groups. If your message didn't appear, please repost them. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlal

Re: [sqlalchemy] Re: Commands out of sync; you can't run this command now

On Wednesday, April 10, 2019 at 3:29:47 PM UTC-4, Mike Bayer wrote: > > The connection pool is safe in single process multiple threads. It breaks > on fork() because the socket travels across the process boundary and is > essentially copied between two or more processes that are unaware of eac

Re: [sqlalchemy] Re: Commands out of sync; you can't run this command now

On Wednesday, April 10, 2019 at 1:17:01 PM UTC-4, Mike Bayer wrote: > > op, you're right, that is a thing.Not sure if mod_wsgi makes it > easy to make that mistake though, that is, I thought all the Python > happens after the fork. Well, if they are using daemon mode which > you definite

[sqlalchemy] Re: Commands out of sync; you can't run this command now

On Wednesday, April 10, 2019 at 9:23:02 AM UTC-4, tonthon wrote: > > Our services are served through apache and mod_wsgi (1 process, 10 > threads). > > ... > > I can't reproduce this problem that seems to happen randomly. > > Considering this is multi-process/thread system - does any of your

[sqlalchemy] Re: Include total count in query results with limit and offset

On Wednesday, April 3, 2019 at 1:40:16 PM UTC-4, Derek Lambert wrote: > > Thanks, that does make more sense. > > I ended up writing a little wrapper that allows me to create the query in > my service including the offset/limit and continue to add additional > filters, etc. when it's run. > I a

[sqlalchemy] Re: Include total count in query results with limit and offset

On Monday, April 1, 2019 at 12:10:52 PM UTC-4, Derek Lambert wrote: > Is it possible to return a query's results as a dictionary containing the total count and a limited portion of the results? You'd be a lot better off trying to accomplish this with a simple helper function than trying to cha

Re: [sqlalchemy] testing/raising errors for queries that do not filter against themselves

On Friday, March 29, 2019 at 7:35:02 PM UTC-4, Mike Bayer wrote: > > > I'm assuming you mean a double equals sign there. Yes. I am embarrassed > I think I was assuming that user would come back with a great success > story and this could be either on the wiki or maybe even an ext or a > fl

[sqlalchemy] testing/raising errors for queries that do not filter against themselves

I was going batty trying to figure out why a unit test kept passing, while the functionality was clearly broken. I eventually realized the problem was a `filter` that didn't apply to the actual query. The code was essentially this: result = s.query(Foo).filter(Bar.id = 1) but it was diffi

Re: [sqlalchemy] user-defined simple fields in a multi-tenant flask app with ORM

On Friday, March 29, 2019 at 4:39:40 AM UTC-4, Xavier Bustamante Talavera wrote: > > > @Ibrahima and @Jonathan, as I understand you are talking about something > like the Entity–Attribute–Value model > , > adapted to

Re: [sqlalchemy] Re: Using operators with multiple InstrumentedAttribute instances

On Thursday, March 28, 2019 at 6:27:52 PM UTC-4, Mike Bayer wrote: > > > is this some standard thing you're both doing? I didn't see anything > about joins or query analyzing.you often have answers for > questions where I don't understand what theyre asking! > Well his question and the

[sqlalchemy] Re: Using operators with multiple InstrumentedAttribute instances

I gave up on attempts to do something similar a while back, because it became to problematic to examine all the SqlAlchemy objects – and the existing query – in an effort to construct the joins and query correctly. I would up using a two-phase approach. phase 1 analyzes the 'requested metrics'

Re: [sqlalchemy] user-defined simple fields in a multi-tenant flask app with ORM

On Thursday, March 28, 2019 at 9:19:51 AM UTC-4, Mike Bayer wrote: > > > > Simpler solutions would be just using hstore or JSON types, but I would > be loosing the goodies of SQLAlchemy / Postgres schemas and consistency. > > this is totally how I'd want to do it unless your clients are given a

Re: [sqlalchemy] RAW SQL working on Postgres but not in SQLAlchemy

On Tuesday, March 26, 2019 at 1:46:37 PM UTC-4, Piyush Nalawade wrote: > > Hi Mike, > > In the above example does the text and params help to protect from SQL > injection attacks? > yes. see https://docs.sqlalchemy.org/en/latest/core/sqlelement.html?#sqlalchemy.sql.expression.text on how

Re: [sqlalchemy] 'unique' exception not translated by sqlalchemy

On Monday, March 25, 2019 at 6:00:28 AM UTC-4, Simon King wrote: SQLAlchemy normally wraps all DBAPI exceptions in its own exception > classes. If that's not working for you, could you show an example that > we can run to reproduce the problem? In addition, please share the versions of SqlA

Re: [sqlalchemy] rendering "NOT EXISTS ()" in PostgreSQL ?

thanks, mike. I may just make a 'not exists' op. there are a handful of places in the docs that show `NOT EXISTS` with no parenthesis. Do you think this is because of a change in the codebase or a difference in the backends? If it's because of legacy code, I can generate a PR to update the doc

Re: [sqlalchemy] is it possible to use a secondary lookup against a session's object map (or other ideas to handle a weird use-case)?

Thanks Simon! For now I'm using a dirty hack - I created a view of the table, and mapped another class to that view. So I have a writeable class based on the 'id', and a readable based on the tuple. This fixes my database traffic issue for the time being. -- SQLAlchemy - The Python SQL Too

[sqlalchemy] is it possible to use a secondary lookup against a session's object map (or other ideas to handle a weird use-case)?

I doubt this is possible, but SqlAlchemy supports so many oddities it might be... I have an association class/table which has a primary key on the serial column `id`, and a unique constraint on the columns `id_a` and `id_b` which acts as a secondary key. is there a sane way to use something

[sqlalchemy] rendering "NOT EXISTS ()" in PostgreSQL ?

Is it possible to easily render "NOT EXISTS( " with a current API command? Both `~exists(` and `not_(exists(` will render "NOT ( EXISTS( ". I need to remove the superfluous parenthesis (this has to do with unit tests and ensuring parity of SQL across a handful of apps and custom commands). I c

[sqlalchemy] Re: Return null object for outer join relationships, instead of object with all null values

it's best to post some (executable) code on this to replicate your problem and illustrate it for everyone. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.

[sqlalchemy] Re: Security Implications of Directly Executing Queries Compiled to Strings

On Friday, March 8, 2019 at 12:56:26 PM UTC-5, Walt wrote: > > Thanks, so the limitations the documentation is bringing up are more that > SQLAlchemy doesn't know how to bind the same variety of types as does the > DBAPI? > Hopefully I will get this write so Mike won't have to correct me... I

[sqlalchemy] Re: Security Implications of Directly Executing Queries Compiled to Strings

forgot to add: in your example: NEVER TRUST USER INPUT. you'll get a much better error and can fix problems when you don't trust them and try to sanitize stuff - (x > user_provided_value) + (x > int(user_provided_value)) # or float or whatever The stuff 'compile' generates won't neces

[sqlalchemy] Re: Security Implications of Directly Executing Queries Compiled to Strings

Do you control the HTTP API or is this someone else's system? Does the API just execute the raw sql it is provided, and you're looking to generate that? What you want to do is pipe a SqlAlchemy query into a function that can compile it into the right statement for your database. Below is an e

Re: [sqlalchemy] Re: Organizing a project

We "define" the model in a separate package - all the classes and relationships are in there. There are database support items as well, and some of the advanced/business logic that manipulate the ORM objects. By advanced-database-specific logic, an example might be: resetting a password is a f

[sqlalchemy] Re: Organizing a project

I generally write the SqlAlchemy models as a separate package, then import that into my MVC/etc patterned app. In terms of the SqlAlchemy logic, some of that I keep in the models package, others are in the core app. This allows other apps and utilities to be built off the SqlAlchemy models and

[sqlalchemy] Re: orm internal question - querying multiple classes

thanks, Mike! -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message

[sqlalchemy] orm internal question - querying multiple classes

I couldn't find an answer to this in the docs. I'm auditing a handful of queries that are in a form similar to this... query = session.query(Foo, Bar).join(Bar, Foo.id == Bar.foo_id) I think SqlAlchemy is interpreting this as: select from the leftmost entity from `query` (Foo), joining t

Re: [sqlalchemy] how to define a correlated self-referential relationship

On Saturday, February 23, 2019 at 1:22:21 PM UTC-5, Mike Bayer wrote: > > what happened to the window function thing I showed you ? > This app is still 1.2 and I don't have time to schedule in a migration + testing for 1.3 (in case anything breaks on upgrade). I think I may just replace thi

[sqlalchemy] how to define a correlated self-referential relationship

I can't seem to figure this out - I need to define a self-referential relationship on an table with a max of 25 items. I use the following recipe often to handle this across tables, but I can't seem to figure out how to do this with a single table as Aliased object (which I'd use) is not allo

Re: [sqlalchemy] Compiling to Postgres VALUES clause with bindparam

On Tuesday, February 12, 2019 at 6:14:44 PM UTC-5, Mike Bayer wrote: > > as you know I prefer making things possible to making things assumed > :) , because in the latter case, I have to keep attending to those > assumptions as they change.The whole "rewrite EXPANDING" part of > defaultdia

Re: [sqlalchemy] Compiling to Postgres VALUES clause with bindparam

This looks very useful to other pg users. What about making this part of the PostgreSQL dialect somehow? -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.

[sqlalchemy] Re: Automapping self referential relationships with sqlalchemy to reduce database round trips

Your relationships are set to load via lazyloading ('select') or subqueries ('subquery'). you want to eager load the relationships, which can be done on the mapper or on a specific query. see https://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html -- SQLAlchemy - The Python SQL

[sqlalchemy] Re: Do autoflushes occur asynchronously?

If you are dynamically creating the list of attributes to inspect, you can make the behavior a bit more predictable by applying a `sort()` to the list of attributes -- otherwise you're iterating over the list in a (somewhat) random order that is not guaranteed to repeat every time. -- SQLAlche

Re: [sqlalchemy] Orm models, transaction and multiple binds

On Friday, January 11, 2019 at 2:45:58 PM UTC-5, Mike Bayer wrote: > > if these "binds" point to exactly the same database schema and just > have different permissions you might find it much easier to use one > session with two binds.You can override get_bind() to use a > different engine

[sqlalchemy] Re: Orm models, transaction and multiple binds

I don't think you can accomplish this with pyramid_tm being used. My initial response was going to be this: since session_1 is readonly, don't configure it to join the pyramid_tm/zope transaction. Then just rollback session_1 when session_2 commits. but session_2 is going to commit a

[sqlalchemy] Re: Boolean column represented in DB as a string 'T' or 'F'

I'm sorry the database was implemented like this. I believe you'll have the best luck using a CustomType or TypeDecorator * https://docs.sqlalchemy.org/en/latest/core/custom_types.html Basically you would create a custom type that supports 'T' and 'F' in SQL but True/False in Python, and set

[sqlalchemy] Re: Announcing SQLAlchemy Dialect for Apache Drill

The best way to handle this would be issuing a PR which lists your dialect against this file: https://github.com/sqlalchemy/sqlalchemy/blob/master/doc/build/dialects/index.rst That is used to build the online documentation for the .org; also Mike is more likely to approve a simple PR tha

Re: [sqlalchemy] Custom mapping of relations

Since you're looking at readonly stuff, one of the faster ways I've found is to write a view on the database and create a sqlalchemy classes for it. That lets you customize and tweak the exact query for performance on the server, SqlAlchemy essentially "selects all" from it, and then you do the

Re: [sqlalchemy] set a query timeout on a per query basis

In postgres, you can execute: SET statement_timeout = 6; at any point. It lasts until the end of the "session", which I believe would be the SqlAlchemy connection's lifetime. On Monday, November 12, 2018 at 6:44:06 PM UTC-5, Mike Bayer wrote: > > > if statement_timeout is accepted

[sqlalchemy] Re: Tiny doc clarification request

On Thursday, November 8, 2018 at 12:45:57 PM UTC-5, Lele Gaifax wrote: > > But maybe I'm missing some detail on the "engine specific" > implementations, > where, say, "foo['string']" may be considered a very different operation > than > "bar[2]"... > Well that also has to do with whatever t

Re: [sqlalchemy] Tiny doc clarification request

is the list operation nested? i.e. the example states: - data_table.c.data[('key_1', 'key_2', 5, ..., 'key_n')] does that correspond to: ['key_1']['key_2']['5']['...']['key_n'] = foo If so, it might make sense to call the first two "toplevel index operations, by key or in

Re: [sqlalchemy] Increase max query length!

On Friday, November 2, 2018 at 7:48:22 PM UTC-4, Ruben Di Battista wrote: > > ... > I was asked to furtherly optimise that query and the solution that was > found was to build up the query textually. I really hate it for a multitude > of reasons, but well… I’m not the one making the decisions!

Re: [sqlalchemy] Increase max query length!

can you elaborate on how much data is being loaded and what performance you're targeting ? if you're concerned with loading many MB of data as periodic batches, the best performance by far is going to be generating a text file in one of the formats your database server natively supports, and us

Re: [sqlalchemy] new tests show a connection/transaction anti-pattern, unsure how to address this.

On Wednesday, October 24, 2018 at 9:01:53 PM UTC-4, Mike Bayer wrote: > > that's not normal unless you are holding a Connection around and > calling close() on it, have a stack trace ? or is this inside an > event handler? > First, *a huge thanks for everything.* This had been troubling me

<    1   2   3   4   5   6   7   8   9   10   >