Required properties of first arg to bulk_insert_mappings

[Skip Montanaro]

I'm trying to insert a list of dictionaries into a table using bulk_insert_mappings(). When I call it, I get this error:

AttributeError: 'Table' object has no attribute 'mapper'

I'm having trouble (in general) figuring out which of the various ways of specifying my table(s) are appropriate. I've been doing this (paraphrasing because I can't copy out of my work env):

    import sqlalchemy as sa

    import sa.orm

    SESSION = sa.orm.sessionmaker()

    engine = sa.create_engine(...)

    metadata = sa.MetaData(engine)

    session = SESSION(bind=engine)

    mytable = sa.Table("mytable", metadata, sa.Column(...), ...)

The session object is created within a contextmanager as described here:

http://docs.sqlalchemy.org/en/latest/orm/session_basics.html

It's within the scope of that contextmanager (a couple levels down in the function call stack) that the call is made:

    mytable = metadata.tables['mytable']

    session.bulk_insert_mappings(mytable, records)

I clearly must not understand something about the parameter properties required of that function. The docs state:

mapper – a mapped class, or the actual Mapper object, representing the single kind of object represented within the mapping list.

I thought I had assembled all the bits (engine, session, bound metadata). What's not mapped about my Table object?

[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:

<class 'sqlalchemy.ext.declarative.api.DeclarativeMeta'>

<class 'sqlalchemy.sql.schema.Table'>

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?

[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

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

[Skip Montanaro]

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

[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

[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

> 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+...@googlegroups.com.