Re: [sqlalchemy] Re: H2 database
2015-04-24 18:06 GMT+02:00 Jonathan Vanasco jonat...@findmeon.com: SqlAlchemy needs 2 things for a database work: 1. A python database driver 2. A SqlAlchemy dialect (that tells SqlAlchemy how to write sql for the driver) So... 1. H2 doesn't seem to have any Python drivers or other support. I just came across an old forum post that talked about possibly using Postgresql client since they supported similar protocols, but that's about all I could find. 2. There are a handful of posts and articles on writing custom dialects. Several recent ones as well. If you're just trying to handle the dialect, and their syntax is reasonably similar to an existing dialect... I think you could do it alone. If you have to write the general Python support as well though, that's a bigger task. I inquired also on the H2 list and got the following response: H2 supports the PostgeSQL wire protocol, so you can use the PostgreSQL database driver. Although you will probably need to tweek the dialect description that SqlAlchemy needs. So it should be doable. I have another project. :-D -- Cecil Westerhof -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] H2 database
I am using the H2 database. (http://h2database.com/html/main.html) It looks like it is not possible to use this database with SQLAlchemy. Is that correct? If so: what kind of effort would it take to make SQLAlchemy work with the H2 database? Would that be something a one person team (yours truly) could pull off? -- Cecil Westerhof -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Is there a simple way to let records have the same groups as it parents
I was just wondering if the folowing possible. A record has severall groups connected through a N:M relation. But it also has a parent. What I would like is that all the groups from the parent (and its parent, etc.) also are seen as group for the record. When the parent has a group added or removed that should then be 'visible' at the child. Not a very good description I am afraid, so I'll try to give an example. Say I have the groups G1, G2, G3, G4 and G5. I have the records R1 and R2. R1 is the parent of R2 and does not have a parent itself. If R1 has the groups G1 and G2 and R2 has the groups G2, G4 and G5, then when asking the list of groups of R2 should give, G1, G2, G4 and G5. When quering for records that have group G1, both R1 and R2 should be returned. When adding G3 to R1 the list for R2 should be: G1, G2, G3, G4 and G5. When removing G1 from R1 the list for R2 should be: G2, G3, G4 and G5. When removing G2 from R1 the list for R2 should be: G2, G3, G4 and G5. (R2 has itself also group G2.) Is this possible with sqlalchemy or has this to be done by hand? -- Cecil Westerhof --~--~-~--~~~---~--~~ 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: wx and SqlAlchemy
2008/8/28 Mike [EMAIL PROTECTED]: Does this work for multiple databases? This particular program I am working on will be connecting to 2 or 3 databases and a table or three in each of those. I'm pretty sure I have to create separate engines for each db and probably bind separate sessions for those. Nope. I use two databases, no engine and only one session. Maybe not optimal (I just started learning sqlalchemy and wxpython), but it is possible. -- Cecil Westerhof --~--~-~--~~~---~--~~ 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: Is there a simple way to let records have the same groups as it parents
2008/8/28 [EMAIL PROTECTED]:
this is sort of inheritance of data, right?
You could it call like that I suppose.
the best i've made so far about this is to get (somehow) all the Rs
each with it's groups, and then do the inheritance (union over the
path towards root in your case) over the result rows by hand.
I was afraid for that.
if u find a better way let me know...
Ofcourse, but I am afraid it is not likely. :-{
traversing a (recursive) tree isn't an easy thing either, unless u
save it as nonrecursive (keeping extra links etc) in a way or
another.
I'll have to think about that. But properly this is only going to work
with tables that are not to big I am afraid.
Maybe I want to fancy things.
--
Cecil Westerhof
--~--~-~--~~~---~--~~
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: Is there a simple way to let records have the same groups as it parents
2008/8/28 Michael Bayer [EMAIL PROTECTED]: On Aug 28, 2008, at 8:11 AM, Cecil Westerhof wrote: I was just wondering if the folowing possible. A record has severall groups connected through a N:M relation. But it also has a parent. What I would like is that all the groups from the parent (and its parent, etc.) also are seen as group for the record. When the parent has a group added or removed that should then be 'visible' at the child. Not a very good description I am afraid, so I'll try to give an example. Say I have the groups G1, G2, G3, G4 and G5. I have the records R1 and R2. R1 is the parent of R2 and does not have a parent itself. If R1 has the groups G1 and G2 and R2 has the groups G2, G4 and G5, then when asking the list of groups of R2 should give, G1, G2, G4 and G5. When quering for records that have group G1, both R1 and R2 should be returned. When adding G3 to R1 the list for R2 should be: G1, G2, G3, G4 and G5. When removing G1 from R1 the list for R2 should be: G2, G3, G4 and G5. When removing G2 from R1 the list for R2 should be: G2, G3, G4 and G5. (R2 has itself also group G2.) Is this possible with sqlalchemy or has this to be done by hand? I wouldn't say by hand. An element's groups are basically its own groups unioned with the groups of its parent.So a simple recursion is the simplest way to form this group, but this only handles the object graph side of the equation, not the Query side. Thanks for the info. I'll have to digest it, because I am just starting with sqlalchemy. But it is nice to know that I could implement this functionality. On the Query side, the basic job is to formulate joins to the parent, Would it no be better to the child? Otherwise you need to traverse all records, which would be inefficient -I think- when for example only 1% of the records are in the group. Or am I overlooking something? -- Cecil Westerhof --~--~-~--~~~---~--~~ 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: Working with several database without needing to know in which database an object resides
2008/8/27 Michael Bayer [EMAIL PROTECTED]: Is this a good way of doing things, or should I work with severall engines? I dont quite understand how the difference between Query and select() has an impact on using a single or multiple Engine objects.So I'll sum up the two multiple databases approaches. Vertical partitioning means that different tables reside in different engines, and horizontal partitioning, also known as sharding, means that the same tables reside in multiple engines, but individual rows are spread across multiple databases. SQLA has support for both of these paradigms which are described (brielfy) at http://www.sqlalchemy.org/docs/05/session.html#unitofwork_partitioning . Not sure if this is what you're looking for. In a way it is. But the problem is that I need to bind the engine to the session. And the engine is only known in the imported models and session only in the main code. But it looks likes it works without this. The selects on tables from different databases return what they should. And I just tried a session.save with a session.commit and it did what it should do. So it looks like I do not have to bind the enigines to the session because the engine is fetched out of the class that is mapped to a sqlalchemy.Table instance. -- Cecil Westerhof --~--~-~--~~~---~--~~ 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] Displaying table partly in table object
I have a program in which I display severall tables. This is partly
generic, so I would like to have one general function and put the
specific functionality in the object definition itself.
For example I have:
#
kmStandTable = sa.Table(
'kmStand', metadata,
sa.Column('datum', sa.Date,primary_key = True),
sa.Column('beginStand', sa.Integer, nullable = False),
sa.Column('eindStand', sa.Integer, nullable = False),
)
class KmStand(object):
def __repr__(self):
return 'KmStand: %10.10s, %6d, %6d' % (self.datum,
self.beginStand, self.eindStand)
columns = (
('Datum', 80),
(BeginStand, 80),
('EindStand', 80),
('Verschil', 60),
)
size = (320, 600)
title = 'Km Stand'
sa_orm.mapper(KmStand, kmStandTable)
#
And in my main code I have:
#
i = 0
for row in session.query(KmStand).all():
if (row.beginStand == -1) or (row.eindStand == -1):
afstand = -1
else:
afstand = row.eindStand - row.beginStand
addRow(self, i,('%10.10s' % (row.datum), '%6d' % (row.beginStand),
'%6d' % (row.eindStand), '%5d' % (afstand)))
i = i + 1
#
This is quite a simple example. There are a few other tables where
there has to be done a lot more and is what has to be done dependend
on the previous record.
What I would like to do is to put in my main code:
#
records = Table().getRecords()
i = 0
for record in records:
addRow(self, i,record)
i = i + 1
#
And in my model:
#
kmStandTable = sa.Table(
'kmStand', metadata,
sa.Column('datum', sa.Date,primary_key = True),
sa.Column('beginStand', sa.Integer, nullable = False),
sa.Column('eindStand', sa.Integer, nullable = False),
)
class KmStand(object):
def __repr__(self):
return 'KmStand: %10.10s, %6d, %6d' % (self.datum,
self.beginStand, self.eindStand)
def getRecords(self):
records = []
for record in kmStandTable.select().execute():
if (record.beginStand == -1) or (record.eindStand == -1):
afstand = -1
else:
afstand = record.eindStand - record.beginStand
records.append(('%10.10s' % (record.datum), '%6d' %
(record.beginStand),
'%6d' % (record.eindStand), '%5d' % (afstand)))
return records
columns = (
('Datum', 80),
(BeginStand, 80),
('EindStand', 80),
('Verschil', 60),
)
size = (320, 600)
title = 'Km Stand'
sa_orm.mapper(KmStand, kmStandTable)
#
I tried this and it works. But is this a good way to do this? For
example is the returning of a big array not to expensive? At the
moment there are not that many records, but when the table grows, the
returned array will grow also.
--
Cecil Westerhof
--~--~-~--~~~---~--~~
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: Working with several database without needing to know in which database an object resides
2008/8/26 Cecil Westerhof [EMAIL PROTECTED]:
First I had:
for row in engine.execute('SELECT MIN(gewicht) AS gewicht '
', MIN(vet) AS vet '
', MAX(water) AS water '
', MAX(spieren) AS spieren '
'FROM gewicht '):
but because I want it to have the code engine independend I changed that to:
for row in sa.select([sa.func.min(Gewicht.gewicht)
, sa.func.min(Gewicht.vet)
, sa.func.max(Gewicht.water)
, sa.func.max(Gewicht.spieren)]).execute():
The problem with this is that I now need to use row[0] instead of
row.gewicht. Is there a way to use row.gewicht also in the case
sa.select?
I found a solution. In my class Gewicht I added:
def getEngine(self):
return gewichtTable.metadata.bind.engine
Before
for row in engine.execute('SELECT MIN(gewicht) AS gewicht '
I added
engine = Gewicht().getEngine()
and now I have the old functionality back.
--
Cecil Westerhof
--~--~-~--~~~---~--~~
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: Working with several database without needing to know in which database an object resides
2008/8/27 Michael Bayer [EMAIL PROTECTED]: OK, then dont bind the engine to your MetaData, thats confusing the issue. Just bind each Session to the appropraite Engine(s). The Session, if not explicitly bound to an engine, gets the engine from the underlying MetaData associated with the Table objects. That usage is not recommended for a multi-database configuration. Also give the docs on Database Metadata a read which has some background on this. Maybe I understand you wrong, or did I not explain myself correctly. Just to be clear, I'll try to explain what I am doing. It seems to work, but maybe it is not a good solution when the tables grow. If so, I'll gladly implement a better solution. First of all I only have one session. What would be the reason to have severall? I do not have a engine. As I understand it, the engine is fetched from the class that is mapped to a sqlalchemy.Table instance. In my main code I have: # import sqlalchemy as sa import sqlalchemy.orm as sa_orm Session = sa_orm.sessionmaker() session = Session() from auto_model import KmStand, Verbruik from metingen_model import Gewicht, Gwe # And in the future possible there will come more models, every one in principal with its own database. For my selects I use sa.select. For example: row = sa.select([sa.func.max(KmStand.eindStand), sa.func.max(KmStand.beginStand)]).execute().fetchone() For insertion I use: newKmStand= KmStand() newKmStand.datum = datum newKmStand.beginStand = beginStand newKmStand.eindStand = eindStand session.save(newKmStand) session.commit() At this moment I am not working with update and delete. (Just started trying to implement something with sqlalchemy.) But when people would like to know how I am going to solve that, I can post that at the moment I am using those. (I could use sa.delete and sa.update, but I suppose it would be better to use session as I do with insert.) As far as I can see, this does what I want it to do. But is it a good way to do things? Or is it inherently wrong? By the way is there a method to put an AS part in the select? For example: # stmt = sa.select([sa.func.max(KmStand.eindStand)]) print stmt SELECT max(kmStand.eindStand) AS max_1 FROM kmStand # I would like to have: # SELECT max(kmStand.eindStand) AS eindStand FROM kmStand # Is there a way to do this? -- Cecil Westerhof --~--~-~--~~~---~--~~ 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: Working with several database without needing to know in which database an object resides
2008/8/27 Cecil Westerhof [EMAIL PROTECTED]: At this moment I am not working with update and delete. (Just started trying to implement something with sqlalchemy.) But when people would like to know how I am going to solve that, I can post that at the moment I am using those. (I could use sa.delete and sa.update, but I suppose it would be better to use session as I do with insert.) I defined a dummy table to test updates and deletes. It works, the way I do it: for updates: # dummy = session.query(Dummy).get(1) dummy.tekst = 'andere tekst' session.commit() # for deletes: # dummy = session.query(Dummy).get(1) session.delete(dummy) session.commit() # Quite straigt forward. -- Cecil Westerhof --~--~-~--~~~---~--~~ 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: Working with several database without needing to know in which database an object resides
2008/8/27 Cecil Westerhof [EMAIL PROTECTED]:
By the way is there a method to put an AS part in the select?
For example:
#
stmt = sa.select([sa.func.max(KmStand.eindStand)])
print stmt
SELECT max(kmStand.eindStand) AS max_1
FROM kmStand
#
I would like to have:
#
SELECT max(kmStand.eindStand) AS eindStand
FROM kmStand
#
I found a way. Maybe not the best, but it works.
#
stmt = sa.text('SELECT MAX(kmStand.eindStand) AS eindStand FROM
kmStand', bind = KmStand.c.datum.table.metadata.bind)
row = stmt.execute().fetchone()
print row.eindStand
#
--
Cecil Westerhof
--~--~-~--~~~---~--~~
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] Working with several database without needing to know in which database an object resides
I just started playing with sqlalchemy. At the moment I am working
with one database, but properly I need to work with severall database
at the same time, but I want to work in such a way, that I do not need
to know in which database a certain table resides.
Is this possible?
For example it looks like it is possible for the session object.
I use in a model file (through an import):
#
import sqlalchemy as sa
import sqlalchemy.orm as sa_orm
metadata = sa.MetaData('sqlite:home/cecil/databases/metingen.db')
engine = metadata.bind
Session = sa_orm.sessionmaker()
session = Session()
gewichtTable = sa.Table(
'gewicht', metadata,
sa.Column('id', sa.Integer, primary_key = True),
sa.Column('datum', sa.Date, nullable = False, unique = True),
sa.Column('gewicht', sa.Float, nullable = False),
sa.Column('vet', sa.Float, nullable = False),
sa.Column('water', sa.Float, nullable = False),
sa.Column('spieren', sa.Float, nullable = False),
)
class Gewicht(object):
def __repr__(self):
return 'Gewicht: %10.10s, %5.1f, %5.1f, %5.1f, %5.1f' %
(self.datum, self.gewicht, self.vet, self.water, self.spieren)
sa_orm.mapper(Gewicht, gewichtTable)
#
In the main program I have:
#
import sqlalchemy as sa
import sqlalchemy.orm as sa_orm
Session = sa_orm.sessionmaker()
session = Session()
from metingen_model import Gewicht, gewichtTable
#
And later on I do:
#
for row in session.query(Gewicht).all():
#
And this does what it should do. So I expect that the engine is
fetched out of Gewicht.
First I had:
for row in engine.execute('SELECT MIN(gewicht) AS gewicht '
', MIN(vet) AS vet '
', MAX(water) AS water '
', MAX(spieren) AS spieren '
'FROM gewicht '):
but because I want it to have the code engine independend I changed that to:
for row in sa.select([sa.func.min(Gewicht.gewicht)
, sa.func.min(Gewicht.vet)
, sa.func.max(Gewicht.water)
, sa.func.max(Gewicht.spieren)]).execute():
The problem with this is that I now need to use row[0] instead of
row.gewicht. Is there a way to use row.gewicht also in the case
sa.select?
Is this a good way of doing things, or should I work with severall engines?
Also sqlalchemy and sqlalchemy.orm are now imported in the main
program and in metingen_model. And later on they will become imported
in even more modules. Is this a problem? I was told that Python was
intelligent enough to see that sqlalchemy and sqlalchemy.orm are
allready imported and that importing again should not have
-significant- consequences. Is that true?
--
Cecil Westerhof
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
