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.

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.

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

[Skip Montanaro]

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.