Re: [sqlalchemy] Confusion over session.dirty, query, and flush

[jens . troeger]

Thank you, the event worked like a charm :-) Though I think that I don't 
need the commit events, because the application terminates anyway.

I modified your approach to gather which objects were flushed so that in 
the end I can give the user more precise information:

dbsession.info["new"] = set()  
 
dbsession.info["dirty"] = set()
 
dbsession.info["deleted"] = set()  
 


def update_session_info(session):  
 
new = session.info["new"]  
 
new |= set(session.new)
 
dirty = session.info["dirty"]  
 
dirty |= set(session.dirty)
 
deleted = session.info["deleted"]  
 
deleted |= set(session.deleted)
 
return new, dirty, deleted  



@event.listens_for(dbsession, "before_flush")  
 
def on_before_flush(session, _, _):
 
update_session_info(session)

...
code.interact(local=locals())  
 
...
   
new, dirty, deleted = update_session_info(dbsession)

if new or dirty or deleted:
 
if new:
 
print("The following objects were created: ", new)  
 
if dirty:  
 
print("The following objects were modified: ", dirty)  

if deleted:
 
print("The following objects were deleted: ", deleted)  
 
yesno = input("Would you like to commit this transaction? [y/N] 
") 
if yesno == "y":

print("Committing transaction...")  
   
else:  
 
print("Rolling back transaction...")
   
raise _SessionRollbackException()  
 

# ...this is where the context closes and the transaction commits 
and the dbsession ends.

Cheers,
Jens



On Saturday, November 18, 2017 at 12:03:05 AM UTC+10, Simon King wrote:
>
> OK, I think tracking session events seems reasonable. You could do 
> something like this (completely untested): 
>
> from sqalchemy.event import event 
>
> @event.listens_for(YourSessionOrSessionMaker, 'before_flush') 
> def on_before_flush(session, flush_context, instances): 
> session.info['flushed'] = True 
>
>
> # You'd probably also want to reset the 'flushed' flag 
> # after a commit or rollback 
> @event.listens_for(YourSessionOrSessionMaker, 'after_commit') 
> @event.listens_for(YourSessionOrSessionMaker, 'after_rollback') 
> def on_session_reset(session): 
> session.info['flushed'] = False 
>
>
> # when user exits interactive session: 
> modified = ( 
> session.info.get('flushed', False) 
> or session.deleted 
> or session.new 
> or session.dirty 
> ) 
> if modified: 
> raw_input('do you want to commit?') 
>
>
> ...but note that if you ever execute raw SQL (ie. 
> session.execute('UPDATE x WHERE y')), that will not be noticed by 
> those events. 
>
> Hope that helps, 
>
> Simon 
>

-- 
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 because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this 

Re: [sqlalchemy] Connecting to AS400 with SQLAlchemy fails

[Mike Bayer]

hi there -

please direct these requests to the IBM list at:

https://groups.google.com/forum/#!forum/ibm_db



On Tue, Nov 21, 2017 at 2:25 PM, nahumcastro  wrote:
> Hello all.
>
> I have the same problem with db2 for as400, seems to be very different from
> db2 in windows, linux.
>
> Here is what I have found:
>
> this string dont apply for as400 as documented:
> ibm_db_sa://user:pass@server:port/database
> because when you connect to an as400 there is only one database with a bunch
> of schems.
> It should be:
> ibm_db_sa+pyodbc://user:password@ server:446/*local
> *local are the equivalent to a database.
> Now the problem is that you need a license to connect to the server or you
> will get:
>
>  SQL1598N  Ha fallado un intento de conectar con el servidor de bases de
> datos debido a un problema de licencia.
> The text is in spanish but says: buy a license to connect to the server.
>
>
>
>
> On Friday, February 12, 2016 at 8:58:39 AM UTC-6, Alex Hall wrote:
>>
>> Thanks so much for your reply--this really helps! I asked the people
>> at work, and was told that my machine does, in fact, have some sort of
>> IBM manager installed. (Can you tell I'm new to this technology and
>> this job?) Using it, I was able to create a DSN to the test database
>> and, it seems, connect. I'm getting an error when I call
>>
>>  dbInspector = inspect(dbEngine)
>>
>> but at least I'm getting that far. I'll ask about the error in a
>> separate thread, since more people are likely to have run across that
>> than seem to have experience with the 400 and IBM's wrapper.
>>
>> On 2/12/16, Michal Petrucha  wrote:
>> > On Thu, Feb 11, 2016 at 01:16:03PM -0500, Alex Hall wrote:
>> >> I've done more research on this topic. There's a lot out there about
>> >> using MSSQL with SA, but next to nothing about using ibm_db_sa or
>> >> specifying drivers.
>> >>
>> >> I have pyodbc installed. I downloaded IBM's ODBC zip file, and I've
>> >> put db2odbc64.dll in my project folder, but don't know how to point SA
>> >> or pyodbc to it. I've tried several versions of
>> >> "?driver="db2odbc64.dll"" appended to my connection string, but I keep
>> >> getting an error: "data source not found and no default driver
>> >> specified". It doesn't even time out anymore, it just errors out
>> >> immediately. I've also tried "ibm_db_sa+pyodbc://" to start the
>> >> string, but that fails too.
>> >>
>> >> This *must* be a simple thing, but I can't work out what to do, and
>> >> Google is failing me. If anyone has any ideas, I'd greatly appreciate
>> >> hearing them. Thanks, and sorry to keep bugging the list about this. I
>> >> just have no other options at the moment and I need to get this
>> >> working soon.
>> >
>> > Hi Alex,
>> >
>> > Unfortunately, I can't offer you any specific help with IBM DB, but
>> > judging by the number of replies, it seems nobody on this list can, so
>> > I only have some stab-in-the-dark suggestions.
>> >
>> > In my experience with enterprise software, *nothing* is ever a simple
>> > thing, not even seemingly trivial operations, such as connecting to a
>> > database.
>> >
>> > You can try using either pyodbc, or the ibm_db driver – in both cases,
>> > those are just the Python DBAPI drivers which take in textual SQL
>> > statements, send them to the database in the low-level network
>> > protocol, and present the results as dumb Python objects. SQLAlchemy
>> > is a layer on top of them. That means, the first step would be to get
>> > your Python runtime to open a raw pyodbc, or ibm_db connection to the
>> > server, and be able to execute raw SQL statements there. Only after
>> > you confirm this works you can move on to getting SQLAlchemy to work
>> > with the DBAPI driver.
>> >
>> >
>> > In my understanding, pyodbc is a wrapper around the library unixodbc.
>> > I'm not sure how it's implemented on Windows – whether it's a port of
>> > unixodbc, or it uses a different ODBC implementation there. Whatever
>> > the case, though, on Linux with unixodbc, when I wanted to connect to
>> > MS SQL, I had to register a low-level driver with the unixodbc
>> > library. I had to edit a system-wide configuration file
>> > (/etc/unixODBC/odbcinst.ini), and create a new driver definition in
>> > there to make unixodbc recognize the FreeTDS driver I'm using as the
>> > low-level protocol implementation.
>> >
>> > I have no idea what low-level ODBC driver is required to connect to
>> > IBM DB, I'm afraid you'll have to figure that out on your own. The
>> > official IBM docs at
>> >
>> > https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/c0007944.html?cp=SSEPGG_9.7.0%2F4-0-4
>> > seem to imply that IBM provides their own low-level ODBC driver which
>> > you'll need to have in place in order to be able to connect to the
>> > server using ODBC.
>> >
>> > In any case, I would expect that the ODBC machinery would expect to
>> > have the db2odbc64.dll 

Re: [sqlalchemy] Connecting to AS400 with SQLAlchemy fails

[nahumcastro]

Hello all.

I have the same problem with db2 for as400, seems to be very different from 
db2 in windows, linux.

Here is what I have found:

this string dont apply for as400 as documented:
ibm_db_sa://user:pass@server:port/database
because when you connect to an as400 there is only one database with a 
bunch of schems.
It should be:
ibm_db_sa+pyodbc://user:password@ server:446/*local
*local are the equivalent to a database.
Now the problem is that you need a license to connect to the server or you 
will get:

 SQL1598N  Ha fallado un intento de conectar con el servidor de bases de 
datos debido a un problema de licencia.
The text is in spanish but says: buy a license to connect to the server.




On Friday, February 12, 2016 at 8:58:39 AM UTC-6, Alex Hall wrote:
>
> Thanks so much for your reply--this really helps! I asked the people 
> at work, and was told that my machine does, in fact, have some sort of 
> IBM manager installed. (Can you tell I'm new to this technology and 
> this job?) Using it, I was able to create a DSN to the test database 
> and, it seems, connect. I'm getting an error when I call 
>
>  dbInspector = inspect(dbEngine) 
>
> but at least I'm getting that far. I'll ask about the error in a 
> separate thread, since more people are likely to have run across that 
> than seem to have experience with the 400 and IBM's wrapper. 
>
> On 2/12/16, Michal Petrucha  
> wrote: 
> > On Thu, Feb 11, 2016 at 01:16:03PM -0500, Alex Hall wrote: 
> >> I've done more research on this topic. There's a lot out there about 
> >> using MSSQL with SA, but next to nothing about using ibm_db_sa or 
> >> specifying drivers. 
> >> 
> >> I have pyodbc installed. I downloaded IBM's ODBC zip file, and I've 
> >> put db2odbc64.dll in my project folder, but don't know how to point SA 
> >> or pyodbc to it. I've tried several versions of 
> >> "?driver="db2odbc64.dll"" appended to my connection string, but I keep 
> >> getting an error: "data source not found and no default driver 
> >> specified". It doesn't even time out anymore, it just errors out 
> >> immediately. I've also tried "ibm_db_sa+pyodbc://" to start the 
> >> string, but that fails too. 
> >> 
> >> This *must* be a simple thing, but I can't work out what to do, and 
> >> Google is failing me. If anyone has any ideas, I'd greatly appreciate 
> >> hearing them. Thanks, and sorry to keep bugging the list about this. I 
> >> just have no other options at the moment and I need to get this 
> >> working soon. 
> > 
> > Hi Alex, 
> > 
> > Unfortunately, I can't offer you any specific help with IBM DB, but 
> > judging by the number of replies, it seems nobody on this list can, so 
> > I only have some stab-in-the-dark suggestions. 
> > 
> > In my experience with enterprise software, *nothing* is ever a simple 
> > thing, not even seemingly trivial operations, such as connecting to a 
> > database. 
> > 
> > You can try using either pyodbc, or the ibm_db driver – in both cases, 
> > those are just the Python DBAPI drivers which take in textual SQL 
> > statements, send them to the database in the low-level network 
> > protocol, and present the results as dumb Python objects. SQLAlchemy 
> > is a layer on top of them. That means, the first step would be to get 
> > your Python runtime to open a raw pyodbc, or ibm_db connection to the 
> > server, and be able to execute raw SQL statements there. Only after 
> > you confirm this works you can move on to getting SQLAlchemy to work 
> > with the DBAPI driver. 
> > 
> > 
> > In my understanding, pyodbc is a wrapper around the library unixodbc. 
> > I'm not sure how it's implemented on Windows – whether it's a port of 
> > unixodbc, or it uses a different ODBC implementation there. Whatever 
> > the case, though, on Linux with unixodbc, when I wanted to connect to 
> > MS SQL, I had to register a low-level driver with the unixodbc 
> > library. I had to edit a system-wide configuration file 
> > (/etc/unixODBC/odbcinst.ini), and create a new driver definition in 
> > there to make unixodbc recognize the FreeTDS driver I'm using as the 
> > low-level protocol implementation. 
> > 
> > I have no idea what low-level ODBC driver is required to connect to 
> > IBM DB, I'm afraid you'll have to figure that out on your own. The 
> > official IBM docs at 
> > 
> https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/c0007944.html?cp=SSEPGG_9.7.0%2F4-0-4
>  
> > seem to imply that IBM provides their own low-level ODBC driver which 
> > you'll need to have in place in order to be able to connect to the 
> > server using ODBC. 
> > 
> > In any case, I would expect that the ODBC machinery would expect to 
> > have the db2odbc64.dll registered somehow with a symbolic name in some 
> > configuration file, registry, or whatever, and that would be the 
> > string you're expected to pass as the driver name in the ODBC 
> > connection string. 
> > 
> > Actually, I 

Re: [sqlalchemy] Re: Two classes linked to the same backref attribute in a third classe

[Mike Bayer]

> I was more interested by the discriminator_on_association. But after some 
> tests, I find that the table_per_related solution works fine and the database 
> is cleaner as I expected.

yep!!  see that   nobody likes it at first



On Tue, Nov 21, 2017 at 1:04 PM, Olaf  wrote:
> Hello,
>
> Sorry for the delay of the answer, I was busy these last days.
>
> I tried every solution and I must honestly say that at the beginning, I was
> not attracted by the table_per_association.py and the table_per_related.py
> solutions because I didn't like the idea of having tables automatically
> generated.
>
> I was more interested by the discriminator_on_association. But after some
> tests, I find that the table_per_related solution works fine and the
> database is cleaner as I expected.
>
> Thank you guys !
>
> Olaf
>
> --
> 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 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@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
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 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@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Two classes linked to the same backref attribute in a third classe

[Olaf]

Hello,

Sorry for the delay of the answer, I was busy these last days.

I tried every solution and I must honestly say that at the beginning, I was 
not attracted by the table_per_association.py and the table_per_related.py 
solutions because I didn't like the idea of having tables automatically 
generated.

I was more interested by the discriminator_on_association. But after some 
tests, I find that the table_per_related solution works fine and the 
database is cleaner as I expected.

Thank you guys !

Olaf

-- 
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 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@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Disable `session_identity_map` for `_instance_processor`

[Mike Bayer]

On Tue, Nov 21, 2017 at 7:39 AM, Антонио Антуан  wrote:
> Hi guys.
>
> I got this code example:
> https://gist.github.com/aCLr/ff9462b634031ee6bccbead8d913c41f.
>
> Here I make custom `Session` and custom `Query`. As you see, `Session` has
> several binds.
>
> Also, you can see that there are two functions:
> `assert_got_correct_objects_with_remove` and
> `assert_got_cached_objects_without_remove`.
>
> The first checks that we got correct results if `Session.remove` called.
> The second checks, that we got incorrect results if `Session.remove` not
> called.
>
> I understand, that behavior is correct: we don't remove session - so, we got
> same result from "cache-like"
> `sqlalchemy.orm.loading._instance_processor.session_identity_map`.
>
> I want to avoid that mechanism and don't want to use `session_identity_map`
> for different binds. In ideal, bind should be used as part of key for
> `session_identity_map`, but I guess, that it is not possible.
> Another way, acceptable for me: disable this mechanism. But I do not found
> ways to achieve this.
> And the third option: construct instances manually. Looks like I should copy
> code from `loading` module and add that method to `CustomSession`:


there's really no reason at all to use a "ShardedSession" if you have
overlapping primary key spaces from each of your binds.   I'm not sure
if I mentioned this at the beginning of the emails regarding this
project but I hope that I mentioned just using separate Session
objects is vastly simpler for non-intricate sharding cases, such as
where you always know which shard you care about and you don't care
about any of the others for a certain operation. The point of
ShardedSession is so that objects pulled from multiple databases can
be intermingled in the same query and in the same transaction - which
by definition means they have unique primary keys.   If that's not
what you're doing here I don't see what advantage all this complexity
is getting you.

If you're still convinced you need to be using a monolithic
ShardedSession then there needs to be some kind of translation of data
such that the mapper sees unique primary keys across the shards, or
unique classes.

I've tried to think of ways to do this without too much difficulty but
none of them are really worth the complexity and hackiness it would
require.   The absolutely quickest and most well-supported, no hacks
required way would be to properly create a composite primary key on
your classes, where the second column is your shard id:

class A(Base):
__tablename__ = 'a'

id = Column(Integer, primary_key=True)
shard_id = Column(Integer, primary_key=True)

I tried to see if the "shard_id" column can be some kind of expression
that is not a Column on the Table but the mapper() is not set up to
support this unless you mapped the whole class to a select()
construct, which would make for too-complicated SQL, and you'd still
need to intercept this select() using events to put the right shard id
in.  Another is to create a custom column that renders in a special
way, but again you need to create events to intercept it in every case
to put the right shard id in, and/or remove it from things like
insert() statements.

by far your two best solutions are: 1. use separate Session objects
per shard  2. make sure your data actually has shard-specific primary
keys







>
> def instances(self, cursor, __context=None):
> context = __context
> if context is None:
> context = QueryContext(self)
> return self._custom_instances(self, cursor, context)
>
>
>
> def custom_instances(query, cursor, context):
>  """copied from `loading.instances` code with disabled
> `session_identity_map`"""
>
>
>
> The third way is the most ugly and I want to avoid it.
>
> Could you help me with my hard choice and, maybe, suggest any other ways and
> options? :)
>
> Thank you.
>
> --
> 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 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@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
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 because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and 

Re: [sqlalchemy] Re: Required properties of first arg to bulk_insert_mappings

[Skip Montanaro]

Alas, the production database is SQL Server (though from Linux). I use
SQLite for testing. One of the attractions of SQLAlchemy is to stop
worrying about database differences.

I'll get it all figured out eventually. Thanks for the help.

Skip

On Tue, Nov 21, 2017 at 7:16 AM, Simon King  wrote:
> I'm pretty sure that bulk_insert_mappings ends up just calling the
> same code that I suggested.
>
> What database are you using? If it's Postgres, you might be interested
> in 
> http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#psycopg2-batch-mode
> (linked from 
> http://docs.sqlalchemy.org/en/latest/faq/performance.html#i-m-inserting-400-000-rows-with-the-orm-and-it-s-really-slow)
>
> If that still isn't fast enough, I guess you'll need to prepare a data
> file and then use the appropriate DB-specific mechanism to load it. I
> don't think SQLAlchemy has any specific tools for that.
>
> Simon
>
> On Tue, Nov 21, 2017 at 12:15 PM, Skip Montanaro
>  wrote:
>> Thanks. I guess I'm still a bit confused. The problem I've been trying
>> to solve happens to involve inserting records into a table. In my real
>> application, the list of records can contain millions of dicts. The
>> name, "bulk_insert_mappings" sort of sounds like it's going to use
>> BULK INSERT types of statements under the covers (though I realize
>> there's certainly no guarantee of that, and I may well be reading more
>> into the name than I should).
>>
>> Like most database applications, this is got some updating, but most
>> database operations involve working with data already in the database.
>> Is it reasonable to adopt an ORM stance w.r.t. most of the application
>> code, then throw it over for more straightforward Core constructs when
>> data needs to be (in this case, bulk) updated? Or is it expected that
>> any given application should live at one level or the other?
>>
>> Skip
>>
>> --
>> 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 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@googlegroups.com.
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>
> --
> 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 because you are subscribed to a topic in the Google 
> Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit 
> https://groups.google.com/d/topic/sqlalchemy/MwDS0snuZ9s/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to 
> sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
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 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@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Required properties of first arg to bulk_insert_mappings

[Simon King]

I'm pretty sure that bulk_insert_mappings ends up just calling the
same code that I suggested.

What database are you using? If it's Postgres, you might be interested
in 
http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#psycopg2-batch-mode
(linked from 
http://docs.sqlalchemy.org/en/latest/faq/performance.html#i-m-inserting-400-000-rows-with-the-orm-and-it-s-really-slow)

If that still isn't fast enough, I guess you'll need to prepare a data
file and then use the appropriate DB-specific mechanism to load it. I
don't think SQLAlchemy has any specific tools for that.

Simon

On Tue, Nov 21, 2017 at 12:15 PM, Skip Montanaro
 wrote:
> Thanks. I guess I'm still a bit confused. The problem I've been trying
> to solve happens to involve inserting records into a table. In my real
> application, the list of records can contain millions of dicts. The
> name, "bulk_insert_mappings" sort of sounds like it's going to use
> BULK INSERT types of statements under the covers (though I realize
> there's certainly no guarantee of that, and I may well be reading more
> into the name than I should).
>
> Like most database applications, this is got some updating, but most
> database operations involve working with data already in the database.
> Is it reasonable to adopt an ORM stance w.r.t. most of the application
> code, then throw it over for more straightforward Core constructs when
> data needs to be (in this case, bulk) updated? Or is it expected that
> any given application should live at one level or the other?
>
> Skip
>
> --
> 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 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@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
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 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@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Disable `session_identity_map` for `_instance_processor`

[Антонио Антуан]

Hi guys.

I got this code example: 
https://gist.github.com/aCLr/ff9462b634031ee6bccbead8d913c41f.

Here I make custom `Session` and custom `Query`. As you see, `Session` has 
several binds.

Also, you can see that there are two functions: 
`assert_got_correct_objects_with_remove` and 
`assert_got_cached_objects_without_remove`.

The first checks that we got correct results if `Session.remove` called.
The second checks, that we got incorrect results if `Session.remove` not 
called.

I understand, that behavior is correct: we don't remove session - so, we 
got same result from "cache-like" 
`sqlalchemy.orm.loading._instance_processor.session_identity_map`.

I want to avoid that mechanism and don't want to use `session_identity_map` 
for different binds. In ideal, bind should be used as part of key for 
`session_identity_map`, but I guess, that it is not possible.
Another way, acceptable for me: disable this mechanism. But I do not found 
ways to achieve this.
And the third option: construct instances manually. Looks like I should 
copy code from `loading` module and add that method to `CustomSession`:

def instances(self, cursor, __context=None):
context = __context
if context is None:
context = QueryContext(self)
return self._custom_instances(self, cursor, context)



def custom_instances(query, cursor, context):
 """copied from `loading.instances` code with disabled 
`session_identity_map`"""



The third way is the most ugly and I want to avoid it.

Could you help me with my hard choice and, maybe, suggest any other ways 
and options? :)

Thank you.

-- 
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 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@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Required properties of first arg to bulk_insert_mappings

[Simon King]

(TLDR: I think bulk_insert_mappings is the wrong function for you to use)

SQLAlchemy consists of 2 main layers. The Core layer deals with SQL
construction, database dialects, connection pooling and so on. The ORM
is built on top of Core, and is intended for people who want to work
with "mapped classes" (such as your User class). The ORM takes
(typically) a Table instance (like your "mytable" object) and connects
a more traditional-looking Python class to it, so that rather than
explicitly inserting, updating and deleting rows in a table, you
create instances of the mapped class and modify its attributes. Older
versions of SQLAlchemy used to require you to declare the tables and
mapped classes separately
(http://docs.sqlalchemy.org/en/latest/orm/mapping_styles.html#classical-mappings),
but the declarative_base style is much more convenient for people who
are mostly going to be using the ORM.

bulk_insert_mappings is part of the ORM layer, so it assumes you are
working with mapped classes. If you just want to insert dictionaries
into a table, you don't need the ORM at all. Something like this ought
to work (using the definitions from your first message):

mytable = metadata.tables['mytable']
session.execute(mytable.insert(), records)

http://docs.sqlalchemy.org/en/latest/core/tutorial.html#executing-multiple-statements

(Note that the Core docs tend to use engines and connections rather
than sessions, because Session is part of the ORM, but
Session.execute() accepts any Core construct)

Hope that helps,

Simon


On Mon, Nov 20, 2017 at 9:16 PM, Skip Montanaro
 wrote:
> I've narrowed down my problem space a bit. Consider this simple code:
>
> from sqlalchemy import (Integer, String, Column, MetaData, create_engine)
> from sqlalchemy.orm import sessionmaker
> from sqlalchemy.ext.declarative import declarative_base
>
> METADATA = MetaData()
> BASE = declarative_base(metadata=METADATA)
> SESSION = sessionmaker()
>
> class User(BASE):
> __tablename__ = "user"
> first_name = Column(String(32))
> last_name = Column(String(32))
> id = Column(Integer, primary_key=True)
>
> print(type(User))
> print(type(METADATA.tables['user']))
>
> When run, I get this output:
>
> 
> 
>
> The User class is suitable to use as the first arg to
> session.bulk_insert_mapping(), but the object plucked from the METADATA
> tables dict is not. Will I have to always carry around my own references to
> the various subclasses of BASE which I defined to describe my schema? If I
> have metadata and session objects, is there a way to get back that usable
> (or a usable) class?
>
> --
> 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 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@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
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 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@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.