[sqlalchemy] Re: rollback not working
Thanks a lot for bearing with me. explicit execution - uses a Connection, but will autocommit conn = engine.connect() conn.execute(insert into table (a, b, c) values (1, 2, 3)) I still want a clarification about the above case. When we use explicit execution like the above, will each conn.execute() statement be in a transaction or not? -- 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 can I do an eager loading when joining multiple tables?
Hi list, I defined two tables, Users, Orders and Items, where Orders has a foreign key user_id depending on User.id and Items has a foreign key order_id on Order.id. I'd like to do eager loading when joining these three tables by: session.query(Users).join(Users.orders, Orders.items).options(contains_eager(User.orders, Orders.items)).all() However it turns out that it is still doing lazy loading, not eager loading. Am I doing something wrong? Thanks Joel -- 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: select distinct on a relation to an entity with composite primary key
thank you very much michael, this is some kind of mind spinning sqla expression !! -- 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] Clearing an already defined mapper
Hi everyone, in my script I have to deal with a table whose number of columns can change at runtime. Since I have no information about the structure I will have to handle at a given moment, I would need to change dynamically the mapping during the program. If I try to re-map the table I obviously get the following error: sqlalchemy.exc.ArgumentError: Class 'class 'MyMappingClassa'' already has a primary mapper defined. Use non_primary=True to create a non primary Mapper. clear_mappers() will remove *all* current mappers from all classes. On the other hand clear_mappers removes all the mappers that has been defined on the classes; furthermore, as the documentation says: clear_mappers` is *not* for normal use, as there is literally no valid usage for it outside of very specific testing scenarios Can anyone give a hint to handle correctly this situation or point me out if there is a better approach to face it? Thanks in advance. -- 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] One commit per request
Dear all, I'm using the web.py framework, the documentation of which recommends to use SqlAlchemy this way : http://webpy.org/cookbook/sqlalchemy Basically a load hook is loaded before the actual request is processed. However : 1) They recommend to bind scoped_session at every request, which is, I think, unnecessary 2) They trigger a *commit *at the end of every request (in the finally block), but I would like to adjust this behavior. I know orm.is_modified requires an instance, so do I have some flag in the scoped_session which could let me know if *some* instance has been modified ? Thanks a lot ! Franck -- 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] Re: rollback not working
On Jan 24, 2011, at 5:46 AM, bool wrote: Thanks a lot for bearing with me. explicit execution - uses a Connection, but will autocommit conn = engine.connect() conn.execute(insert into table (a, b, c) values (1, 2, 3)) I still want a clarification about the above case. When we use explicit execution like the above, will each conn.execute() statement be in a transaction or not? Everything is in a transaction. The above transaction autocommits upon each INSERT/UPDATE/DELETE statement. -- 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.
Re: [sqlalchemy] Clearing an already defined mapper
On Jan 24, 2011, at 6:55 AM, Massi wrote: Hi everyone, in my script I have to deal with a table whose number of columns can change at runtime. Since I have no information about the structure I will have to handle at a given moment, I would need to change dynamically the mapping during the program. If I try to re-map the table I obviously get the following error: sqlalchemy.exc.ArgumentError: Class 'class 'MyMappingClassa'' already has a primary mapper defined. Use non_primary=True to create a non primary Mapper. clear_mappers() will remove *all* current mappers from all classes. On the other hand clear_mappers removes all the mappers that has been defined on the classes; furthermore, as the documentation says: clear_mappers` is *not* for normal use, as there is literally no valid usage for it outside of very specific testing scenarios Can anyone give a hint to handle correctly this situation or point me out if there is a better approach to face it? First I'll note that if the use case here is a table where some human or process is constantly adding/dropping columns and the application needs to just keep running and auto-adjusting to the schema changes, that is completely the wrong way to use relational databases. Its like renovating your kitchen as you make dinner at the same time. Secondly, assuming that use case, you want to create the classes themselves on the fly. Use the recipe at entity name: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName -- 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] One commit per request
On Jan 24, 2011, at 7:17 AM, Franck wrote: Dear all, I'm using the web.py framework, the documentation of which recommends to use SqlAlchemy this way : http://webpy.org/cookbook/sqlalchemy Basically a load hook is loaded before the actual request is processed. However : 1) They recommend to bind scoped_session at every request, which is, I think, unnecessary i would agree, the purpose of scoped_session is to be a global thread local. Their placing it's construction into a per-request context is incorrect. In their example, you only need to say Session(bind), i.e. call the Session constructor, since they are generating it local to the application's contextual object. 2) They trigger a commit at the end of every request (in the finally block), but I would like to adjust this behavior. I do a remove() at the end of the request, if their example used a straight Session, it should call close(), in their handler code. If a specific controller method is receiving a POST and is modifying data, I explicitly put Session.commit() at the end within that method. Or you can use a decorator on controller methods, like @commits.I think your app should have clear demarcations over which methods intend to commit and which don't so I wouldn't rely upon testing. That said its easy to test the session's state, just do a bool on session.new, dirty, and deleted: http://www.sqlalchemy.org/docs/orm/session.html#session-attributes I know orm.is_modified requires an instance, so do I have some flag in the scoped_session which could let me know if some instance has been modified ? Thanks a lot ! Franck -- 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.
Re: [sqlalchemy] How can I do an eager loading when joining multiple tables?
On Jan 24, 2011, at 5:54 AM, Joel Zhou wrote: Hi list, I defined two tables, Users, Orders and Items, where Orders has a foreign key user_id depending on User.id and Items has a foreign key order_id on Order.id. I'd like to do eager loading when joining these three tables by: session.query(Users).join(Users.orders, Orders.items).options(contains_eager(User.orders, Orders.items)).all() However it turns out that it is still doing lazy loading, not eager loading. Am I doing something wrong? yes you need contains_eager() for both paths, contains_eager(User.orders), contains_eager(User.orders, Orders.items). Though I can see how this might be improved since the use case of contains_eager() on a non-eager parent is almost impossible. I put ticket #2032 against version 0.7.0 to take a look at this. Thanks Joel -- 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.
Re: [sqlalchemy] Adjacency List Relationship in a Child Class
yeah always send us the error message with these since otherwise we have to
replicate it , as this mapping looks entirely fine. it asks for a primaryjoin,
and additionally the id column, until 0.7.0 is released where this has been
fixed, is in terms of Node not Department so you have to rename it:
class Department(Node):
__tablename__ = 'department'
__mapper_args__ = {'polymorphic_identity': 'department'}
dept_id = Column(id, Integer, ForeignKey('node.id'), primary_key=True)
description = Column(Text)
parent_department_id = Column(Integer,ForeignKey('department.id'))
parent_department = relationship(Department,
backref=backref(subdepartments),
primaryjoin=dept_id==parent_department_id,
remote_side=dept_id)
On Jan 23, 2011, at 8:55 PM, Michael Naber wrote:
I'm still having trouble getting an adjacency list relationship to
work within a child class (Department class in this example), and am
hoping someone might offer some insight:
class Node(Base):
__tablename__ = 'node'
id = Column(Integer, primary_key=True)
name = Column(String(100))
discriminator = Column('discriminator', String(50))
class Department(Node):
__tablename__ = 'department'
__mapper_args__ = {'polymorphic_identity': 'department'}
id = Column(Integer, ForeignKey('node.id'), primary_key=True)
description = Column(Text)
parent_department_id = Column(Integer,
ForeignKey('department.id'))
parent_department = relationship(Department,
backref=backref(subdepartments),
remote_side=id)
Thanks,
Michael Naber
--
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] reflecting schema with autoload in Table creation
I am wanting to have SQLAlchemy pull the table schema from the database. I
have just started trying to work through Essential SQLAlchemy and believe that
I am using a syntax that is no longer supported in SQLAlchemy 0.6.6. This what
I have tried:
from sqlalchemy import MetaData, create_engine, Table
meta = MetaData
engine1 = create_engine(mssql+pyodbc://mydsn)
valid_species_table = Table('tf_valid_species', meta, autoload = True,
autoload_with = engine1)
I get a traceback that indicates MetaData does not have an attribute 'tables'
(see below). Thus far, though I expect it exists, I have not found anything
comparable within the 0.6.6 documentation.
Where should I be looking?
Traceback (most recent call last):
File stdin, line 2, in module
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/schema.py,
line 195, in __new__
if key in metadata.tables:
AttributeError: type object 'MetaData' has no attribute 'tables'
R. Mark Sharp, Ph.D.
msh...@sfbr.orgmailto:msh...@sfbr.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] SqlSoup ProgrammingError The data types nvarchar and ntext are incompatible in the equal operator
I decided to try to get some reflection using SqlSoup on a MS SQL Server 2008
database.
This is a copy of my session with the DSN obfuscated.
from sqlalchemy.ext.sqlsoup import SqlSoup
engine1 = SqlSoup(mssql+pyodbc://mydsn)
valid_species = db.valid_species.all()
Traceback (most recent call last):
File stdin, line 1, in module
NameError: name 'db' is not defined
valid_species = engine1.valid_species.all()
Traceback (most recent call last):
File stdin, line 1, in module
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/ext/sqlsoup.py,
line 792, in __getattr__
return self.entity(attr)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/ext/sqlsoup.py,
line 789, in entity
return self.map_to(attr, tablename=attr, schema=schema)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/ext/sqlsoup.py,
line 672, in map_to
schema=schema or self.schema)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/schema.py,
line 210, in __new__
table._init(name, metadata, *args, **kw)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/schema.py,
line 258, in _init
include_columns=include_columns)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1863, in reflecttable
self.dialect.reflecttable(conn, table, include_columns)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/default.py,
line 228, in reflecttable
return insp.reflecttable(table, include_columns)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/reflection.py,
line 382, in reflecttable
for col_d in self.get_columns(table_name, schema, **tblkw):
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/reflection.py,
line 229, in get_columns
**kw)
File string, line 1, in lambda
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/reflection.py,
line 46, in cache
ret = fn(self, con, *args, **kw)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/dialects/mssql/base.py,
line 1244, in get_columns
c = connection.execute(s)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1191, in execute
params)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1271, in _execute_clauseelement
return self.__execute_context(context)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1302, in __execute_context
context.parameters[0], context=context)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1401, in _cursor_execute
context)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1394, in _cursor_execute
context)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/default.py,
line 299, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', '[42000]
[Actual][SQL Server] The data types nvarchar and ntext are incompatible in the
equal to operator. (402) (SQLExecDirectW)') u'SELECT
[COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_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].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME] \nFROM
[INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] \nWHERE [COLUMNS_1].[TABLE_NAME]
= ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER BY [COLUMNS_1].[ORDINAL_POSITION]'
(u'valid_species', u'dbo')
R. Mark Sharp, Ph.D.
msh...@sfbr.orgmailto:msh...@sfbr.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
Re: [sqlalchemy] reflecting schema with autoload in Table creation
On Jan 24, 2011, at 5:45 PM, Mark Sharp wrote:
I am wanting to have SQLAlchemy pull the table schema from the database. I
have just started trying to work through Essential SQLAlchemy and believe
that I am using a syntax that is no longer supported in SQLAlchemy 0.6.6.
This what I have tried:
from sqlalchemy import MetaData, create_engine, Table
meta = MetaData
engine1 = create_engine(mssql+pyodbc://mydsn)
valid_species_table = Table('tf_valid_species', meta, autoload = True,
autoload_with = engine1)
I get a traceback that indicates MetaData does not have an attribute 'tables'
(see below). Thus far, though I expect it exists, I have not found anything
comparable within the 0.6.6
you need to construct the MetaData:
meta = MetaData()
otherwise the example looks OK.
--
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] SqlSoup ProgrammingError The data types nvarchar and ntext are incompatible in the equal operator
that's weird since I don't have that issue testing against a 2008 database. I
can't actually reproduce it. You might want to try ensuring you're on a recent
Pyodbc build.
You can also try this:
from sqlalchemy import create_engine
from sqlalchemy.ext.sqlsoup import SqlSoup
db = SqlSoup(create_engine(mssql+pyodbc://mydsn, convert_unicode=True))
On Jan 24, 2011, at 7:17 PM, Mark Sharp wrote:
I decided to try to get some reflection using SqlSoup on a MS SQL Server 2008
database.
This is a copy of my session with the DSN obfuscated.
from sqlalchemy.ext.sqlsoup import SqlSoup
engine1 = SqlSoup(mssql+pyodbc://mydsn)
valid_species = db.valid_species.all()
Traceback (most recent call last):
File stdin, line 1, in module
NameError: name 'db' is not defined
valid_species = engine1.valid_species.all()
Traceback (most recent call last):
File stdin, line 1, in module
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/ext/sqlsoup.py,
line 792, in __getattr__
return self.entity(attr)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/ext/sqlsoup.py,
line 789, in entity
return self.map_to(attr, tablename=attr, schema=schema)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/ext/sqlsoup.py,
line 672, in map_to
schema=schema or self.schema)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/schema.py,
line 210, in __new__
table._init(name, metadata, *args, **kw)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/schema.py,
line 258, in _init
include_columns=include_columns)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1863, in reflecttable
self.dialect.reflecttable(conn, table, include_columns)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/default.py,
line 228, in reflecttable
return insp.reflecttable(table, include_columns)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/reflection.py,
line 382, in reflecttable
for col_d in self.get_columns(table_name, schema, **tblkw):
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/reflection.py,
line 229, in get_columns
**kw)
File string, line 1, in lambda
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/reflection.py,
line 46, in cache
ret = fn(self, con, *args, **kw)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/dialects/mssql/base.py,
line 1244, in get_columns
c = connection.execute(s)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1191, in execute
params)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1271, in _execute_clauseelement
return self.__execute_context(context)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1302, in __execute_context
context.parameters[0], context=context)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1401, in _cursor_execute
context)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1394, in _cursor_execute
context)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/default.py,
line 299, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', '[42000]
[Actual][SQL Server] The data types nvarchar and ntext are incompatible in
the equal to operator. (402) (SQLExecDirectW)') u'SELECT
[COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_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].[COLUMN_DEFAULT],
Re: [sqlalchemy] reflecting schema with autoload in Table creation
Michael,
I had missed calling MetaData, but after making the call, I get the following
traceback.
from sqlalchemy import MetaData, create_engine, Table
meta = MetaData()
engine1 = create_engine(mssql+pyodbc://mydsn)
valid_species_table = Table(
... 'tf_valid_species', meta, autoload = True, autoload_with = engine1)
Traceback (most recent call last):
File stdin, line 2, in module
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/schema.py,
line 210, in __new__
table._init(name, metadata, *args, **kw)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/schema.py,
line 258, in _init
include_columns=include_columns)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1863, in reflecttable
self.dialect.reflecttable(conn, table, include_columns)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/default.py,
line 228, in reflecttable
return insp.reflecttable(table, include_columns)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/reflection.py,
line 382, in reflecttable
for col_d in self.get_columns(table_name, schema, **tblkw):
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/reflection.py,
line 229, in get_columns
**kw)
File string, line 1, in lambda
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/reflection.py,
line 46, in cache
ret = fn(self, con, *args, **kw)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/dialects/mssql/base.py,
line 1244, in get_columns
c = connection.execute(s)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1191, in execute
params)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1271, in _execute_clauseelement
return self.__execute_context(context)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1302, in __execute_context
context.parameters[0], context=context)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1401, in _cursor_execute
context)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1394, in _cursor_execute
context)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/default.py,
line 299, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', '[42000]
[Actual][SQL Server] The data types nvarchar and ntext are incompatible in the
equal to operator. (402) (SQLExecDirectW)') u'SELECT
[COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_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].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME] \nFROM
[INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] \nWHERE [COLUMNS_1].[TABLE_NAME]
= ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER BY [COLUMNS_1].[ORDINAL_POSITION]'
(u'tf_valid_species', u'dbo')
R. Mark Sharp, Ph.D.
msh...@sfbr.orgmailto:msh...@sfbr.org
On Jan 24, 2011, at 6:25 PM, Michael Bayer wrote:
On Jan 24, 2011, at 5:45 PM, Mark Sharp wrote:
I am wanting to have SQLAlchemy pull the table schema from the database. I
have just started trying to work through Essential SQLAlchemy and believe that
I am using a syntax that is no longer supported in SQLAlchemy 0.6.6. This what
I have tried:
from sqlalchemy import MetaData, create_engine, Table
meta = MetaData
engine1 = create_engine(mssql+pyodbc://mydsn)
valid_species_table = Table('tf_valid_species', meta, autoload = True,
autoload_with = engine1)
I get a traceback that indicates MetaData does not have an attribute 'tables'
(see below). Thus far, though I expect it exists, I have not found anything
comparable within the 0.6.6
you need to construct the MetaData:
meta = MetaData()
otherwise the example looks OK.
--
You received this message because you are subscribed to the Google
Re: [sqlalchemy] SqlSoup ProgrammingError The data types nvarchar and ntext are incompatible in the equal operator
Michael,
pyodbc.version returns 2.1.8
I tried your code snippet with the same results.
I am going to try creating some tables and see how that goes.
Mark
R. Mark Sharp, Ph.D.
msh...@sfbr.orgmailto:msh...@sfbr.org
On Jan 24, 2011, at 6:33 PM, Michael Bayer wrote:
that's weird since I don't have that issue testing against a 2008 database. I
can't actually reproduce it. You might want to try ensuring you're on a recent
Pyodbc build.
You can also try this:
from sqlalchemy import create_engine
from sqlalchemy.ext.sqlsoup import SqlSoup
db = SqlSoup(create_engine(mssql+pyodbc://mydsn, convert_unicode=True))
On Jan 24, 2011, at 7:17 PM, Mark Sharp wrote:
I decided to try to get some reflection using SqlSoup on a MS SQL Server 2008
database.
This is a copy of my session with the DSN obfuscated.
from sqlalchemy.ext.sqlsoup import SqlSoup
engine1 = SqlSoup(mssql+pyodbc://mydsn)
valid_species = db.valid_species.all()
Traceback (most recent call last):
File stdin, line 1, in module
NameError: name 'db' is not defined
valid_species = engine1.valid_species.all()
Traceback (most recent call last):
File stdin, line 1, in module
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/ext/sqlsoup.py,
line 792, in __getattr__
return self.entity(attr)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/ext/sqlsoup.py,
line 789, in entity
return self.map_to(attr, tablename=attr, schema=schema)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/ext/sqlsoup.py,
line 672, in map_to
schema=schema or self.schema)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/schema.py,
line 210, in __new__
table._init(name, metadata, *args, **kw)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/schema.py,
line 258, in _init
include_columns=include_columns)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1863, in reflecttable
self.dialect.reflecttable(conn, table, include_columns)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/default.py,
line 228, in reflecttable
return insp.reflecttable(table, include_columns)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/reflection.py,
line 382, in reflecttable
for col_d in self.get_columns(table_name, schema, **tblkw):
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/reflection.py,
line 229, in get_columns
**kw)
File string, line 1, in lambda
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/reflection.py,
line 46, in cache
ret = fn(self, con, *args, **kw)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/dialects/mssql/base.py,
line 1244, in get_columns
c = connection.execute(s)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1191, in execute
params)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1271, in _execute_clauseelement
return self.__execute_context(context)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1302, in __execute_context
context.parameters[0], context=context)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1401, in _cursor_execute
context)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1394, in _cursor_execute
context)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/default.py,
line 299, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', '[42000]
[Actual][SQL Server] The data types nvarchar and ntext are incompatible in the
equal to operator. (402) (SQLExecDirectW)') u'SELECT
[COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME],
[COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
Re: [sqlalchemy] SqlSoup ProgrammingError The data types nvarchar and ntext are incompatible in the equal operator
Here's a script that runs fine for me on Windows as well as OSX using SQL
Server 2008 with pyodbc 2.1.4, I also tried 2.1.8 on OSX without issue (on OSX
you need to pass '' and not u''). Try it on yours, and if you continue to get
the NTEXT error you may have to report this issue to the pyodbc mailing list.
import pyodbc
conn = pyodbc.connect('DSN=mydsn', user='scott', password='tiger')
cursor = conn.cursor()
cursor.execute(u
SELECT [COLUMNS_1].[TABLE_SCHEMA],
[COLUMNS_1].[TABLE_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].[COLUMN_DEFAULT],
[COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?
ORDER BY [COLUMNS_1].[ORDINAL_POSITION]
, (u'tf_valid_species', u'dbo'))
print cursor.fetchall()
On Jan 24, 2011, at 8:35 PM, Mark Sharp wrote:
Michael,
pyodbc.version returns 2.1.8
I tried your code snippet with the same results.
I am going to try creating some tables and see how that goes.
Mark
R. Mark Sharp, Ph.D.
msh...@sfbr.org
On Jan 24, 2011, at 6:33 PM, Michael Bayer wrote:
that's weird since I don't have that issue testing against a 2008 database.
I can't actually reproduce it. You might want to try ensuring you're on a
recent Pyodbc build.
You can also try this:
from sqlalchemy import create_engine
from sqlalchemy.ext.sqlsoup import SqlSoup
db = SqlSoup(create_engine(mssql+pyodbc://mydsn, convert_unicode=True))
On Jan 24, 2011, at 7:17 PM, Mark Sharp wrote:
I decided to try to get some reflection using SqlSoup on a MS SQL Server
2008 database.
This is a copy of my session with the DSN obfuscated.
from sqlalchemy.ext.sqlsoup import SqlSoup
engine1 = SqlSoup(mssql+pyodbc://mydsn)
valid_species = db.valid_species.all()
Traceback (most recent call last):
File stdin, line 1, in module
NameError: name 'db' is not defined
valid_species = engine1.valid_species.all()
Traceback (most recent call last):
File stdin, line 1, in module
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/ext/sqlsoup.py,
line 792, in __getattr__
return self.entity(attr)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/ext/sqlsoup.py,
line 789, in entity
return self.map_to(attr, tablename=attr, schema=schema)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/ext/sqlsoup.py,
line 672, in map_to
schema=schema or self.schema)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/schema.py,
line 210, in __new__
table._init(name, metadata, *args, **kw)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/schema.py,
line 258, in _init
include_columns=include_columns)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1863, in reflecttable
self.dialect.reflecttable(conn, table, include_columns)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/default.py,
line 228, in reflecttable
return insp.reflecttable(table, include_columns)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/reflection.py,
line 382, in reflecttable
for col_d in self.get_columns(table_name, schema, **tblkw):
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/reflection.py,
line 229, in get_columns
**kw)
File string, line 1, in lambda
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/reflection.py,
line 46, in cache
ret = fn(self, con, *args, **kw)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/dialects/mssql/base.py,
line 1244, in get_columns
c = connection.execute(s)
File
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py,
line 1191, in execute
params)
File
Re: [sqlalchemy] SqlSoup ProgrammingError The data types nvarchar and ntext are incompatible in the equal operator
This is the result that I received.
conn = pyodbc.connect(DSN=frankie-w7-animal-sa;UID=sa;PWD=select $ from
Bill)
cursor = conn.cursor()
cursor.execute(u
... SELECT [COLUMNS_1].[TABLE_SCHEMA],
... [COLUMNS_1].[TABLE_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].[COLUMN_DEFAULT],
... [COLUMNS_1].[COLLATION_NAME]
... FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
... WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] =
?
... ORDER BY [COLUMNS_1].[ORDINAL_POSITION],
... ,(u'tf_valid_species', u'dbo'))
Traceback (most recent call last):
File stdin, line 16, in module
pyodbc.ProgrammingError: ('42000', '[42000] [Actual][SQL Server] Statement(s)
could not be prepared. (8180) (SQLExecDirectW)')
R. Mark Sharp, Ph.D.
msh...@sfbr.orgmailto:msh...@sfbr.org
On Jan 24, 2011, at 7:42 PM, Michael Bayer wrote:
cursor = conn.cursor()
--
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] SqlSoup ProgrammingError The data types nvarchar and ntext are incompatible in the equal operator
you have a trailing comma at the end of the statement.
its a long string probably easier to paste it into a .py file and run that.
On Jan 24, 2011, at 9:06 PM, Mark Sharp wrote:
This is the result that I received.
conn = pyodbc.connect(DSN=frankie-w7-animal-sa;UID=sa;PWD=select $ from
Bill)
cursor = conn.cursor()
cursor.execute(u
... SELECT [COLUMNS_1].[TABLE_SCHEMA],
... [COLUMNS_1].[TABLE_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].[COLUMN_DEFAULT],
... [COLUMNS_1].[COLLATION_NAME]
... FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
... WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA]
= ?
... ORDER BY [COLUMNS_1].[ORDINAL_POSITION],
... ,(u'tf_valid_species', u'dbo'))
Traceback (most recent call last):
File stdin, line 16, in module
pyodbc.ProgrammingError: ('42000', '[42000] [Actual][SQL Server] Statement(s)
could not be prepared. (8180) (SQLExecDirectW)')
R. Mark Sharp, Ph.D.
msh...@sfbr.org
On Jan 24, 2011, at 7:42 PM, Michael Bayer wrote:
cursor = conn.cursor()
--
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.
Re: [sqlalchemy] SqlSoup ProgrammingError The data types nvarchar and ntext are incompatible in the equal operator
Michael,
Back to the same error.
import pyodbc
conn = pyodbc.connect('DSN=frankie-w7-animal-sa;UID=sa;PWD=select $ from
Bill')
cursor = conn.cursor()
cursor.execute(u
... SELECT [COLUMNS_1].[TABLE_SCHEMA],
... [COLUMNS_1].[TABLE_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].[COLUMN_DEFAULT],
... [COLUMNS_1].[COLLATION_NAME]
... FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
... WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] =
?
... ORDER BY [COLUMNS_1].[ORDINAL_POSITION]
... ,(u'tf_valid_species', u'dbo'))
Traceback (most recent call last):
File stdin, line 16, in module
pyodbc.ProgrammingError: ('42000', '[42000] [Actual][SQL Server] The data types
nvarchar and ntext are incompatible in the equal to operator. (402)
(SQLExecDirectW)')
R. Mark Sharp, Ph.D.
msh...@sfbr.orgmailto:msh...@sfbr.org
On Jan 24, 2011, at 8:21 PM, Michael Bayer wrote:
you have a trailing comma at the end of the statement.
its a long string probably easier to paste it into a .py file and run that.
On Jan 24, 2011, at 9:06 PM, Mark Sharp wrote:
This is the result that I received.
conn = pyodbc.connect(DSN=frankie-w7-animal-sa;UID=sa;PWD=select $ from
Bill)
cursor = conn.cursor()
cursor.execute(u
... SELECT [COLUMNS_1].[TABLE_SCHEMA],
... [COLUMNS_1].[TABLE_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].[COLUMN_DEFAULT],
... [COLUMNS_1].[COLLATION_NAME]
... FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
... WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] =
?
... ORDER BY [COLUMNS_1].[ORDINAL_POSITION],
... ,(u'tf_valid_species', u'dbo'))
Traceback (most recent call last):
File stdin, line 16, in module
pyodbc.ProgrammingError: ('42000', '[42000] [Actual][SQL Server] Statement(s)
could not be prepared. (8180) (SQLExecDirectW)')
R. Mark Sharp, Ph.D.
msh...@sfbr.orgmailto:msh...@sfbr.org
On Jan 24, 2011, at 7:42 PM, Michael Bayer wrote:
cursor = conn.cursor()
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to
sqlalchemy@googlegroups.commailto:sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.commailto: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.commailto:sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.commailto: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.
Re: [sqlalchemy] SqlSoup ProgrammingError The data types nvarchar and ntext are incompatible in the equal operator
great. report it to pyodbc, since I have no idea what configuration you have
which would cause that:
http://groups.google.com/group/pyodbc
On Jan 24, 2011, at 9:31 PM, Mark Sharp wrote:
Michael,
Back to the same error.
import pyodbc
conn = pyodbc.connect('DSN=frankie-w7-animal-sa;UID=sa;PWD=select $ from
Bill')
cursor = conn.cursor()
cursor.execute(u
... SELECT [COLUMNS_1].[TABLE_SCHEMA],
... [COLUMNS_1].[TABLE_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].[COLUMN_DEFAULT],
... [COLUMNS_1].[COLLATION_NAME]
... FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
... WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA]
= ?
... ORDER BY [COLUMNS_1].[ORDINAL_POSITION]
... ,(u'tf_valid_species', u'dbo'))
Traceback (most recent call last):
File stdin, line 16, in module
pyodbc.ProgrammingError: ('42000', '[42000] [Actual][SQL Server] The data
types nvarchar and ntext are incompatible in the equal to operator. (402)
(SQLExecDirectW)')
R. Mark Sharp, Ph.D.
msh...@sfbr.org
On Jan 24, 2011, at 8:21 PM, Michael Bayer wrote:
you have a trailing comma at the end of the statement.
its a long string probably easier to paste it into a .py file and run that.
On Jan 24, 2011, at 9:06 PM, Mark Sharp wrote:
This is the result that I received.
conn = pyodbc.connect(DSN=frankie-w7-animal-sa;UID=sa;PWD=select $
from Bill)
cursor = conn.cursor()
cursor.execute(u
... SELECT [COLUMNS_1].[TABLE_SCHEMA],
... [COLUMNS_1].[TABLE_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].[COLUMN_DEFAULT],
... [COLUMNS_1].[COLLATION_NAME]
... FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
... WHERE [COLUMNS_1].[TABLE_NAME] = ? AND
[COLUMNS_1].[TABLE_SCHEMA] = ?
... ORDER BY [COLUMNS_1].[ORDINAL_POSITION],
... ,(u'tf_valid_species', u'dbo'))
Traceback (most recent call last):
File stdin, line 16, in module
pyodbc.ProgrammingError: ('42000', '[42000] [Actual][SQL Server]
Statement(s) could not be prepared. (8180) (SQLExecDirectW)')
R. Mark Sharp, Ph.D.
msh...@sfbr.org
On Jan 24, 2011, at 7:42 PM, Michael Bayer wrote:
cursor = conn.cursor()
--
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.
--
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] Can you declaratively concatenate two columns
Hi does anyone know if is possible to declaratively concatenate two
columns together which you can later do query's on.
E.g. if I wanted to compute a new column course_name made up of
CONCAT(course_code,course_name)
Base = declarative_base()
class Course(Base):
__tablename__ = 'Course'
course_code = Column(VARCHAR(length=4), nullable=False)
course_num = Column(INTEGER(), nullable=False)
course_name = func.CONCAT(course_code,course_num) # only an
example, this doesn't actually work
So later you could do queries on the Course table like
course_data =
session.query(Course).filter( Course.course_name.op('regexp')
('^A.*4') )).first()
print course_data.course_name
It is possible to do a query to generate the data outside the Course
class as below, but how can you
make it as a normal mapped column in the Course class ?
query = session.query( func.CONCAT(Course.course_code,
Course.course_num) )
query = query.filter( func.CONCAT(Course.course_code,
Course.course_num).op('regexp')('^A.*4') )
Cheers
Royce
--
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] mappers failed to initialize/class name is not defined
I'm using the declarative style and unable to to get a mapper to initialize.
Here's a simplified version of the class from client_transaction.py:
class ClientTransaction(Base):
__tablename__ = 'client_transactions'
id = Column(Integer, primary_key=True)
client_promotion_id = Column(Integer,
ForeignKey('clients_promotions.id'))
client_promotion = relationship('ClientPromotion',
primaryjoin='ClientTransction.client_promotion_id == ClientPromotion.id')
And here's the error:
InvalidRequestError: One or more mappers failed to initialize - can't
proceed with initialization of other mappers. Original exception was: When
initializing mapper Mapper|ClientTransaction|client_transactions, expression
'ClientPromotion' failed to locate a name (name 'ClientPromotion' is not
defined). If this is a class name, consider adding this relationship() to
the class 'uber.model.client_transaction.ClientTransaction' class after
both dependent classes have been defined.
Which seems to be in conflict with this statement from the docs:
In addition to the main argument for relationship(), other arguments which
depend upon the columns present on an as-yet undefined class may also be
specified as strings.
For the sake of reference, here's the other class in client_promotion.py as
well:
class ClientPromotion(SmartModel):
__tablename__ = 'clients_promotions'
id = Column(Integer, primary_key=True)
client_transaction_id = Column(Integer,
ForeignKey('client_transactions.id'))
client_transaction = relationship('ClientTransaction',
primaryjoin='ClientPromotion.client_transaction_id == ClientTransaction.id',
uselist=False)
Any help on getting a 'client_promotion' attribute on the
'ClientTransaction' class would be greatly 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.
Re: [sqlalchemy] Can you declaratively concatenate two columns
Hey Royce,
This sounds like a job for composite columns:
http://www.sqlalchemy.org/docs/orm/mapper_config.html#composite-column-types
One gotcha that I ran into here is that you cannot have both the component
columns and the composite column mapped at the same time, like you do in
your example. So depending on what you are trying to do, you might need to
make a comparable property instead:
http://www.sqlalchemy.org/docs/orm/mapper_config.html#custom-comparators
Let me know if you need any more help.
-Lenza
On Mon, Jan 24, 2011 at 7:35 PM, Royce roycek...@gmail.com wrote:
Hi does anyone know if is possible to declaratively concatenate two
columns together which you can later do query's on.
E.g. if I wanted to compute a new column course_name made up of
CONCAT(course_code,course_name)
Base = declarative_base()
class Course(Base):
__tablename__ = 'Course'
course_code = Column(VARCHAR(length=4), nullable=False)
course_num = Column(INTEGER(), nullable=False)
course_name = func.CONCAT(course_code,course_num) # only an
example, this doesn't actually work
So later you could do queries on the Course table like
course_data =
session.query(Course).filter( Course.course_name.op('regexp')
('^A.*4') )).first()
print course_data.course_name
It is possible to do a query to generate the data outside the Course
class as below, but how can you
make it as a normal mapped column in the Course class ?
query = session.query( func.CONCAT(Course.course_code,
Course.course_num) )
query = query.filter( func.CONCAT(Course.course_code,
Course.course_num).op('regexp')('^A.*4') )
Cheers
Royce
--
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.comsqlalchemy%2bunsubscr...@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.
