Re: [sqlalchemy] Re: Required properties of first arg to bulk_insert_mappings
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 <si...@simonking.org.uk> 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 > <skip.montan...@gmail.com> 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.
[sqlalchemy] Re: Required properties of first arg to bulk_insert_mappings
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] Required properties of first arg to bulk_insert_mappings
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?
--
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: Buildbot
Rick Huh? I thought this was something you wantedI guess I Rick misunderstood the thread. Rick I don't have time to do this by myself. If someone wants to pick Rick it up and run with it, I'll be happy to work with you and to host Rick the buildslave here, or I'm OK with just letting the thing die, Rick too. I've got an SQLalchemy build slave running on my Mac at hom, but I've never been able to get a workable combination of auxiliary database packages. I eventually got it mostly working with PostgreSQL but there is still some fishy sqlite dependency that I can't resolve. If someone would like to help me figure things out I'd be most appreciative. Skip Montanaro --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Buildbot
Rick a) using the included pysqlite in Python 2.5+ and issues with Rick that Rick b) conflict with the sqlite library that ships with OSX Rick and out of those, I would bet (b). The sqlite that ships for Rick Coredata with OSX Tiger is old, and I think that SA doesn't like Rick it. I had the same issue with an older version of FC Linux that Rick came with a dependency on sqlite that I couldn't break. Fixed Rick things by use ./configure with --prefix to re locate the binary. Yes, SA doesn't like the sqlite that ships with OSX and core Python doesn't seem to like the latest sqlite. I eventually switched to PostgreSQL but there's still something in there that SA doesn't like. Skip --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy test suite - no success/failure exit code
Michael go into python and type: Michael from pysqlite2 import dbapi2 Michael dbapi2.sqlite_version Well, that failed: $ /tmp/python-buildbot/local/bin/python Python 2.6a0 (trunk:54421, Mar 17 2007, 11:17:17) [GCC 4.0.0 (Apple Computer, Inc. build 5026)] on darwin Type help, copyright, credits or license for more information. from pysqlite2 import dbapi2 Traceback (most recent call last): File stdin, line 1, in module ImportError: No module named pysqlite2 but pysqlite3 is there: $ /tmp/python-buildbot/local/bin/python Python 2.6a0 (trunk:54421, Mar 17 2007, 11:17:17) [GCC 4.0.0 (Apple Computer, Inc. build 5026)] on darwin Type help, copyright, credits or license for more information. from pysqlite3 import dbapi2 Traceback (most recent call last): File stdin, line 1, in module ImportError: No module named pysqlite3 from sqlite3 import dbapi2 dbapi2.sqlite_version '3.1.3' Does that help? Forgive me, I really have no idea how much of sqlite comes with Python (is it just pysqlite?) or what it uses if there is another version installed on the machine. Skip --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy test suite - no success/failure exit code
Michael all tests pass on Mac OSX with the latest sqlite (as well as Michael postgres, mysql). Hmmm... Then I wonder what's different about the buildbot setup and a normal interactive run. Michael solaris, not so sure, ill have a look later. There's no particular need. I was just hoping to get a different data point to compare my buildbot results with. You provided that with your Mac OSX results. Skip --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy test suite - no success/failure exit code
Michael geez, what version of sqlite / pysqlite / python is running Michael there ? tons of those errors ive never seen before. That will have to wait for me to get home. Probably not until tomorrow at the earliest. I'll let you know what I figure out. Perhaps I can tweak the buildslave script to emit the relevant version information. Skip --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy test suite - no success/failure exit code
Michael ah, in fact one failure youre having, the testcast failure, Michael is indicative of running against a very old sqlite. Can you tell me what version I'm using other than very old? Here's what I see: $ otool -L lib-dynload/_sqlite3.so lib-dynload/_sqlite3.so: /usr/lib/libsqlite3.0.dylib (compatibility version 9.0.0, current version 9.6.0) /usr/lib/libmx.A.dylib (compatibility version 1.0.0, current version 93.0.0) /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 88.1.8) and the stuff in /usr/lib looks fairly new: $ ls -Ll libsqlite3.* -rwxr-xr-x 1 root wheel 284508 Mar 14 20:04 libsqlite3.0.8.6.dylib -rwxr-xr-x 1 root wheel 284508 Mar 14 20:04 libsqlite3.0.dylib -rwxr-xr-x 1 root wheel 284508 Mar 14 20:04 libsqlite3.dylib I suppose it's possible that I'm picking up another version. I have an old version of Darwin Ports on the machine and Python comes with sqlite of some version. Skip --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy test suite - no success/failure exit code
Michael OK this was something small, the alltests.py scripts needed Michael to call testbase's main() function and not its runTests() Michael method, so that the exit code is propigated. rev 2414. Excellent. Trying a test run now... Skip --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Announcing Elixir!
Jonathan Anyway, we might just end up keeping both... I say punt and pick one. Even it it's a coin flip. Skip --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy 0.3.2 released
Mike yeah, still waiting for someone to show me some easy Mike non-commercial library that can create a decent PDF out of either Mike markdown, or HTML, or whatever... Why not load the HTML into a web browser, print it, but select Save as PDF instead of completing the print? Are there distribution restrictions on such output? Skip --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
