[sqlalchemy] Re: count function problem
Glauco wrote:
Michael Bayer ha scritto:
On Mar 14, 2007, at 12:49 PM, Glauco wrote:
This is perfect but when i try to use count function the SQL
composer try to do an expensive sql.
In [63]: print select([tbl['azienda'].c.id], tbl['azienda']).count()
*SELECT count(id) AS tbl_row_count
FROM (SELECT azienda.id AS id
FROM azienda)*
what makes you think that query is expensive ? anyway, more succinct
to just say table.count().
because i expect that engine do a SELECT COUNT FROM BLABLA and no a
SELECT COUNT FROM ( SELECT BLABLA)
i think this is expensive for my DataBase.
in this example i've used a simple table but this is a complex qry so
i cannot use tbl.count()
Another question:
Does anyone know how to use this object ?
select([tbl['azienda'].c.id], tbl['azienda']).count()
sqlalchemy.sql.Select object at 0xb6c803ac
select(...).count().execute()
or
engine.connect().execute(select(...).count())
I've done a lot of try this don't work
print select([tbl['azienda'].c.id], tbl['azienda']).execute()
*In [8]: print select([tbl['azienda'].c.id],
tbl['azienda']).count().execute()*
2007-03-15 09:51:29,870 sqlalchemy.engine.base.Engine.0x..d4 INFO
SELECT count(id) AS tbl_row_count
FROM (SELECT azienda.id AS id
FROM azienda)
2007-03-15 09:51:29,870 sqlalchemy.engine.base.Engine.0x..d4 INFO {}
2007-03-15 09:51:29,871 sqlalchemy.engine.base.Engine.0x..d4 INFO ROLLBACK
---
_SQLError: (ProgrammingError) ERROR: subquery in FROM must have an alias
HINT: For example, FROM (SELECT ...) [AS] foo.
_
SELECT count(id) AS tbl_row_count
FROM (SELECT azienda.id AS id
FROM azienda) 'SELECT count(id) AS tbl_row_count \nFROM (SELECT
azienda.id AS id \nFROM azienda)' {}
When i use count() function on a mapper - SelectResult , this return
exactly rowcount, but the same on a select seems to go in error.
for Marco Mariani:
Credo che sia
select([ sa.func.count(tbl['azienda'].c.id ])
almeno io, con la max(), faccio cosi'..
I cannot redefine select_clause because this is a feature for
returning paginate result and rowcount from the same funtion.
Example.
i intend
Azienda.my_generic_search( bla, bla, limit, offset
).execute().fetchall() -- give paginated result
Azienda.my_generic_search( bla, bla,
).execute().fetchone() -- give rowcount
For the second line, I suppose you want to say...
Azienda.my_generic_search( bla, bla).count().execute().fetchone() --
give rowcount
Thank you
Glauco
--
++
Glauco Uri - Programmatore
glauco(at)allevatori.com
Sfera Carta Software® [EMAIL PROTECTED]
Via Bazzanese,69 Casalecchio di Reno(BO) - Tel. 051591054
++
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] SQLite driver and space in the TypeEgine.get_col_spec
Hi. After this thread: http://lists.initd.org/pipermail/pysqlite/2007-March/000994.html I think that the get_col_spec method for SLNumeric, SLString and SLChar should add a space after the type name, since pySQLite uses the first string for its type registry. However I'm not sure this is a bug, so I have not create a ticket. Manlio Perillo --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] how to sum path in a tree?
There is some graph - represented as edges in some assoc.table, and they having some associated item with them (e.g. weight or length). Is it possible to calculate the overall lenght of path from node to node (if there is a path at all) in SQL? Finding if there is a path in the graph from node1 to node2, with max, say, 2 hops will look like: FROM node as node1, node as node2, node as node3 link as link1, link as link2 SELECT link1.length, WHERE node1.id == link1.in AND node2.id == link1.out##1 hop OR node1.id == link1.in AND node2.id == link1.out AND node2.id == link2.in AND node3.id == link2.out ##2 hops but i dont see how to also sum the link.length. or should i use UNION instead of OR? This can be extended to any static number of hops... but is it possible to be done for a dynamic (unknown) number? ciao svil --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: pyodbc and tables with triggers
Sorry, Stephen, I replied too early; your second email arrived before the
first. A whole day before the first.
So until we get a real cleanup, you're looking to try modules in this order:
['pyodbc', 'adodbapi', 'pymssql']
Sounds OK to me -- any objections out there?
Rick
On 3/14/07, Rick Morrison [EMAIL PROTECTED] wrote:
It's the second case, that is, it sniffs out what modules are installed.
As I said before, this
(along with other modules that effectively do the same thing), is up for a
clean-up soon, see ticket #480.
Rick
On 3/14/07, polaar [EMAIL PROTECTED] wrote:
{'pyodbc': use_pyodbc, 'adodbapi': use_adodbapi, 'pyodbc':
use_pyodbc}.get(module.__name__, use_default)()
Sorry, should be pymssql instead of pyodbc twice, but I guess you got
that...
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: count function problem
On Mar 15, 2007, at 5:09 AM, Glauco wrote: because i expect that engine do a SELECT COUNT FROM BLABLA and no a SELECT COUNT FROM ( SELECT BLABLA) i think this is expensive for my DataBase. its not. optimizers can usually figure things like that out. I've done a lot of try this don't work print select([tbl['azienda'].c.id], tbl['azienda']).execute() what happened to table.count() ? if you want to count manually: select([func.count(table.c.whatevercolumn)]).execute() --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy test suite - no success/failure exit code
there is a sys.exit() that I added at Grig's request at the end of testbase.py; its testbase.py's main() method that runs the actual tests in all cases. the result code is determined by unittest's result.wasSuccessful(). it was working when i first implemented it, maybe you can help track down why its not returning. On Mar 14, 2007, at 10:30 PM, [EMAIL PROTECTED] wrote: Some of you may know that I run a Python buildbot for SQLAlchemy: http://www.python.org/dev/buildbot/community/all/?show=g5%20OSX% 20trunkshow=g5%20OSX%202.5 It always shows build successful even though there are plenty of actual test failures on my Mac. After letting it languish for a long time I dug into the problem a bit this evening. It seems that SQLAlchemy's test script (test/alltests.py) doesn't exit with any indication of success or failure. Consequently, it appears to always succeed. I'm not much of a unittest module person, but it appears the unittest.TextTestRunner class is a fundamental barrier to extracting a useful error code from the tests. Its run() method doesn't seem to return anything useful, at least nothing that's documented. I suppose the simplest hack would be to temporarily replace sys.stderr with a StringIO object when running the test, rummage around in it looking for failures=NN, errors=MM, restore sys.stderr, dump the output, then exit with NN+MM. I could also replace test/alltests.py with something of my own invention but I'd prefer to rely as much as possible on the test setup presented by the SQA distribution. Skip --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLite driver and space in the TypeEgine.get_col_spec
read the thread, no idea what a space has to do with anything. can you be more specific. On Mar 15, 2007, at 8:37 AM, Manlio Perillo wrote: Hi. After this thread: http://lists.initd.org/pipermail/pysqlite/2007-March/000994.html I think that the get_col_spec method for SLNumeric, SLString and SLChar should add a space after the type name, since pySQLite uses the first string for its type registry. However I'm not sure this is a bug, so I have not create a ticket. Manlio Perillo --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] statement
Hello list, Any idea how I could generate this SQL statement under SQLAlchemy (especially the PostreSQL ARRAY clause ...) ? SELECT i.id, t.name AS taxo, i.geographic_range, ARRAY( (SELECT h.name FROM habitats h, invasive_habitats ih WHERE h.id=ih.habitat_id AND ih.invasive_id=i.id) ) AS habitats, (SELECT ivn.name FROM invasive_names ivn, languages l WHERE ivn.language_id=l.id AND l.iso_code='la' AND ivn.invasive_id=i.id ) AS scientific_name FROM invasives i, taxonomies t WHERE t.id=i.group_id ORDER BY i.id; Thanks, Julien -- Julien Cigar Belgian Biodiversity Platform http://www.biodiversity.be Université Libre de Bruxelles Campus de la Plaine CP 257 Bâtiment NO, Bureau 4 N4 115C (Niveau 4) Boulevard du Triomphe, entrée ULB 2 B-1050 Bruxelles office: [EMAIL PROTECTED] home: [EMAIL PROTECTED] --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: how to sum path in a tree?
i think this kind of thing is more suited to the nested sets model for trees. a google will show you a million hits, heres one: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html im not a fan of nested sets because theres a huge overhead to changing nodes (requires updates of 50% of the rows) and it doesnt suit what I generally use trees for, which is instead of one huge tree, many smaller trees which correspond to some document-oriented concept like an XML document or something, that I read fully into an object structure anyway. but if youre doing math type stuff nested sets probably better. On Mar 15, 2007, at 10:25 AM, svilen wrote: There is some graph - represented as edges in some assoc.table, and they having some associated item with them (e.g. weight or length). Is it possible to calculate the overall lenght of path from node to node (if there is a path at all) in SQL? Finding if there is a path in the graph from node1 to node2, with max, say, 2 hops will look like: FROM node as node1, node as node2, node as node3 link as link1, link as link2 SELECT link1.length, WHERE node1.id == link1.in AND node2.id == link1.out ##1 hop OR node1.id == link1.in AND node2.id == link1.out AND node2.id == link2.in AND node3.id == link2.out ##2 hops but i dont see how to also sum the link.length. or should i use UNION instead of OR? This can be extended to any static number of hops... but is it possible to be done for a dynamic (unknown) number? ciao svil --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLite driver and space in the TypeEgine.get_col_spec
Michael Bayer ha scritto:
read the thread, no idea what a space has to do with anything. can
you be more specific.
pySQLite read the first word of the column spec to find the converter.
Here is a full example:
import decimal
from pysqlite2 import dbapi2 as sqlite
from sqlalchemy import *
db = create_engine('sqlite:///',
connect_args={'detect_types': sqlite.PARSE_DECLTYPES})
conn = db.contextual_connect()
# To avoid the conversion to float
sqlite.register_converter(NUMERIC, lambda s: s)
class Decimal(TypeEngine):
def __init__(self, precision=10, length=2):
self.precision = precision
self.length = length
def get_col_spec(self):
if 1:
# Make sure to add a spece after the first string
prefix = 'NUMERIC '
else:
prefix = 'NUMERIC'
return prefix + '(%(precision)s, %(length)s)' % {'precision':
self.precision, 'length' : self.length}
def convert_bind_param(self, value, dialect):
return str(value)
def convert_result_value(self, value, dialect):
return decimal.Decimal(value)
metadata = BoundMetaData(db)
a = Table(
'a', metadata,
Column('x', Decimal(7, 3)),
Column('y', Decimal(10, 5)),
)
metadata.create_all()
i = a.insert()
conn.execute(i, x=decimal.Decimal('12.42'), y=decimal.Decimal('1'))
s = a.select()
print conn.execute(s).fetchone()
Whitout the space after 'NUMERIC', pysqlite does not call the converter
I have registered.
Regards Manlio Perillo
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: how to sum path in a tree?
i think this kind of thing is more suited to the nested sets model for trees. a google will show you a million hits, heres one: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html ahha. thanks. So this is to look/represent/store different part of the thing; in my case i need to store and query the transitive closure (all possible paths) of the graph - and keep the node/edges elsewhere (which is graph's primary description). im not a fan of nested sets because theres a huge overhead to changing nodes (requires updates of 50% of the rows) and it doesnt suit what I generally use trees for, which is instead of one huge tree, many smaller trees which correspond to some document-oriented concept like an XML document or something, that I read fully into an object structure anyway. but if youre doing math type stuff nested sets probably better. yeah, it queries/stores some precomputed thing and not the original tree/graph decription itself. Hence the overhead of pre-computing at every change. in my case i may not realy need SQL for this as the graph is relatively small, so i can load all of it, compute somehow and store just the query-results for future direct reference/query. Which is more or less the same thing as above, just going one step further. On Mar 15, 2007, at 10:25 AM, svilen wrote: There is some graph - represented as edges in some assoc.table, and they having some associated item with them (e.g. weight or length). Is it possible to calculate the overall lenght of path from node to node (if there is a path at all) in SQL? Finding if there is a path in the graph from node1 to node2, with max, say, 2 hops will look like: FROM node as node1, node as node2, node as node3 link as link1, link as link2 SELECT link1.length, WHERE node1.id == link1.in AND node2.id == link1.out##1 hop OR node1.id == link1.in AND node2.id == link1.out AND node2.id == link2.in AND node3.id == link2.out ##2 hops but i dont see how to also sum the link.length. or should i use UNION instead of OR? This can be extended to any static number of hops... but is it possible to be done for a dynamic (unknown) number? ciao svil --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: statement
use literal text (since its PG only anyway), or use func.ARRAY(). On Mar 15, 2007, at 11:16 AM, Julien Cigar wrote: Hello list, Any idea how I could generate this SQL statement under SQLAlchemy (especially the PostreSQL ARRAY clause ...) ? SELECT i.id, t.name AS taxo, i.geographic_range, ARRAY( (SELECT h.name FROM habitats h, invasive_habitats ih WHERE h.id=ih.habitat_id AND ih.invasive_id=i.id) ) AS habitats, (SELECT ivn.name FROM invasive_names ivn, languages l WHERE ivn.language_id=l.id AND l.iso_code='la' AND ivn.invasive_id=i.id ) AS scientific_name FROM invasives i, taxonomies t WHERE t.id=i.group_id ORDER BY i.id; Thanks, Julien -- Julien Cigar Belgian Biodiversity Platform http://www.biodiversity.be Université Libre de Bruxelles Campus de la Plaine CP 257 Bâtiment NO, Bureau 4 N4 115C (Niveau 4) Boulevard du Triomphe, entrée ULB 2 B-1050 Bruxelles office: [EMAIL PROTECTED] home: [EMAIL PROTECTED] --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLite driver and space in the TypeEgine.get_col_spec
the docs say register_converter is for custom types. seems like
kind of a hack here ? why not make your own type ?
On Mar 15, 2007, at 11:22 AM, Manlio Perillo wrote:
Michael Bayer ha scritto:
read the thread, no idea what a space has to do with anything. can
you be more specific.
pySQLite read the first word of the column spec to find the converter.
Here is a full example:
import decimal
from pysqlite2 import dbapi2 as sqlite
from sqlalchemy import *
db = create_engine('sqlite:///',
connect_args={'detect_types':
sqlite.PARSE_DECLTYPES})
conn = db.contextual_connect()
# To avoid the conversion to float
sqlite.register_converter(NUMERIC, lambda s: s)
class Decimal(TypeEngine):
def __init__(self, precision=10, length=2):
self.precision = precision
self.length = length
def get_col_spec(self):
if 1:
# Make sure to add a spece after the first string
prefix = 'NUMERIC '
else:
prefix = 'NUMERIC'
return prefix + '(%(precision)s, %(length)s)' % {'precision':
self.precision, 'length' : self.length}
def convert_bind_param(self, value, dialect):
return str(value)
def convert_result_value(self, value, dialect):
return decimal.Decimal(value)
metadata = BoundMetaData(db)
a = Table(
'a', metadata,
Column('x', Decimal(7, 3)),
Column('y', Decimal(10, 5)),
)
metadata.create_all()
i = a.insert()
conn.execute(i, x=decimal.Decimal('12.42'), y=decimal.Decimal('1'))
s = a.select()
print conn.execute(s).fetchone()
Whitout the space after 'NUMERIC', pysqlite does not call the
converter
I have registered.
Regards Manlio Perillo
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Making msg board
Hi, I doing a msg board with nested sets as descrived on http://dev.mysql.com/tech-resources/articles/hierarchical-data.html I'm not sure how to do a statement like this in sqlalchemy: SELECT node.id, node.topic, node.content FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 1 ORDER BY node.lft; how do i specify parent and node distinctions? I have a assign_mapped Message() so, i'm doing Message.select(_and(Message.c.lft.between(Message.c.lft, Message.c.rgt), Message.c.id==1), order_by=Message.c.lft) which might not work right? can someone suggest a how do to this please? thanks, -tml --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Making msg board
use table.alias() for one of the roles - or for both. Hi, I doing a msg board with nested sets as descrived on http://dev.mysql.com/tech-resources/articles/hierarchical-data.html I'm not sure how to do a statement like this in sqlalchemy: SELECT node.id, node.topic, node.content FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 1 ORDER BY node.lft; how do i specify parent and node distinctions? I have a assign_mapped Message() so, i'm doing Message.select(_and(Message.c.lft.between(Message.c.lft, Message.c.rgt), Message.c.id==1), order_by=Message.c.lft) which might not work right? can someone suggest a how do to this please? thanks, -tml --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy test suite - no success/failure exit code
OK this was something small, the alltests.py scripts needed to call testbase's main() function and not its runTests() method, so that the exit code is propigated. rev 2414. On Mar 14, 2007, at 10:30 PM, [EMAIL PROTECTED] wrote: Some of you may know that I run a Python buildbot for SQLAlchemy: http://www.python.org/dev/buildbot/community/all/?show=g5%20OSX% 20trunkshow=g5%20OSX%202.5 It always shows build successful even though there are plenty of actual test failures on my Mac. After letting it languish for a long time I dug into the problem a bit this evening. It seems that SQLAlchemy's test script (test/alltests.py) doesn't exit with any indication of success or failure. Consequently, it appears to always succeed. I'm not much of a unittest module person, but it appears the unittest.TextTestRunner class is a fundamental barrier to extracting a useful error code from the tests. Its run() method doesn't seem to return anything useful, at least nothing that's documented. I suppose the simplest hack would be to temporarily replace sys.stderr with a StringIO object when running the test, rummage around in it looking for failures=NN, errors=MM, restore sys.stderr, dump the output, then exit with NN+MM. I could also replace test/alltests.py with something of my own invention but I'd prefer to rely as much as possible on the test setup presented by the SQA distribution. Skip --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Best practices for database connection errors
Hi, In my application I'm working with three different databases. Case 1: For 2 databases I'm using the connection object directly execute sql and Case 2: In the 3rd database I'm using the statement object and then doing the statement.execute, where the db is implicitly associated with the statement through the columns. What is the best way to handle database connection errors in both the cases above. Ideally you would want the database layer to recreate the connection and then try to execute the statement again. Thanks, Vineet --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Making msg board
Hi, that worked great! thanks :) - tml On Mar 15, 9:10 am, svilen [EMAIL PROTECTED] wrote: use table.alias() for one of the roles - or for both. Hi, I doing a msg board with nested sets as descrived on http://dev.mysql.com/tech-resources/articles/hierarchical-data.html I'm not sure how to do a statement like this in sqlalchemy: SELECT node.id, node.topic, node.content FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 1 ORDER BY node.lft; how do i specify parent and node distinctions? I have a assign_mapped Message() so, i'm doing Message.select(_and(Message.c.lft.between(Message.c.lft, Message.c.rgt), Message.c.id==1), order_by=Message.c.lft) which might not work right? can someone suggest a how do to this please? thanks, -tml --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLite driver and space in the TypeEgine.get_col_spec
Michael Bayer ha scritto:
the docs say register_converter is for custom types. seems like
kind of a hack here ?
As I can see, it is not an hack.
why not make your own type ?
I have tried to replace 'NUMERIC' with 'DECIMAL' or 'XDECIMAL';
the result is the same: pysqlite converts the column's value to a float.
I do not understand this behaviour, however, as for SQLite docs[1],
every user defined type has the NUMERIC affinity (this means that SQLite
treats it as a float, if the value can be converted to a float).
It seems that the only solution is to register a custom converter, but
SQLAlchemy *must* add a space before the '(' in the col_spec.
[1]
2.1 Determination Of Column Affinity
The type affinity of a column is determined by the declared type of the
column, according to the following rules:
1. If the datatype contains the string INT then it is assigned
INTEGER affinity.
2. If the datatype of the column contains any of the strings CHAR,
CLOB, or TEXT then that column has TEXT affinity. Notice that the
type VARCHAR contains the string CHAR and is thus assigned TEXT affinity.
3. If the datatype for a column contains the string BLOB or if no
datatype is specified then the column has affinity NONE.
4. If the datatype for a column contains any of the strings REAL,
FLOA, or DOUB then the column has REAL affinity
5. Otherwise, the affinity is NUMERIC.
So, it seems that pysqlite just enforces the type affinity converting
the column's value to the correspondind Python type
(NUMERIC - REAL - float).
Thanks and regards Manlio Perillo
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] table name bind param
t = metadata.engine.text(LOCK TABLE :table_name WRITE; ,
bindparams=[bindparam('table_name', type=types.String)])
will not work as the generated statement looks like:
LOCK TABLE 'my_table_name' WRITE;
Is there any other type I can specify which doesn't quote the string
for tablename? The table name isn't input by user. I get it from
self.mapper.local_table.name inside a class's method.
thanks.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Making msg board
Hi svilen, thanks. I have that query working now. On Mar 15, 9:10 am, svilen [EMAIL PROTECTED] wrote: use table.alias() for one of the roles - or for both. Hi, I doing a msg board with nested sets as descrived on http://dev.mysql.com/tech-resources/articles/hierarchical-data.html I'm not sure how to do a statement like this in sqlalchemy: SELECT node.id, node.topic, node.content FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.id = 1 ORDER BY node.lft; how do i specify parent and node distinctions? I have a assign_mapped Message() so, i'm doing Message.select(_and(Message.c.lft.between(Message.c.lft, Message.c.rgt), Message.c.id==1), order_by=Message.c.lft) which might not work right? can someone suggest a how do to this please? thanks, -tml --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: table name bind param
also to clarify, the text actually has :table_name used in many other
places:
t = metadata.engine.text(LOCK TABLE :table_name WRITE;
UPDATE :table_name SET rgt=rgt + 2 WHERE
rgt :insert_node_val and parent_id = :parent_id;
UPDATE :table_name SET lft=lft + 2 WHERE
lft :insert_node_val and parent_id = :parent_id;
..
so if i had it as %s, i would have to repeat the same name multiple
times in % (name, name, name). I'm ok with this, just curious if there
is a better way.
thanks.
On Mar 15, 9:37 am, tml [EMAIL PROTECTED] wrote:
t = metadata.engine.text(LOCK TABLE :table_name WRITE; ,
bindparams=[bindparam('table_name', type=types.String)])
will not work as the generated statement looks like:
LOCK TABLE 'my_table_name' WRITE;
Is there any other type I can specify which doesn't quote the string
for tablename? The table name isn't input by user. I get it from
self.mapper.local_table.name inside a class's method.
thanks.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] reflection of unicode columns
Hi all,
I'm working with postgres and sqlalchemy, I found a strange behavior
(a bug?) of the reflection code,
take a look at this:
#
import sqlalchemy as sa
engine = sa.create_engine('postgres://xxx:[EMAIL PROTECTED]/xxx')
metadata = sa.BoundMetaData(engine)
tbl1 = sa.Table('test', metadata, sa.Column('test', sa.Unicode(100)))
print tbl1.c['test'].type, type(tbl1.c['test'].type) is sa.Unicode
metadata.create_all()
metadata = sa.BoundMetaData(engine)
tbl2 = sa.Table('test', metadata, autoload=True)
print tbl2.c['test'].type, type(tbl2.c['test'].type) is sa.Unicode
#
The output of this snippet is
# ---8-
Unicode() True
PGString(length=100) False
# ---8--
The reflection code doesn't recognize the Unicode type, same behavior
with
engine = sa.create_engine('sqlite:///')
thank you
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: table name bind param
tml wrote:
also to clarify, the text actually has :table_name used in many other
places:
t = metadata.engine.text(LOCK TABLE :table_name WRITE;
UPDATE :table_name SET rgt=rgt +
2 WHERE rgt :insert_node_val and parent_id = :parent_id;
UPDATE :table_name SET lft=lft +
2 WHERE lft :insert_node_val and parent_id = :parent_id;
..
so if i had it as %s, i would have to repeat the same name
multiple times in % (name, name, name). I'm ok with this,
just curious if there is a better way.
I don't think bind parameters can be used for table names, so you are
stuck with python format strings, but you can use a dictionary instead
of a tuple when formatting strings. Instead of using %s, you use
%(name)s, and instead of the tuple (name, name, name) you pass a single
dictionary {'name': your_table_name}
http://docs.python.org/lib/typesseq-strings.html
Hope that helps,
Simon
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: statement
On Mar 15, 2007, at 12:23 PM, Julien Cigar wrote: it returns something like: (u'freshwater', 33, 1983, u'Vascular plants', u'Lemna minuta', u'Minute duckweed') (u'terrestial', 39, 2006, u'Vascular plants', u'Lysichiton americanus', u'American skunk cabbage') (u'freshwater', 39, 2006, u'Vascular plants', u'Lysichiton americanus', u'American skunk cabbage') What I would like is: (u'freshwater', 33, 1983, u'Vascular plants', u'Lemna minuta', u'Minute duckweed') (['terrestial', 'freshwater'], 39, 2006, u'Vascular plants', u'Lysichiton americanus', u'American skunk cabbage') I don't understand why it fails with func.array() ... any idea ? no, i dont have much experience with PG arrays. look at the SQL text the expression is generating, and compare that against literal SQL text that you know does what you want. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Best practices for database connection errors
On Mar 15, 2007, at 12:19 PM, vinjvinj wrote: cases above. Ideally you would want the database layer to recreate the connection and then try to execute the statement again. whys that ? what if you just have the wrong connection string, or the database is stopped ? do you expect the database to suddenly start working again ? we have currently very limited support for auto-reconnecting to a database that has stopped and started again, since the DBAPI's do not throw consistent errors at consistent points of execution. if you think your database was stopped and is now re-started, you can call invalidate() on an individual connection and then close() it for it to be replaced in the pool, or more likely you can call dispose() on your engine and it will load up a new connection pool upon the next usage. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: reflection of unicode columns
postgres doesnt have a unicode column type. the Unicode SA type is a
string type with additional python-side processing of the data.
to reflect a table with some columns coming back as Unicode, see the
metadata docs on overriding columns.
also you might want to forego the Unicode type altogether and just
use convert_unicode=True on your create_engine().
On Mar 15, 2007, at 12:57 PM, dvd wrote:
Hi all,
I'm working with postgres and sqlalchemy, I found a strange behavior
(a bug?) of the reflection code,
take a look at this:
#
import sqlalchemy as sa
engine = sa.create_engine('postgres://xxx:[EMAIL PROTECTED]/xxx')
metadata = sa.BoundMetaData(engine)
tbl1 = sa.Table('test', metadata, sa.Column('test', sa.Unicode(100)))
print tbl1.c['test'].type, type(tbl1.c['test'].type) is sa.Unicode
metadata.create_all()
metadata = sa.BoundMetaData(engine)
tbl2 = sa.Table('test', metadata, autoload=True)
print tbl2.c['test'].type, type(tbl2.c['test'].type) is sa.Unicode
#
The output of this snippet is
# ---8-
Unicode() True
PGString(length=100) False
# ---8--
The reflection code doesn't recognize the Unicode type, same behavior
with
engine = sa.create_engine('sqlite:///')
thank you
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLite driver and space in the TypeEgine.get_col_spec
if a space in the DDL for a create table is actually significant to
SQLite, then im not sure if i want to build that into the core.
also im not even sure what you really want, is it this?
NUMERIC (19,5)
anyway, i still dont understand why you dont make your own type. by
which I mean:
class MyType(types.Numeric):
def get_col_spec(self):
return SOME_TOTALLY_NEW_TYPE
On Mar 15, 2007, at 12:27 PM, Manlio Perillo wrote:
Michael Bayer ha scritto:
the docs say register_converter is for custom types. seems like
kind of a hack here ?
As I can see, it is not an hack.
why not make your own type ?
I have tried to replace 'NUMERIC' with 'DECIMAL' or 'XDECIMAL';
the result is the same: pysqlite converts the column's value to a
float.
I do not understand this behaviour, however, as for SQLite docs[1],
every user defined type has the NUMERIC affinity (this means that
SQLite
treats it as a float, if the value can be converted to a float).
It seems that the only solution is to register a custom converter, but
SQLAlchemy *must* add a space before the '(' in the col_spec.
[1]
2.1 Determination Of Column Affinity
The type affinity of a column is determined by the declared type of
the
column, according to the following rules:
1. If the datatype contains the string INT then it is assigned
INTEGER affinity.
2. If the datatype of the column contains any of the strings
CHAR,
CLOB, or TEXT then that column has TEXT affinity. Notice that the
type VARCHAR contains the string CHAR and is thus assigned TEXT
affinity.
3. If the datatype for a column contains the string BLOB or
if no
datatype is specified then the column has affinity NONE.
4. If the datatype for a column contains any of the strings
REAL,
FLOA, or DOUB then the column has REAL affinity
5. Otherwise, the affinity is NUMERIC.
So, it seems that pysqlite just enforces the type affinity converting
the column's value to the correspondind Python type
(NUMERIC - REAL - float).
Thanks and regards Manlio Perillo
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Best practices for database connection errors
throw consistent errors at consistent points of execution. if you
think your database was stopped and is now re-started, you can call
invalidate() on an individual connection and then close() it for it
to be replaced in the pool, or more likely you can call dispose() on
your engine and it will load up a new connection pool upon the next
usage.
So where should I put this logic. In the past I would have created
wrappers around the db.execute command and in that I would have
checked if there were any errors. If there were any errors I would try
to see if there were any connection related errors and then try to
reconect and then resubmit the sql statement.
However, I'm a little confused on how I would do this with sql
alchemy. More sepcifically with all the introspection that goes on.
For instance for one of my connections I do the following:
metadata = BoundMetaData(mysqlDb.mysql_db)
view1= sqlalchemy.Table('view1', db.metadata, autoload=True)
So would I do the following:
connect_to_db():
[recreate the mysqlDb engine]
metadata = BoundMetaData(mysqlDb.mysql_db)
view1= sqlalchemy.Table('view1', db.metadata, autoload=True)
execute_statement(statement):
try:
statement.execute()
except: [check for connection related errors]
connect_to_db
statement.execute()
Or,
I'm assuming the above is not enough, because all the other views
would be invalidated as well.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy test suite - no success/failure exit code
Michael OK this was something small, the alltests.py scripts needed Michael to call testbase's main() function and not its runTests() Michael method, so that the exit code is propigated. rev 2414. Excellent. Trying a test run now... Skip --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Best practices for database connection errors
Id say you have to monkeypatch Connection._execute_raw() with a
wrapping method for now (or dialect.do_execute()/do_execute_many
()).I have observed that execute() is not the only place you
might get an exception. sometimes you get it when calling cursor()
(we do catch those and invalidate). We do have connection dropped
logic built in for MySQL but have not implemented for postgres or
others. if you have exception checking logic that is reliable for
a particular database, send it over and I will patch it.
On Mar 15, 2007, at 1:28 PM, vinjvinj wrote:
throw consistent errors at consistent points of execution. if you
think your database was stopped and is now re-started, you can call
invalidate() on an individual connection and then close() it for it
to be replaced in the pool, or more likely you can call dispose() on
your engine and it will load up a new connection pool upon the next
usage.
So where should I put this logic. In the past I would have created
wrappers around the db.execute command and in that I would have
checked if there were any errors. If there were any errors I would try
to see if there were any connection related errors and then try to
reconect and then resubmit the sql statement.
However, I'm a little confused on how I would do this with sql
alchemy. More sepcifically with all the introspection that goes on.
For instance for one of my connections I do the following:
metadata = BoundMetaData(mysqlDb.mysql_db)
view1= sqlalchemy.Table('view1', db.metadata, autoload=True)
So would I do the following:
connect_to_db():
[recreate the mysqlDb engine]
metadata = BoundMetaData(mysqlDb.mysql_db)
view1= sqlalchemy.Table('view1', db.metadata, autoload=True)
execute_statement(statement):
try:
statement.execute()
except: [check for connection related errors]
connect_to_db
statement.execute()
Or,
I'm assuming the above is not enough, because all the other views
would be invalidated as well.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: don't automatically stringify compiled statements - patch to base.py
On 3/15/07, Michael Bayer [EMAIL PROTECTED] wrote:
well at least make a full blown patch that doesnt break all the other
DB's. notice that an Engine doesnt just execute Compiled objects, it
can execute straight strings as well. thats why the dialect's
do_execute() and do_executemany() take strings - they are assumed to
go straight to a DBAPI representation. to take the stringness out
of Engine would be a large rework to not just Engine but all the
dialects.
im surprised the execute_compiled() method works for you at all, as
its creating a cursor, calling result set metadata off the cursor,
etc. all these DBAPI things which you arent supporting. it seems
like it would be cleaner for you if you werent even going through
that implementation of it.
Well, I was trying my best to be a good citizen, so I made some
classes that implement all of the methods that the pieces of
execute_compiled seemed to want, faking it for now when I didn't need
it. The semantics of most of the DBAPI map to the NDBAPI fairly well
(it's still all the same underlying db ideas - just no sql strings)
BUT...
the theme here is that the base Engine is assuming a DBAPI
underneath. if you want an Engine that does not assume string
statements and DBAPI's it might be easier for you to just provide a
subclass of Engine instead (or go even lower level, subclass
sqlalchemy.sql.Executor). either way you can change what
create_engine() returns by using a new strategy to create_engine(),
which is actually a pluggable API. e.g.
This seems like what I really want to try, because you are right,
trying to get this to pretend to be totally DBAPI is going to be not
totally fun. I'll see what trouble I get myself into this way... or
I'll send a patch that changes all the internals. :)
Thanks!
from sqlalchemy.engine.strategies import DefaultEngineStrategy
class NDBAPIEngineStrategy(DefaultEngineStrategy):
def __init__(self):
DefaultEngineStrategy.__init__(self, 'ndbapi')
def get_engine_cls(self):
return NDBAPIEngine
# register the strategy
NDBAPIEngineStrategy()
now you connect via:
create_engine(url, strategy='ndbapi')
if you want to go one level lower, which i think you do because you
dont really want pooling or any of that either, you dont even need to
have connection pooling or anything like thatyou can totally
override what create_engine() does, have different connection
parameters, whatever. just subclass EngineStrategy directly:
class NDBAPIEngineStrategy(EngineStrategy):
def __init__(self):
EngineStrategy.__init__(self, 'ndbapi')
def create(self, *args, **kwargs):
# this is some arbitrary set of arguments
return NDAPIEngine(kwargs.get('connect_string'), kwargs.get
('some_other_argument'), new NDBAPIDialect(*args), etc etc)
# register
NBAPIEngineStrategy()
then you just say:
create_engine(connect_string='someconnectstring',
some_other_argument='somethingelse', strategy='ndbapi')
i.e. whatever you want. create_engine() just passes *args/**kwargs
through to create() after pulling out the strategy keyword.
if you dont like having to send over strategy i can add a hook in
there to look it up on the dialect, so it could be more like
create_engine('ndbapi://whatever'). but anyway this method would
mean we wouldnt have to rewrite half of Engine's internals.
On Mar 14, 2007, at 7:28 PM, Monty Taylor wrote:
Hi - I'd attach this as a patch file, but it's just too darned
small...
I would _love_ it if we didn't automatically stringify compiled
statements here in base.py, because there is no way to override this
behavior in a dialect. I'm working on an NDBAPI dialect to support
direct access to MySQL Cluster storage, and so I never actually have a
string representation of the query. Most of the time this is fine, but
to make it work, I had to have pre_exec do the actual execution,
because by the time I got to do_execute, I didn't have my real object
anymore.
I know this would require some other code changes to actually get
applied - namely, I'm sure there are other places now where the
statement should be str()'d to make sense.
Alternately, we could add a method to Compiled. (I know there is
already get_str()) like get_final_query() that gets called in this
context instead. Or even, although it makes my personal code less
readable, just call compiled.get_str() here, which is the least
invasive, but requires non-string queries to override a method called
get_str() to achieve a purpose that is not a stringification.
Other than this, so far I've actually got the darned thing inserting
records, so it's going pretty well... other than a whole bunch of test
code I put in to find out why it wasn't inserting when the problem was
that I was checking the wrong table... *doh*
Thanks!
Monty
=== modified file 'lib/sqlalchemy/engine/base.py'
---
[sqlalchemy] Examples of joins between three or more tables?
Hi All, Good examples of rudimentary joins of 3 or more tables are hard to find. Please point me to some decent examples. Thank you, ~G~ --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: don't automatically stringify compiled statements - patch to base.py
YES. This is good stuff.
Thanks again.
On 3/16/07, Monty Taylor [EMAIL PROTECTED] wrote:
On 3/15/07, Michael Bayer [EMAIL PROTECTED] wrote:
well at least make a full blown patch that doesnt break all the other
DB's. notice that an Engine doesnt just execute Compiled objects, it
can execute straight strings as well. thats why the dialect's
do_execute() and do_executemany() take strings - they are assumed to
go straight to a DBAPI representation. to take the stringness out
of Engine would be a large rework to not just Engine but all the
dialects.
im surprised the execute_compiled() method works for you at all, as
its creating a cursor, calling result set metadata off the cursor,
etc. all these DBAPI things which you arent supporting. it seems
like it would be cleaner for you if you werent even going through
that implementation of it.
Well, I was trying my best to be a good citizen, so I made some
classes that implement all of the methods that the pieces of
execute_compiled seemed to want, faking it for now when I didn't need
it. The semantics of most of the DBAPI map to the NDBAPI fairly well
(it's still all the same underlying db ideas - just no sql strings)
BUT...
the theme here is that the base Engine is assuming a DBAPI
underneath. if you want an Engine that does not assume string
statements and DBAPI's it might be easier for you to just provide a
subclass of Engine instead (or go even lower level, subclass
sqlalchemy.sql.Executor). either way you can change what
create_engine() returns by using a new strategy to create_engine(),
which is actually a pluggable API. e.g.
This seems like what I really want to try, because you are right,
trying to get this to pretend to be totally DBAPI is going to be not
totally fun. I'll see what trouble I get myself into this way... or
I'll send a patch that changes all the internals. :)
Thanks!
from sqlalchemy.engine.strategies import DefaultEngineStrategy
class NDBAPIEngineStrategy(DefaultEngineStrategy):
def __init__(self):
DefaultEngineStrategy.__init__(self, 'ndbapi')
def get_engine_cls(self):
return NDBAPIEngine
# register the strategy
NDBAPIEngineStrategy()
now you connect via:
create_engine(url, strategy='ndbapi')
if you want to go one level lower, which i think you do because you
dont really want pooling or any of that either, you dont even need to
have connection pooling or anything like thatyou can totally
override what create_engine() does, have different connection
parameters, whatever. just subclass EngineStrategy directly:
class NDBAPIEngineStrategy(EngineStrategy):
def __init__(self):
EngineStrategy.__init__(self, 'ndbapi')
def create(self, *args, **kwargs):
# this is some arbitrary set of arguments
return NDAPIEngine(kwargs.get('connect_string'), kwargs.get
('some_other_argument'), new NDBAPIDialect(*args), etc etc)
# register
NBAPIEngineStrategy()
then you just say:
create_engine(connect_string='someconnectstring',
some_other_argument='somethingelse', strategy='ndbapi')
i.e. whatever you want. create_engine() just passes *args/**kwargs
through to create() after pulling out the strategy keyword.
if you dont like having to send over strategy i can add a hook in
there to look it up on the dialect, so it could be more like
create_engine('ndbapi://whatever'). but anyway this method would
mean we wouldnt have to rewrite half of Engine's internals.
On Mar 14, 2007, at 7:28 PM, Monty Taylor wrote:
Hi - I'd attach this as a patch file, but it's just too darned
small...
I would _love_ it if we didn't automatically stringify compiled
statements here in base.py, because there is no way to override this
behavior in a dialect. I'm working on an NDBAPI dialect to support
direct access to MySQL Cluster storage, and so I never actually have a
string representation of the query. Most of the time this is fine, but
to make it work, I had to have pre_exec do the actual execution,
because by the time I got to do_execute, I didn't have my real object
anymore.
I know this would require some other code changes to actually get
applied - namely, I'm sure there are other places now where the
statement should be str()'d to make sense.
Alternately, we could add a method to Compiled. (I know there is
already get_str()) like get_final_query() that gets called in this
context instead. Or even, although it makes my personal code less
readable, just call compiled.get_str() here, which is the least
invasive, but requires non-string queries to override a method called
get_str() to achieve a purpose that is not a stringification.
Other than this, so far I've actually got the darned thing inserting
records, so it's going pretty well... other than a whole bunch of test
code I put
[sqlalchemy] Re: Examples of joins between three or more tables?
a SQL statement containing joins usually has the joins in a chain: select * from table1 JOIN table2 ON table1.somecolumn = table2.somecolumn JOIN table3 on table2.somecolumn=table3.somecolumn ... SQLAlchemy's join function works in a similar way. you can keep calling join on the previous join in a generative fashion: j = table1.join(table2, table1.c.somecolumn==table2.c.somecolumn).join (table3, table2.c.somecolumn==table3.c.somecolumn) the above construct generates into just the join clause part of the SQL statement. but it is a selectable, meaning its an element that contains its own list of columns and can be used in the FROM clause of a SELECT statement. when you have a selectable, you can select all columns by saying: j.select() or the selectable can be added to the FROM clause of any select using from_obj; s = select([table1.c.col1, table2.c.col2, ...], from_obj=[j]) using from_obj as above, you can combine the join with any other set of selectables. On Mar 15, 2007, at 7:13 PM, Gloria wrote: Hi All, Good examples of rudimentary joins of 3 or more tables are hard to find. Please point me to some decent examples. Thank you, ~G~ --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
