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.
[sqlalchemy] mssql queries with accented column names fails
I'm connecting to a MSSQL server using pymssql+freetds. I could connect
well, and managed to reflect the original tables. In order to map them to
python object's, I'm adding ascii column keys, and then simply declare a
declarative ORM:
biztosito_columns_mapping = {
'BiztKod': 'pk', # this is the primary key column
'BIZTNEVE': 'name',
u'Fi\xf3kVezet\u0151': 'Fiokvezeto',
}
@event.listens_for(Table, column_reflect)
def column_reflect(inspector, table, column_info):
When the table is reflected, we can change the column names from
accented characters.
if table.name == 'BIZTOSIT':
column_info['key'] =
biztosito_columns_mapping.get(column_info['name'], column_info['name'])
def inspect_biztositok():
logger.debug(inspecting biztositok table)
return Table(BIZTOSIT, Base.metadata, autoload=True)
BiztositoTable = inspect_biztositok()
class Biztosito(Base):
__table__ = BiztositoTable
Until now, all works fine. Now, I would like to query this DB.
In [25]: session.query(Biztosito.pk).filter(Biztosito.pk==1).all()
Out[25]: [(1)]
In [26]: session.query(Biztosito.pk,
Biztosito.Fiokvezeto).filter(Biztosito.pk==1).all()
---
OperationalError Traceback (most recent call last)
ipython-input-26-bb237bb52d92 in module()
1 session.query(Biztosito.pk,
Biztosito.Fiokvezeto).filter(Biztosito.pk==1).all()
/Users/viktornagy/.virtualenvs/odoo/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in all(self)
2298
2299
- 2300 return list(self)
2301
2302 @_generative(_no_clauseelement_condition)
/Users/viktornagy/.virtualenvs/odoo/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in __iter__(self)
2410 if self._autoflush and not self._populate_existing:
2411 self.session._autoflush()
- 2412 return self._execute_and_instances(context)
2413
2414 def _connection_from_session(self, **kw):
/Users/viktornagy/.virtualenvs/odoo/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc
in _execute_and_instances(self, querycontext)
2425 close_with_result=True)
2426
- 2427 result = conn.execute(querycontext.statement, self._params)
2428 return loading.instances(self, result, querycontext)
2429
/Users/viktornagy/.virtualenvs/odoo/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc
in execute(self, object, *multiparams, **params)
727 type(object))
728 else:
-- 729 return meth(self, multiparams, params)
730
731 def _execute_function(self, func, multiparams, params):
/Users/viktornagy/.virtualenvs/odoo/lib/python2.7/site-packages/sqlalchemy/sql/elements.pyc
in _execute_on_connection(self, connection, multiparams, params)
319
320 def _execute_on_connection(self, connection, multiparams,
params):
-- 321 return connection._execute_clauseelement(self, multiparams,
params)
322
323 def unique_params(self, *optionaldict, **kwargs):
/Users/viktornagy/.virtualenvs/odoo/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc
in _execute_clauseelement(self, elem, multiparams, params)
824 compiled_sql,
825 distilled_params,
-- 826 compiled_sql, distilled_params
827 )
828 if self._has_events or self.engine._has_events:
/Users/viktornagy/.virtualenvs/odoo/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc
in _execute_context(self, dialect, constructor, statement, parameters,
*args)
956 parameters,
957 cursor,
-- 958 context)
959
960 if self._has_events or self.engine._has_events:
/Users/viktornagy/.virtualenvs/odoo/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc
in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
1158 util.raise_from_cause(
1159 sqlalchemy_exception,
- 1160 exc_info
1161 )
1162 else:
/Users/viktornagy/.virtualenvs/odoo/lib/python2.7/site-packages/sqlalchemy/util/compat.pyc
in raise_from_cause(exception, exc_info)
197 exc_info = sys.exc_info()
198 exc_type, exc_value, exc_tb = exc_info
-- 199 reraise(type(exception), exception, tb=exc_tb)
200
201 if py3k:
/Users/viktornagy/.virtualenvs/odoo/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc
in _execute_context(self, dialect, constructor, statement, parameters,
*args)
949 statement,
950 parameters,
-- 951 context)
952 except Exception as e:
953 self._handle_dbapi_exception(
/Users/viktornagy/.virtualenvs/odoo/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc
in
[sqlalchemy] OperationalError: (OperationalError) database is locked
Hi,
I'm trying to load my database with fixtures, but it fails with database
is locked error.
The setup is the following:
1. I set up database connection, sessions and create the tables
2. I run a post setup script to add some relationships and other data that
were not known before
3. I load the fixtures
I've tracked down that the database is locked error comes from my post
setup scripts. (If I leave it out, the fixtures don't use those tables)
The blocking call is:
PUBLIC_GROUP_ID = 0
def get_public_group(settings):
global PUBLIC_GROUP_ID
if PUBLIC_GROUP_ID:
return PUBLIC_GROUP_ID
group_name = settings.get('messageboard.public_group_name', 'public')
group = GroupModel.get_or_create(name=group_name)
PUBLIC_GROUP_ID = group.id
return PUBLIC_GROUP_ID
where get_pr_create is a simple method like in Django's ORM, where the
create part is used (simple object creation, session.add, session.flush).
I've created a more detailed, but still stripped down version of my full
setup at: http://pastebin.com/HAseVtqB
Could someone help me out? I'm running my tests with sqlite, but I guess
this should be database independent anyway.
Viktor
--
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] Integer as Minutes + DateTime
Hi, I have the following column_property definition time = Column(DateTime, nullable=False) end_time = column_property( select([Event.duration + time]).where(Event.id==event_id) ) where Event.duration stands for minutes and is an Integer, while time is a DateTime clearly, the select statement is not what I would like to have, but something casted is there a way to define end_time properly with these two arguments? Viktor -- 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] quasi-run time ForeignKey definition
hi,
I would like to build a reusabe forum application. For the sake of
simplicity we need two tables Owner and Thread. There is a ForeignKey from
Thread to Owner. As reusability goes, the Owner table is given in a
configuration argument, thus it's unknown when the code is written.
Using pyramid, something along the following lines shows my setup:
in models.py
def get_owner_table(settings):
owner_table = settings.get('messageboard.owner_table')
resolver = DottedNameResolver(owner_table.split('.')[0])
return resolver.resolve(owner_table)
class Thread(Base):
__tablename__ = 'threads'
id = Column(Integer, primary_key=True)
fk = Column(Integer, ForeignKey('OwnerTable.id'))
name = Column(Text, unique=True)
value = Column(Integer)
in some other file
from models import Base
class Owner(Base):
__tablename__ = 'owners'
id = Column(Integer, primary_key=True)
name = Column(Text, unique=True)
value = Column(Integer)
in main file:
def main(argv=sys.argv):
if len(argv) != 2:
usage(argv)
config_uri = argv[1]
setup_logging(config_uri)
settings = get_appsettings(config_uri)
engine = engine_from_config(settings, 'sqlalchemy.')
models.OwnerTable = models.get_owner_table(settings)
models.DBSession.configure(bind=engine)
models.Base.metadata.create_all(engine)
Unfortunately, my main function never succeeds with
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column '
models.fk' could not find table 'OwnerTable' with which to generate a
foreign key to target column 'id'
I've managed to accomplish that, if I change
fk = Column(Integer, ForeignKey('OwnerTable.id'))
to
fk = Column(Integer, ForeignKey('owners.id'))
with this fk column the main method runs fine, and the database structure
is properly created. But as it would restrict table names, it does not
fulfill my needs.
How can I make SQLAlchemy aware of models.OwnerTable?
thanks, Viktor
--
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] restrict columns in outerjoin
Hi, I'm building a nice query of polymorphically related tables similar to the one in the docs session.query(Employee.name).\ outerjoin((engineer, engineer.c.employee_id==Employee.employee_id)).\ outerjoin((manager, manager.c.employee_id==Employee.employee_id)).\ filter(or_(Engineer.engineer_info=='w', Manager.manager_data=='q')) now, my problem is that this includes all the columns of engineer and manager into the subquery SELECT board_owners.id AS board_owners_id FROM board_owners LEFT OUTER JOIN (SELECT board_owners.id AS board_owners_id, board_owners.type AS board_owners_type, mb_user_owners.user_id AS mb_user_owners_user_id, mb_user_owners.board_owner_id AS mb_user_owners_board_owner_id FROM board_owners JOIN mb_user_owners ON board_owners.id = mb_user_owners.board_owner_id) AS anon_1 ON anon_1.mb_user_owners_board_owner_id = board_owners.id how can I specify to ask for the name (or a specific column present everywhere) only from every table? (Actually, I don't need any data from the joined tables, I need them only for filtering the results from the Employee table) Viktor -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] restrict columns in outerjoin
thanks, specifying the tables instead of the declarative classes solved my problem. On Mon, Nov 21, 2011 at 4:23 AM, Michael Bayer mike...@zzzcomputing.comwrote: On Nov 20, 2011, at 10:09 PM, Viktor Nagy wrote: Hi, I'm building a nice query of polymorphically related tables similar to the one in the docs session.query(Employee.name).\ outerjoin((engineer, engineer.c.employee_id==Employee.employee_id)).\ outerjoin((manager, manager.c.employee_id==Employee.employee_id)).\ filter(or_(Engineer.engineer_info=='w', Manager.manager_data=='q')) now, my problem is that this includes all the columns of engineer and manager into the subquery The query you picture above wouldn't quite produce the subquery in the left outer join, so I'd assume thats capital Engineer and Manager you're stating as targets. The subquery is because you're specifying mapped classes and not Table objects. In a joined inheritance scenario, engineer here would give you exactly the columns of the engineer table alone; while Engineer means the join of employee to engineer as a subquery. When you want exact columns and full control over what's rendered, use the Table objects exclusively. The other approach I'd normally suggest is to use with_polymorphic(), though apparently it's not picking up on a single column in the query() right now, which is unfortunate. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] tables locked in MSSQL, unlocked in PostgreSQL
hi, we've developed an app with sqlalchemy that should run both with MSSQL and PostgreSQL (the client is moving from MS to Postgres). The application runs fine on Postgres, but the tables are locked when we run it with MSSQL, thus only one application instance can be used with the database. There is one session instance defined that we use (almost) everywhere, and this session runs session.commit at the end of code blocks when necessary. Is this the proposed way to do it? Do you have any idea about the problem? thanks, Viktor -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] how to build up an or_ clause programmatically?
Hi, I would like to build an or_ statement using a for cycle, something like the following: employeetask_or = or_(False) # to exclude everything not satisfied by later appended criteria, is this needed or empty or_() is fine? for period in periods.all(): period.close(session, machine_id) employeetask_or.append(and_(EmployeeTasks.work_id==period.operation.work_id, EmployeeTasks.is_reparation==period.operation.is_reparation)) qry = session.query(EmployeeTasks).filter(and_(employeetask_or, EmployeeTasks.paused==None, EmployeeTasks.finished==0)) of course(?) this does not work, and I don't really have any ideas after inspecting or_'s methods. Could someone give me a hand, please? (this inefficient for cycle is needed for a very efficient bulk update) Viktor -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: how to build up an or_ clause programmatically?
I've just found http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg14672.html what answers my question. sorry for bothering you On Fri, Jan 28, 2011 at 9:36 PM, Viktor Nagy viktor.n...@toolpart.huwrote: Hi, I would like to build an or_ statement using a for cycle, something like the following: employeetask_or = or_(False) # to exclude everything not satisfied by later appended criteria, is this needed or empty or_() is fine? for period in periods.all(): period.close(session, machine_id) employeetask_or.append(and_(EmployeeTasks.work_id==period.operation.work_id, EmployeeTasks.is_reparation==period.operation.is_reparation)) qry = session.query(EmployeeTasks).filter(and_(employeetask_or, EmployeeTasks.paused==None, EmployeeTasks.finished==0)) of course(?) this does not work, and I don't really have any ideas after inspecting or_'s methods. Could someone give me a hand, please? (this inefficient for cycle is needed for a very efficient bulk update) Viktor -- 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] interdatabase joins with mssql
hi, I have an app that should run both on postgresql and mssql. (The client moves its applications one-by-one to postgres.) Some parts of the app query several databases, in Postgres I've solved this problem using schemas, but I have problems with mssql. I use Session.configure(binds=db_bindings) to get several schemas/databases in the session. finally I call: session.query(metamob.Machines.id, metamob.Machines.name).join((power. MachineSignalSources, power.MachineSignalSources.machine_id== metamob.Machines.id )).all() in postgresql this generates a proper join between the two schemas, but in mssql I get a simple one-db join (cbomasini is metamob.Machines, and idautomatuilaje is power.MachineSignalSources: SELECT cbomasini.[ID] AS [cbomasini_ID], cbomasini.[Den umire] AS [cbomasini_Denumire] \nFROM cbomasini JOIN idautomatutilaje ON idautom atutilaje.[IDUtilaj] = cbomasini.[ID] I'm using the pyodbc drivers for mssql. Viktor -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] how to call a function with multiple engines attached to the session
hi, I have function defined in one of my postgresql schemas, and my Session is bound to tables in both schemas. How can I run in this case a simple function call like the following: select public.fnPunctajAngajatPeZI(23, cast(now() as timestamp)); I've tried session.execute, but - as the docs state - a mapper should be specified. What does this mean in my case? thanks, Viktor -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] how to call a function with multiple engines attached to the session
On Sat, Dec 25, 2010 at 6:59 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Dec 25, 2010, at 11:02 AM, Viktor Nagy wrote: hi, I have function defined in one of my postgresql schemas, and my Session is bound to tables in both schemas. How can I run in this case a simple function call like the following: select public.fnPunctajAngajatPeZI(23, cast(now() as timestamp)); I've tried session.execute, but - as the docs state - a mapper should be specified. What does this mean in my case? The session would like to know which engine it should be using, if it isn't just using session.bind. So you'd pass a mapper or SQL clause that would point it to one engine or the other. We'll be adding bind to session.execute() and session.connection() soon so that you can just pass the actual engine you'd like to use. sorry for my stupid question, but how can I get a mapper or an SQL clause that points to an engine? I have declaratively defined tables, and as this is a function call, no table could be mapped to its results. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com . For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- ToolPart Team Ltd 6725 Szeged, Boldogasszony sgt. 65. Info: +36 30 430 4971 Tel.: +36 62 469 321 Fax: +36 62 426 738 E-mail: toolp...@toolpart.hu Web: www.toolpart.hu -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] how to call a function with multiple engines attached to the session
thanks for your previous post, I've found the mapper in MyDeclarativeTable.__mapper__ On Sat, Dec 25, 2010 at 10:05 PM, Viktor Nagy viktor.n...@toolpart.huwrote: On Sat, Dec 25, 2010 at 6:59 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Dec 25, 2010, at 11:02 AM, Viktor Nagy wrote: hi, I have function defined in one of my postgresql schemas, and my Session is bound to tables in both schemas. How can I run in this case a simple function call like the following: select public.fnPunctajAngajatPeZI(23, cast(now() as timestamp)); I've tried session.execute, but - as the docs state - a mapper should be specified. What does this mean in my case? The session would like to know which engine it should be using, if it isn't just using session.bind. So you'd pass a mapper or SQL clause that would point it to one engine or the other. We'll be adding bind to session.execute() and session.connection() soon so that you can just pass the actual engine you'd like to use. sorry for my stupid question, but how can I get a mapper or an SQL clause that points to an engine? I have declaratively defined tables, and as this is a function call, no table could be mapped to its results. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com . For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- ToolPart Team Ltd 6725 Szeged, Boldogasszony sgt. 65. Info: +36 30 430 4971 Tel.: +36 62 469 321 Fax: +36 62 426 738 E-mail: toolp...@toolpart.hu Web: www.toolpart.hu -- ToolPart Team Ltd 6725 Szeged, Boldogasszony sgt. 65. Info: +36 30 430 4971 Tel.: +36 62 469 321 Fax: +36 62 426 738 E-mail: toolp...@toolpart.hu Web: www.toolpart.hu -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
