[sqlalchemy] Re: mysql utf8 encoding problem
Michael Bayer wrote: On Oct 8, 2008, at 8:54 PM, jason kirtland wrote: Most likely you just need to configure the db-api's client encoding by adding ?charset=utf8 onto your connection URL. Enough folks have hit this recently that I'm (again) considering passing through the engine encoding= parameter to the MySQLdb connection setup. I've resisted the urge for a while because we don't to my knowledge re-configure any db-apis in any of the backends. But this keeps coming up despite being documented in the mysql section of the docs, and last time I traced through it, it seemed like MySQLdb was ignoring the server's configured connection_encoding so a little assist from the SA side would probably be useful. I'll look at sneaking that into the upcoming rc2 unless the implementation is untenable for some reason or there's an outcry. since im a total dumdum, why have i never had this issue in my own dealings with MySQL and Unicode ? I use the Unicode type, i dont use any charset= on my URL, and things work fine, including all of our unit tests. Is it actually storing the data incorrectly and we just see the same info at the SQLA round trip level ? i don't know about your env, but the unit tests under mysql use testing.engines.utf8_engine to configure a connection that can send unicode across the wire without encoding failures. --~--~-~--~~~---~--~~ 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] MapperExtension.methods property
Is there any reason why I would expect after_update to be called but
before_update to never be called?
What is responsible for creating the .methods property on the
extension? I'm creating it by hand so that I can manually set the
extension property of a mapper after the fact. But there's nothing in
the documentation to suggest that this property is necessary for
correct behavior.
I would humbly suggest that this ought to be a property on
MapperExtension that creates that list if it doesn't already exist.
Whoever is creating it now is difficult enough to track down that I
gave up after about 30 minutes of tracing code.
Regards,
Ross
class MyExtension(MapperExtension):
def __init__(self):
# KLUDGE rab: added to avert error in sqlalchemy.orm.query
FulltextUpdate has no attribute 'methods'
MapperExtension.__init__(self)
self.methods = ('after_insert', 'after_update',
'before_delete')
def after_insert(self, mapper, connection, instance):
log.debug('after_insert() ran')
return EXT_CONTINUE;
def after_update(self, mapper, connection, instance):
log.debug('before_update() ran')
return EXT_CONTINUE;
def before_delete(self, mapper, connection, instance):
log.debug('before_delete() ran')
return EXT_CONTINUE;
SomeClass.mapper.extension = MyExtension()
--~--~-~--~~~---~--~~
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] subselect and auto-correlate issue (0.5rc1)
I have the following code: tags = [utag1, utag2] tag_count = len(tags) inner_q = select([shiptag_table.c.shipid]) inner_w = inner_q.where( and_(shiptag_table.c.tagid == Tag.id,Tag.name.in_(tags)) ).group_by(shiptag_table.c.shipid).having(func.count(shiptag_table.c.shipid) == tag_count) outer_q = select([Tag.id, Tag.name, func.count(shiptag_table.c.shipid)]) outer_w = outer_q.where( and_(shiptag_table.c.shipid.in_(inner_w), not_(Tag.name.in_(tags)), Tag.id == shiptag_table.c.tagid) ).group_by(shiptag_table.c.tagid) related_tags = meta.Session.execute(outer_w).fetchall() return related_tags If I try to execute as is, I get the following error: InvalidRequestError: Select statement 'SELECT shiptag.shipid FROM shiptag, tag WHERE shiptag.tagid = tag.id AND tag.name IN (:name_1) GROUP BY shiptag.shipid HAVING count(shiptag.shipid) = :count_1' returned no FROM clauses due to auto-correlation; specify c orrelate(tables) to control correlation manually. The inner_w select is claiming to not have any FROM clauses, though they are clearly listed in the debug? If I run the inner_w separate, not as a subselect, then feed the results in to the in_ of outer_w it works. I figure I have to be missing something obvious? --~--~-~--~~~---~--~~ 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: mysql utf8 encoding problem
Michael Bayer wrote: On Oct 9, 2008, at 3:39 AM, jason kirtland wrote: i don't know about your env, but the unit tests under mysql use testing.engines.utf8_engine to configure a connection that can send unicode across the wire without encoding failures. I can run python test/sql/testtypes.py --db mysql --verbose UnicodeTest.testbasic with a raise or pdb.set_trace() inside of utf8_engine, and engines.utf8_engine is never called. Might be something about the data being tested in that test method. It's needed in the tests where it's used. --~--~-~--~~~---~--~~ 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: mysql utf8 encoding problem
Thanks all! The connection string is getting unicode into the db intact. Is it possible that the charset is set in my.conf or elsewhere on some environments? --~--~-~--~~~---~--~~ 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: subselect and auto-correlate issue (0.5rc1)
On Oct 9, 2008, at 10:20 AM, Wayne Witzel wrote: I have the following code: tags = [utag1, utag2] tag_count = len(tags) inner_q = select([shiptag_table.c.shipid]) inner_w = inner_q.where( and_(shiptag_table.c.tagid == Tag.id,Tag.name.in_(tags)) ).group_by (shiptag_table.c.shipid).having(func.count(shiptag_table.c.shipid) == tag_count) outer_q = select([Tag.id, Tag.name, func.count(shiptag_table.c.shipid)]) outer_w = outer_q.where( and_(shiptag_table.c.shipid.in_(inner_w), not_(Tag.name.in_(tags)), Tag.id == shiptag_table.c.tagid) ).group_by(shiptag_table.c.tagid) related_tags = meta.Session.execute(outer_w).fetchall() return related_tags If I try to execute as is, I get the following error: InvalidRequestError: Select statement 'SELECT shiptag.shipid FROM shiptag, tag WHERE shiptag.tagid = tag.id AND tag.name IN (:name_1) GROUP BY shiptag.shipid HAVING count(shiptag.shipid) = :count_1' returned no FROM clauses due to auto-correlation; specify c orrelate(tables) to control correlation manually. The inner_w select is claiming to not have any FROM clauses, though they are clearly listed in the debug? the debug prints out the inner select statement non-nested in the outer statement, so you get the full list of from clauses. the func.count(shiptag_table.c.shipid) on the outer query sticks shiptag_table in the outer FROM, thus removing it from the inner query due to auto-correlation. you probably mean to say func.count(inner_q.c.shipid) instead so that the outer is selecting from the subquery, not the shiptag_table by itself (but then I see a lot of other references to shiptag_table there, so its not really clear what statement you're going for...). If I run the inner_w separate, not as a subselect, then feed the results in to the in_ of outer_w it works. I figure I have to be missing something obvious? --~--~-~--~~~---~--~~ 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: subselect and auto-correlate issue (0.5rc1)
On Oct 9, 10:20 am, Wayne Witzel [EMAIL PROTECTED] wrote: I have the following code: tags = [utag1, utag2] tag_count = len(tags) inner_q = select([shiptag_table.c.shipid]) inner_w = inner_q.where( and_(shiptag_table.c.tagid == Tag.id,Tag.name.in_(tags)) ).group_by(shiptag_table.c.shipid).having(func.count(shiptag_table.c.shipid ) == tag_count) outer_q = select([Tag.id, Tag.name, func.count(shiptag_table.c.shipid)]) outer_w = outer_q.where( and_(shiptag_table.c.shipid.in_(inner_w), not_(Tag.name.in_(tags)), Tag.id == shiptag_table.c.tagid) ).group_by(shiptag_table.c.tagid) related_tags = meta.Session.execute(outer_w).fetchall() return related_tags If I try to execute as is, I get the following error: InvalidRequestError: Select statement 'SELECT shiptag.shipid FROM shiptag, tag WHERE shiptag.tagid = tag.id AND tag.name IN (:name_1) GROUP BY shiptag.shipid HAVING count(shiptag.shipid) = :count_1' returned no FROM clauses due to auto-correlation; specify c orrelate(tables) to control correlation manually. Well I can laugh at myself. Re-read the docs and it was layed out for me right there. Added .correlate(None) to my inner_w and all is well. Thanks, Wayne --~--~-~--~~~---~--~~ 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: subselect and auto-correlate issue (0.5rc1)
On Oct 9, 10:57 am, Michael Bayer [EMAIL PROTECTED] wrote: the debug prints out the inner select statement non-nested in the outer statement, so you get the full list of from clauses. the func.count(shiptag_table.c.shipid) on the outer query sticks shiptag_table in the outer FROM, thus removing it from the inner query due to auto-correlation. you probably mean to say func.count(inner_q.c.shipid) instead so that the outer is selecting from the subquery, not the shiptag_table by itself (but then I see a lot of other references to shiptag_table there, so its not really clear what statement you're going for...). Yeah, this was exactly what was happening, I turned off the auto-correlation for the inner_q and got the desired output SQL and results. Thanks Michael Thanks, Wayne --~--~-~--~~~---~--~~ 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: mysql utf8 encoding problem
On Oct 9, 2008, at 3:39 AM, jason kirtland wrote: i don't know about your env, but the unit tests under mysql use testing.engines.utf8_engine to configure a connection that can send unicode across the wire without encoding failures. I can run python test/sql/testtypes.py --db mysql --verbose UnicodeTest.testbasic with a raise or pdb.set_trace() inside of utf8_engine, and engines.utf8_engine is never called. --~--~-~--~~~---~--~~ 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: mysql utf8 encoding problem
On Oct 9, 2008, at 10:02 AM, Michael Bayer wrote: On Oct 9, 2008, at 3:39 AM, jason kirtland wrote: i don't know about your env, but the unit tests under mysql use testing.engines.utf8_engine to configure a connection that can send unicode across the wire without encoding failures. I can run python test/sql/testtypes.py --db mysql --verbose UnicodeTest.testbasic with a raise or pdb.set_trace() inside of utf8_engine, and engines.utf8_engine is never called. doh ! yupround trip works but data is stored as garbage. --~--~-~--~~~---~--~~ 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] Can't connect to local MySQL server
Hello Guys, I'm receiving errors in my application on a fairly regular basis now and I'm not sure how to begin solving it. Please find attached a backtrace for the error. It seems that its struggling to connect to the MySQL server, however I get this after the application has been running and querying the database for some time. Any ideas what might be causing this? I'd appreciate your thoughts. The code which throws the error is a very simple query(some_object).get(id) Cheers all, Heston --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 414, in get return self._get(key, ident) File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 1211, in _get return q.all()[0] File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 985, in all return list(self) File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 1073, in __iter__ return self._execute_and_instances(context) File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 1076, in _execute_and_instances result = self.session.execute(querycontext.statement, params=self._params, mapper=self._mapper_zero_or_none(), _state=self._refresh_state) File /var/lib/python-support/python2.5/sqlalchemy/orm/session.py, line 750, in execute return self.__connection(engine, close_with_result=True).execute( File /var/lib/python-support/python2.5/sqlalchemy/orm/session.py, line 717, in __connection return self.transaction._connection_for_bind(engine) File /var/lib/python-support/python2.5/sqlalchemy/orm/session.py, line 326, in _connection_for_bind conn = bind.contextual_connect() File /var/lib/python-support/python2.5/sqlalchemy/engine/base.py, line 1247, in contextual_connect return self.Connection(self, self.pool.connect(), close_with_result=close_with_result, **kwargs) File /var/lib/python-support/python2.5/sqlalchemy/pool.py, line 161, in connect return _ConnectionFairy(self).checkout() File /var/lib/python-support/python2.5/sqlalchemy/pool.py, line 321, in __init__ rec = self._connection_record = pool.get() File /var/lib/python-support/python2.5/sqlalchemy/pool.py, line 180, in get return self.do_get() File /var/lib/python-support/python2.5/sqlalchemy/pool.py, line 618, in do_get con = self.create_connection() File /var/lib/python-support/python2.5/sqlalchemy/pool.py, line 141, in create_connection return _ConnectionRecord(self) File /var/lib/python-support/python2.5/sqlalchemy/pool.py, line 217, in __init__ self.connection = self.__connect() File /var/lib/python-support/python2.5/sqlalchemy/pool.py, line 280, in __connect connection = self.__pool._creator() File /var/lib/python-support/python2.5/sqlalchemy/engine/strategies.py, line 80, in connect raise exc.DBAPIError.instance(None, None, e) OperationalError: (OperationalError) (2002, Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)) None None
[sqlalchemy] infinity with mysql backend
I have a sqlalchemy table with a float column, and I would like to be able to store +/- infinity. I am using numpy, and have access to np.inf. However, if I try and store this value, I get OperationalError: (OperationalError) (1054, Unknown column 'Infinity' in 'field list') Is there a way to store infinity using sqlalchemy with a mysql backend? In [128]: sa.__version__ Out[128]: '0.5.0beta4' [EMAIL PROTECTED]:~ mysql --version mysql Ver 12.22 Distrib 4.0.24, for pc-solaris2.10 (i386) --~--~-~--~~~---~--~~ 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: Configuring sqlalchemy pool
nvm guys I figured it out. The kwargs can be used to set pool_size, pool_recycle, pool_timeout, etc. I found the documentation here: http://www.sqlalchemy.org/docs/04/dbengine.html On Oct 9, 1:27 pm, ashok [EMAIL PROTECTED] wrote: Hi All, I'm having a problem with my pool in that it's not enough connections and I need to modify them. The default settings are: max_overflow=10 pool_size=5 timeout=30 I want to change these values, and when looking through the documentation I found this:http://www.sqlalchemy.org/docs/04/pooling.html This is for 0.4 however, and I'm using 0.5. Lastly, in the example they use on this page it creates a connection to create a QueuePool Example from website: import sqlalchemy.pool as pool import psycopg2 def getconn(): c = psycopg2.connect(username='ed', host='127.0.0.1', dbname='test') # execute an initialization function on the connection before returning c.cursor.execute(setup_encodings()) return c p = pool.QueuePool(getconn, max_overflow=10, pool_size=5, use_threadlocal=True) What exactly is getconn? Do I need it? All I want to do is create my own pool settings and send them through my engine_from_config commands. Any ideas? Thanks for your help! --~--~-~--~~~---~--~~ 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: Can't connect to local MySQL server
Looks like the MySQL server isnt running, or you have a sporadic network. On Oct 9, 2008, at 11:32 AM, Heston James - Cold Beans wrote: Hello Guys, I’m receiving errors in my application on a fairly regular basis now and I’m not sure how to begin solving it. Please find attached a backtrace for the error. It seems that its struggling to connect to the MySQL server, however I get this after the application has been running and querying the database for some time. Any ideas what might be causing this? I’d appreciate your thoughts. The code which throws the error is a very simple query(some_object).get(id) Cheers all, Heston File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 414, in get return self._get(key, ident) File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 1211, in _get return q.all()[0] File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 985, in all return list(self) File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 1073, in __iter__ return self._execute_and_instances(context) File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 1076, in _execute_and_instances result = self.session.execute(querycontext.statement, params=self._params, mapper=self._mapper_zero_or_none(), _state=self._refresh_state) File /var/lib/python-support/python2.5/sqlalchemy/orm/session.py, line 750, in execute return self.__connection(engine, close_with_result=True).execute( File /var/lib/python-support/python2.5/sqlalchemy/orm/session.py, line 717, in __connection return self.transaction._connection_for_bind(engine) File /var/lib/python-support/python2.5/sqlalchemy/orm/session.py, line 326, in _connection_for_bind conn = bind.contextual_connect() File /var/lib/python-support/python2.5/sqlalchemy/engine/base.py, line 1247, in contextual_connect return self.Connection(self, self.pool.connect(), close_with_result=close_with_result, **kwargs) File /var/lib/python-support/python2.5/sqlalchemy/pool.py, line 161, in connect return _ConnectionFairy(self).checkout() File /var/lib/python-support/python2.5/sqlalchemy/pool.py, line 321, in __init__ rec = self._connection_record = pool.get() File /var/lib/python-support/python2.5/sqlalchemy/pool.py, line 180, in get return self.do_get() File /var/lib/python-support/python2.5/sqlalchemy/pool.py, line 618, in do_get con = self.create_connection() File /var/lib/python-support/python2.5/sqlalchemy/pool.py, line 141, in create_connection return _ConnectionRecord(self) File /var/lib/python-support/python2.5/sqlalchemy/pool.py, line 217, in __init__ self.connection = self.__connect() File /var/lib/python-support/python2.5/sqlalchemy/pool.py, line 280, in __connect connection = self.__pool._creator() File /var/lib/python-support/python2.5/sqlalchemy/engine/ strategies.py, line 80, in connect raise exc.DBAPIError.instance(None, None, e) OperationalError: (OperationalError) (2002, Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)) None None --~--~-~--~~~---~--~~ 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] ratio on multiple tables
I have some tables with financial data -- one table has price data on
a given date and another data has fundamental data on a given report
date. I am wondering if it is possible to create another object that
is a ratio on a join between values in the two tables, eg in pseudo
code
# divide the current price by the most recent sales figure
price_sales = price_table.price / fundamental_table.sales where
fundamental_table.reportdate=price_table.date order by
fundamental_table.reportdate limit 1
I would also like this price_sales to be an attribute that is itself
queryable, eg, so I can express
select all where ratio_data.price_sales2 and and price_data.price5
I am a bit of a sqlalchemy newbie -- I have written the price data and
fundamental tables below. If there is a way to express the above
ratio data as a handy sqlalchemy map, I'd appreciate any suggestions
Version and table example code below
In [81]: sa.__version__
Out[81]: '0.5.0beta4'
import datetime
import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base
engine = sa.create_engine(mysql://johnh:[EMAIL PROTECTED]/trdlnksec)
Base = declarative_base(bind=engine)
class PriceData(Base):
__tablename__ = 'price_data'
ticker = sa.Column(sa.String(12), primary_key=True)
date = sa.Column(sa.Date, primary_key=True)
price = sa.Column(sa.FLOAT)
def __init__(self, ticker, date, price, volume):
self.ticker = ticker
self.date = date
self.price = price
def __repr__(self):
return PriceData('%s', %r, %r')%(self.ticker, self.date, self.price)
class FundamentalData(Base):
__tablename__ = 'fundamentals'
ticker = sa.Column(sa.String(12), primary_key=True)
reportdate = sa.Column(sa.Date, primary_key=True)
sales = sa.Column(sa.FLOAT)
income = sa.Column(sa.FLOAT)
def __init__(self, ticker, reportdate, sales, income):
self.ticker = ticker
self.reportdate = reportdate
self.sales = sales
self.income = income
def __repr__(self):
return FundamentalData('%s', %r, %r, %r')%(self.ticker,
self.reportdate, self.sales, self.income)
if __name__=='__main__':
Session = orm.sessionmaker()
session = Session(bind=engine)
Base.metadata.drop_all()
Base.metadata.create_all()
i1 = PriceData('IBM', datetime.date(2008,1,1), 100, 1000.)
i2 = PriceData('IBM', datetime.date(2008,1,2), 101, 2000.)
i3 = PriceData('IBM', datetime.date(2008,1,3), 102, 2000.)
q4 = FundamentalData('IBM', datetime.date(2007,12,1), 3., 3000.)
q3 = FundamentalData('IBM', datetime.date(2007,9,1), 2., 2000.)
q2 = FundamentalData('IBM', datetime.date(2007,6,1), 1., 1000.)
q1 = FundamentalData('IBM', datetime.date(2007,3,1), 10001., 1001.)
session.add(i1)
session.add(i2)
session.add(i3)
session.add(q1)
session.add(q2)
session.add(q3)
session.add(q4)
session.commit()
--~--~-~--~~~---~--~~
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] auto incrementing
Hello, I was wondering if anybody has a good strategy for auto incrementing fields. I want to auto increment field called case# . I have a choice of database auto increment on field case# or do it myself? (correct? No other choices exists? or something in between?) 1. I would like to be able to do pick a number where we will start doing a case#? 2. Reserver a case# for a special group which can auto increment case# between 2,000,000-2,999,999, and add them as they come. 3. I don't want to use (system_id) So it seems as the only way is to make my primary key: case# - unique key, primary, not auto incrementing and let some program manage auto incrementing. What options do I have with sqlalchemy to manage any range of these primary keys? 1. let db auto increment 2. Hold the next case# in a separate database table, and let my program use it to find next case# value. How would I lock/unlock the next case# to make there is no race condition and each case# is taken/successfully saved. 3. Any other options? Have people exeperienced with other strategy that is semi-automatic, and would for for these cases.? Thanks, Lucas --~--~-~--~~~---~--~~ 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: mysql to sqlalchemy table definition in .py file?
On Tue, Sep 30, 2008 at 2:35 AM, [EMAIL PROTECTED] wrote: well it's up to you to extend it to mysql... i don't use mysql, nor i know much about sql anyway; all specific stuff there is a steal/copy/try/error. http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc/metadata/autoload.py?revision=208view=markup give it dburl Does this code actually prints the table structure in a sqlalchemy way? or just moves the data from one db to another? Lucas --~--~-~--~~~---~--~~ 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: ratio on multiple tables
On Oct 9, 2008, at 3:28 PM, John Hunter wrote: I have some tables with financial data -- one table has price data on a given date and another data has fundamental data on a given report date. I am wondering if it is possible to create another object that is a ratio on a join between values in the two tables, eg in pseudo code # divide the current price by the most recent sales figure price_sales = price_table.price / fundamental_table.sales where fundamental_table.reportdate=price_table.date order by fundamental_table.reportdate limit 1 I would also like this price_sales to be an attribute that is itself queryable, eg, so I can express select all where ratio_data.price_sales2 and and price_data.price5 I am a bit of a sqlalchemy newbie -- I have written the price data and fundamental tables below. If there is a way to express the above ratio data as a handy sqlalchemy map, I'd appreciate any suggestions if the attribute is attached to either PriceData or FundamentalData, the general route towards this kind of thing is to use column_property(). You can place subqueries which correlate to the base table in those. If you're looking for a third mapped object along the lines of RatioData, you can map such a class to a select() object which represents the query, although this seems more like an attribute on PriceData. the column_property() is usable in queries and you can also customize how it compares using a user-defined PropComparator. a brief example is at: http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_mapper_expressions --~--~-~--~~~---~--~~ 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] avoiding locks with SA
Let's say you have two concurrent processes where each might increment
some integer field in some table row.
If you query first and the increment in memory and then update, you
need to query with_lockmode('update') to avoid the case where both
processes read the same value and the do the same increment.
A simpler method is to use SQL s.a. set field = field+1.
Q: How do you generate such SQL with SA ?
Is there a better method to do it?
--~--~-~--~~~---~--~~
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] Copying SQLA objects from one DB to another
Hi, I was wondering if there is an easy way to copy SQLA objects from one DB to another. I've poked around and found this thread: http://groups.google.com/group/sqlalchemy/browse_thread/thread/c1bd8cfe862441d6 but my application is different so I thought I'd post. Basically, I have a database of many SQLA objects, call them Users. Each user has foreign key references to addresses, groups, etc. I want to create a new test database which contains a small subset of Users, but pulls any addresses, or groups over as well and creates rows in those tables to satisfy the foreign key constraints. I don't care if primary keys change (as long as the constraints are satisfied). To copy over the first 10 users I naively tried doing something along the lines of: sess1 = ProductionDBSession() sess2 = TestDBSession() users = sess1.query(User).filter(User.id10) for user in users: sess2.add(user) sess2.commit() Of course this doesn't work because user is in the persisted state within sess1. What I was hoping though, was that the user.address_list and user.group_list attributes would also be copied over by creating rows in the address and group tables as well as the user table and setting the proper foreign key relationships. I'm prepared to write a more custom script that copies individual objects and sets up the foreign key relationships by hand, but if there was something like this I could do to make it a simpler process, that would be great. Thanks! Sam --~--~-~--~~~---~--~~ 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: ratio on multiple tables
On Thu, Oct 9, 2008 at 6:28 PM, Michael Bayer [EMAIL PROTECTED] wrote:
On Oct 9, 2008, at 3:28 PM, John Hunter wrote:
I have some tables with financial data -- one table has price data on
a given date and another data has fundamental data on a given report
date. I am wondering if it is possible to create another object that
is a ratio on a join between values in the two tables, eg in pseudo
code
# divide the current price by the most recent sales figure
price_sales = price_table.price / fundamental_table.sales where
fundamental_table.reportdate=price_table.date order by
fundamental_table.reportdate limit 1
I would also like this price_sales to be an attribute that is itself
queryable, eg, so I can express
select all where ratio_data.price_sales2 and and price_data.price5
I am a bit of a sqlalchemy newbie -- I have written the price data and
fundamental tables below. If there is a way to express the above
ratio data as a handy sqlalchemy map, I'd appreciate any suggestions
if the attribute is attached to either PriceData or FundamentalData,
the general route towards this kind of thing is to use
column_property(). You can place subqueries which correlate to the
base table in those. If you're looking for a third mapped object
along the lines of RatioData, you can map such a class to a select()
object which represents the query, although this seems more like an
attribute on PriceData.
the column_property() is usable in queries and you can also customize
how it compares using a user-defined PropComparator.
a brief example is at:
http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_mapper_expressions
Hey Michael,
Thanks for your answer. This has been tremendously helpful. I now
have an example that is doing more-or-less what I want for my
toy-example (see below). In this example, I went ahead and attached
the ratio to the price data, because as you suggested this is a fairly
natural place for it (for any fundamental reportdate there are
multiple price data points at which I might want to compute the
ratio).
To simplify the problem, I have written code that simply connects the
reportdate of the fundamental data with a give price data point, eg
class PriceData(Base):
__tablename__ = 'price_data'
ticker = sa.Column(sa.String(12), primary_key=True)
date = sa.Column(sa.Date, primary_key=True)
price = sa.Column(sa.FLOAT)
reportdate = orm.column_property(
sa.select(
[FundamentalData.reportdate],
(ticker==FundamentalData.ticker) (date=FundamentalData.reportdate)
).order_by(FundamentalData.reportdate.desc()).limit(1).label('reportdate'))
This works fine, as in the complete example posted below (the code
identifies a report date less-than-or-equal-to a price date for each
date). But I am confused by the effect of the limit method. My
intention is to use it to limit the results to one match, the most
recent reportdate before date. But if I remove the limit method
call, I still get the same result, a single reportdate, though I
would expect a sequence instead since there are multiple reportdates
prior to the price date in the example included below. Or if I write
limit(4) I still get a single result for reportdate, though I would
expect multiple matches.
Any ideas what is going on here? Compete example below
import datetime
import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base
engine = sa.create_engine(sqlite:///test.db)
Base = declarative_base(bind=engine)
class FundamentalData(Base):
__tablename__ = 'fundamentals'
ticker = sa.Column(sa.String(12), primary_key=True)
reportdate = sa.Column(sa.Date, primary_key=True)
sales = sa.Column(sa.FLOAT)
income = sa.Column(sa.FLOAT)
def __init__(self, ticker, reportdate, sales, income):
self.ticker = ticker
self.reportdate = reportdate
self.sales = sales
self.income = income
def __repr__(self):
return FundamentalData('%s', %r, %r, %r)%(self.ticker,
self.reportdate, self.sales, self.income)
class PriceData(Base):
__tablename__ = 'price_data'
ticker = sa.Column(sa.String(12), primary_key=True)
date = sa.Column(sa.Date, primary_key=True)
price = sa.Column(sa.FLOAT)
reportdate = orm.column_property(
sa.select(
[FundamentalData.reportdate],
(ticker==FundamentalData.ticker) (date=FundamentalData.reportdate)
).order_by(FundamentalData.reportdate.desc()).limit(1).label('reportdate'))
price_sales = orm.column_property(
sa.select(
[price/FundamentalData.sales],
(ticker==FundamentalData.ticker) (date=FundamentalData.reportdate),
).label('price_sales'))
def __init__(self, ticker, date, price, volume):
self.ticker = ticker
self.date = date
self.price = price
def __repr__(self):
return PriceData('%s', %r, %r); ps=%r,
