[sqlalchemy] Re: Trying to join a session query to a union

you probably need to modify the various objections with a `.select()` or `.subquery()` e.g: query_select = query.select() q = intersect.join(query_select, query_select.c.sid, intersect.c.sid) pay attention to the docs on what the various methods return. some return a selectable, others don

[sqlalchemy] extreme/aggressive query simplification with load_only

wondering- has anyone tried ways to mimic the load_only feature but not fetch the primary key (which is autoloaded, even if not specified)? I know that the orm needs this to make load-only work as intended, which is why I used the work 'mimic'. trying to get some data-transfer down, and some se

[sqlalchemy] Re: sqlaclhemy ORM don't update jsonb[postgres-psycopg2]

This sounds like you may be having issues with mutation tracking on edits. > > http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html?highlight=jsonb#sqlalchemy.dialects.postgresql.JSONB > The JSON

[sqlalchemy] Re: how to recycle connections grown stale after checkout

http://docs.sqlalchemy.org/en/latest/core/pooling.html#dealing-with-disconnects -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups

Re: [sqlalchemy] Re: SQLAlchemy 1.0.6 Released

I guess they are related. I was just thinking about a link on each page that says "For another type of versioning within SqlAlchemy, click here". -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving e

[sqlalchemy] Re: SQLAlchemy 1.0.6 Released

Michael, I had no idea about this versioning. Looking it up, I first found this in the docs: http://docs.sqlalchemy.org/en/latest/orm/examples.html#versioning-objects That didn't look right, so I checked the changelogs and saw some specifics in the 09 release note. Looking up those terms, I

[sqlalchemy] Re: create/restore a dump-file

Depending on how much data and foreign keys your application has, anything other than pg_dump will often be a nightmare in terms of performance. If you just have a negligible amount of data, sure - you can write migration scripts. But if you're using Postgres – vs sqlite or other tiny, localiz

Re: [sqlalchemy] Query hangs after a while

Are you positive there isn't some transactional locking going on? What does mysql say about locked tables/rows when things are stalled? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from

[sqlalchemy] Re: What is the SQLAlchemy terminology for ..?

Your question and example are both a bit confusing. I'll try to impart some knowledge that can help you re-focus an ask. 1. In your example, `Session.query(CaseTestInstance)` will return all the data in the `cases_test_instance` table. 2. "Recursive Queries" can mean a few things (and is often

[sqlalchemy] Re: .any() queries and MySQL performance

FWIW, the first thing I noticed is that this will rarely work well, as the index is on a 255length column. if your table is big, you would definitely see a large improvement by storing a 32char md5 version of that column, creating a partial index on the first 5-8 characters, and then adding a f

[sqlalchemy] Re: Session is commiting without an add()

You're experiencing exactly what Michael pointed you to. Person has a relationship+backref to SourceDatabase though `Person.source_database`. When you create a `John` and assign the existing source_database, sqlalchemy is implicitly adding the Person so that the update you explicitly defined

[sqlalchemy] Re: How to find out if an object is a SQLAlchemy mapped one?

You should be able to handle this by inspecting the object. http://docs.sqlalchemy.org/en/latest/core/inspection.html It will raise `sqlalchemy.exc.NoInspectionAvailable` if you can't inspect. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To

Re: [sqlalchemy] how to return an array of dicts

All my models inherit from an additional base class with this method: def columns_as_dict(self): """return a dict of the columns; does not handle relationships""" return dict((col.name, getattr(self, col.name)) for col in sqlalchemy_orm.class_mapper(self.__class__).mapped_tabl

[sqlalchemy] Re: ...and MVC

On Sunday, May 31, 2015 at 4:48:09 AM UTC-4, Fayaz Yusuf Khan wrote: > > I do the former in cases which involve very long transactions. (Batch > uploads and processing. I'm counting batch and background tasks as part of > the controller layer here.) > I manage transactions manually in the cont

Re: [sqlalchemy] Session.close() implicite

Michael- FYI, memoized_property isn't documented. it looks to be in the active source, and there is a recipe for a roll-your-own memoized orm properties. On Monday, May 25, 2015 at 11:43:58 AM UTC-4, c.b...@posteo.jp wrote: > > I am quite new to Python and not familiar with the decorator conce

[sqlalchemy] Re: ...and MVC

In most implementations, the answer would be: "None of the above". Many MVC style frameworks (including all the near variants) will place the SqlAlchemy session management in a WSGI middleware layer or a "tween". Usually it will look like (pseudocode): try: sqlalchemy_session_setu

[sqlalchemy] Re: restrict child count?

I think I might understand you... You have a BOX, which could be a variable amount of sizes, and each size can hold a variable amount of items. You want to create a rule(s) that will ensure you do not have too many things in each box. If that is that case: 1. You could use Triggers in Postgre

Re: [sqlalchemy] flake8 tips?

I just found a bug in my code that is related to this, and wanted to circle back... I keep forgetting that these are not the same comparison on Postgres: Foo != True Foo IS NOT True They match as follows: `!= True` = [False, ] `IS NOT True` = [False, NULL] -- You received

[sqlalchemy] Re: Authoring Dialects: How to Fix AssertionError: assert Decimal('15.7563') in [15.7563] ?

Can you share the test and any relevant code (ie, anything in the dialect that deals with this)? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubs

Re: [sqlalchemy] Abort Query

The easiest thing to do would be to preemptively set a shorter query timeout for your connection (this would be a database command). A possible, much harder, effort would be in telling the database backend to cancel the query. Databases all support this, but it would be an effort to monitor, t

Re: [sqlalchemy] python customer function

Would you be able to use a TypeDecorator? http://docs.sqlalchemy.org/en/latest/core/custom_types.html#sqlalchemy.types.TypeDecorator That will allow you to define a function for handling how sqlalchemy inserts and accesses the data from sql. -- You received this message because you are subsc

[sqlalchemy] Re: contains_eager limited by .first()?

The problem is a behavioral quirk. I'm not sure if this is a regression or not, but I've seen it brought up recently... IIRC: The join creates a matrix result (many rows), but `first()` only pulls the first item from the result. Calling 'one()' will pull all the results, but raise an error i

Re: [sqlalchemy] prototyping a complex query?

I'll try the selectable. That's a good idea. This won't work as a view -- it'll run too slow. It could be a view of a function, but then it's not really prototyping. I'm trying to figure out an interim solution on the SqlAlchemy side. Right now I'm using some custom objects that re-implement

[sqlalchemy] prototyping a complex query?

I have a rather complex query that I've written in SQL • The result-set is 6 columns (1 = timestamp, 2-6 = fkeys on tables/objects) • The query involves 15 tables • The query is about 100 lines of raw SQL • The query is not fun, and apt to change • There are 2 placeholders/bind variables to be exe

Re: [sqlalchemy] How can i use LIKE with an association proxy?

FWIW, another option is to pull in all the addresses and use a class method to filter. class User(): def all_emails_like(self, expression): return [e for e in self.all_emails if regex_match(expression, e)] I've found that depending on your app/db and the size of `.al

[sqlalchemy] Re: H2 database

SqlAlchemy needs 2 things for a database work: 1. A python database driver 2. A SqlAlchemy dialect (that tells SqlAlchemy how to write sql for the driver) So... 1. H2 doesn't seem to have any Python drivers or other support. I just came across an old forum post that talked about possibly usin

[sqlalchemy] Re: pandas.DataFrame.to_sql method: how to speed up exporting to Microsoft SQL Server (6 minutes for 11 MB!)

I've ran into similar issues like this before -- but on different databases. I think it's bad to think of the problem as "transmitting 31k/s" -- which assumes a particular issue; and much better to think of it as "processing 31k/s", which gives a lot more room for interpretation. Looking on St

Re: [sqlalchemy] SQLAlchemy as plain-SQL generator

Just be warned of this method -- make sure you're using the most recent SqlAlchemy version in the .9x or 1.x branches. Earlier versions would not apply LIMIT or OFFSET into the bind. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscrib

[sqlalchemy] Re: The use of SQLAlchemy for a long term project

As a heavy user, an occasional contributor, and the person who recently mined PyPi for all the historical SqlAlchemy data to generate the new release history matrix... I don't think you have anything to really worry about for long term use. The majority of updates over the past 7 years that hav

Re: [sqlalchemy] Documentation for SQLAlchemy 0.4.5?

PyPi doesn't always let you see old releases easily anymore. In order to get to older versions off PyPi, you need to use one of 2 tricks: 1. If you know the version number: visit https://pypi.python.org/pypi/SQLAlchemy/{VERSION_NUMBER} e.g. https://pypi.python.org/pypi/SQLAlchemy/0

[sqlalchemy] Re: SQLAlchemy delay reconnecting

I've had issues like this in the past, and in the worst situations your services can end up giving your system a ddos. what i usually do to avoid this, is implement a delay on the connection pool itself, and have each service cycle through a set of delays with a different starting point in th

Re: [sqlalchemy] generating safe sqlalchemy.sql.text elements?

oh, a HUGE thanks! -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googleg

Re: [sqlalchemy] generating safe sqlalchemy.sql.text elements?

It's never 'untrusted user input', but could be 'worrisome junior developer input'. I didn't realize that I could attach the bindparam to `text()`. This is exactly what I should have wanted in the first place! -- You received this message because you are subscribed to the Google Groups "sqlal

[sqlalchemy] generating safe sqlalchemy.sql.text elements?

I have an include file that generates a handful of timestamp clauses: def sql_now(): return sqlalchemy.sql.text("(CURRENT_TIMESTAMP AT TIME ZONE 'UTC')") def sql_now_minus_10_minutes(): return sqlalchemy.sql.text("(CURRENT_TIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '10 MINUTES')") One of them need

Re: [sqlalchemy] PostgreSQL UNION with ORDER or LIMIT parenthesis patch

I wish I saw this earlier. This thread basically describes a situation I've had, and explains a few of the weird hacks I've had to use. If you need a workaround, I've been getting around this by using a union on 2 subqueries, querying the union columns, and using plaintext sql to order (the sq

Re: [sqlalchemy] Long transactions with SQLite (and of course locks)

I've used timing around the events to determine long statement execution and transaction times. A trick I learned was logging that stuff to a separate database 'autocommit' enabled database and session. • If a statement took took long to execute, I'd log the query + params. • If the session to

Re: [sqlalchemy] is True vs ==True

On Wednesday, April 1, 2015 at 2:56:31 AM UTC-4, jo wrote: > > *session.query(Rischio.c.codice).select_from(Rischio).filter(Rischio.c.peso_gruppo > > == '1') #**true* > *session.query(Rischio.c.codice).select_from(Rischio).filter(Rischio.c.peso_gruppo > > == '0') #false* > I don't think

[sqlalchemy] Re: Long transactions with SQLite (and of course locks)

On a sidenote, I found the best approach to a similar problem (which used Twisted) was to make the transactions as short as possible. Three hours of changing how transactions were used was far more helpful than 2 weeks of trying to get around long transactions. -- You received this message be

Re: [sqlalchemy] is it possible to turn off DELETE cascades globally for a session?

Thanks! The workaround is best for my purposes. I need to keep the relationships for the "app", but they were benching to triple the migration script's timing.Using the engine directly to get around this, everything migrated overnight. _delete = dbSession.execute( model.core.M

[sqlalchemy] is it possible to turn off DELETE cascades globally for a session?

I'm running a migration script, and the deletes via the ORM were creating a performance issue. (trying to load 4 relationships 9million times adds up) I couldn't find any docs for this (just some references on update cascades) so i sidestepped the issue by just running the Engine's delete on th

[sqlalchemy] Re: Is there any potential problem to store datetime as strings in one column?

On Monday, March 30, 2015 at 9:06:48 PM UTC-4, Bao Niu wrote: > > To make sure that I understand it right, did you mean "hybrid attribute > methods" when you mentioned "property methods" here? Thanks > No, just simple property methods. A super-simple first Minimum Viable Product/iteration mi

Re: [sqlalchemy] is True vs ==True

If you're just worried about warnings in Flake8, you can turn them off line-by-line: filter( Rischio.c.peso_gruppo == True # noqa )\ -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop recei

[sqlalchemy] Re: SQLAlchemy 1.0.0b4 released

If you're not ready for 1.x you should also make sure that you don't upgrade with easy_install or pip <= 1.4; both of which will pull the 1.x branch. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receivi

[sqlalchemy] Windowed Range Example was out-of-date

Just a quick warning to others: I noticed this warning when running a maintenance script that used the Windowed Range Query example: /sqlalchemy/sql/elements.py:3736: SAWarning: Textual SQL expression 'rownum % 100=1' should be explicitly declared as text('rownum % 100=1') (this warning ma

Re: [sqlalchemy] Table Views

SqlAlchemy doesn't natively support traditional database "views". They're often handled by "reflecting" an existing database view as a database table (http://docs.sqlalchemy.org/en/latest/core/reflection.html#reflecting-views). There are also some API functions that deal with getting "view" m

[sqlalchemy] Re: creating a table that shadows (clone of) another table

The easiest way is to use a mixin: http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/mixins.html -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sq

[sqlalchemy] Re: Dynamically constructing joins

Yeah, there's no reason to touch eval -- and a lot of reasons not to. Security issues aside, when you make a mistake the error will be completely unintelligible. You can create joins dynamically very easily by just iteratively building up on it, and using getattr() if needed. If you're doing

[sqlalchemy] Re: Dynamically constructing joins

any reason why you're not building a query like this? query = db.session.query(label('sid', distinct(a[1].c.patient_sid))) if n >= 2 query = query.\ join(a[2],a[2].c.patient_sid==a[1].c.patient_sid) if n >= 3 query = query.\

[sqlalchemy] Re: Is there any potential problem to store datetime as strings in one column?

On Sunday, March 22, 2015 at 7:01:35 PM UTC-4, Bao Niu wrote: > > Also because sql datetime datatype doesn't persist timezone information. > Therefore, I tried to store all the time information as strings. > If your database doesn't support timezones, I think it would be easiest to convert eve

[sqlalchemy] Re: SQLAlchemy 1.0.0b2 released

Wow. This is noticeably faster. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sq

Re: [sqlalchemy] Sqlalchemy, Flask-User & scoped_session

On Monday, March 16, 2015 at 10:22:27 AM UTC-4, Simon King wrote: > > It sounds like flask-user assumes that you are using > Flask-SQLAlchemy's special declarative_base class. I think that as > long as you use Flask-SQLAlchemy's session (which is a scoped_session) > and declarative_base everyw

Re: [sqlalchemy] duplicate an object

Thats a mixin class that can be added onto any object inheriting from declared_base. You only need one of the `columns_as_dict` options. I showed 3 variations: one that gives all columns, one that lets you specify which columns to include, and another that shows which columns to exclude.

Re: [sqlalchemy] duplicate an object

Use a mixin on the classes to create a dict of the column values. Then create a new object with that dict. You can customize the mixin to include or exclude columns as you wish. I use this approach for revision tracking. Class Duplicable(object) @property def columns_as_dict(self): return

[sqlalchemy] Re: Having a proxy attribute along a relationship path

What version of SqlAlchemy are you using, and what error are you seeing? I use the association_proxy for M2M, M2O, O2O all the time. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from i

[sqlalchemy] Re: Connection pooling strategy for a small/fixed number of db users

I basically do this too in a multi-tier setup (1 write, n read, also some loggers). Everything in my /account section is handled by the Master (read/write), everything else is read only off slaves. The initial engine setup is nothing. The biggest hit is the metadata, and that can be recycled

[sqlalchemy] Re: DetachedInstanceError

On Sunday, February 22, 2015 at 10:09:18 PM UTC-5, Ed Rahn wrote: > > Let mw know if you need anything else! > What version of SqlAlchemy are you using? That may affect someone's answer. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsub

Re: [sqlalchemy] SQL Alchemy on Insert to DB

The simplest way using your code would be: for _candidate_data in candidates: _existing = session.query(CompassCandidate).filter(CompassCandidate.id == _candidate_data.id).first() if not _existing: # add the new item session.add(_candidate_data) else: # translate the attributes from the new item

Re: [sqlalchemy] SQL Alchemy on Insert to DB

A serial would usually be an integer that is tied to a sequence (postgres, oracle) or auto_increment (mysql). I see in your example that you have a unicode string -- `ORaE9+aCdP0` If that is coming from another db, then you've already migrated that candidate. you'll should do a search for th

Re: [sqlalchemy] SQL Alchemy on Insert to DB

This looks to be your problem: id = Column(String(64), primary_key=True) `id` is a primary key, which means it must be unique. But you're not setting it to a unique value. Most people will use a Serial column for this. -- You received this message because you are subscribed to the Goog

Re: [sqlalchemy] idle in transaction

On Saturday, February 14, 2015 at 9:58:26 PM UTC-5, Ed Rahn wrote: > > This seems like a fairly common use case, do people just not care about > it or how do they handle it? > This isn't common, and looks like an anti-pattern. Consider these lines from your emails - they're not really compati

[sqlalchemy] Re: idle in transaction

Just to add to Michael's reply -- keep in mind that how you handle the session (commit/rollback/close) will impact the state of any objects the session loaded if you're using the ORM and that can have implications in your app. The default behavior of sessions is to have `expire_on_commit=True`

[sqlalchemy] Re: Is there a tool/library that visually renders SQLAlchemy classes as UMLs or something similar?

If there is a graphical sql designer you prefer -- just use that to create your sql/database. Once you create the SQL, you can use `sqlalcodegen` to convert it to sqlalchemy models. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe

Re: [sqlalchemy] Seamlessly combining data from multiple tables into a single SQLalchemy object

Yeah, I use this use-case a lot. I never actually search with it -- just usually map the scalar for display needs. But it appear to recognize the join and automaps if needed: s.query(Foo).filter(Foo.qux == 'abc').first() generates SELECT foo.id AS foo_id, foo.id_bar AS foo_id_bar, bar_1.id

Re: [sqlalchemy] Seamlessly combining data from multiple tables into a single SQLalchemy object

Simon, why not use the association_proxy? You just described this: from sqlalchemy.ext.associationproxy import association_proxy class Foo(Base): _bar = relationship(Bar, uselist=False, lazy='joined') qux = association_proxy('_bar', 'qux') -- You received this message because you are su

[sqlalchemy] Re: Seamlessly combining data from multiple tables into a single SQLalchemy object

That's what the association proxy does: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/associationproxy.html On Tuesday, February 10, 2015 at 8:47:29 PM UTC-5, Dun Peal wrote: > > I have a declarative base called Foo, instances of which get most of their > attributes from a Foo SQL

[sqlalchemy] Re: Old event listeners firing on the wrong session?

Oh yeah, I forgot the important detail -- I use a secondary session that autocommits for the logging. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy

[sqlalchemy] Re: Old event listeners firing on the wrong session?

I just wanted to suggest another approach that may work, at least as a fallback. When I upload files to Amazon S3 I track the activity in a logging table (via sqlalchemy) that looks a bit like this: id | filename | timestamp_upload_start | upload_status (bool) | timestamp_deleted Before upl

Re: [sqlalchemy] Introspecting sequences

On Thursday, February 5, 2015 at 8:51:36 PM UTC-5, Michael Bayer wrote: > and then only in conjunction with columns that explicitly use SERIAL. Just a quick warning on this: SERIAL could also be BIGSERIAL. They're just INT/BIGINT fields with certain properties set and a declared relationsh

[sqlalchemy] refactoring "write heavy" fields with SqlAlchemy? Is association_proxy a valid option?

My primary datastore under SqlAlchemy is PostgreSQL. I'm starting to run into some performance issues on a few tables that have heavy write activity on some columns -- the general operation of Postgres on UPDATE is to rewrite the entire row as new, and mark the older one as invalid (under some

[sqlalchemy] Re: How to load sql file during unit test

I use the same stack quite often. For something like that, I usually just have the testing script create the database: os.system("psql -Utestuser testdatabase < path/to/script.sql") you can store the db credentials in your shell's env variables. you could do everything through sqlalchemy,

[sqlalchemy] One to many lazy load generates incorrect query when the parent attribute appears more than once in the join expression

This is a longshot, but does applying .label('another_name') on one of your columns work? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@g

[sqlalchemy] Re: How to log all SQL executed during database introspection?

On Saturday, January 31, 2015 at 7:41:15 PM UTC-5, Fabio Caritas Barrionuevo da Luz wrote: > > Hello, how to log all SQL executed during database introspection? > > I would like to understand how exactly SQLAlchemy does on introspection > the database. > The easiest way is to turn on ALL sql

[sqlalchemy] Re: Mapping Similar yet different table structures automatically

You could use sqlacodegen (https://pypi.python.org/pypi/sqlacodegen) to generate your model: • generate both models • generate one model, then regex the other if you make a lookup table/function to map one column name to another, you could also loop over the columns. something sort of like th

[sqlalchemy] Re: Join SQL not optimal with inheritance

This should generate your second query: q = s.query(Foo)\ .join( Boo, Foo.id == Boo.id )\ .join( Bar, Boo.id == Bar.id )\ .first() -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscri

[sqlalchemy] Re: as_dictionary between classes

Unless I understand you wrong, this should be all you need: def as_dictionary(self): song = { "id": self.id, "info": self.info.as_dictionary() } return song > FWIW, I just use a base class / mixin that has this method: class UtilityObject(object): def

Re: [sqlalchemy] Difficulties with parallel data insertion into the same table

On Friday, January 23, 2015 at 1:10:37 PM UTC-5, SF Markus Elfring wrote: > > How often do you need to fill these data structures in > a concurrent way? > Does parallel table creation become more interesting then? > > Often and Not at All. -- You received this message because you are subscr

Re: [sqlalchemy] Difficulties with parallel data insertion into the same table

On Friday, January 23, 2015 at 12:48:46 PM UTC-5, SF Markus Elfring wrote: > > Should the Python class be sufficient for the definition > of a table structure? > If you're using the declarative syntax, yes. It's common to have a `models.py` file that simply defines the classes in one place;

Re: [sqlalchemy] Difficulties with parallel data insertion into the same table

On Friday, January 23, 2015 at 12:39:02 PM UTC-5, SF Markus Elfring wrote: > > Is the reason good enough to avoid the repeated specification > of corresponding meta-data? Is it safer to maintain and manage column attributes for some > tables only at a single place? > How are you currently

Re: [sqlalchemy] Difficulties with parallel data insertion into the same table

On Friday, January 23, 2015 at 11:30:33 AM UTC-5, SF Markus Elfring wrote: > > Are there any chances that this database software > implementation will become robust and safe against > the discussed race condition? I would not count on this happening in the near future as it doesn't seem to b

Re: [sqlalchemy] Difficulties with parallel data insertion into the same table

Using "IF NOT EXISTS" would not solve this problem in a high concurrency scenario. There would still be a race condition within the Postgres internal functions. This is because of how Postgres checks for existing tables and creates new ones with its internal bookkeeping. It's explained in t

[sqlalchemy] Re: Handling unique key violations with bulk inserts of large number of rows

+1 to mike's suggestion of batches to weed out potential dupes. that will save a lot. you'd also have to do some pre-processing within your 1000, to make sure one member of that set won't violate a constraint created by another. if your DB supports savepoints, you can also add in a savepoint

Re: [sqlalchemy] Difficulties with parallel data insertion into the same table

> > On Thursday, January 22, 2015 at 2:40:19 PM UTC-5, SF Markus Elfring wrote: > I am surprised that this database software show such (unexpected) > behaviour. > Aside from what Michael wrote... The reason why you're seeing an IntegrityError like that, is because Postgres is raising an integ

Re: [sqlalchemy] SQL join between two tables from two databases

On Tuesday, January 20, 2015 at 7:12:18 PM UTC-5, Michael Bayer wrote: > why is text() needed here?these could be the Table objects set up with > “schema=‘schema name’” to start with, then you’d just do the join with > query.join(). > It's not, I just didn't think of it. But I also sa

Re: [sqlalchemy] SQL join between two tables from two databases

On Tuesday, January 20, 2015 at 5:34:27 PM UTC-5, Brian Glogower wrote: > > > Thanks for the idea. Do you have an example? > I don't have a personal example handle, but from the docs... http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#using-literal-sql >>> session.query(User).from_state

Re: [sqlalchemy] SQL join between two tables from two databases

I haven't seen anyone bring this up before. If you get stuck and no better answer shows up here... I'd try just having a single session with tables from both DBs in it, and using raw SQL to populate the ORM objects -- using the MySQL native cross-database query format. It's not elegant, but I

[sqlalchemy] Re: ORM code example for a query of a single table

Take a look at sqlacodegen https://pypi.python.org/pypi/sqlacodegen/ It can build the Python classes (sqlalchemy declarative) from your existing database. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and st

Re: [sqlalchemy] Always Render Binds Inline

On Tuesday, January 13, 2015 at 8:12:49 PM UTC-5, Michael Bayer wrote: > > If you really want to disable bound parameters there’s a flag > “literal_binds” that’s on Compiler, but it isn’t going to work for things > like dates unless you implement renderers for them. > IIRC, `literal_binds` do

Re: [sqlalchemy] Difficulty pre-populating session without using .get

> > yeah, holding onto the all() result is pretty key :) > A hacky trick I use on web projects is to have a "sqlalchemy_persistance" array on the `request` object. i just append everything I load that "might be needed again" onto that. -- You received this message because you are subscrib

[sqlalchemy] Re: Slow commit after large select

It's hard to tell what could cause the issue: How many horses are there? What is going on in that other function? Are there foreign key checks involved with the commit? If you want to break a huge batch into smaller commits, there is the `Windowed Range Query` strategy -- https://bitbucket.org/

[sqlalchemy] Re: Distribute Source for Debugging?

Would this work for you: - download the source from github - in your environment, `python setup.py develop` the reason why i suggest using the github source, is that you can insert whatever breakpoints/traces you need in a new branch, and quickly see what is modified with a git command or switc

Re: [sqlalchemy] loop over two large tables to join them

I just want to point out something in Simon's first response that might have been missed: Simon batched the query, then used a `commit()` at the end of each batch. That `commit` is pretty important, because it limits his code to only keeping SqlAlchemy objects for the batch -- not every item t

[sqlalchemy] Re: sqlalchemy and MS Access Database

There is an outdated, unsupported dialect for MS Access that has been deprecated to the "attic" http://docs.sqlalchemy.org/en/latest/dialects/index.html """Dialects in the “attic” are those that were contributed for SQLAlchemy long ago but have received little attention or demand since then, an

[sqlalchemy] Re: When to use .cte(), .alias(), or .subquery() in a UNION of CTEs

It's really confusing. I ran into this a few months ago and wanted to stab things. I can't answer your question, but I can push you in the right direction: The trick is in looking at the actual objects that are returned/expected by each item. Some operations will return objects that require a

[sqlalchemy] Re: aggregate data by date-window over multiple import files

On Thursday, December 18, 2014 12:42:03 PM UTC-5, dewey wrote: > > Per file?? Even if it's a really large file?? Is the only limit on > transaction size available client memory or are their constraints on the > server side as well? > Usually, yes. If you fail partway through the file, you

[sqlalchemy] Re: aggregate data by date-window over multiple import files

Based on your specs, I think it would be simplest to store the rows in the DB then just run reports on them. You'd have a solution in minutes. You should be doing a transaction per Excel file, not per row. You could probably create a function or view that generates the record you want. -- Y

[sqlalchemy] Re: Unclear on a few things about the transactional rollback-style of unit testing

Take a look at the unit tests for the ORM itself, that might set you straight: https://bitbucket.org/zzzeek/sqlalchemy/src/7cd4362924dd0133a604d4a0c52f1566acbd31ff/test/orm/test_transaction.py?at=master -- You received this message because you are subscribed to the Google Groups "sqlalchemy" g

Re: [sqlalchemy] AmbiguousForeignKeysError or CircularDependencyError

Just a quick warning on this pattern (which I use as well)... When dealing with Edits/Updates and Database Migrations, you might run into issues where any mix of SqlAlchemy commands simply will not make the mapper happy. A workaround is to ignore the session and do these operations using the c

[sqlalchemy] Re: ORM Instance field validation

delete everything I said and listen to Mike! -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send e

[sqlalchemy] Re: ORM Instance field validation

Have you looked at using the `inspect` tool? It could solve all your issues within an event http://docs.sqlalchemy.org/en/rel_0_9/core/inspection.html 1. You can check to see if an object is `.transient` or not. if it is, then it's not bound to a session and you don't have to validate. 2. the

[sqlalchemy] Re: create a relationship to the "latest" item (i.e. many-to-one, where the 'one' is ordered)

Ok this works great! Thanks! The approach is not optimized for speed at all, but it's acceptable for now. A given query might take 112ms with this, and 14ms with my optimized select -- however this lets me use the ORMs eagerloading and I do extensive caching, so the speed issues are offset f

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