Re: [sqlalchemy] passing parameters to subquery in mapped select
its a little awkward but if you use bindparam() in the inner select, query.params() can access those parameters just fine, you'd just need to use it in all cases. there's some related example of doing this with a relationship at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/GlobalFilter . On Oct 13, 2011, at 6:39 PM, Burak Arslan wrote: > hi, > > is there a way to pass a parameter to a subquery inside a select mapped > to a class? the generated query looks like this: > > select * from ( >select distinct on (some_table.id) some_table.id, ... from > some_table where some_condition > ) as v join ... > > the outer select is mapped to a class, but when i try to query on that > class, the conditions are naturally applied to the outer query. because > of distinct in the subquery, i get random results. i need to put > conditions on the inner query. (and yes, I do need that distinct in > there. i can't put it to the outer query because that messes with order > by. i can't use group by because not all columns respond to aggregates.) > > should I just drop the orm for this specific case? > > thanks, > burak > > -- > 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 > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] passing parameters to subquery in mapped select
hi, is there a way to pass a parameter to a subquery inside a select mapped to a class? the generated query looks like this: select * from ( select distinct on (some_table.id) some_table.id, ... from some_table where some_condition ) as v join ... the outer select is mapped to a class, but when i try to query on that class, the conditions are naturally applied to the outer query. because of distinct in the subquery, i get random results. i need to put conditions on the inner query. (and yes, I do need that distinct in there. i can't put it to the outer query because that messes with order by. i can't use group by because not all columns respond to aggregates.) should I just drop the orm for this specific case? thanks, burak -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: DateTimeType
Thanks Mike, you're right it's python-sybase. I'll report the bug. On Oct 13, 2:18 pm, Michael Bayer wrote: > On Oct 13, 2011, at 4:30 PM, jn wrote: > > > Using: > > SQLAlchemy-0.7.2 > > python_sybase-0.40pre1 > > Sybase ASE 12.5.3 > > > I have column: > > edate = Column(DateTime, nullable=False, quote=False) > > > This is info printed using the fetched row: > > type: > > edate: Jan 28 2009 12:00AM > > edate year: 2009 > > edate month: 0 > > > Why is the month zero-based? (I can't tell where the origin of this > > is, since mxDateTime & sybase are 1-based) > > It's not SQLAlchemy - we take what python-sybase gives us and send it > straight on. It should be sending back a Python datetime object which does > not accept "0" for a month, not sure how you're getting a zero or what type > of object you're getting. > > The sybase DBAPIs are *extemely* buggy and have a very low userbase - this is > largely the product of Sybase itself. Expect to hit a lot of gotchas. > > For this issue I'd create a python-sybase only (no SQLAlchemy) test case, > confirm you get the same broken date back under the same conditions, and > report a bug to the python-sybase authors. Or otherwise illustrate a > properly working python-sybase script and exactly what SQLAlchemy usage is > not returning that result - the above snippet is not specific enough for me > to take any action upon. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] DateTimeType
On Oct 13, 2011, at 4:30 PM, jn wrote: > Using: > SQLAlchemy-0.7.2 > python_sybase-0.40pre1 > Sybase ASE 12.5.3 > > I have column: > edate = Column(DateTime, nullable=False, quote=False) > > This is info printed using the fetched row: > type: > edate: Jan 28 2009 12:00AM > edate year: 2009 > edate month: 0 > > Why is the month zero-based? (I can't tell where the origin of this > is, since mxDateTime & sybase are 1-based) It's not SQLAlchemy - we take what python-sybase gives us and send it straight on.It should be sending back a Python datetime object which does not accept "0" for a month, not sure how you're getting a zero or what type of object you're getting. The sybase DBAPIs are *extemely* buggy and have a very low userbase - this is largely the product of Sybase itself. Expect to hit a lot of gotchas. For this issue I'd create a python-sybase only (no SQLAlchemy) test case, confirm you get the same broken date back under the same conditions, and report a bug to the python-sybase authors. Or otherwise illustrate a properly working python-sybase script and exactly what SQLAlchemy usage is not returning that result - the above snippet is not specific enough for me to take any action upon. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] MSSQL negative SMALLINT returned by SA as weird number
makes sense.. I'm using pydobc. I do the test you suggested.. thanks! -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/4XlySuLtQxUJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] DateTimeType
Using: SQLAlchemy-0.7.2 python_sybase-0.40pre1 Sybase ASE 12.5.3 I have column: edate = Column(DateTime, nullable=False, quote=False) This is info printed using the fetched row: type: edate: Jan 28 2009 12:00AM edate year: 2009 edate month: 0 Why is the month zero-based? (I can't tell where the origin of this is, since mxDateTime & sybase are 1-based) I want to be able to convert the DateTimeType object to datetime as follows: datetime.datetime(edate.year, edate.month, edate.day, edate.hour, edate.minute, edate.second) i.e. without having to fix anything prior to that. Thanks. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: How to Query.selecting_from(subquery).join()
Dear Mr. Bayer, I'm impressed by such a fast response and fix, thank you! On 13 Ott, 16:55, Michael Bayer wrote: > hi Daniele - > > You're in luck because I saw you ask this on IRC yesterday. Using a > non-mapped selectable as the "thing to select from" in Query wasn't > supported, but I considered this a bug which has been fixed: > > http://www.sqlalchemy.org/trac/ticket/2298 > > If you get the latest tip > athttp://hg.sqlalchemy.org/sqlalchemy/archive/default.tar.gz, it should work > now. > > On Oct 13, 2011, at 10:42 AM, Daniele wrote: > > > > > > > > > What is the proper way to use a subquery as the FROM clause, while > > being able to use the Query.join() method? > > > Here is an example of what I mean:http://pastebin.com/RUktuZZm > > > The docs > > athttp://www.sqlalchemy.org/docs/orm/query.html#sqlalchemy.orm.query.Qu... > > state: "Eager JOIN generation within the query is disabled.". > > If I understand it correctly, is there a specific reason for this not > > being enabled by default, or possible at all? > > > Many thanks. > > > -- > > 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 > > sqlalchemy+unsubscr...@googlegroups.com. > > For more options, visit this group > > athttp://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] How to Query.selecting_from(subquery).join()
hi Daniele - You're in luck because I saw you ask this on IRC yesterday. Using a non-mapped selectable as the "thing to select from" in Query wasn't supported, but I considered this a bug which has been fixed: http://www.sqlalchemy.org/trac/ticket/2298 If you get the latest tip at http://hg.sqlalchemy.org/sqlalchemy/archive/default.tar.gz, it should work now. On Oct 13, 2011, at 10:42 AM, Daniele wrote: > What is the proper way to use a subquery as the FROM clause, while > being able to use the Query.join() method? > > Here is an example of what I mean: http://pastebin.com/RUktuZZm > > The docs at > http://www.sqlalchemy.org/docs/orm/query.html#sqlalchemy.orm.query.Query.subquery > state: "Eager JOIN generation within the query is disabled.". > If I understand it correctly, is there a specific reason for this not > being enabled by default, or possible at all? > > Many thanks. > > -- > 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 > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] How to Query.selecting_from(subquery).join()
What is the proper way to use a subquery as the FROM clause, while being able to use the Query.join() method? Here is an example of what I mean: http://pastebin.com/RUktuZZm The docs at http://www.sqlalchemy.org/docs/orm/query.html#sqlalchemy.orm.query.Query.subquery state: "Eager JOIN generation within the query is disabled.". If I understand it correctly, is there a specific reason for this not being enabled by default, or possible at all? Many thanks. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Copying one table from one database to the other
On Oct 13, 2011, at 6:38 AM, Eduardo wrote: > Hi, > I am trying to copy a table (or more of them) from one database to the > other. I found somewhere in internet a code snippet that I have > slightly modified. I read sequentially rows from the existing table > and write them to the new one. The code worked for smaller table (up > to several thousand rows). However when working with the table with > more than million rows, the session commit failed more frequently as > the number of the copied rows grows. The rows to be committed in the > previous loop are still stored in the session and when the session > filled with the rows from next sequence they are still in the session > although they are supposed to have bee already committed. > Is there any problem with the code or it is dependent upon the RDBMS > (Postgres) configuration. > Is there any other way to copy a table from one database to the other > (except of using pg_dump)? there's nothing obviously wrong with the code and its not the case that the rows remain in the Session after commit; I don't see them being placed in any kind of persistent collection and the Session does not maintain strong references to objects with no pending changes. A check against len(gc.get_objects()) would confirm whether or not the total size of objects in memory is growing. You're also emitting SELECTs with LIMIT/OFFSET which also will ensure that psycopg2 isn't buffering the entire result set, even though LIMIT/OFFSET gets very slow as you move deeper into the table (I typically use this recipe instead: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowedRangeQuery) That said, an operation like this is strictly table->table, you should be using the Core for this, exchanging simple tuples from the result set of one statement to a single parameter list for the other which will insert the full span of rows in one go (i.e. http://www.sqlalchemy.org/docs/core/tutorial.html#executing-multiple-statements). The ORM code here is unnecessary and complex, and makes more a much slower operation. > Thanks > ___ > > > def pull_data(from_db, to_db, tables,slicesize): >smeta,source, sengine = create_meta_session_engine(from_db) >smeta = MetaData(bind=sengine) >dmeta,destination, dengine = create_meta_session_engine(to_db) >for table_name in tables: >table = Table(table_name, smeta, autoload=True) >table.metadata.create_all(dengine) >NewRecord = quick_mapper(table) >columns = table.columns.keys() >rowquery=source.query(table) >count=rowquery.count() >loop=0 >while count-(loop*slicesize)>slicesize: > > oneslice=source.query(table).slice(slicesize*loop,slicesize*(loop > +1)).all() >for record in oneslice: >data = dict([(str(column), getattr(record, column)) > for column in columns]) >destination.add(NewRecord(**data)) >try: >destination.commit() >loop+=1 >except: >destination.rollback() >time.sleep(25) >print loop >lastpending=True >lastslice=source.query(table).slice(slicesize*loop,count > +1).all() >lastloop=0 >while lastpending: >for record in lastslice: >data = dict([(str(column), getattr(record, column)) > for column in columns]) >destination.add(NewRecord(**data)) >try: >destination.commit() >lastpending=False >except: >destination.roolback() >time.sleep(25) >lastloop+=1 >print lastloop >source.close() >destination.close() > def quick_mapper(table): >Base = declarative_base() >class GenericMapper(Base): >__table__ = table >return GenericMapper > ___ > > -- > 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 > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] MSSQL negative SMALLINT returned by SA as weird number
this is something occurring within the DBAPI you're using, i.e. pyodbc, pymssql, etc. you'd need to compose a simple test case using only the DBAPI (else the DBAPI authors will suspect its on SQLAlchemy's side) and report it to them. if a plain DBAPI test does not reproduce the problem, send it over here and we can compare the plain DBAPI case to the SQLAlchemy case. unlikely here since SQLA doesn't do anything with ints coming back from the database. On Oct 13, 2011, at 3:27 AM, Matt Bodman wrote: > I have a SMALLINT column in MSSQL. The value of the column is -2 > > SQLAlchemy also has the column as SMALLINT but the value is translated as > 4294967294 > > I can't seem to correct this and I haven't found anything on SA and negative > numbers. Any help would be really great, thanks. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/sqlalchemy/-/F49ec3O3IGkJ. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Copying one table from one database to the other
Hi, I am trying to copy a table (or more of them) from one database to the other. I found somewhere in internet a code snippet that I have slightly modified. I read sequentially rows from the existing table and write them to the new one. The code worked for smaller table (up to several thousand rows). However when working with the table with more than million rows, the session commit failed more frequently as the number of the copied rows grows. The rows to be committed in the previous loop are still stored in the session and when the session filled with the rows from next sequence they are still in the session although they are supposed to have bee already committed. Is there any problem with the code or it is dependent upon the RDBMS (Postgres) configuration. Is there any other way to copy a table from one database to the other (except of using pg_dump)? Thanks ___ def pull_data(from_db, to_db, tables,slicesize): smeta,source, sengine = create_meta_session_engine(from_db) smeta = MetaData(bind=sengine) dmeta,destination, dengine = create_meta_session_engine(to_db) for table_name in tables: table = Table(table_name, smeta, autoload=True) table.metadata.create_all(dengine) NewRecord = quick_mapper(table) columns = table.columns.keys() rowquery=source.query(table) count=rowquery.count() loop=0 while count-(loop*slicesize)>slicesize: oneslice=source.query(table).slice(slicesize*loop,slicesize*(loop +1)).all() for record in oneslice: data = dict([(str(column), getattr(record, column)) for column in columns]) destination.add(NewRecord(**data)) try: destination.commit() loop+=1 except: destination.rollback() time.sleep(25) print loop lastpending=True lastslice=source.query(table).slice(slicesize*loop,count +1).all() lastloop=0 while lastpending: for record in lastslice: data = dict([(str(column), getattr(record, column)) for column in columns]) destination.add(NewRecord(**data)) try: destination.commit() lastpending=False except: destination.roolback() time.sleep(25) lastloop+=1 print lastloop source.close() destination.close() def quick_mapper(table): Base = declarative_base() class GenericMapper(Base): __table__ = table return GenericMapper ___ -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] MSSQL negative SMALLINT returned by SA as weird number
I have a SMALLINT column in MSSQL. The value of the column is -2 SQLAlchemy also has the column as SMALLINT but the value is translated as 4294967294 I can't seem to correct this and I haven't found anything on SA and negative numbers. Any help would be really great, thanks. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/F49ec3O3IGkJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
