[sqlalchemy] Re: InsertFromSelect on SQL server
Mike, I tried to apply the new patch but it was rejected (working on
windows 7 and using patch from GnuWin32); it seems that the target
lines in you patch are misaligned with those in my SA source files. I
tried to make the changes by hand, but I could not get it working. I
still get the 'postfetch' error.
On 18 Ago, 19:34, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 18, 2011, at 1:19 PM, Massi wrote:
I got:
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 2285, in execute
return connection.execute(statement, *multiparams, **params)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1399, in execute
params)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1532, in _execute_clauseelement
compiled_sql, distilled_params
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1599, in _execute_context
None, None)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1595, in _execute_context
context = constructor(dialect, self, conn, *args)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\default.py, line 433, in _init_compiled
self.postfetch_cols = self.compiled.postfetch
StatementError: 'MSSQLCompiler' object has no attribute
'postfetch' (original cause: AttributeError: 'MSSQLCompiler' object
has no attribute 'postfetch') 'INSERT INTO...'
here's another patch that should default all that stuff appropriately:
diff -r 113a7ed95335 lib/sqlalchemy/dialects/mssql/base.py
--- a/lib/sqlalchemy/dialects/mssql/base.py Thu Aug 18 13:03:30 2011 -0400
+++ b/lib/sqlalchemy/dialects/mssql/base.py Thu Aug 18 13:31:16 2011 -0400
@@ -655,7 +655,9 @@
seq_column = tbl._autoincrement_column
insert_has_sequence = seq_column is not None
- if insert_has_sequence:
+ if getattr(self.compiled._mssql_requires_identity_insert, False):
+ self._enable_identity_insert = True
+ elif insert_has_sequence:
self._enable_identity_insert = \
seq_column.key in self.compiled_parameters[0]
else:
diff -r 113a7ed95335 lib/sqlalchemy/sql/compiler.py
--- a/lib/sqlalchemy/sql/compiler.py Thu Aug 18 13:03:30 2011 -0400
+++ b/lib/sqlalchemy/sql/compiler.py Thu Aug 18 13:31:16 2011 -0400
@@ -198,6 +198,10 @@
# driver/DB enforces this
ansi_bind_rules = False
+ postfetch = ()
+ prefetch = ()
+ returning = ()
+
def __init__(self, dialect, statement, column_keys=None,
inline=False, **kwargs):
Construct a new ``DefaultCompiler`` object.
diff -r 113a7ed95335 lib/sqlalchemy/sql/expression.py
--- a/lib/sqlalchemy/sql/expression.py Thu Aug 18 13:03:30 2011 -0400
+++ b/lib/sqlalchemy/sql/expression.py Thu Aug 18 13:31:16 2011 -0400
@@ -4792,6 +4792,8 @@
Executable._execution_options.union({'autocommit': True})
kwargs = util.immutabledict()
+ _returning = None
+
def _process_colparams(self, parameters):
if isinstance(parameters, (list, tuple)):
pp = {}
--
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: Table creation/deletetion checkfirst parameter in mssql+pyodbc
Hello! Is there any new information about my problem? Thank you for your support! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/FL6xRQr3ZzwJ. 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] Repetitive Fields in declarative
On Aug 18, 2011, at 7:01 PM, Mark Erbaugh wrote:
On Aug 18, 2011, at 6:06 PM, Mark Erbaugh wrote:
want to create a table that has several similar fields. For example, assume
the fields are field1, field2, ...
Is there a way in the declarative class that I can do something like:
for i in range(10):
'field%d' % i = Column( ... )
Thanks,
Mark
Figured it out:
after the class definition:
for i in range(10):
class.__table__.append_column(Column('field%d' % i, ...))
I guess not: while the above code adds the fields to the database table, it
doesn't add them as named data members of the class. Here's my latest effort:
class Preferences:
...
for i in range(10):
setattr(Preferences, 'field%d' % i, Column(...
This also answers my question about relationships
setattr(Preferences 'relationship%d' % i, relationship(...
Mark
--
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] Mapping a CTE
I am interested in any feedback on the code at
https://gist.github.com/1156683 . The code works for me but it's a hacked
solution and I'm not sure if this will work generally. I am not sure if
there is a better way to handle gather the CTE's, I simply tacked the found
CTE onto the compiler object. Also this should support multiple CTE's.
print Date.range('2011-01-01 00:00:00', '2012-02-01
00:00:00').filter(date='2011-01-15 00:00:00').order_by(Date.date.desc())
WITH all_dates(date) AS (
SELECT CAST(? AS DATETIME) AS anon_1 UNION ALL SELECT DATEADD(dd, ?, date)
AS [DATEADD_1]
FROM all_dates
WHERE DATEADD(dd, ?, date) = CAST(? AS DATETIME)
)
SELECT date AS date
FROM (SELECT * FROM all_dates) as x
WHERE date='2011-01-15 00:00:00' ORDER BY date DESC
OPTION (MAXRECURSION 0)
On Thu, Aug 18, 2011 at 2:20 PM, Michael Bayer mike...@zzzcomputing.comwrote:
On Aug 18, 2011, at 2:01 PM, Marc DellaVolpe wrote:
I understand what you are saying about this being an uphill battle on
mssql.
If we ignore the CTE for the moment and pretend that all_dates is a regular
table, shouldn't there be no difference for generation. You can subquery
as needed and all that really needs to happen is to prepend the CTE to the
rest of the SQL before execution. Maybe this approach
isn't generally applicable, CTE's already make my head hurt :)
yeah that could work as well but its not clear to me if the CTE can be up
above everything, then referenced within a nested structure like that.I
haven't really worked with CTEs to have that level of experience.
Searching the mailing list found a thread (
http://groups.google.com/group/sqlalchemy/browse_thread/thread/c20a4f380e277af/fb179a515bf48868)
where you used a regular expression to shim in a special comment. This
seems hackish but could a similar approach work on mssql? Is it possible to
hook into only the top-level/last Select's compile()?
if it works, then yes you'd get your Select compiler to dig in, look for
CTEs, put the WITH all the way on top, assuming the Select is the
outermost. You can tell if you're the outermost select by checking
bool(compiler.stack), which is a stack of SELECT constructs.
--
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] Re: Table creation/deletetion checkfirst parameter in mssql+pyodbc
you need to take your problem to the PyODBC tracker/mailing list. The problem can be reproduced without using SQLAlchemy. On Aug 19, 2011, at 5:57 AM, Michael Kvyatkovskiy wrote: Hello! Is there any new information about my problem? Thank you for your support! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/FL6xRQr3ZzwJ. 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] Re: InsertFromSelect on SQL server
the attached patch is against r113a7ed95335. Test program is attached,
relevant output using PyODBC + FreeTDS:
CREATE TABLE t2 (
id INTEGER NOT NULL IDENTITY(1,1),
data VARCHAR(50) NULL,
PRIMARY KEY (id)
)
2011-08-19 10:34:42,142 INFO sqlalchemy.engine.base.Engine ()
2011-08-19 10:34:42,143 INFO sqlalchemy.engine.base.Engine COMMIT
2011-08-19 10:34:42,145 INFO sqlalchemy.engine.base.Engine
CREATE TABLE t1 (
id INTEGER NOT NULL IDENTITY(1,1),
data VARCHAR(50) NULL,
PRIMARY KEY (id)
)
2011-08-19 10:34:42,145 INFO sqlalchemy.engine.base.Engine ()
2011-08-19 10:34:42,170 INFO sqlalchemy.engine.base.Engine COMMIT
2011-08-19 10:34:42,173 INFO sqlalchemy.engine.base.Engine INSERT INTO t1
(data) VALUES (?)
2011-08-19 10:34:42,173 INFO sqlalchemy.engine.base.Engine (('d0',), ('d1',),
('d2',), ('d3',), ('d4',), ('d5',), ('d6',), ('d7',), ('d8',), ('d9',),
('d10',), ('d11',), ('d12',), ('d13',), ('d14',), ('d15',), ('d16',), ('d17',),
('d18',), ('d19',), ('d20',), ('d21',), ('d22',), ('d23',), ('d24',), ('d25',),
('d26',), ('d27',), ('d28',), ('d29',), ('d30',), ('d31',), ('d32',), ('d33',),
('d34',), ('d35',), ('d36',), ('d37',), ('d38',), ('d39',), ('d40',), ('d41',),
('d42',), ('d43',), ('d44',), ('d45',), ('d46',), ('d47',), ('d48',), ('d49',),
('d50',), ('d51',), ('d52',), ('d53',), ('d54',), ('d55',), ('d56',), ('d57',),
('d58',), ('d59',), ('d60',), ('d61',), ('d62',), ('d63',), ('d64',), ('d65',),
('d66',), ('d67',), ('d68',), ('d69',), ('d70',), ('d71',), ('d72',), ('d73',),
('d74',), ('d75',), ('d76',), ('d77',), ('d78',), ('d79',), ('d80',), ('d81',),
('d82',), ('d83',), ('d84',), ('d85',), ('d86',), ('d87',), ('d88',), ('d89',),
('d90',), ('d91',), ('d92',), ('d93',), ('d94',), ('d95',), ('d96',), ('d97',),
('d98',), ('d99',))
2011-08-19 10:34:42,247 INFO sqlalchemy.engine.base.Engine COMMIT
2011-08-19 10:34:42,249 INFO sqlalchemy.engine.base.Engine INSERT INTO t2
(t2.id, t2.data) SELECT t1.id, t1.data
FROM t1
2011-08-19 10:34:42,249 INFO sqlalchemy.engine.base.Engine ()
2011-08-19 10:34:42,252 INFO sqlalchemy.engine.base.Engine COMMIT
On Aug 19, 2011, at 4:54 AM, Massi wrote:
Mike, I tried to apply the new patch but it was rejected (working on
windows 7 and using patch from GnuWin32); it seems that the target
lines in you patch are misaligned with those in my SA source files. I
tried to make the changes by hand, but I could not get it working. I
still get the 'postfetch' error.
On 18 Ago, 19:34, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 18, 2011, at 1:19 PM, Massi wrote:
I got:
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 2285, in execute
return connection.execute(statement, *multiparams, **params)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1399, in execute
params)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1532, in _execute_clauseelement
compiled_sql, distilled_params
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1599, in _execute_context
None, None)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py, line 1595, in _execute_context
context = constructor(dialect, self, conn, *args)
File C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\default.py, line 433, in _init_compiled
self.postfetch_cols = self.compiled.postfetch
StatementError: 'MSSQLCompiler' object has no attribute
'postfetch' (original cause: AttributeError: 'MSSQLCompiler' object
has no attribute 'postfetch') 'INSERT INTO...'
here's another patch that should default all that stuff appropriately:
diff -r 113a7ed95335 lib/sqlalchemy/dialects/mssql/base.py
--- a/lib/sqlalchemy/dialects/mssql/base.py Thu Aug 18 13:03:30 2011
-0400
+++ b/lib/sqlalchemy/dialects/mssql/base.py Thu Aug 18 13:31:16 2011
-0400
@@ -655,7 +655,9 @@
seq_column = tbl._autoincrement_column
insert_has_sequence = seq_column is not None
-if insert_has_sequence:
+if getattr(self.compiled._mssql_requires_identity_insert,
False):
+self._enable_identity_insert = True
+elif insert_has_sequence:
self._enable_identity_insert = \
seq_column.key in self.compiled_parameters[0]
else:
diff -r 113a7ed95335 lib/sqlalchemy/sql/compiler.py
--- a/lib/sqlalchemy/sql/compiler.pyThu Aug 18 13:03:30 2011 -0400
+++ b/lib/sqlalchemy/sql/compiler.pyThu Aug 18 13:31:16 2011 -0400
@@ -198,6 +198,10 @@
# driver/DB enforces this
ansi_bind_rules = False
+postfetch = ()
+prefetch = ()
+returning = ()
+
def __init__(self, dialect,
Re: [sqlalchemy] Repetitive Fields in declarative
On Aug 18, 2011, at 6:06 PM, Mark Erbaugh wrote: I want to create a table that has several similar fields. For example, assume the fields are field1, field2, ... Is there a way in the declarative class that I can do something like: for i in range(10): 'field%d' % i = Column( ... ) Id use a mixin so that a superclass can be generated in a data driven manner: MyCols = type(MyCols, (object, ), dict((field%d % i, Column(Integer)) for i in xrange(1, 10))) class MyClass(MyCols, Base): ... otherwise if you want to go the append_column() route, you can just tack them on the class, declarative will call append_column() as well as mapper.add_property(): for name, col in (field%d % i, Column(Integer)) for i in xrange(1, 10)): setattr(MyClass, name, col) -- 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] Repetitive Fields in declarative
On Aug 19, 2011, at 10:41 AM, Michael Bayer wrote: Id use a mixin so that a superclass can be generated in a data driven manner: MyCols = type(MyCols, (object, ), dict((field%d % i, Column(Integer)) for i in xrange(1, 10))) class MyClass(MyCols, Base): ... otherwise if you want to go the append_column() route, you can just tack them on the class, declarative will call append_column() as well as mapper.add_property(): for name, col in (field%d % i, Column(Integer)) for i in xrange(1, 10)): setattr(MyClass, name, col) Michael, Thanks for the info. Mark -- 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] Handling optional relationship
I have a table that has a foreign key field that is optional. IOW, the current row may be linked to at most one row in the foreign table. If the foreign key field is not NULL, it must point to a valid row in the foreign table, but if it is NULL that means that it it not linked. Is there an automatic way to have the value of the foreign key field set to NULL if the linked row in the foreign table is deleted? Thanks, Mark -- 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] Handling optional relationship
The default relationship cascade settings will do it for you. Here I made
them explicit.
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
p_id = Column(Integer, ForeignKey(Parent.id))
parent = relation(Parent, backref=backref('children',
cascade=save-update,merge))
sess.add(Parent(children=[Child(),Child()]))
sess.commit()
p = sess.query(Parent).first()
sess.delete(p)
sess.commit()
--
Mike Conley
On Fri, Aug 19, 2011 at 12:10 PM, Mark Erbaugh m...@microenh.com wrote:
I have a table that has a foreign key field that is optional. IOW, the
current row may be linked to at most one row in the foreign table. If the
foreign key field is not NULL, it must point to a valid row in the foreign
table, but if it is NULL that means that it it not linked.
Is there an automatic way to have the value of the foreign key field set to
NULL if the linked row in the foreign table is deleted?
Thanks,
Mark
--
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] Handling optional relationship
On Aug 19, 2011, at 2:10 PM, Mike Conley wrote:
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
p_id = Column(Integer, ForeignKey(Parent.id))
parent = relation(Parent, backref=backref('children',
cascade=save-update,merge))
sess.add(Parent(children=[Child(),Child()]))
sess.commit()
p = sess.query(Parent).first()
sess.delete(p)
sess.commit()
Mike,
Thanks. that does indeed work. For some reason, it didn't seem to be work in
my schema. I'll have to do some more testing.
Mark
--
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] Repetitive Fields in declarative
On Fri, Aug 19, 2011 at 9:23 AM, Mark Erbaugh m...@microenh.com wrote:
On Aug 19, 2011, at 10:41 AM, Michael Bayer wrote:
Id use a mixin so that a superclass can be generated in a data driven
manner:
MyCols = type(MyCols, (object, ), dict((field%d % i, Column(Integer))
for i in xrange(1, 10)))
If you are going to do this a lot, it can be a pain with declarative. I
developed a framework that I used, based on ORM, which uses an explicit
_makeTable() and _mapTable() objects that I call in order to create and map
the tables. Since these are python methods, I can use any kind of python
iteration or code I want to decide what columns to create. It is a pretty
flexible model. Example conceptual code:
class Database(object):
table_args = { 'mysql_engine' : 'InnoDB' }
schema = database_name
def __init__(self,dbclasses=[]):
self.metadata = MetaData()
self.engine = ... (set up engine, etc.)
self.dbclasses = dbclasses
for c in self.dbclasses:
c._makeTable(self,self.engine)
cls.__table__.create(bind=self.engine,checkfirst=True)
for c in self.dbclasses:
c._mapTable(self)
class FooRecord(object):
@classmethod
def _makeTable(cls,db,engine):
cls.db = db
cls.__table__ = Table('foo', db.metadata,
Column('x'),
Column('y'),
**cls.table_args,
schema=cls.schema
etc.)
@classmethod
def _mapTable(cls,db):
mapper(cls, cls.__table__, properties={ ... })
db = Database([FooRecord])
You may find a model like this easier to use to create dynamically-generated
tables. The point here is that SQLAlchemy is sufficiently flexible so that
if declarative doesn't meet your needs or is a bit cumbersome for what you
want to do, you can just start at the ORM (below declarative) level and
build up a framework that works for you.
Regards,
Daniel
--
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.
