[sqlalchemy] Re: creating a database through SQLAlchemy
Travis Kriplean ha scritto: However, this seems a bit ugly. Is there a way to obtain a non- transactional connection from an engine in 0.3.10? If not, is it possible in 0.4? I use this with SA 0.3.10 and Postgres: engine.raw_connection().set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) engine.text(CREATE DATABASE %s ENCODING = 'utf8' % dbname).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] SA does implicit cascading
Hello, here is a sample:
children_table = Table('children', metadata,
Column('id', Integer, primary_key=True))
child2group_table = Table('child2group', metadata,
Column('child_id', Integer,
ForeignKey('children.id'), nullable=False),
Column('group_id', Integer,
ForeignKey('groups.id'), nullable=False))
groups_table = Table('groups', metadata,
Column('id', Integer, primary_key=True))
mapper(Child, children_table,
properties={ 'groups':relation(Group,
secondary=child2group_table) })
mapper(Group, groups_table, properties={
'children':relation(Child, secondary=child2group_table) })
Speaking English, this means there are groups and children, and child
can belong to some groups.
When I issue this:
group = session.query(Group).get(2)
session.delete(group)
session.flush()
SA does this:
2007-09-03 11:40:25,915 INFO sqlalchemy.engine.base.Engine.0x..8c
BEGIN
2007-09-03 11:40:25,918 INFO sqlalchemy.engine.base.Engine.0x..8c
DELETE FROM child2group WHERE child2group.child_id = ? AND
child2group.group_id = ?
2007-09-03 11:40:25,918 INFO sqlalchemy.engine.base.Engine.0x..8c [[3,
2], [4, 2]]
2007-09-03 11:40:25,919 INFO sqlalchemy.engine.base.Engine.0x..8c
DELETE FROM groups WHERE groups.id = ?
2007-09-03 11:40:25,920 INFO sqlalchemy.engine.base.Engine.0x..8c [2]
2007-09-03 11:40:25,921 INFO sqlalchemy.engine.base.Engine.0x..8c
COMMIT
Well, I dont mind, because this is what I really wanted to have.
Please, explain, why does this happen? I thought only group item would
be deleted and I would get orphaned records in child2group table.
--~--~-~--~~~---~--~~
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: sql executemany and postgresql - probably bug
Hi,
this issue (#759) seems fixed with the SA0.4b5. Thanks, Michael.
One additional question concerning last_inserted_ids() - is it
supposed to work below:
...
isql = Insert( table_Manager, values= {'name':bindparam('name'),
'duties':bindparam('duties
r2 = con.execute( isql, [
dict( name= 'torencho', duties= 'bany'),
dict( name= 'mnogoVojdMalkoIndianec', duties= 'lany'),
])
ids = r2.last_inserted_ids()
...
or i am using it improperly?
as it is now on the trunk(r3449) it gives error: AttributeError:
'PGExecutionContext' object has no attribute '_last_inserted_ids'.
regards,
stefan
--~--~-~--~~~---~--~~
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] A question about shard strategy
I have read about hibernate's shard strategyhttp://www.hibernate.org/hib_docs/shards/reference/en/html/shards-shardstrategy.html , and sqlalchemy's shard implementation. which remind me of a system in my company which implemented similar function, but both hibernate and sqlalchemy only separate data into multiple databases, but our system not only separate data into multiple databases but also into multiple tables of each database. i.e. there's not just db1,db2,db3... , but in each database, there is also table_weather_locations_1, table_weather_locations_2, table_weather_locations_3 ... I think the main reason for separating data into tables is when a table become huge the operations on that table would become slow. Then i'm wondering why both hibernate and sqlalchemy don't do that. -- http://codeplayer.blogspot.com/ --~--~-~--~~~---~--~~ 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: sql executemany and postgresql - probably bug
On Sep 3, 2007, at 4:38 AM, che wrote:
Hi,
this issue (#759) seems fixed with the SA0.4b5. Thanks, Michael.
One additional question concerning last_inserted_ids() - is it
supposed to work below:
...
isql = Insert( table_Manager, values= {'name':bindparam('name'),
'duties':bindparam('duties
r2 = con.execute( isql, [
dict( name= 'torencho', duties= 'bany'),
dict( name= 'mnogoVojdMalkoIndianec', duties= 'lany'),
])
ids = r2.last_inserted_ids()
...
or i am using it improperly?
as it is now on the trunk(r3449) it gives error: AttributeError:
'PGExecutionContext' object has no attribute '_last_inserted_ids'.
OK, well the code needs to be cleaned up such that you get None and
not an attribute error there, but the last_inserted_ids
functionality only works when you execute a single set of
arguments. the reason for this is that DBAPI doesnt even define any
way to get at list of last inserted ids for an executemany() (i.e. it
only has cursor.lastrowid at best). With PG we execute a sequence,
but we still dont go through the extra overhead of storing it for
every row of the executemany, and in the latest release ive optimized
executemany a lot more so that we arent even pre executing the
sequence.
the name of the method is actually not that great, its plural ids
because of the potentially mulitple PK columns from a single row.
So in this case it should be returing None.
--~--~-~--~~~---~--~~
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: A question about shard strategy
i think multiple tables within one database is not that common since you get all the downside of a sharded approach but not so much of the performance enhancement of moving the load over to a new server. also a lot of DBAs would be skeptical that one big table is so much slower than several smaller tables, if proper indexing is applied to the one big table. we do support storing the same class across multiple local tables, though slightly more explicitly than the sharding API does, using the entity_name parameter. Its in the mapper configuration docs. On Sep 3, 2007, at 9:11 AM, 黄毅 wrote: I have read about hibernate's shard strategy , and sqlalchemy's shard implementation. which remind me of a system in my company which implemented similar function, but both hibernate and sqlalchemy only separate data into multiple databases, but our system not only separate data into multiple databases but also into multiple tables of each database. i.e. there's not just db1,db2,db3... , but in each database, there is also table_weather_locations_1, table_weather_locations_2, table_weather_locations_3 ... I think the main reason for separating data into tables is when a table become huge the operations on that table would become slow. Then i'm wondering why both hibernate and sqlalchemy don't do that. -- http://codeplayer.blogspot.com/ --~--~-~--~~~---~--~~ 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] Self referencing keys
Excuse the newbie question, how does one declare a sef-referencing key? I would like to have fields modified_by and created_by in my users table, the field should refer back to the user id or user name in the same table. 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: Self referencing keys
would this work? users = Table(users, metadata, Column(id,Integer,primary_key=True), Column(username, String(50),unique=True, nullable=False), Column(password, String(255)), Column(email, String(255),unique=True, nullable=False), Column(firstname, String(255)), Column(lastname, String(255)), Column(modifiedby_id, Integer, ForeignKey(users.id)) Column(modifiedon,DateTime(timezone=True), default=func.now()), On Sep 3, 6:50 pm, voltron [EMAIL PROTECTED] wrote: Excuse the newbie question, how does one declare a sef-referencing key? I would like to have fields modified_by and created_by in my users table, the field should refer back to the user id or user name in the same table. 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: Self referencing keys
On Monday 03 September 2007 19:57:54 voltron wrote: would this work? users = Table(users, metadata, Column(id,Integer,primary_key=True), Column(username, String(50),unique=True, nullable=False), Column(password, String(255)), Column(email, String(255),unique=True, nullable=False), Column(firstname, String(255)), Column(lastname, String(255)), Column(modifiedby_id, Integer, ForeignKey(users.id)) Column(modifiedon,DateTime(timezone=True), default=func.now()), On Sep 3, 6:50 pm, voltron [EMAIL PROTECTED] wrote: Excuse the newbie question, how does one declare a sef-referencing key? I would like to have fields modified_by and created_by in my users table, the field should refer back to the user id or user name in the same table. Thanks yes, foreign key + eventualy use_alter=True if u get cyclic --~--~-~--~~~---~--~~ 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] insert of a sequence of dictionaries
The following line:
r = cnnctn.execute(insert(t),
{'xkey': 'k1','yval':1},
{'xkey': 'k2','yval':2},
{'xkey': 'k3'})
Cause the following :
INSERT INTO `A` (xkey, yval) VALUES (%s, %s)
[['k1', 1], ['k2', 2], ['k3', 1]]
i.e. the unspecified value in the 3rd dict is copied from the 1st one.
This is quite surprising as I would have expected null() to be used in
such cases.
Is there a rationale behind this?
Is there a way to have some non-full dicts as in the above example so
that it will behave as I expected?
--~--~-~--~~~---~--~~
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: Self referencing keys
A pointing to A, is cyclical dependency. same as A - B - A. but in latter case u must choose one of the links to be added later, that is use_later=True for ForeignKey. in former case the table declaration may or may not work without use_alter. in both cases u need post_update=True for the relation/mapper of the loop-closing link of your choice. On Tuesday 04 September 2007 00:21:06 you wrote: Thanks for the reply, what do you mean by cyclic? On Sep 3, 9:00 pm, [EMAIL PROTECTED] wrote: On Monday 03 September 2007 19:57:54 voltron wrote: would this work? users = Table(users, metadata, Column(id,Integer,primary_key=True), Column(username, String(50),unique=True, nullable=False), Column(password, String(255)), Column(email, String(255),unique=True, nullable=False), Column(firstname, String(255)), Column(lastname, String(255)), Column(modifiedby_id, Integer, ForeignKey(users.id)) Column(modifiedon,DateTime(timezone=True), default=func.now()), On Sep 3, 6:50 pm, voltron [EMAIL PROTECTED] wrote: Excuse the newbie question, how does one declare a sef-referencing key? I would like to have fields modified_by and created_by in my users table, the field should refer back to the user id or user name in the same table. Thanks yes, foreign key + eventualy use_alter=True if u get cyclic --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
