Re: [sqlalchemy] subquery as column
Yes,I know, so simple... but it didn't work for me as far I didn't set the label for the internal select. I try it many times, but always without it; I didn't know it was mandatory. Thank you. first_id_row = s.query(Row.id_row).\ filter(Row.id_head == Head.id_head).\ order_by(Row.id_row).\ limit(1).label(first_id_row) -- 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/-/sQWv1yGoC8gJ. 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] ManyToMany query
Dear all,
I've the following tables:
site_table = Table('site', metadata,
Column('id', types.Integer, primary_key=True),
Column('name', types.Unicode(15), nullable=False))
chain_table = Table('chain', metadata,
Column('id', types.Integer, primary_key=True),
Column('letter', types.Unicode(1), nullable=False),
Column('ec_number', types.Unicode(50), nullable=True))
sites_chains_table = Table('sites_chains', metadata,
Column('site_id', types.Integer,
ForeignKey('site.id'),primary_key=True),
Column('chain_id', types.Integer,ForeignKey('chain.id'),
primary_key=True))
and the following mappers:
mapper(Site, site_table,
properties={'chain': relationship(Chain, backref='site',
secondary=sites_chains_table), })
mapper(Chain, chain_table)
Which query I've to write to count all site where
chain.ec_number==something using its relation.
I tried to do something like:
count =
Session.query(func.count(Site.id)).filter(Chain.ec_number==ec_number).filter(Site.chain.any(id=Chain.id)).all()
but the result isn't real.
--
---
(o_
(o_//\ Coltivate Linux che tanto Windows si pianta da solo.
(/)_ V_/_
+--+
| ENRICO MORELLI | email: more...@cerm.unifi.it |
| * * * *| phone: +39 055 4574269 |
| University of Florence| fax : +39 055 4574253 |
| CERM - via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY|
+--+
--
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] ManyToMany query
On Jul 24, 2012, at 11:25 AM, Enrico Morelli wrote:
Dear all,
I've the following tables:
site_table = Table('site', metadata,
Column('id', types.Integer, primary_key=True),
Column('name', types.Unicode(15), nullable=False))
chain_table = Table('chain', metadata,
Column('id', types.Integer, primary_key=True),
Column('letter', types.Unicode(1), nullable=False),
Column('ec_number', types.Unicode(50), nullable=True))
sites_chains_table = Table('sites_chains', metadata,
Column('site_id', types.Integer,
ForeignKey('site.id'),primary_key=True),
Column('chain_id', types.Integer,ForeignKey('chain.id'),
primary_key=True))
and the following mappers:
mapper(Site, site_table,
properties={'chain': relationship(Chain, backref='site',
secondary=sites_chains_table), })
mapper(Chain, chain_table)
Which query I've to write to count all site where
chain.ec_number==something using its relation.
query(Site).join(Site.chain).filter(Chain.ec_number==something).count()
see this recent stackoverflow answer:
http://stackoverflow.com/a/11568381/34549
I tried to do something like:
count =
Session.query(func.count(Site.id)).filter(Chain.ec_number==ec_number).filter(Site.chain.any(id=Chain.id)).all()
but the result isn't real.
--
---
(o_
(o_//\ Coltivate Linux che tanto Windows si pianta da solo.
(/)_ V_/_
+--+
| ENRICO MORELLI | email: more...@cerm.unifi.it |
| * * * *| phone: +39 055 4574269 |
| University of Florence| fax : +39 055 4574253 |
| CERM - via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY|
+--+
--
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] Performance Mystery
Hi All This is addressed to anyone who may be able to shed some light on this strange behaviour; I'm running MySQL on Windows 2003 Server (Sorry) and have a table that has ~2M rows of sales data in it. Knowing the type of BI queries I would want to be doing, I have added indexes where I feel they would help. I tested the queries I wanted to perform manually by directly entering the SQL into the MySQL console. Results were fine. (Tested by putting raw SQL into MySQL Workbench on the same server as the database) I then simply moved the logic into my code, and created very simple queries on tables using the table.select() syntax, and adding a few basic group_by and 'where' additions. All works perfectly, and I get exactly the same results as my manual query BUT... the manual query returns the results in 3 seconds, and the programatic query returns the results in 200+ seconds!! This is the round trip time from .execute() 'til the next line of code being hit. i.e. I have fetched nothing yet. This is what I have done; I added an audit line in my code to show the final SQL that was being emitted, then cut and paste that into MySQL Workbench and executed it. 3 seconds was the result, with exactly the same actual rows being returned. The Python code is running on the same server as the MySQL Console. I have created the same query manually on the Python console, using the sqlalchemy constructs like query=table.select() etc and run it there - 180+ seconds again - it's I've basically eliminated by code. I'm using 'mysqlconnector' which I have had no problems in the past, and it's not like I'm hammering it with 1000's of queries - just 1 query that will return around 12 rows (having processed several 1000 and then GROUPed them). I can see the query sitting there in Workbench, taking minutes to complete. I've tried adding index hints just in case.. Made no difference, as it should have been using that index anyway.. When creating the SQLAlchemy test query, (and indeed in my real code) I used all defaults for engine, metadata etc with no additional settings at all. Finally, to add insult to injury, I can execute the identical manual query DURING the slow queries execution, and still get it back 3 seconds.. whilst the slow one clunks along and spits out the same result minutes later. In the SQL below, invoice and customer are VARCHAR columns, whilst date is DATE and TotalEx is FLOAT I have restarted the MySQL Service (several times) It's *like* the sqlalchemy query is not allowed to use any of the indexes, and is trawling through all the data.. Are there permissions or something on using an index that I don't know about? (Both queries are being executed by the same user - root) It is EXACTLY the same query in all ways, and in fact is an extremely simple query - as below; SELECT sum(`sales_data`.`TotalEx`) AS totalSales, date, invoice, customer FROM sales_data USE INDEX(idx_date) WHERE date =2011-12-01 AND date =2012-07-31 GROUP BY customer, invoice Any clues? My project just fell in a hole! Thanks an advance for any pointers. Warwick -- 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] Performance Mystery
One difference that exists here is the usage of bound parameters, which I'm assuming you're not using when you run the query in the console. What happens if you run a test script with straight mysqlconnector ? Try with and without the bound parameters. On Jul 24, 2012, at 11:50 PM, Warwick Prince wrote: Hi All This is addressed to anyone who may be able to shed some light on this strange behaviour; I'm running MySQL on Windows 2003 Server (Sorry) and have a table that has ~2M rows of sales data in it. Knowing the type of BI queries I would want to be doing, I have added indexes where I feel they would help. I tested the queries I wanted to perform manually by directly entering the SQL into the MySQL console. Results were fine. (Tested by putting raw SQL into MySQL Workbench on the same server as the database) I then simply moved the logic into my code, and created very simple queries on tables using the table.select() syntax, and adding a few basic group_by and 'where' additions. All works perfectly, and I get exactly the same results as my manual query BUT... the manual query returns the results in 3 seconds, and the programatic query returns the results in 200+ seconds!! This is the round trip time from .execute() 'til the next line of code being hit. i.e. I have fetched nothing yet. This is what I have done; I added an audit line in my code to show the final SQL that was being emitted, then cut and paste that into MySQL Workbench and executed it. 3 seconds was the result, with exactly the same actual rows being returned. The Python code is running on the same server as the MySQL Console. I have created the same query manually on the Python console, using the sqlalchemy constructs like query=table.select() etc and run it there - 180+ seconds again - it's I've basically eliminated by code. I'm using 'mysqlconnector' which I have had no problems in the past, and it's not like I'm hammering it with 1000's of queries - just 1 query that will return around 12 rows (having processed several 1000 and then GROUPed them). I can see the query sitting there in Workbench, taking minutes to complete. I've tried adding index hints just in case.. Made no difference, as it should have been using that index anyway.. When creating the SQLAlchemy test query, (and indeed in my real code) I used all defaults for engine, metadata etc with no additional settings at all. Finally, to add insult to injury, I can execute the identical manual query DURING the slow queries execution, and still get it back 3 seconds.. whilst the slow one clunks along and spits out the same result minutes later. In the SQL below, invoice and customer are VARCHAR columns, whilst date is DATE and TotalEx is FLOAT I have restarted the MySQL Service (several times) It's *like* the sqlalchemy query is not allowed to use any of the indexes, and is trawling through all the data.. Are there permissions or something on using an index that I don't know about? (Both queries are being executed by the same user - root) It is EXACTLY the same query in all ways, and in fact is an extremely simple query - as below; SELECT sum(`sales_data`.`TotalEx`) AS totalSales, date, invoice, customer FROM sales_data USE INDEX(idx_date) WHERE date =2011-12-01 AND date =2012-07-31 GROUP BY customer, invoice Any clues? My project just fell in a hole! Thanks an advance for any pointers. Warwick -- 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] Issue with `filter_by`?
I've defined a column declaratively like so my_column = Column(Unicode(30), index=True, unique=True) If I pass in an integer instead of a string, it will actually return all records that start with the string representation of that integer [obj.code for obj in Session.query(Model).filter_by(my_column=123).all()] [u'123ad', u'123lpb', u'123xd8', u'123za0'] I would expect no results as no column exactly matches the string representation of my number -- 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/-/mGMKZ0NB9_AJ. 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.
