Re: [sqlalchemy] Difficulties with parallel data insertion into the same table
If the table structure/name is known and expected to be used -- there's not really a good reason to defer creating it . 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? Regards, Markus -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] SQL join between two tables from two databases
I am trying to do a join between two tables, each residing on a separate databases. Would you like to consider another software design option? * Do you know if any special connectors or data source adaptors are available for your database software implementations? * Can one of them be configured as a data source for the other database so that you would only need to deal with a single connection for the desired query? Regards, Markus -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
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 specifying the meta-data? Most SqlAlchemy implementations will only specify this once in the application. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Difficulties with parallel data insertion into the same table
How are you currently specifying the meta-data? Should the Python class be sufficient for the definition of a table structure? Will the mapping interface work also without tables that were created by other SQL scripts before? Regards, Markus -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Difficulties with parallel data insertion into the same table
SF Markus Elfring elfr...@users.sourceforge.net wrote: There would still be a race condition within the Postgres internal functions. Are there any chances that this database software implementation will become robust and safe against the discussed race condition? I wonder why you’re asking of the SQLAlchemy list about a specific developmental goal of the Postgresql project? Wouldn’t you ask them about 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
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 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
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 be slated as a bug to be worked on, but you can ask on one of the postgres lists (http://www.postgresql.org/list/). As others noted, dynamic table creation within an application's runtime isn't really a standard approach -- it usually only happens in specific situations (multi-tenancy platforms, admin interfaces, automated plugin installations [ie, wordpress, drupal, etc]). Having multiple processes attempt to dynamically create tables in a high-concurrency situation is even less standard. The status-quo is to have some sort of setup script, or a setup routine that happens before forking threads (or whatever). If the table structure/name is known and expected to be used -- there's not really a good reason to defer creating it . -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
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; then that is imported and metadata associated to the engine. Will the mapping interface work also without tables that were created by other SQL scripts before? SqlAlchemy is one of the very few ORMs that does not impose any design requirements. Classes/Tables can be easily mapped to any legacy scenario. The only issue that arises is if there are complex relationships/joins -- then special mappers may be needed. There is an extended section in the docs for that, but it is a rare and advanced topic. There are also 3rd party packages like sqlacodegen (https://pypi.python.org/pypi/sqlacodegen) that can query a legacy database for you and generate the corresponding SqlAlchemy python classes. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Difficulties with parallel data insertion into the same table
Should the Python class be sufficient for the definition of a table structure? If you're using the declarative syntax, yes. Thanks for your acknowledgement. It's common to have a `models.py` file that simply defines the classes in one place; then that is imported and metadata associated to the engine. How often do you need to fill these data structures in a concurrent way? Does parallel table creation become more interesting then? Regards, Markus -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Difficulties with parallel data insertion into the same table
I wonder why you’re asking of the SQLAlchemy list about a specific developmental goal of the Postgresql project? I hoped that some more corresponding experiences could already be shared here. Wouldn’t you ask them about this? That might follow ... How should I add the parameter IF NOT EXISTS to Python classes in the meantime eventually? Regards, Markus -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] SQL join between two tables from two databases
Simon,
I was able to get the select join working with the following:
select =
SELECT hostname, sha256
FROM hosts
JOIN environments ON hosts.environment_id = environments
.environmentID
JOIN zones ON environments.zone_id = zones.ZoneID
JOIN %s.ssh_host_keys USING (hostname)
WHERE ZoneName = %s
% (self.config['db']['private']['database'], zone)
rp = self.session.execute(select)
It might not be the best, but it works. Luckily, I only need to read one
table from the other database.
On 21 January 2015 at 12:31, Brian Glogower bglogo...@ifwe.co wrote:
Simon, thanks for your response. Let me wrap my head around this and try
it out.
Brian
On 21 January 2015 at 04:59, Simon King si...@simonking.org.uk wrote:
You don't need to convert it to a Table object, but you probably do
need to add 'schema': 'whatever' to the __table_args__ dictionary.
In answer to your second question, I very much doubt you can use
query.join() with 2 DB connections. query.join() simply adds an SQL
JOIN clause to the query that is eventually sent to the database -
there's no way of making that work with 2 separate connections.
As an alternative, I think it should be possible to put the tables
that exist in a separate schema in a separate SQLAlchemy MetaData
(they'd need to use a separate declarative Base class). The MetaData
can hold the default schema for the tables, and I *think* you should
be able to use tables from different MetaData in query.join(). (I
haven't tested this though).
http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative/basic_use.html#accessing-the-metadata
http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html#sqlalchemy.schema.MetaData
http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html#metadata-describing
Hope that helps,
Simon
On Wed, Jan 21, 2015 at 9:09 AM, Brian Glogower bglogo...@ifwe.co
wrote:
Hi Michael,
Do I need to redefined mapped class ssh_host_keys as a Table object?
ssh_host_keys = Table('ssh_host_keys', metadata,
Column('hostname', VARCHAR(30), primary_key=True),
Column('pub', VARCHAR(1600)),
Column('sha256', CHAR(64)),
Column('priv', VARCHAR(2000)),
schema='keys',
mysql_engine='InnoDB'
)
Do I need to convert mapped class 'Host' to a Table object as well? I
would
prefer not to touch this class, since it is part of a separate module,
but
if needed, it is possible.
class Host(Base):
__tablename__ = 'hosts'
__table_args__ = {'mysql_engine': 'InnoDB'}
id = Column(u'HostID', INTEGER(), primary_key=True)
hostname = Column(String(length=30))
Can you please give an example how to use schema with a query.join(),
for my
scenario (two sessions, one for each DB connection)?
Thanks,
Brian
On 20 January 2015 at 16:12, Michael Bayer mike...@zzzcomputing.com
wrote:
Jonathan Vanasco jonat...@findmeon.com wrote:
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_statement(
... text(SELECT * FROM users where
name=:name)).
\
... params(name='ed').all()
[User(name='ed', fullname='Ed Jones', password='f8s7ccs')]
So you should be able to do something like:
query = Session.query(Host)\
.from_statement(
sqlaclhemy.text(SELECT hostname, sha256 FROM
DATABASE1.hosts
LEFT JOIN DATABASE2.ssh_host_keys ssh_host_keys ON
ssh_host_keys.hostname ==
hosts.hostname)
)
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().
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To
[sqlalchemy] Handling of differences between a table and its mapped class
Hello, What will (or should) happen if the column attributes which are specified by a class that is derived from declarative_base() differ (e. g. an other default value) from the settings of an existing database table? Is it occasionally appropriate to use different column properties by the mapped Python classes? Regards, Markus -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Difficulties with parallel data insertion into the same table
I would expect that database implementations will provide functionality for parallel updates including concurrent creation of each table. What would you expect a database to do if it receives 2 CREATE TABLE my_table(...) instructions simultaneously? This depends on the passed parameters. What if the table definitions are different? I am going to pass the same settings for the application I am developing at the moment. One of the instructions would *have* to fail. Not in every case. The parameter IF NOT EXISTS could be passed to the SQL statement CREATE TABLE. * Is this setting already used by the class library SQLAlchemy 0.9.8-78.1? * Does it really work in the current praxis? Regards, Markus -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Difficulties with parallel data insertion into the same table
The parameter IF NOT EXISTS could be passed to the SQL statement CREATE TABLE. * Is this setting already used by the class library SQLAlchemy 0.9.8-78.1? From a quick scan of the docs it appears not. How can parameter additions be achieved for this software? Are you asking if IF NOT EXISTS will work in a high-concurrency context? Yes. How many database implementations support the simultaneous table creation according to the rules from transaction management? Regards, Markus -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Handling of differences between a table and its mapped class
On Fri, Jan 23, 2015 at 9:11 AM, SF Markus Elfring elfr...@users.sourceforge.net wrote: Hello, What will (or should) happen if the column attributes which are specified by a class that is derived from declarative_base() differ (e. g. an other default value) from the settings of an existing database table? I assume you are talking about the server_default property (since the default property is not part of the database table definition). server_default is only passed to the database at CREATE TABLE time, so if you don't use SQLAlchemy to create the tables (or you ALTER the tables in the database after they've been created), the new default will be used, but SQLAlchemy won't care. Is it occasionally appropriate to use different column properties by the mapped Python classes? Maybe? *shrug* Simon -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Firebird SQL 'rdb$get_context eq' equivalent in PostGreSQL
Hi,
Just for the archive.
Got an answer on stackoverflow:
http://stackoverflow.com/questions/28047911/how-to-set-some-context-variable-for-a-user-connection/28061244#28061244
On 1/19/2015 16:59, Werner wrote:
Hi,
I like to move to eventually support PGSQL 9.3+ as an alternative engine.
With Firebird SQL I am using the following to set/get the language
used by a connection/user.
rdb$get_context('USER_SESSION', 'LANGUAGE_ID') -
http://www.firebirdsql.org/refdocs/langrefupd20-get-context.html
googling and looking at the PG documentation I can't find something
similar, probably just using the wrong terminology:-( .
Anyone can give me a tip of where to look?
Thanks
Werner
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Sharding, query_chooser and lazy select
Thanks for the tip. It works fine when I load an instance from the database but I have another question : is it possible to specify load_options on relationship because in the case of an object creation, the query used to load the relationship didn't have my ShardId attached Le mercredi 21 janvier 2015 15:53:53 UTC+1, Michael Bayer a écrit : Julien Meyer julien...@gmail.com javascript: wrote: Hello, I'm using sharding and it works fine except for the relationship. In my mapper, i define a relationship with lazy mode set to select. When I want to access to this relationship, my query_chooser implementation is called and I want to retrieve the parent instance but I don't know if it's possible and if yes, how to do it . I need to access to the parent instance because the shard_id to use is stored into it. tricky, not much support for that. the only thing passed from parent to the actual query are the “load options”. If you made your own MapperOption like this: session.query(Parent).options(ShardId(“some shard)) that ShardId option would be applied to the lazy loader query as well. maybe you could subclass Query so that the ShardId option is applied automatically. the option itself would be like: from sqlalchemy.orm.interfaces import MapperOption class ShardId(MapperOption): propagate_to_loaders = True # the Query will send it to lazy loaders also def __init__(self, shard_id): self.shard_id = shard_id def process_query(self, query): Apply a modification to the given :class:`.Query`.” # apply criteria here if needed def process_query_conditionally(self, query): # same thing, but called only in a lazy loader might be a nifty way to do the sharding overall, if it works out maybe we should add this to the docs. Thanks in advance -- 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 javascript:. To post to this group, send email to sqlal...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Difficulties with parallel data insertion into the same table
On Fri, Jan 23, 2015 at 12:17 PM, SF Markus Elfring elfr...@users.sourceforge.net wrote: The parameter IF NOT EXISTS could be passed to the SQL statement CREATE TABLE. * Is this setting already used by the class library SQLAlchemy 0.9.8-78.1? From a quick scan of the docs it appears not. How can parameter additions be achieved for this software? The general mechanism for generating SQL that isn't directly supported by SQLAlchemy is to use the compiler module: http://docs.sqlalchemy.org/en/rel_0_9/core/compiler.html I guess it's the CreateTable object you'd need to adapt: http://docs.sqlalchemy.org/en/rel_0_9/core/ddl.html Although this sounds to me like a reasonable flag to add to core (it's supported by multiple database implementations), so perhaps if you added support for it directly into SQLAlchemy, Mike might accept that patch. Are you asking if IF NOT EXISTS will work in a high-concurrency context? Yes. How many database implementations support the simultaneous table creation according to the rules from transaction management? No idea. You'd need to ask the makers of all the databases you are interested in. Simon -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
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 the link that Mike shared above. Have you tried using savepoints? SqlAlchemy handles them within the nested transaction concept. I'm not sure how Postgres would handle race conditions of table creation within a savepoint, but it might get you past this issue. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
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. Thanks for your feedback. There would still be a race condition within the Postgres internal functions. Are there any chances that this database software implementation will become robust and safe against the discussed race condition? Have you tried using savepoints? I am going to use a serial database preparation step instead for my application so that the corresponding tables will be explicitly deleted and created by a small SQL script. Regards, Markus -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[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/nested transaction and then immediately flush. you'll be able to avoid most errors like that. i use postgres, and I found the best option FOR ME was to use Python+SqlAlchemy to query and track the duplicates -- and then to generate a text file in the postgres COPY FROM format for bulk import. I don't know if other dbs have a similar function. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
