On systems where I do not have administrative access, I emulate an
auxilliary table of numbers with a subquery that has a bunch of UNION
ALL statements in it. I would like to do same with SQL Alchemy but
have not been able to figure out yet how to do it. My current effort
is almost there. I need to find out how to name a subquery and say
what columns it returns. In this case, I would like to say 'nums is a
derived table implemented by <raw sql> and it has one column, n, which
is an integer'. It is possible to define a table SchemaItem with a
from_obj rather than a table name?
pjjH
nums = sql.select(['n'], from_obj=sql.text(r"""
SELECT 1 as n
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11
UNION ALL SELECT 12
UNION ALL SELECT 13
UNION ALL SELECT 14
UNION ALL SELECT 15
UNION ALL SELECT 16
"""
)
).alias('nums')
s = sql.select([sysindexes.c.name.label('index_name'),
sysindexes.c.status,
index_col(sql.func.object_name
(sysindexes.c.id), sysindexes.c.indid, nums.c.n).label('column_name'),
nums.c.n.label('column_ordinal'),
],from_obj=[sysindexes.join
(nums,sysindexes.c.keycnt <= nums.c.n)]).where(sql.func.object_name
(sysindexes.c.id) ==table.name).order_by(nums.c.n)
ProgrammingError: (ProgrammingError) ('42000', "[42000] [FreeTDS][SQL
Server]Incorrect syntax near the keyword 'SELECT'.\n (156)") u'SELECT
sysindexes.name AS index_name, sysindexes.status, index_col(OBJECT_NAME
(sysindexes.id), sysindexes.indid, nums.n) AS column_name, nums.n AS
column_ordinal \nFROM sysindexes JOIN (SELECT n \nFROM \n
SELECT 1 as n\n UNION ALL SELECT
2\n UNION ALL SELECT 3
\n UNION ALL SELECT 4
\n UNION ALL SELECT
5\n UNION ALL SELECT
5\n UNION ALL SELECT
6\n UNION ALL SELECT
7\n UNION ALL SELECT
8\n UNION ALL SELECT
9\n UNION ALL SELECT
10\n UNION ALL SELECT
11\n UNION ALL SELECT
12\n UNION ALL SELECT
13\n UNION ALL SELECT
14\n UNION ALL SELECT
15\n UNION ALL SELECT 16\n) AS nums ON
sysindexes.keycnt <= nums.n \nWHERE OBJECT_NAME(sysindexes.id) = ?
ORDER BY nums.n' [u'plain']
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
