Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
Hi Rich, I don't like using 'name' columns as primary keys I would instead use an 'id' column and would set 'index=True' on the name column. On the primary key also define a Sequence: Column('id', Integer, Sequence('tablename_id_seq'), primary_key=True) http://sqlalchemy.readthedocs.org/en/rel_0_9/dialects/postgresql.html?highlight=sequence For name columns I would use Unicode instead of string. You might want to read the following. http://sqlalchemy.readthedocs.org/en/rel_0_9/core/metadata.html#sqlalchemy.schema.MetaData.params.naming_convention https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/NamingConventions Werner -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Problem going from 0.7.10 to 0.8.7
On Wed, Jul 30, 2014 at 3:30 AM, Mike Bernson m...@mlb.org wrote: I have started moving my code base from 0.7.10 to 0.8.7 as first step to getting to current version. (There code changes for me to get to 0.9.x where there are no code changes to run under 0.8.x) I have found a problem with a query that worked on 0.7.10 and now fails with a raise. The code is failing in unit test which means the database is sqllite. The klass is mapped class of table table_prl_paid_time_off. earlist_predecessor: datetime.date(2012, 8, 16) last: datetime.date(2013, 4, 30) account: 151 Query: query = query.filter( and_(klass.account == account, earliest_predecessor = klass.start_date = last) ).order_by('start_date') Does it work if you rewrite that as: query = query.filter( and_(klass.account == account, earliest_predecessor = klass.start_date, klass.start_date = last) ).order_by('start_date') Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How can I use the transaction to make sure data is one by on?
HI! Jonathan I use self.trans = self.connection.begin() but now i use debug lock before commit then i try to use my pgAdmin to add or update same data. and then it's sussesful to add data by pgAdmin i think that meaning my trans did not work and it maybe just can lock the same client or same code but it can't lock another client by the way my isolation_levelset self.engine = create_engine(Database.get_db_connection_string(), isolation_level=SERIALIZABLE, echo=True) can you give me some guide to fix this problem? thanks for all -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
On Wed, 30 Jul 2014, Werner wrote: I don't like using 'name' columns as primary keys I would instead use an 'id' column and would set 'index=True' on the name column. Werner, The use of natural keys (such as a vehicle VIN, the US's SSN, or equipment serial number) is prefered over an artificial, meaningless, integer key to prevent duplicate data. See any of Joe Celko's SQL books. Over the past 30 or so years I've resorted to artificial keys only when absolutely necessary. Consider a table for water chemistry constituent concentrations. There can be no more than one row for the concentration of a specified constituent from a distinct location on a given day. The only way to ensure this uniqueness is with the compound primary key of (parameter, sampdate, site). An articial 'id' column fails to prevent duplication because someone could enter the same laboratory results more than once and each row would have a unique 'id' primary key but duplicate data. In the early 1990s I was fired from a database consulting assignment with a medical resarch unit because I changed their flat-file database structure to a relational schema and turned up duplcate data for a number of patients. When you consider the effects on published analyses of data that contained duplicate entries, they had to pick a scapegoat and I was it. :-) Seriously, read Joe Celko's SQL for Smarties (I think the 4th edition is the latest) for robust DDL practices. The unicode vs string suggestion is interesting. I'm not sure of the advantages (or disadvantages) but if the change is neutral I'll run a global search-and-replace. Thanks, Rich -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
Hi Rich, On 7/30/2014 15:04, Rich Shepard wrote: On Wed, 30 Jul 2014, Werner wrote: I don't like using 'name' columns as primary keys I would instead use an 'id' column and would set 'index=True' on the name column. Werner, The use of natural keys (such as a vehicle VIN, the US's SSN, or equipment serial number) is prefered over an artificial, meaningless, integer key to prevent duplicate data. See any of Joe Celko's SQL books. His book looks very interesting. Over the past 30 or so years I've resorted to artificial keys only when absolutely necessary. Consider a table for water chemistry constituent concentrations. There can be no more than one row for the concentration of a specified constituent from a distinct location on a given day. The only way to ensure this uniqueness is with the compound primary key of (parameter, sampdate, site). An articial 'id' column fails to prevent duplication because someone could enter the same laboratory results more than once and each row would have a unique 'id' primary key but duplicate data. I can see the advantage these things, but not sure on 'agency_contacts'. In the early 1990s I was fired from a database consulting assignment with a medical resarch unit because I changed their flat-file database structure to a relational schema and turned up duplcate data for a number of patients. When you consider the effects on published analyses of data that contained duplicate entries, they had to pick a scapegoat and I was it. :-) Seriously, read Joe Celko's SQL for Smarties (I think the 4th edition is the latest) for robust DDL practices. The unicode vs string suggestion is interesting. I'm not sure of the advantages (or disadvantages) but if the change is neutral I'll run a global search-and-replace. I find it easier to deal with things like 'éüö' etc and IIUC there is no en/decoding if you use Unicode in the DB and Python. Werner -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Issue with the QueuePool limit
I have seen many posts about the QueuePool limit but I really don't know how to solve it in my code. The error is: *TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30* I start one unique session in the __init__.py like this: engine = create_engine(mysql:// + loadConfigVar(user) + : + loadConfigVar(password) + @ + loadConfigVar(host) + / + loadConfigVar(schema)) #Sets the engine to the session and the Base model class DBSession.configure(bind=engine) Then in subsequent code I use it like this: from .dbmodels import DBSession transaction.commit() rescount = DBSession.connection().execute(select resource_id,count(resource_id) as total FROM resourcestats) DBSession is defined like: DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension ())) Does the execution of an SQL increases the pool limit? Or what you reckon is going on? Thanks, Carlos. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
On Jul 30, 2014, at 9:04 AM, Rich Shepard rshep...@appl-ecosys.com wrote: On Wed, 30 Jul 2014, Werner wrote: I don't like using 'name' columns as primary keys I would instead use an 'id' column and would set 'index=True' on the name column. Werner, The use of natural keys (such as a vehicle VIN, the US's SSN, or equipment serial number) is prefered over an artificial, meaningless, integer key to prevent duplicate data. See any of Joe Celko's SQL books. Celko's books are great but surrogate integer PKs are an unavoidable practice within relational databases, they are a requirement of most DBAs I've dealt with as they perform predictably in terms of indexing and space requirements, especially considering that a PK implies the format of all the FKs that will refer to it. Typically a UNIQUE constraint is placed on the natural key to prevent dupes. In my own experience we actually tried using meaningful UUIDs as primary keys in a project some years ago and it was an utter disaster. All PK / FK indexes quadrupled in space and performance suffered terribly. This was on a Postgresql backend which should have been a better performer in a non-standard context like that (on a big ol' DB like SQL server, forget it). -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
On Wed, 30 Jul 2014, Michael Bayer wrote: Celko's books are great but surrogate integer PKs are an unavoidable practice within relational databases, they are a requirement of most DBAs I've dealt with as they perform predictably in terms of indexing and space requirements, especially considering that a PK implies the format of all the FKs that will refer to it. Typically a UNIQUE constraint is placed on the natural key to prevent dupes. Mike, That's interesting. I've not had any issues, but I've not developed many multi-user, large databases. In my own experience we actually tried using meaningful UUIDs as primary keys in a project some years ago and it was an utter disaster. All PK / FK indexes quadrupled in space and performance suffered terribly. This was on a Postgresql backend which should have been a better performer in a non-standard context like that (on a big ol' DB like SQL server, forget it). Wonder if that's been improved in later versions. So, do you recommend that surrogate keys be used in all tables, or only on those that meet certain criteria? I'm always open to learning new things and improving the work I do. Regards, Rich -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
On Jul 30, 2014, at 12:28 PM, Rich Shepard rshep...@appl-ecosys.com wrote: On Wed, 30 Jul 2014, Michael Bayer wrote: Celko's books are great but surrogate integer PKs are an unavoidable practice within relational databases, they are a requirement of most DBAs I've dealt with as they perform predictably in terms of indexing and space requirements, especially considering that a PK implies the format of all the FKs that will refer to it. Typically a UNIQUE constraint is placed on the natural key to prevent dupes. Mike, That's interesting. I've not had any issues, but I've not developed many multi-user, large databases. In my own experience we actually tried using meaningful UUIDs as primary keys in a project some years ago and it was an utter disaster. All PK / FK indexes quadrupled in space and performance suffered terribly. This was on a Postgresql backend which should have been a better performer in a non-standard context like that (on a big ol' DB like SQL server, forget it). Wonder if that's been improved in later versions. So, do you recommend that surrogate keys be used in all tables, or only on those that meet certain criteria? I'm always open to learning new things and improving the work I do. I find surrogate PKs very easy to deal with in that I never have to worry about UPDATE CASCADE situations, some of which are thorny enough that even SQLAlchemy doesn't have a 100% approach for every situation. With that, plus the predictable indexing, I'm always going to use them. But, I think there's a fair degree of preference still here. With natural PKs, the biggest issue is how much space indexes are going to take up considering that everything that FKs to that PK has to mirror out those same columns. Just on those occasions I've had to deal with the Trac database, which uses natural primary keys, for every table it's a mystery how to join things, b.c. every table has a different notion of primary. But then again they also have pretty inconsistent conventions. I think natural PKs just complicate the natural plumbing of being able to wire rows together, they are of course more pure but in relational DBs we are already seriously impure compared to Codd, RDBMS draws from relational algebra to the point that is practical and that's about it. You of course will always have a natural key and hopefully adequate constraints on it. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
On Wed, 30 Jul 2014, Michael Bayer wrote: With that, plus the predictable indexing, I'm always going to use them. But, I think there's a fair degree of preference still here. With natural PKs, the biggest issue is how much space indexes are going to take up considering that everything that FKs to that PK has to mirror out those same columns. Mike, Fair enough. In the current application I'm developing most tables will have comparatively few rows ( 500 in most cases). The data tables can easily have 100,000 rows, but the only table that would relate to those is the 'changed' table which records what, when, why, and by whom a change was made. This is for audit trail purposes. Thanks for your insights, Rich -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
On Wed, 30 Jul 2014, Michael Bayer wrote: Typically a UNIQUE constraint is placed on the natural key to prevent dupes. I can see this when the natural key is a single column, but wonder how a compound natural key is represented if a serial integer is used as the surrogate 'id' key. For example, class Changed_Data(Base): __tablename__ = changed_data id = Column(Integer, primary_key = True) which_table = Column(unicode(32), nullable = False) which_attrib = Column(unicode(32), nullable = False) when_changed = Column(Timestamp, nullable = False) curr_value = Column(Unicode(32), nullable = False) new_value = Column(Unicode(32), nullable = False) changed_by = Column(Unicode(32), nullable = False) reason = Column(Text) The postgres schema specifies the primary key as (which_table, which_attribute, when_changed). If I make each of those columns Unique is it the set of columns that is unique or each individual column? TIA, Rich -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
You make a single explicit UniqueConstraint object that specifies all three. Sent from my iPhone On Jul 30, 2014, at 2:30 PM, Rich Shepard rshep...@appl-ecosys.com wrote: On Wed, 30 Jul 2014, Michael Bayer wrote: Typically a UNIQUE constraint is placed on the natural key to prevent dupes. I can see this when the natural key is a single column, but wonder how a compound natural key is represented if a serial integer is used as the surrogate 'id' key. For example, class Changed_Data(Base): __tablename__ = changed_data id = Column(Integer, primary_key = True) which_table = Column(unicode(32), nullable = False) which_attrib = Column(unicode(32), nullable = False) when_changed = Column(Timestamp, nullable = False) curr_value = Column(Unicode(32), nullable = False) new_value = Column(Unicode(32), nullable = False) changed_by = Column(Unicode(32), nullable = False) reason = Column(Text) The postgres schema specifies the primary key as (which_table, which_attribute, when_changed). If I make each of those columns Unique is it the set of columns that is unique or each individual column? TIA, Rich -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
On Wed, 30 Jul 2014, Michael Bayer wrote: You make a single explicit UniqueConstraint object that specifies all three. Thanks, Mike. I missed that in the docs. Next question will appear only after a thorough search of your book. Rich -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] referencing a `label` in a group by clause ?
Revisiting this question, with coalesce. The target sql is: SELECT letter ,COALESCE(cache_counted, 0) AS counted FROM letters ORDER BY counted DESC The closest I can get to it is this: dbSession.query( sqlalchemy.func.count( model.Letters.letter ), sqlalchemy.func.coalesce( model.Letters.cache_counted, 0).label('counted'), ).order_by( sqlalchemy.func.coalesce( model.Letters.cache_counted, 0).desc ) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] referencing a `label` in a group by clause ?
order_by('counted') ? there's a feature for 1.0 that will actually search out counted when you pass as a string and match it up too. On Jul 30, 2014, at 3:35 PM, Jonathan Vanasco jonat...@findmeon.com wrote: Revisiting this question, with coalesce. The target sql is: SELECT letter ,COALESCE(cache_counted, 0) AS counted FROM letters ORDER BY counted DESC The closest I can get to it is this: dbSession.query( sqlalchemy.func.count( model.Letters.letter ), sqlalchemy.func.coalesce( model.Letters.cache_counted, 0).label('counted'), ).order_by( sqlalchemy.func.coalesce( model.Letters.cache_counted, 0).desc ) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Single and Double Quotes
Is there a SQLAlchemy rule defining when single and double quotes are to be used? I see examples of both in a class, such as this example on page 103 of the 0.9.7 doc: class Entry(Base): __tablename__ = 'entry' entry_id = Column(Integer, primary_key=True) widget_id = Column(Integer, ForeignKey('widget.widget_id')) name = Column(String(50)) __table_args__ = ( UniqueConstraint(entry_id, widget_id), ) In there the only double quotes are the two arguments to UniqueConstraint so I assume there's a general rule that I've missed reading. TIA, Rich -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Single and Double Quotes
within python, single and double quotes are interchangeable and to the extent you see them used inconsistently is due to inconsistency on the part of myself and in some cases other developers who have written these docs. On Jul 30, 2014, at 4:44 PM, Rich Shepard rshep...@appl-ecosys.com wrote: Is there a SQLAlchemy rule defining when single and double quotes are to be used? I see examples of both in a class, such as this example on page 103 of the 0.9.7 doc: class Entry(Base): __tablename__ = 'entry' entry_id = Column(Integer, primary_key=True) widget_id = Column(Integer, ForeignKey('widget.widget_id')) name = Column(String(50)) __table_args__ = ( UniqueConstraint(entry_id, widget_id), ) In there the only double quotes are the two arguments to UniqueConstraint so I assume there's a general rule that I've missed reading. TIA, Rich -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Single and Double Quotes
On Wed, 30 Jul 2014, Michael Bayer wrote: within python, single and double quotes are interchangeable and to the extent you see them used inconsistently is due to inconsistency on the part of myself and in some cases other developers who have written these docs. Mike, I thought that might be the case, but wanted to make sure before I stuck my foot in my mouth. Thanks for clarifying, Rich -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How can I use the transaction to make sure data is one by on?
On 07/30/2014 05:59 AM, 'Frank Liou' via sqlalchemy wrote: HI! Jonathan I use self.trans = self.connection.begin() but now i use debug lock before commit I'm afraid I don't know what that means. then i try to use my pgAdmin to add or update same data. At least that implies that you're using PostgreSQL. I don't think you'd mentioned with RDBMS you're using. and then it's sussesful to add data by pgAdmin i think that meaning my trans did not work and it maybe just can lock the same client or same code but it can't lock another client Again, I'm not really sure what you mean. by the way my isolation_levelset self.engine = create_engine(Database.get_db_connection_string(), isolation_level=SERIALIZABLE, echo=True) can you give me some guide to fix this problem? The best documentation I'm aware of is the official PostgreSQL documentation: URL:http://www.postgresql.org/docs/9.3/static/transaction-iso.html -- Jonathan Rogers -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] mssql queries with accented column names fails
FYI, I've found the corresponding pymssql bug. I hope for the fix being included in their next minor release. https://github.com/pymssql/pymssql/issues/185 2014. július 27., vasárnap 16:25:23 UTC+2 időpontban Michael Bayer a következőt írta: On Jul 27, 2014, at 8:58 AM, Viktor Nagy vikto...@gmail.com javascript: wrote: /Users/viktornagy/.virtualenvs/odoo/lib/python2.7/site-packages/pymssql.so in pymssql.Cursor.execute (pymssql.c:6347)() OperationalError: (OperationalError) (105, Unclosed quotation mark after the character string 'BiztKod1_1)s'.DB-Lib error message 105, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 102, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n) 'SELECT [BIZTOSIT].[BiztKod] AS [BIZTOSIT_BiztKod], [BIZTOSIT].[Fi\xc3\xb3kVezet\xc5\x91] AS [BIZTOSIT_Fi\xc3\xb3kVezet\xc5\x91] \nFROM [BIZTOSIT] \nWHERE [BIZTOSIT].[BiztKod] = %(BiztKod_1)s' {'BiztKod_1': 1} Nota bene, that the Table object was built with reflecting the existing table. Do you have any idea how to allow querying with the accented columns being included? the query looks fine, unless you can point out where it's incorrect, I’m not seeing this unclosed quotation mark it refers to. So this may perhaps be a pymssql issue? Does the query above run directly in a pymssql cursor, and if not, why not ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] mssql queries with accented column names fails
that’s great news! On Jul 30, 2014, at 6:11 PM, Viktor Nagy viktor.n...@gmail.com wrote: FYI, I've found the corresponding pymssql bug. I hope for the fix being included in their next minor release. https://github.com/pymssql/pymssql/issues/185 2014. július 27., vasárnap 16:25:23 UTC+2 időpontban Michael Bayer a következőt írta: On Jul 27, 2014, at 8:58 AM, Viktor Nagy vikto...@gmail.com wrote: /Users/viktornagy/.virtualenvs/odoo/lib/python2.7/site-packages/pymssql.so in pymssql.Cursor.execute (pymssql.c:6347)() OperationalError: (OperationalError) (105, Unclosed quotation mark after the character string 'BiztKod1_1)s'.DB-Lib error message 105, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 102, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n) 'SELECT [BIZTOSIT].[BiztKod] AS [BIZTOSIT_BiztKod], [BIZTOSIT].[Fi\xc3\xb3kVezet\xc5\x91] AS [BIZTOSIT_Fi\xc3\xb3kVezet\xc5\x91] \nFROM [BIZTOSIT] \nWHERE [BIZTOSIT].[BiztKod] = %(BiztKod_1)s' {'BiztKod_1': 1} Nota bene, that the Table object was built with reflecting the existing table. Do you have any idea how to allow querying with the accented columns being included? the query looks fine, unless you can point out where it's incorrect, I’m not seeing this unclosed quotation mark it refers to. So this may perhaps be a pymssql issue? Does the query above run directly in a pymssql cursor, and if not, why not ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] referencing a `label` in a group by clause ?
On Wednesday, July 30, 2014 3:49:15 PM UTC-4, Michael Bayer wrote: order_by(‘counted’) That generates: ORDER BY counted I can't figure out a way to apply `.desc()` (or specify `.asc()`) with that : ORDER BY counted DESC I tried going though the docs and constructing various objects that might allow me to do something like: .order_by( foo('counted').desc() ) but I couldn't find the right `foo`, if any. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] vacation...
hey folks - I'm on vacation from thursday tomorrow through next thursday, so folks please hold down the fort! I won't be off the grid but I might not be able to get to my email as regularly. - mike -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] vacation...
On Wed, 30 Jul 2014, Michael Bayer wrote: I'm on vacation from thursday tomorrow through next thursday, so folks please hold down the fort! I won't be off the grid but I might not be able to get to my email as regularly. mike, Have fun, travel safely, and don't worry about all of us. Rich -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] referencing a `label` in a group by clause ?
On 30 Jul 2014, at 23:32, Jonathan Vanasco jonat...@findmeon.com wrote: On Wednesday, July 30, 2014 3:49:15 PM UTC-4, Michael Bayer wrote: order_by('counted') That generates: ORDER BY counted I can't figure out a way to apply `.desc()` (or specify `.asc()`) with that : ORDER BY counted DESC I tried going though the docs and constructing various objects that might allow me to do something like: .order_by( foo('counted').desc() ) but I couldn't find the right `foo`, if any. I haven't followed the whole thread, but can't you just use: .order_by(sa.desc('counted')) http://docs.sqlalchemy.org/en/rel_0_9/core/sqlelement.html#sqlalchemy.sql.expression.desc) Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: vacation...
Go off the grid! You deserve it! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] referencing a `label` in a group by clause ?
THAT IS EXACTLY WHAT I NEEDED !!! I seriously spent a very stressful hour today combing through the docs and trying to construct something that would let me apply a `.desc()` ; I never thought of looking for a desc function to apply. Thank you so much! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.