[sqlalchemy] Re: puzzling outerjoin in the mapper
Missing an answer I opened ticket #1392: http://www.sqlalchemy.org/trac/ticket/1392#preview --~--~-~--~~~---~--~~ 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: puzzling outerjoin in the mapper
You need to set allow_null_pks=true on your mapper. This will be on by default in 06. On Apr 27, 2009, at 3:40 AM, sandro dentella san...@e-den.it wrote: Missing an answer I opened ticket #1392: http://www.sqlalchemy.org/trac/ticket/1392#preview --~--~-~--~~~---~--~~ 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] 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
Hi all, Am having a problem with SQLAlchemy 0.5.3 and MSSQL. Running on a Debian stack, using FreeTDS 0.82, pyodbc 2.1.4, Python 2.5 and (separately) SQL Server 2000 and SQL Server 2005. The (nose) test below fails with the exception: ProgrammingError: (ProgrammingError) ('24000', '[24000] [FreeTDS][SQL Server]Invalid cursor state (0) (SQLExecDirectW)') 'INSERT INTO activities (institution, application_id) VALUES (?, ?); select scope_identity()' ['UMass', 1] Complete stack can be found below. I'd love to hear from anyone running under a comparable configuration-- whether you see the same results or not! :-) FYI: The test succeeds using SQLAlchemy 0.5.0rc3, but fails with every subsequent release. It also passes running against a sqllite db. Thanks very much. Tom Wood University of Connecticut # begin test code import sqlalchemy as sa from sqlalchemy import orm conn = 'mssql://insert your string here' engine = sa.create_engine(conn) metadata = sa.MetaData(bind=engine) applications_table = sa.Table('applications', metadata, sa.Column('id', sa.Integer, primary_key=True), sa.Column('last_name', sa.types.String (20))) activities_table = sa.Table('activities', metadata, sa.Column('id', sa.Integer, primary_key=True), sa.Column('institution', sa.types.String (20)), sa.Column('application_id', sa.Integer, sa.ForeignKey('applications.id'))) Session = orm.sessionmaker() class Application(object): def __init__(self, last_name): self.last_name = last_name class Activity(object): def __init__(self, institution): self.institution = institution orm.mapper(Application, applications_table, properties={'activities': orm.relation(Activity, backref='application')}) orm.mapper(Activity, activities_table) class Tester(object): def setup(self): metadata.create_all() self.session = Session() def teardown(self): self.session.close() metadata.drop_all() def test_orm_relation(self): app = Application(last_name='Wood') act = Activity(institution='UConn') act2 = Activity(institution='UMass') app.activities.append(act) app.activities.append(act2) self.session.add(app) self.session.commit() assert act.id is not None assert app.id is not None assert act2.id is not None assert act.application_id == app.id assert act2.application_id == app.id # begin stack crawl ERROR: simple_test.Tester.test_orm_relation -- Traceback (most recent call last): File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/ nose-0.10.4-py2.5.egg/nose/case.py, line 182, in runTest self.test(*self.arg) File /home/XXX/unicode_tests/simple_test.py, line 45, in test_orm_relation self.session.commit() File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/ SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 673, in commit self.transaction.commit() File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/ SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 378, in commit self._prepare_impl() File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/ SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 362, in _prepare_impl self.session.flush() File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/ SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 1351, in flush self._flush(objects) File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/ SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 1422, in _flush flush_context.execute() File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/ SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 244, in execute UOWExecutor().execute(self, tasks) File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/ SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 707, in execute self.execute_save_steps(trans, task) File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/ SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 722, in execute_save_steps self.save_objects(trans, task) File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/ SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 713, in save_objects task.mapper._save_obj(task.polymorphic_tosave_objects, trans) File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/ SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/mapper.py, line 1347, in _save_obj c = connection.execute(statement.values(value_params), params) File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Mon, Apr 27, 2009 at 10:01 AM, Tom Wood thomas.a.w...@gmail.com wrote: Hi all, Am having a problem with SQLAlchemy 0.5.3 and MSSQL. Running on a Debian stack, using FreeTDS 0.82, pyodbc 2.1.4, Python 2.5 and (separately) SQL Server 2000 and SQL Server 2005. The (nose) test below fails with the exception: ProgrammingError: (ProgrammingError) ('24000', '[24000] [FreeTDS][SQL Server]Invalid cursor state (0) (SQLExecDirectW)') 'INSERT INTO activities (institution, application_id) VALUES (?, ?); select scope_identity()' ['UMass', 1] Complete stack can be found below. I'd love to hear from anyone running under a comparable configuration-- whether you see the same results or not! :-) FYI: The test succeeds using SQLAlchemy 0.5.0rc3, but fails with every subsequent release. It also passes running against a sqllite db. Thanks very much. Tom Wood University of Connecticut # begin test code import sqlalchemy as sa from sqlalchemy import orm conn = 'mssql://insert your string here' engine = sa.create_engine(conn) I'm not sure if I can help but if we could start with the basics and find out what version of tds are you using and how are you connecting? 1. Are you using dsn-less or dsn connection string? @dsn ? 2. What tds version have you set in /etc/freetds/tds.dsn.template http://lucasmanual.com/mywiki/unixODBC Have you tried setting it to tds version 8.0? Thanks, Lucas metadata = sa.MetaData(bind=engine) applications_table = sa.Table('applications', metadata, sa.Column('id', sa.Integer, primary_key=True), sa.Column('last_name', sa.types.String (20))) activities_table = sa.Table('activities', metadata, sa.Column('id', sa.Integer, primary_key=True), sa.Column('institution', sa.types.String (20)), sa.Column('application_id', sa.Integer, sa.ForeignKey('applications.id'))) Session = orm.sessionmaker() class Application(object): def __init__(self, last_name): self.last_name = last_name class Activity(object): def __init__(self, institution): self.institution = institution orm.mapper(Application, applications_table, properties={'activities': orm.relation(Activity, backref='application')}) orm.mapper(Activity, activities_table) class Tester(object): def setup(self): metadata.create_all() self.session = Session() def teardown(self): self.session.close() metadata.drop_all() def test_orm_relation(self): app = Application(last_name='Wood') act = Activity(institution='UConn') act2 = Activity(institution='UMass') app.activities.append(act) app.activities.append(act2) self.session.add(app) self.session.commit() assert act.id is not None assert app.id is not None assert act2.id is not None assert act.application_id == app.id assert act2.application_id == app.id # begin stack crawl ERROR: simple_test.Tester.test_orm_relation -- Traceback (most recent call last): File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/ nose-0.10.4-py2.5.egg/nose/case.py, line 182, in runTest self.test(*self.arg) File /home/XXX/unicode_tests/simple_test.py, line 45, in test_orm_relation self.session.commit() File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/ SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 673, in commit self.transaction.commit() File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/ SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 378, in commit self._prepare_impl() File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/ SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 362, in _prepare_impl self.session.flush() File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/ SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 1351, in flush self._flush(objects) File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/ SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 1422, in _flush flush_context.execute() File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/ SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 244, in execute UOWExecutor().execute(self, tasks) File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/ SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 707, in execute self.execute_save_steps(trans, task) File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/ SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 722, in execute_save_steps self.save_objects(trans, task) File
[sqlalchemy] Ambiguous column name in SQL literal query
Dear group, I'm having the following issue with Ambiguous column name, in this situation: #code example begin connString = 'sqlite:///data/prosafelocal.sqlite' engine = create_engine(connString) connection = engine.connect() query = 'SELECT crfItem.idCrf, crfItem.idClass, class.name, attribute.id, attribute.name from crfItem JOIN class ON crfItem.idClass = class.id JOIN attribute on class.id = attribute.idClass WHERE crfItem.idCrf = 1' result = self.connection.execute(query, use_labels=True) #code end I'm using connection.execute(query) directly with literal sql syntax. I get the following error: try 'use_labels' option on select statement. % colname) InvalidRequestError: Ambiguous column name 'name' in result set! try 'use_labels' option on select statement. The name column is present in two joint tables, and it generates the ambiguity. Anyway, it seems like the option use_labels=True does is not affecting my query behaviour. Any help would be really appreciated. Thanks, Mauro --~--~-~--~~~---~--~~ 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: Ambiguous column name in SQL literal query
use_labels is a flag used by a select() construct. this is a literal text expression, so just label your name column something else, i.e. class.name AS class_name. Mauro wrote: Dear group, I'm having the following issue with Ambiguous column name, in this situation: #code example begin connString = 'sqlite:///data/prosafelocal.sqlite' engine = create_engine(connString) connection = engine.connect() query = 'SELECT crfItem.idCrf, crfItem.idClass, class.name, attribute.id, attribute.name from crfItem JOIN class ON crfItem.idClass = class.id JOIN attribute on class.id = attribute.idClass WHERE crfItem.idCrf = 1' result = self.connection.execute(query, use_labels=True) #code end I'm using connection.execute(query) directly with literal sql syntax. I get the following error: try 'use_labels' option on select statement. % colname) InvalidRequestError: Ambiguous column name 'name' in result set! try 'use_labels' option on select statement. The name column is present in two joint tables, and it generates the ambiguity. Anyway, it seems like the option use_labels=True does is not affecting my query behaviour. Any help would be really appreciated. Thanks, Mauro --~--~-~--~~~---~--~~ 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: Ambiguous column name in SQL literal query
as the SQl is literal, u have to apply that advice by hand: SELECT crfItem.idCrf, class.name as clsname, attribute.name as attrname ... or something of sorts On Monday 27 April 2009 19:00:24 Mauro wrote: Dear group, I'm having the following issue with Ambiguous column name, in this situation: #code example begin connString = 'sqlite:///data/prosafelocal.sqlite' engine = create_engine(connString) connection = engine.connect() query = 'SELECT crfItem.idCrf, crfItem.idClass, class.name, attribute.id, attribute.name from crfItem JOIN class ON crfItem.idClass = class.id JOIN attribute on class.id = attribute.idClass WHERE crfItem.idCrf = 1' result = self.connection.execute(query, use_labels=True) #code end I'm using connection.execute(query) directly with literal sql syntax. I get the following error: try 'use_labels' option on select statement. % colname) InvalidRequestError: Ambiguous column name 'name' in result set! try 'use_labels' option on select statement. The name column is present in two joint tables, and it generates the ambiguity. Anyway, it seems like the option use_labels=True does is not affecting my query behaviour. Any help would be really appreciated. Thanks, Mauro --~--~-~--~~~---~--~~ 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: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
Lucas, Thanks for taking a peek at this! My connection string looks like: 'mssql://user:p...@server/db? driver=FreeTDS_82Servername=MyServer' where FreeTDS_82 is defined in my odbcinst.ini, and MyServer is defined in my freetds.conf. I've tried 'tds version = 8.0' and 'tds version = 7.0' in my freetds.conf, but still see the same behavior. I'm fairly certain both config files are being read correctly. Using FreeTDS 0.82, I see the exception as reported. Using FreeTDS 0.63, I see a slightly different exception: raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) DBAPIError: (Error) ('HY000', 'The driver did not supply an error!') 'INSERT INTO activities (institution, application_id) VALUES (?, ?); select scope_identity()' ['UMass', 1] and the FreeTDS log includes: 17:28:26.864001 tds_submit_query(): state is PENDING 17:28:26.864015 tds_client_msg: #20019: Attempt to initiate a new SQL Server operation with results pending.. Connection state is now 1. which suggests to me the same problem, just being caught in a different place. I should also mention that simpler tests (e.g., using the ORM to save an object with no relations) do succeed. -Tom On Apr 27, 12:18 pm, Lukasz Szybalski szybal...@gmail.com wrote: On Mon, Apr 27, 2009 at 10:01 AM, Tom Wood thomas.a.w...@gmail.com wrote: Hi all, Am having a problem with SQLAlchemy 0.5.3 and MSSQL. Running on a Debian stack, using FreeTDS 0.82, pyodbc 2.1.4, Python 2.5 and (separately) SQL Server 2000 and SQL Server 2005. The (nose) test below fails with the exception: ProgrammingError: (ProgrammingError) ('24000', '[24000] [FreeTDS][SQL Server]Invalid cursor state (0) (SQLExecDirectW)') 'INSERT INTO activities (institution, application_id) VALUES (?, ?); select scope_identity()' ['UMass', 1] Complete stack can be found below. I'd love to hear from anyone running under a comparable configuration-- whether you see the same results or not! :-) FYI: The test succeeds using SQLAlchemy 0.5.0rc3, but fails with every subsequent release. It also passes running against a sqllite db. Thanks very much. Tom Wood University of Connecticut # begin test code import sqlalchemy as sa from sqlalchemy import orm conn = 'mssql://insert your string here' engine = sa.create_engine(conn) I'm not sure if I can help but if we could start with the basics and find out what version of tds are you using and how are you connecting? 1. Are you using dsn-less or dsn connection string? �...@dsn ? 2. What tds version have you set in /etc/freetds/tds.dsn.templatehttp://lucasmanual.com/mywiki/unixODBC Have you tried setting it to tds version 8.0? 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] MySQL deadlocks and retry
My application uses MySQL with InnoDB tables and replication. We're starting to encounter an issue on a few particular parts of the application where we're getting tracebacks like this: class 'sqlalchemy.exc.OperationalError': (OperationalError) (1213, 'Deadlock found when trying to get lock; try restarting transaction') According to MySQL documentation, there are a large variety of circumstances in which this could happen, mostly innocuous in nature. The official recommended solution to the problem is: retry the transaction. Does SQLAlchemy offer some method for me to catch this exception and then retry the transaction? Thanks in advance! -- Jonathan LaCour http://cleverdevil.org --~--~-~--~~~---~--~~ 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] count() not working?
Hi, Ok, I am doing a standard tags thingee which is many-to-many. I want to to a query so that I get the main records which have all the named keywords. To use webpage paging efficiently, I need to get a count() of the records. This does not seem to work the way I am doing it. Also, I don't think ranges are working, either, for this query (e.g., . [3:5]). The query: taglist = ['science'] tagcount = 1 page_q = SES.query(Quote) quotes_q = page_q.join(Quote.tags).filter(Tag.tagword.in_(taglist)).\ group_by(Quote.id).having(func.count(Quote.id) == tagcount) n = quotes_q.count() print count n=, n USUALLY puts out a 1, or None (both of which are incorrect). NOTE: qrecs = quotes_q.all() works! 3 tables: the relation table, plus two classes (irrelevant stuff removed): quote_tag = Table('quote_tag', meta.metadata, Column('quote_id', Integer, ForeignKey ('quote.id')), Column('tag_id', Integer, ForeignKey('tag.id')) ) class Quote(Base): __tablename__ = quote id = Column(Integer, primary_key=True) date_create = Column(DateTime) qbody = Column(UnicodeText) tags = relation(Tag, secondary=quote_tag, backref='quote') class Tag(Base): __tablename__ = tag id = Column(Integer, primary_key=True) tagword = Column(Unicode(20), nullable=False, unique=True) Any ideas what I am doing wrong? (Also, I am obviously a SQL dyslexic!) 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: MySQL deadlocks and retry
Jonathan LaCour wrote: My application uses MySQL with InnoDB tables and replication. We're starting to encounter an issue on a few particular parts of the application where we're getting tracebacks like this: class 'sqlalchemy.exc.OperationalError': (OperationalError) (1213, 'Deadlock found when trying to get lock; try restarting transaction') According to MySQL documentation, there are a large variety of circumstances in which this could happen, mostly innocuous in nature. The official recommended solution to the problem is: retry the transaction. Does SQLAlchemy offer some method for me to catch this exception and then retry the transaction? there's nothing offered beyond the usual notion of catching an exception and running the function again. It also depends very much upon the construction of your application, whether you're looking to do this in an ORM context, etc. --~--~-~--~~~---~--~~ 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: MySQL deadlocks and retry
Michael Bayer wrote: Does SQLAlchemy offer some method for me to catch this exception and then retry the transaction? there's nothing offered beyond the usual notion of catching an exception and running the function again. It also depends very much upon the construction of your application, whether you're looking to do this in an ORM context, etc. Yeah, we sort of figured. Our application uses WSGI middleware to wrap particular requests in transactions. We ended up writing something in there to catch this particular exception and re-run the request up to three times. Its not quite as pretty as I'd like, but it works :) -- Jonathan LaCour http://cleverdevil.org --~--~-~--~~~---~--~~ 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: pyodbc issues
Could this be related to this change: http://www.sqlalchemy.org/trac/changeset/5564 I encountered a similar problem with mssql/pyodbc today (the echo output showed the correct statements and params, but no changes seemed to be made in the db at all). The problem only occurred when using the orm/session layer, same statements with engine.execute seemed to work. I managed to fix it by reverting the change with the following monkeypatch: from sqlalchemy.databases.mssql import MSSQLDialect MSSQLDialect.do_begin = lambda self, conn: pass Not sure this is related though (or if this is a good solution), just thought I'd let it know in case it helps. greetings, Steven On 25 apr, 15:05, Michael Trier mtr...@gmail.com wrote: On Apr 24, 2009, at 7:50 PM, Michael Mileusnich justmike2...@gmail.com wrote: Wow..your example worked for me. Could the kwargs the issue? No. Likely you have some sort of conflict on the dbapi side. If it's possible for you to send me your actual code (mtr...@gmail.com) I'd be happy to try and figure out the issue. On Fri, Apr 24, 2009 at 3:14 PM, mtrier mtr...@gmail.com wrote: On Apr 24, 2009, at 4:02 AM, Michael Mileusnich wrote: I have formated my PC with Vista 32. I also Installed SQL Server 2008 Express. Installed Python 2.6 and pyodbc...SAME ISSUE. I would be willing to have somebody overlook my python code. With echo on everything looks like it should be INSERTING. I've written this script based on the information you have supplied. It works fine for me: from sqlalchemy import * from sqlalchemy.orm import * engine = create_engine('mssql://sprint:spr...@localhost/sprint', echo=True) metadata = MetaData(engine) Session = scoped_session(sessionmaker(bind=engine, autoflush=False, autocommit=True)) action_table = Table( 'ACTIONS', metadata, Column('ACTIONID', String(48), primary_key=True), Column('TITLE', String(128)), Column('CMDLINE', String(512)), Column('STDIN', Text), Column('STARTINDIR', String(512)), Column('PRIO', Integer), ) class action(object): def __init__(self, ACTIONID, CMDLINE): self.ACTIONID = ACTIONID self.CMDLINE = CMDLINE def __repr__(self): return action('%s', '%s') % (self.ACTIONID, self.CMDLINE) mapper(action, action_table) metadata.create_all() session = Session() new_action = action(ACTIONID = '500', CMDLINE = 'sol') session.add(new_action) session.flush() session.expunge_all() act = session.query(action).filter_by(ACTIONID='500').one() assert new_action.ACTIONID == act.ACTIONID -- Would you please try it and let me know what results you get. The following is my output. S:\sqlalchemy.git\libpython msssqlprob.py 2009-04-24 16:10:30,473 INFO sqlalchemy.engine.base.Engine.0x...6110 SELECT user_name() as user_name; 2009-04-24 16:10:30,552 INFO sqlalchemy.engine.base.Engine.0x...6110 [] 2009-04-24 16:10:30,568 INFO sqlalchemy.engine.base.Engine.0x...6110 SELECT default_schema_name FROM sys.database_principals WHERE name = ? AND type = 'S' 2009-04-24 16:10:30,568 INFO sqlalchemy.engine.base.Engine.0x...6110 [u'dbo'] 2009-04-24 16:10:30,582 INFO sqlalchemy.engine.base.Engine.0x...6110 SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TAB LE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1]. [NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLU MN_DEFAULT], [COLUMNS_1].[COLLATION_NAME] FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? 2009-04-24 16:10:30,598 INFO sqlalchemy.engine.base.Engine.0x...6110 ['ACTIONS', u'dbo'] 2009-04-24 16:10:30,598 INFO sqlalchemy.engine.base.Engine.0x...6110 CREATE TABLE [ACTIONS] ( [ACTIONID] VARCHAR(48) NOT NULL, [TITLE] VARCHAR(128) NULL, [CMDLINE] VARCHAR(512) NULL, [STDIN] TEXT NULL, [STARTINDIR] VARCHAR(512) NULL, [PRIO] INTEGER NULL, PRIMARY KEY ([ACTIONID]) ) 2009-04-24 16:10:30,630 INFO sqlalchemy.engine.base.Engine.0x...6110 () 2009-04-24 16:10:30,661 INFO sqlalchemy.engine.base.Engine.0x...6110 COMMIT 2009-04-24 16:10:30,693 INFO sqlalchemy.engine.base.Engine.0x...6110 BEGIN 2009-04-24 16:10:30,707 INFO sqlalchemy.engine.base.Engine.0x...6110 INSERT INTO [ACTIONS] ([ACTIONID], [TITLE], [CMDLIN E], [STDIN], [STARTINDIR], [PRIO]) VALUES (?, ?, ?, ?, ?, ?) 2009-04-24 16:10:30,707 INFO sqlalchemy.engine.base.Engine.0x...6110 ['500', None, 'sol', None, None, None] 2009-04-24 16:10:30,723 INFO sqlalchemy.engine.base.Engine.0x...6110 COMMIT 2009-04-24 16:10:30,723 INFO
[sqlalchemy] Re: pyodbc issues
Oops, make that: MSSQLDialect.do_begin = lambda self, conn: None (pass is not allowed in lambda statements ;-) I had already corrected the mistake in my code, only to make it again posting the message, sorry...) On 27 apr, 22:33, polaar steven.vereec...@gmail.com wrote: Could this be related to this change:http://www.sqlalchemy.org/trac/changeset/5564 I encountered a similar problem with mssql/pyodbc today (the echo output showed the correct statements and params, but no changes seemed to be made in the db at all). The problem only occurred when using the orm/session layer, same statements with engine.execute seemed to work. I managed to fix it by reverting the change with the following monkeypatch: from sqlalchemy.databases.mssql import MSSQLDialect MSSQLDialect.do_begin = lambda self, conn: pass Not sure this is related though (or if this is a good solution), just thought I'd let it know in case it helps. greetings, Steven On 25 apr, 15:05, Michael Trier mtr...@gmail.com wrote: On Apr 24, 2009, at 7:50 PM, Michael Mileusnich justmike2...@gmail.com wrote: Wow..your example worked for me. Could the kwargs the issue? No. Likely you have some sort of conflict on the dbapi side. If it's possible for you to send me your actual code (mtr...@gmail.com) I'd be happy to try and figure out the issue. On Fri, Apr 24, 2009 at 3:14 PM, mtrier mtr...@gmail.com wrote: On Apr 24, 2009, at 4:02 AM, Michael Mileusnich wrote: I have formated my PC with Vista 32. I also Installed SQL Server 2008 Express. Installed Python 2.6 and pyodbc...SAME ISSUE. I would be willing to have somebody overlook my python code. With echo on everything looks like it should be INSERTING. I've written this script based on the information you have supplied. It works fine for me: from sqlalchemy import * from sqlalchemy.orm import * engine = create_engine('mssql://sprint:spr...@localhost/sprint', echo=True) metadata = MetaData(engine) Session = scoped_session(sessionmaker(bind=engine, autoflush=False, autocommit=True)) action_table = Table( 'ACTIONS', metadata, Column('ACTIONID', String(48), primary_key=True), Column('TITLE', String(128)), Column('CMDLINE', String(512)), Column('STDIN', Text), Column('STARTINDIR', String(512)), Column('PRIO', Integer), ) class action(object): def __init__(self, ACTIONID, CMDLINE): self.ACTIONID = ACTIONID self.CMDLINE = CMDLINE def __repr__(self): return action('%s', '%s') % (self.ACTIONID, self.CMDLINE) mapper(action, action_table) metadata.create_all() session = Session() new_action = action(ACTIONID = '500', CMDLINE = 'sol') session.add(new_action) session.flush() session.expunge_all() act = session.query(action).filter_by(ACTIONID='500').one() assert new_action.ACTIONID == act.ACTIONID -- Would you please try it and let me know what results you get. The following is my output. S:\sqlalchemy.git\libpython msssqlprob.py 2009-04-24 16:10:30,473 INFO sqlalchemy.engine.base.Engine.0x...6110 SELECT user_name() as user_name; 2009-04-24 16:10:30,552 INFO sqlalchemy.engine.base.Engine.0x...6110 [] 2009-04-24 16:10:30,568 INFO sqlalchemy.engine.base.Engine.0x...6110 SELECT default_schema_name FROM sys.database_principals WHERE name = ? AND type = 'S' 2009-04-24 16:10:30,568 INFO sqlalchemy.engine.base.Engine.0x...6110 [u'dbo'] 2009-04-24 16:10:30,582 INFO sqlalchemy.engine.base.Engine.0x...6110 SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TAB LE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1]. [NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLU MN_DEFAULT], [COLUMNS_1].[COLLATION_NAME] FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? 2009-04-24 16:10:30,598 INFO sqlalchemy.engine.base.Engine.0x...6110 ['ACTIONS', u'dbo'] 2009-04-24 16:10:30,598 INFO sqlalchemy.engine.base.Engine.0x...6110 CREATE TABLE [ACTIONS] ( [ACTIONID] VARCHAR(48) NOT NULL, [TITLE] VARCHAR(128) NULL, [CMDLINE] VARCHAR(512) NULL, [STDIN] TEXT NULL, [STARTINDIR] VARCHAR(512) NULL, [PRIO] INTEGER NULL, PRIMARY KEY ([ACTIONID]) ) 2009-04-24 16:10:30,630 INFO sqlalchemy.engine.base.Engine.0x...6110 () 2009-04-24 16:10:30,661 INFO sqlalchemy.engine.base.Engine.0x...6110 COMMIT 2009-04-24 16:10:30,693 INFO sqlalchemy.engine.base.Engine.0x...6110 BEGIN 2009-04-24 16:10:30,707 INFO
[sqlalchemy] Re: pyodbc issues
I'd love to revert that change and just say MSSQL doesn't support SAVEPOINT for now. or put an enable_savepoint flag in the dialect. polaar wrote: Could this be related to this change: http://www.sqlalchemy.org/trac/changeset/5564 I encountered a similar problem with mssql/pyodbc today (the echo output showed the correct statements and params, but no changes seemed to be made in the db at all). The problem only occurred when using the orm/session layer, same statements with engine.execute seemed to work. I managed to fix it by reverting the change with the following monkeypatch: from sqlalchemy.databases.mssql import MSSQLDialect MSSQLDialect.do_begin = lambda self, conn: pass Not sure this is related though (or if this is a good solution), just thought I'd let it know in case it helps. greetings, Steven On 25 apr, 15:05, Michael Trier mtr...@gmail.com wrote: On Apr 24, 2009, at 7:50 PM, Michael Mileusnich justmike2...@gmail.com wrote: Wow..your example worked for me. Could the kwargs the issue? No. Likely you have some sort of conflict on the dbapi side. If it's possible for you to send me your actual code (mtr...@gmail.com) I'd be happy to try and figure out the issue. On Fri, Apr 24, 2009 at 3:14 PM, mtrier mtr...@gmail.com wrote: On Apr 24, 2009, at 4:02 AM, Michael Mileusnich wrote: I have formated my PC with Vista 32. I also Installed SQL Server 2008 Express. Installed Python 2.6 and pyodbc...SAME ISSUE. I would be willing to have somebody overlook my python code. With echo on everything looks like it should be INSERTING. I've written this script based on the information you have supplied. It works fine for me: from sqlalchemy import * from sqlalchemy.orm import * engine = create_engine('mssql://sprint:spr...@localhost/sprint', echo=True) metadata = MetaData(engine) Session = scoped_session(sessionmaker(bind=engine, autoflush=False, autocommit=True)) action_table = Table( 'ACTIONS', metadata, Column('ACTIONID', String(48), primary_key=True), Column('TITLE', String(128)), Column('CMDLINE', String(512)), Column('STDIN', Text), Column('STARTINDIR', String(512)), Column('PRIO', Integer), ) class action(object): def __init__(self, ACTIONID, CMDLINE): self.ACTIONID = ACTIONID self.CMDLINE = CMDLINE def __repr__(self): return action('%s', '%s') % (self.ACTIONID, self.CMDLINE) mapper(action, action_table) metadata.create_all() session = Session() new_action = action(ACTIONID = '500', CMDLINE = 'sol') session.add(new_action) session.flush() session.expunge_all() act = session.query(action).filter_by(ACTIONID='500').one() assert new_action.ACTIONID == act.ACTIONID -- Would you please try it and let me know what results you get. The following is my output. S:\sqlalchemy.git\libpython msssqlprob.py 2009-04-24 16:10:30,473 INFO sqlalchemy.engine.base.Engine.0x...6110 SELECT user_name() as user_name; 2009-04-24 16:10:30,552 INFO sqlalchemy.engine.base.Engine.0x...6110 [] 2009-04-24 16:10:30,568 INFO sqlalchemy.engine.base.Engine.0x...6110 SELECT default_schema_name FROM sys.database_principals WHERE name = ? AND type = 'S' 2009-04-24 16:10:30,568 INFO sqlalchemy.engine.base.Engine.0x...6110 [u'dbo'] 2009-04-24 16:10:30,582 INFO sqlalchemy.engine.base.Engine.0x...6110 SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TAB LE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1]. [NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLU MN_DEFAULT], [COLUMNS_1].[COLLATION_NAME] FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? 2009-04-24 16:10:30,598 INFO sqlalchemy.engine.base.Engine.0x...6110 ['ACTIONS', u'dbo'] 2009-04-24 16:10:30,598 INFO sqlalchemy.engine.base.Engine.0x...6110 CREATE TABLE [ACTIONS] ( [ACTIONID] VARCHAR(48) NOT NULL, [TITLE] VARCHAR(128) NULL, [CMDLINE] VARCHAR(512) NULL, [STDIN] TEXT NULL, [STARTINDIR] VARCHAR(512) NULL, [PRIO] INTEGER NULL, PRIMARY KEY ([ACTIONID]) ) 2009-04-24 16:10:30,630 INFO sqlalchemy.engine.base.Engine.0x...6110 () 2009-04-24 16:10:30,661 INFO sqlalchemy.engine.base.Engine.0x...6110 COMMIT 2009-04-24 16:10:30,693 INFO sqlalchemy.engine.base.Engine.0x...6110 BEGIN 2009-04-24 16:10:30,707 INFO sqlalchemy.engine.base.Engine.0x...6110 INSERT INTO [ACTIONS] ([ACTIONID], [TITLE], [CMDLIN E], [STDIN], [STARTINDIR], [PRIO]) VALUES (?, ?, ?, ?, ?, ?) 2009-04-24 16:10:30,707 INFO sqlalchemy.engine.base.Engine.0x...6110 ['500', None,
[sqlalchemy] Re: count() not working?
dont use query.count() for anything but the most simple counts of entities. if you're already creating a complex query using aggregate functions and such, query the func.count() values directly using query(func.count(whatever)), query.value(func.count(whatever)), query.values(func.count(whatever)), etc. DavidG wrote: Hi, Ok, I am doing a standard tags thingee which is many-to-many. I want to to a query so that I get the main records which have all the named keywords. To use webpage paging efficiently, I need to get a count() of the records. This does not seem to work the way I am doing it. Also, I don't think ranges are working, either, for this query (e.g., . [3:5]). The query: taglist = ['science'] tagcount = 1 page_q = SES.query(Quote) quotes_q = page_q.join(Quote.tags).filter(Tag.tagword.in_(taglist)).\ group_by(Quote.id).having(func.count(Quote.id) == tagcount) n = quotes_q.count() print count n=, n USUALLY puts out a 1, or None (both of which are incorrect). NOTE: qrecs = quotes_q.all() works! 3 tables: the relation table, plus two classes (irrelevant stuff removed): quote_tag = Table('quote_tag', meta.metadata, Column('quote_id', Integer, ForeignKey ('quote.id')), Column('tag_id', Integer, ForeignKey('tag.id')) ) class Quote(Base): __tablename__ = quote id = Column(Integer, primary_key=True) date_create = Column(DateTime) qbody = Column(UnicodeText) tags = relation(Tag, secondary=quote_tag, backref='quote') class Tag(Base): __tablename__ = tag id = Column(Integer, primary_key=True) tagword = Column(Unicode(20), nullable=False, unique=True) Any ideas what I am doing wrong? (Also, I am obviously a SQL dyslexic!) 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] Issues with InstrumentationManager
I am looking at using the InstrumentationManager to add AttributeExtension's to my objects. I am using Elixir 0.6, SQL Alchemy 0.5.3 From what I can gather, I need to add '__sa_instrumentation_manager__' all my mapped classes. Currently, with using the Elixir Metaclass, I have one class that is the root of them all, but which is not mapped. Something like the following: class ElementInstrumentationManager(InstrumentationManager): def __init__(self, class_): self.class_ = class_ def post_configure_attribute(self, class_, key, instr_attr): pass class Element(object) __metaclass__ = ElixirMeta __sa_instrumentation_manager__ = ElementInstrumentationManager It seems as soon as I add __sa_instrumentation_manager__ = ElementInstrumentationManager I begin to have problems with the session identity_map changing size. Since this is single threaded, I can only assume that I am fighting with the GC, and the WeakRef Map. (I can turn off the weak_ref identity map, but that seems to have other side-effects that I am not totally aware. That is one solution, but I want to understand the current problem before I go there, if possible) I have been reading the code in session.py, identity.py, and attribute.py.I am beginning to get the gist. Kinda. Anyone seen this before and have an obvious answer. It kinda seems that the default ClassMananger and the wrapped InstrumentationManager aren't really interchangable. --~--~-~--~~~---~--~~ 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: Issues with InstrumentationManager
i dont think theres anything intrinsic about custom InstrumentationManager that would change weakref behavior, they live at the class level and shouldn't change anything about instances. let me know what you mean by identity map changing size. nicholas wrote: I am looking at using the InstrumentationManager to add AttributeExtension's to my objects. I am using Elixir 0.6, SQL Alchemy 0.5.3 From what I can gather, I need to add '__sa_instrumentation_manager__' all my mapped classes. Currently, with using the Elixir Metaclass, I have one class that is the root of them all, but which is not mapped. Something like the following: class ElementInstrumentationManager(InstrumentationManager): def __init__(self, class_): self.class_ = class_ def post_configure_attribute(self, class_, key, instr_attr): pass class Element(object) __metaclass__ = ElixirMeta __sa_instrumentation_manager__ = ElementInstrumentationManager It seems as soon as I add __sa_instrumentation_manager__ = ElementInstrumentationManager I begin to have problems with the session identity_map changing size. Since this is single threaded, I can only assume that I am fighting with the GC, and the WeakRef Map. (I can turn off the weak_ref identity map, but that seems to have other side-effects that I am not totally aware. That is one solution, but I want to understand the current problem before I go there, if possible) I have been reading the code in session.py, identity.py, and attribute.py.I am beginning to get the gist. Kinda. Anyone seen this before and have an obvious answer. It kinda seems that the default ClassMananger and the wrapped InstrumentationManager aren't really interchangable. --~--~-~--~~~---~--~~ 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] query on a field from an expression
Hi I am new to Python. I need to produce a simple query on the key field. exists = self.session.query(BILLS).filter(ID==1) However, the process is running generically. That is the declarative table definitions are in the file schema.py and the table, field and value are determined at runtime. So what I have is the following ( I have omitted a bit of code for clarity) : class myClass(): def import_data import schema # self.tableName defined elsewhere TI = eval(schema.+self.tableName+()) exists = None for node in tupleNode.childNodes: for dataNode in node.childNodes: cValue = dataNode.data if node.tagName == self.keyField: Prob. # self.keyField is determined elsewhere exists = self.session.query(TI).filter(getattr (TI,self.keyField)==cValue) I get to the query and get the following message: Invalid column expression 'schema.BILLS object at 0x29DB7330' Any help would be appreciated. --~--~-~--~~~---~--~~ 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: pyodbc issues
On Mon, Apr 27, 2009 at 6:08 PM, Michael Trier mtr...@gmail.com wrote: Okay I'll get rid of it. On Apr 27, 2009, at 6:04 PM, Michael Bayer mike...@zzzcomputing.com wrote: I'd love to revert that change and just say MSSQL doesn't support SAVEPOINT for now. or put an enable_savepoint flag in the dialect. polaar wrote: Could this be related to this change: http://www.sqlalchemy.org/trac/changeset/5564 I encountered a similar problem with mssql/pyodbc today (the echo output showed the correct statements and params, but no changes seemed to be made in the db at all). The problem only occurred when using the orm/session layer, same statements with engine.execute seemed to work. I managed to fix it by reverting the change with the following monkeypatch: from sqlalchemy.databases.mssql import MSSQLDialect MSSQLDialect.do_begin = lambda self, conn: pass Not sure this is related though (or if this is a good solution), just thought I'd let it know in case it helps. greetings, Steven On 25 apr, 15:05, Michael Trier mtr...@gmail.com wrote: On Apr 24, 2009, at 7:50 PM, Michael Mileusnich justmike2...@gmail.com wrote: Wow..your example worked for me. Could the kwargs the issue? No. Likely you have some sort of conflict on the dbapi side. If it's possible for you to send me your actual code (mtr...@gmail.com) I'd be happy to try and figure out the issue. On Fri, Apr 24, 2009 at 3:14 PM, mtrier mtr...@gmail.com wrote: On Apr 24, 2009, at 4:02 AM, Michael Mileusnich wrote: I have formated my PC with Vista 32. I also Installed SQL Server 2008 Express. Installed Python 2.6 and pyodbc...SAME ISSUE. I would be willing to have somebody overlook my python code. With echo on everything looks like it should be INSERTING. I've written this script based on the information you have supplied. It works fine for me: from sqlalchemy import * from sqlalchemy.orm import * engine = create_engine('mssql://sprint:spr...@localhost/sprint', echo=True) metadata = MetaData(engine) Session = scoped_session(sessionmaker(bind=engine, autoflush=False, autocommit=True)) action_table = Table( 'ACTIONS', metadata, Column('ACTIONID', String(48), primary_key=True), Column('TITLE', String(128)), Column('CMDLINE', String(512)), Column('STDIN', Text), Column('STARTINDIR', String(512)), Column('PRIO', Integer), ) class action(object): def __init__(self, ACTIONID, CMDLINE): self.ACTIONID = ACTIONID self.CMDLINE = CMDLINE def __repr__(self): return action('%s', '%s') % (self.ACTIONID, self.CMDLINE) mapper(action, action_table) metadata.create_all() session = Session() new_action = action(ACTIONID = '500', CMDLINE = 'sol') session.add(new_action) session.flush() session.expunge_all() act = session.query(action).filter_by(ACTIONID='500').one() assert new_action.ACTIONID == act.ACTIONID -- Would you please try it and let me know what results you get. The following is my output. S:\sqlalchemy.git\libpython msssqlprob.py 2009-04-24 16:10:30,473 INFO sqlalchemy.engine.base.Engine.0x... 6110 SELECT user_name() as user_name; 2009-04-24 16:10:30,552 INFO sqlalchemy.engine.base.Engine.0x... 6110 [] 2009-04-24 16:10:30,568 INFO sqlalchemy.engine.base.Engine.0x... 6110 SELECT default_schema_name FROM sys.database_principals WHERE name = ? AND type = 'S' 2009-04-24 16:10:30,568 INFO sqlalchemy.engine.base.Engine.0x... 6110 [u'dbo'] 2009-04-24 16:10:30,582 INFO sqlalchemy.engine.base.Engine.0x... 6110 SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TAB LE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1]. [NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLU MN_DEFAULT], [COLUMNS_1].[COLLATION_NAME] FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? 2009-04-24 16:10:30,598 INFO sqlalchemy.engine.base.Engine.0x... 6110 ['ACTIONS', u'dbo'] 2009-04-24 16:10:30,598 INFO sqlalchemy.engine.base.Engine.0x... 6110 CREATE TABLE [ACTIONS] ( [ACTIONID] VARCHAR(48) NOT NULL, [TITLE] VARCHAR(128) NULL, [CMDLINE] VARCHAR(512) NULL, [STDIN] TEXT NULL, [STARTINDIR] VARCHAR(512) NULL, [PRIO] INTEGER NULL, PRIMARY KEY ([ACTIONID]) ) 2009-04-24 16:10:30,630 INFO sqlalchemy.engine.base.Engine.0x... 6110 () 2009-04-24 16:10:30,661 INFO sqlalchemy.engine.base.Engine.0x... 6110 COMMIT 2009-04-24 16:10:30,693 INFO sqlalchemy.engine.base.Engine.0x... 6110 BEGIN 2009-04-24
[sqlalchemy] Re: pyodbc issues
Id like to thank you for all your help however is my solution adequate to use in that I perform a metadata.create_all() in the function in my db.py python file that returns the session? On Mon, Apr 27, 2009 at 8:07 PM, Michael Mileusnich justmike2...@gmail.comwrote: On Mon, Apr 27, 2009 at 6:08 PM, Michael Trier mtr...@gmail.com wrote: Okay I'll get rid of it. On Apr 27, 2009, at 6:04 PM, Michael Bayer mike...@zzzcomputing.com wrote: I'd love to revert that change and just say MSSQL doesn't support SAVEPOINT for now. or put an enable_savepoint flag in the dialect. polaar wrote: Could this be related to this change: http://www.sqlalchemy.org/trac/changeset/5564 I encountered a similar problem with mssql/pyodbc today (the echo output showed the correct statements and params, but no changes seemed to be made in the db at all). The problem only occurred when using the orm/session layer, same statements with engine.execute seemed to work. I managed to fix it by reverting the change with the following monkeypatch: from sqlalchemy.databases.mssql import MSSQLDialect MSSQLDialect.do_begin = lambda self, conn: pass Not sure this is related though (or if this is a good solution), just thought I'd let it know in case it helps. greetings, Steven On 25 apr, 15:05, Michael Trier mtr...@gmail.com wrote: On Apr 24, 2009, at 7:50 PM, Michael Mileusnich justmike2...@gmail.com wrote: Wow..your example worked for me. Could the kwargs the issue? No. Likely you have some sort of conflict on the dbapi side. If it's possible for you to send me your actual code (mtr...@gmail.com) I'd be happy to try and figure out the issue. On Fri, Apr 24, 2009 at 3:14 PM, mtrier mtr...@gmail.com wrote: On Apr 24, 2009, at 4:02 AM, Michael Mileusnich wrote: I have formated my PC with Vista 32. I also Installed SQL Server 2008 Express. Installed Python 2.6 and pyodbc...SAME ISSUE. I would be willing to have somebody overlook my python code. With echo on everything looks like it should be INSERTING. I've written this script based on the information you have supplied. It works fine for me: from sqlalchemy import * from sqlalchemy.orm import * engine = create_engine('mssql://sprint:spr...@localhost/sprint', echo=True) metadata = MetaData(engine) Session = scoped_session(sessionmaker(bind=engine, autoflush=False, autocommit=True)) action_table = Table( 'ACTIONS', metadata, Column('ACTIONID', String(48), primary_key=True), Column('TITLE', String(128)), Column('CMDLINE', String(512)), Column('STDIN', Text), Column('STARTINDIR', String(512)), Column('PRIO', Integer), ) class action(object): def __init__(self, ACTIONID, CMDLINE): self.ACTIONID = ACTIONID self.CMDLINE = CMDLINE def __repr__(self): return action('%s', '%s') % (self.ACTIONID, self.CMDLINE) mapper(action, action_table) metadata.create_all() session = Session() new_action = action(ACTIONID = '500', CMDLINE = 'sol') session.add(new_action) session.flush() session.expunge_all() act = session.query(action).filter_by(ACTIONID='500').one() assert new_action.ACTIONID == act.ACTIONID -- Would you please try it and let me know what results you get. The following is my output. S:\sqlalchemy.git\libpython msssqlprob.py 2009-04-24 16:10:30,473 INFO sqlalchemy.engine.base.Engine.0x... 6110 SELECT user_name() as user_name; 2009-04-24 16:10:30,552 INFO sqlalchemy.engine.base.Engine.0x... 6110 [] 2009-04-24 16:10:30,568 INFO sqlalchemy.engine.base.Engine.0x... 6110 SELECT default_schema_name FROM sys.database_principals WHERE name = ? AND type = 'S' 2009-04-24 16:10:30,568 INFO sqlalchemy.engine.base.Engine.0x... 6110 [u'dbo'] 2009-04-24 16:10:30,582 INFO sqlalchemy.engine.base.Engine.0x... 6110 SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TAB LE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1]. [NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLU MN_DEFAULT], [COLUMNS_1].[COLLATION_NAME] FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? 2009-04-24 16:10:30,598 INFO sqlalchemy.engine.base.Engine.0x... 6110 ['ACTIONS', u'dbo'] 2009-04-24 16:10:30,598 INFO sqlalchemy.engine.base.Engine.0x... 6110 CREATE TABLE [ACTIONS] ( [ACTIONID] VARCHAR(48) NOT NULL, [TITLE] VARCHAR(128) NULL, [CMDLINE] VARCHAR(512) NULL, [STDIN] TEXT NULL, [STARTINDIR] VARCHAR(512) NULL, [PRIO] INTEGER NULL, PRIMARY KEY
[sqlalchemy] Re: pyodbc issues
On Apr 27, 9:10 pm, Michael Mileusnich justmike2...@gmail.com wrote: Id like to thank you for all your help however is my solution adequate to use in that I perform a metadata.create_all() in the function in my db.py python file that returns the session? On Mon, Apr 27, 2009 at 8:07 PM, Michael Mileusnich justmike2...@gmail.comwrote: On Mon, Apr 27, 2009 at 6:08 PM, Michael Trier mtr...@gmail.com wrote: Okay I'll get rid of it. On Apr 27, 2009, at 6:04 PM, Michael Bayer mike...@zzzcomputing.com wrote: I'd love to revert that change and just say MSSQL doesn't support SAVEPOINT for now. or put an enable_savepoint flag in the dialect. With r5930 I've moved all savepoint logic into savepoint oriented routines so it will not step on non-savepoint based code. Savepoint support is still very experimental in mssql. I'd appreciate as many eyes as possible on this changeset, as well as testing it out against problem code to see if this corrects our issues. At this point I'm pretty satisfied with the implementation based on the tests. I only have one failing test and after a couple of hours of comparing profile traces I'm still unable to understand why it's failing. The trace results are exactly the same. All other savepoint related tests are passing just fine. If this changeset still creates a problem (it shouldn't) let me know and we'll just revert all savepoint functionality. As of yet I still have not seen an isolated test case that illustrates the failures, so that makes me a bit uneasy in making code changes. So, if someone could produce an isolated test case that fails on the previous version of trunk that would still be very helpful to understand where the problem is. Thanks for everyone's help. Michael --~--~-~--~~~---~--~~ 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: pyodbc issues
I will try to come up with a test case later this week. On Mon, Apr 27, 2009 at 10:42 PM, mtrier mtr...@gmail.com wrote: On Apr 27, 9:10 pm, Michael Mileusnich justmike2...@gmail.com wrote: Id like to thank you for all your help however is my solution adequate to use in that I perform a metadata.create_all() in the function in my db.py python file that returns the session? On Mon, Apr 27, 2009 at 8:07 PM, Michael Mileusnich justmike2...@gmail.comwrote: On Mon, Apr 27, 2009 at 6:08 PM, Michael Trier mtr...@gmail.com wrote: Okay I'll get rid of it. On Apr 27, 2009, at 6:04 PM, Michael Bayer mike...@zzzcomputing.com wrote: I'd love to revert that change and just say MSSQL doesn't support SAVEPOINT for now. or put an enable_savepoint flag in the dialect. With r5930 I've moved all savepoint logic into savepoint oriented routines so it will not step on non-savepoint based code. Savepoint support is still very experimental in mssql. I'd appreciate as many eyes as possible on this changeset, as well as testing it out against problem code to see if this corrects our issues. At this point I'm pretty satisfied with the implementation based on the tests. I only have one failing test and after a couple of hours of comparing profile traces I'm still unable to understand why it's failing. The trace results are exactly the same. All other savepoint related tests are passing just fine. If this changeset still creates a problem (it shouldn't) let me know and we'll just revert all savepoint functionality. As of yet I still have not seen an isolated test case that illustrates the failures, so that makes me a bit uneasy in making code changes. So, if someone could produce an isolated test case that fails on the previous version of trunk that would still be very helpful to understand where the problem is. Thanks for everyone's help. Michael --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---