[sqlalchemy] SQLAlchemy best practise
I'm new to SQLAlchemy and loving it. But reading all documentation online makes me wonder if there are any best practice documentation for sqlalchemy out there ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] oursql two phase commit syntax error
I'm testing this on SQLAlchemy 0.7.1, oursql 0.9.2, MySQL 5.5.13 on
Mac OS X 10.6.7
Here's my test script:
from sqlalchemy import create_engine, Column, Integer, Unicode
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(128), nullable=False, unique=True)
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, autoincrement=True, primary_key=True)
address = Column(Unicode(128), nullable=False, unique=True)
engine = create_engine(mysql+oursql://tester:tester@localhost/
test_hometasty?charset=utf8)
engine_bindings = {User: engine, Address: engine}
User.metadata.create_all(engine)
Address.metadata.create_all(engine)
Session = scoped_session(sessionmaker(twophase=True))
Session.configure(binds=session_bindings)
Session.configure(binds=engine_bindings)
session = Session()
alice = User(name=ualice)
session.add(alice)
hk = Address(address=uHong Kong)
session.add(hk)
session.commit()
Here's the error I get:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, 'You have
an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near
\'_sa_7fd8e09924568e2e2a653185227c2929\' at line 1', None) 'XA BEGIN
_sa_7fd8e09924568e2e2a653185227c2929' ()
Am I doing something wrong or is this a bug?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] SQLAlchemy best practise
I think the session about sessions do that explanation: http://www.sqlalchemy.org/docs/orm/session.html 2011/6/6 Liju lij...@gmail.com I'm new to SQLAlchemy and loving it. But reading all documentation online makes me wonder if there are any best practice documentation for sqlalchemy out there ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] oursql two phase commit syntax error
On Jun 6, 2011, at 2:23 PM, Yuen Ho Wong wrote:
I'm testing this on SQLAlchemy 0.7.1, oursql 0.9.2, MySQL 5.5.13 on
Mac OS X 10.6.7
Here's my test script:
from sqlalchemy import create_engine, Column, Integer, Unicode
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(128), nullable=False, unique=True)
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, autoincrement=True, primary_key=True)
address = Column(Unicode(128), nullable=False, unique=True)
engine = create_engine(mysql+oursql://tester:tester@localhost/
test_hometasty?charset=utf8)
engine_bindings = {User: engine, Address: engine}
User.metadata.create_all(engine)
Address.metadata.create_all(engine)
Session = scoped_session(sessionmaker(twophase=True))
Session.configure(binds=session_bindings)
Session.configure(binds=engine_bindings)
session = Session()
alice = User(name=ualice)
session.add(alice)
hk = Address(address=uHong Kong)
session.add(hk)
session.commit()
you might want to check that you're on MySQL 5.5 on all systems - the script
works for me, provided I comment out the non-existent session_bindings
variable.
my output is below:
2011-06-06 14:56:37,278 INFO sqlalchemy.engine.base.Engine
CREATE TABLE address (
id INTEGER NOT NULL AUTO_INCREMENT,
address VARCHAR(128) NOT NULL,
PRIMARY KEY (id),
UNIQUE (address)
)
2011-06-06 14:56:37,278 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,279 INFO sqlalchemy.engine.base.Engine COMMIT
2011-06-06 14:56:37,280 INFO sqlalchemy.engine.base.Engine
CREATE TABLE user (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(128) NOT NULL,
PRIMARY KEY (id),
UNIQUE (name)
)
2011-06-06 14:56:37,280 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine COMMIT
2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine DESCRIBE `address`
2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,282 INFO sqlalchemy.engine.base.Engine DESCRIBE `user`
2011-06-06 14:56:37,282 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,285 INFO sqlalchemy.engine.base.Engine XA BEGIN
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,285 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,286 INFO sqlalchemy.engine.base.Engine INSERT INTO address
(address) VALUES (?)
2011-06-06 14:56:37,286 INFO sqlalchemy.engine.base.Engine (u'Hong Kong',)
2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine INSERT INTO user
(name) VALUES (?)
2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine (u'alice',)
2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine XA END
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine XA PREPARE
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine XA COMMIT
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
Here's the error I get:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, 'You have
an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near
\'_sa_7fd8e09924568e2e2a653185227c2929\' at line 1', None) 'XA BEGIN
_sa_7fd8e09924568e2e2a653185227c2929' ()
Am I doing something wrong or is this a bug?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: oursql two phase commit syntax error
Ah it seems that this bug only happens with sql-mode = ANSI set in
my.cnf. This doesn't seem to be an issue with the mysql-python driver
tho.
On Jun 7, 2:57 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Jun 6, 2011, at 2:23 PM, Yuen Ho Wong wrote:
I'm testing this on SQLAlchemy 0.7.1, oursql 0.9.2, MySQL 5.5.13 on
Mac OS X 10.6.7
Here's my test script:
from sqlalchemy import create_engine, Column, Integer, Unicode
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(128), nullable=False, unique=True)
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, autoincrement=True, primary_key=True)
address = Column(Unicode(128), nullable=False, unique=True)
engine = create_engine(mysql+oursql://tester:tester@localhost/
test_hometasty?charset=utf8)
engine_bindings = {User: engine, Address: engine}
User.metadata.create_all(engine)
Address.metadata.create_all(engine)
Session = scoped_session(sessionmaker(twophase=True))
Session.configure(binds=session_bindings)
Session.configure(binds=engine_bindings)
session = Session()
alice = User(name=ualice)
session.add(alice)
hk = Address(address=uHong Kong)
session.add(hk)
session.commit()
you might want to check that you're on MySQL 5.5 on all systems - the script
works for me, provided I comment out the non-existent session_bindings
variable.
my output is below:
2011-06-06 14:56:37,278 INFO sqlalchemy.engine.base.Engine
CREATE TABLE address (
id INTEGER NOT NULL AUTO_INCREMENT,
address VARCHAR(128) NOT NULL,
PRIMARY KEY (id),
UNIQUE (address)
)
2011-06-06 14:56:37,278 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,279 INFO sqlalchemy.engine.base.Engine COMMIT
2011-06-06 14:56:37,280 INFO sqlalchemy.engine.base.Engine
CREATE TABLE user (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(128) NOT NULL,
PRIMARY KEY (id),
UNIQUE (name)
)
2011-06-06 14:56:37,280 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine COMMIT
2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine DESCRIBE `address`
2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,282 INFO sqlalchemy.engine.base.Engine DESCRIBE `user`
2011-06-06 14:56:37,282 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,285 INFO sqlalchemy.engine.base.Engine XA BEGIN
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,285 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,286 INFO sqlalchemy.engine.base.Engine INSERT INTO
address (address) VALUES (?)
2011-06-06 14:56:37,286 INFO sqlalchemy.engine.base.Engine (u'Hong Kong',)
2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine INSERT INTO user
(name) VALUES (?)
2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine (u'alice',)
2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine XA END
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine XA PREPARE
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine XA COMMIT
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
Here's the error I get:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, 'You have
an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near
\'_sa_7fd8e09924568e2e2a653185227c2929\' at line 1', None) 'XA BEGIN
_sa_7fd8e09924568e2e2a653185227c2929' ()
Am I doing something wrong or is this a bug?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] porting GeoAlchemy to 0.7
Hi i'm currently in the process of porting GeoAlchemy to SQLAlchemy 0.7. The first issue I'm having is related to before_create and after_create DDL listeners we have in GeoAlchemy. We use before_create and after_create listeners to prevent SQLA from adding the geometry column, and do it ourselves. Basically, the before_create function removes the geometry column from table._columns, and the after_create function adds the geometry column by calling the AddGeometryColumn SQL function. I'm trying to use a similar mechanism with 0.7, relying on before_create and after_create event listeners. That doesn't work, because setting table._colums seems to have no effect, i.e. SQLA still attempts to add the gemetry column. I've been thinking about resetting table.c (setting it to None or something) and using table.append_column to add all columns but the geometry column in before_create, but I'm wondering if that's the proper way. Thanks for any guidance on that, PS: I was hoping to get inspiration from examples/postgis.py, but this example looks outdated. Maybe it should be removed from the 0.7 code base. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: oursql two phase commit syntax error
Replace the double quotes around the %s with single quotes seems to
have solved the problem with either default SQL MODE or ANSI_QUOTES
set.
Thanks for the helping!
On Jun 7, 3:25 am, Yuen Ho Wong wyue...@gmail.com wrote:
Ah it seems that this bug only happens with sql-mode = ANSI set in
my.cnf. This doesn't seem to be an issue with the mysql-python driver
tho.
On Jun 7, 2:57 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Jun 6, 2011, at 2:23 PM, Yuen Ho Wong wrote:
I'm testing this on SQLAlchemy 0.7.1, oursql 0.9.2, MySQL 5.5.13 on
Mac OS X 10.6.7
Here's my test script:
from sqlalchemy import create_engine, Column, Integer, Unicode
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(128), nullable=False, unique=True)
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, autoincrement=True, primary_key=True)
address = Column(Unicode(128), nullable=False, unique=True)
engine = create_engine(mysql+oursql://tester:tester@localhost/
test_hometasty?charset=utf8)
engine_bindings = {User: engine, Address: engine}
User.metadata.create_all(engine)
Address.metadata.create_all(engine)
Session = scoped_session(sessionmaker(twophase=True))
Session.configure(binds=session_bindings)
Session.configure(binds=engine_bindings)
session = Session()
alice = User(name=ualice)
session.add(alice)
hk = Address(address=uHong Kong)
session.add(hk)
session.commit()
you might want to check that you're on MySQL 5.5 on all systems - the
script works for me, provided I comment out the non-existent
session_bindings variable.
my output is below:
2011-06-06 14:56:37,278 INFO sqlalchemy.engine.base.Engine
CREATE TABLE address (
id INTEGER NOT NULL AUTO_INCREMENT,
address VARCHAR(128) NOT NULL,
PRIMARY KEY (id),
UNIQUE (address)
)
2011-06-06 14:56:37,278 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,279 INFO sqlalchemy.engine.base.Engine COMMIT
2011-06-06 14:56:37,280 INFO sqlalchemy.engine.base.Engine
CREATE TABLE user (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(128) NOT NULL,
PRIMARY KEY (id),
UNIQUE (name)
)
2011-06-06 14:56:37,280 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine COMMIT
2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine DESCRIBE
`address`
2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,282 INFO sqlalchemy.engine.base.Engine DESCRIBE `user`
2011-06-06 14:56:37,282 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,285 INFO sqlalchemy.engine.base.Engine XA BEGIN
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,285 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,286 INFO sqlalchemy.engine.base.Engine INSERT INTO
address (address) VALUES (?)
2011-06-06 14:56:37,286 INFO sqlalchemy.engine.base.Engine (u'Hong Kong',)
2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine INSERT INTO user
(name) VALUES (?)
2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine (u'alice',)
2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine XA END
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine XA PREPARE
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine XA COMMIT
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
Here's the error I get:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, 'You have
an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near
\'_sa_7fd8e09924568e2e2a653185227c2929\' at line 1', None) 'XA BEGIN
_sa_7fd8e09924568e2e2a653185227c2929' ()
Am I doing something wrong or is this a bug?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options,
Re: [sqlalchemy] porting GeoAlchemy to 0.7
it just changes to .columns, should fix it as below:
def __init__(self, table):
event.listen(table, before_create, self.before_create)
event.listen(table, after_create, self.after_create)
event.listen(table, before_drop, self.before_drop)
event.listen(table, after_drop, self.before_drop)
self._stack = []
def before_create(self, target, connection, **kw):
self(before-create, target, connection)
def after_create(self, target, connection, **kw):
self(after-create, target, connection)
def before_drop(self, target, connection, **kw):
self(before-drop, target, connection)
def after_drop(self, target, connection, **kw):
self(after-drop, target, connection)
def __call__(self, event, table, bind):
if event in ('before-create', 'before-drop'):
regular_cols = [c for c in table.c if not isinstance(c.type,
Geometry)]
gis_cols = set(table.c).difference(regular_cols)
self._stack.append(table.c)
table.columns = expression.ColumnCollection(*regular_cols)
if event == 'before-drop':
for c in gis_cols:
bind.execute(select([func.DropGeometryColumn('public',
table.name, c.name)], autocommit=True))
elif event == 'after-create':
table.columns = self._stack.pop()
for c in table.c:
if isinstance(c.type, Geometry):
bind.execute(select([func.AddGeometryColumn(table.name,
c.name, c.type.srid, c.type.name, c.type.dimension)], autocommit=True))
elif event == 'after-drop':
table.columns = self._stack.pop()
On Jun 6, 2011, at 3:47 PM, Eric Lemoine wrote:
Hi
i'm currently in the process of porting GeoAlchemy to SQLAlchemy 0.7.
The first issue I'm having is related to before_create and
after_create DDL listeners we have in GeoAlchemy.
We use before_create and after_create listeners to prevent SQLA from
adding the geometry column, and do it ourselves.
Basically, the before_create function removes the geometry column from
table._columns, and the after_create function adds the geometry column
by calling the AddGeometryColumn SQL function.
I'm trying to use a similar mechanism with 0.7, relying on
before_create and after_create event listeners. That doesn't work,
because setting table._colums seems to have no effect, i.e. SQLA
still attempts to add the gemetry column.
I've been thinking about resetting table.c (setting it to None or
something) and using table.append_column to add all columns but the
geometry column in before_create, but I'm wondering if that's the
proper way.
Thanks for any guidance on that,
PS: I was hoping to get inspiration from examples/postgis.py, but this
example looks outdated. Maybe it should be removed from the 0.7 code
base.
--
Eric Lemoine
Camptocamp France SAS
Savoie Technolac, BP 352
73377 Le Bourget du Lac, Cedex
Tel : 00 33 4 79 44 44 96
Mail : eric.lemo...@camptocamp.com
http://www.camptocamp.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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: oursql two phase commit syntax error
ah, so little bit of a bug on our end then. Seems to be hardcoded in the
oursql driver. this is #2186
On Jun 6, 2011, at 3:56 PM, Yuen Ho Wong wrote:
Replace the double quotes around the %s with single quotes seems to
have solved the problem with either default SQL MODE or ANSI_QUOTES
set.
Thanks for the helping!
On Jun 7, 3:25 am, Yuen Ho Wong wyue...@gmail.com wrote:
Ah it seems that this bug only happens with sql-mode = ANSI set in
my.cnf. This doesn't seem to be an issue with the mysql-python driver
tho.
On Jun 7, 2:57 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Jun 6, 2011, at 2:23 PM, Yuen Ho Wong wrote:
I'm testing this on SQLAlchemy 0.7.1, oursql 0.9.2, MySQL 5.5.13 on
Mac OS X 10.6.7
Here's my test script:
from sqlalchemy import create_engine, Column, Integer, Unicode
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(128), nullable=False, unique=True)
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, autoincrement=True, primary_key=True)
address = Column(Unicode(128), nullable=False, unique=True)
engine = create_engine(mysql+oursql://tester:tester@localhost/
test_hometasty?charset=utf8)
engine_bindings = {User: engine, Address: engine}
User.metadata.create_all(engine)
Address.metadata.create_all(engine)
Session = scoped_session(sessionmaker(twophase=True))
Session.configure(binds=session_bindings)
Session.configure(binds=engine_bindings)
session = Session()
alice = User(name=ualice)
session.add(alice)
hk = Address(address=uHong Kong)
session.add(hk)
session.commit()
you might want to check that you're on MySQL 5.5 on all systems - the
script works for me, provided I comment out the non-existent
session_bindings variable.
my output is below:
2011-06-06 14:56:37,278 INFO sqlalchemy.engine.base.Engine
CREATE TABLE address (
id INTEGER NOT NULL AUTO_INCREMENT,
address VARCHAR(128) NOT NULL,
PRIMARY KEY (id),
UNIQUE (address)
)
2011-06-06 14:56:37,278 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,279 INFO sqlalchemy.engine.base.Engine COMMIT
2011-06-06 14:56:37,280 INFO sqlalchemy.engine.base.Engine
CREATE TABLE user (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(128) NOT NULL,
PRIMARY KEY (id),
UNIQUE (name)
)
2011-06-06 14:56:37,280 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine COMMIT
2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine DESCRIBE
`address`
2011-06-06 14:56:37,281 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,282 INFO sqlalchemy.engine.base.Engine DESCRIBE `user`
2011-06-06 14:56:37,282 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,285 INFO sqlalchemy.engine.base.Engine XA BEGIN
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,285 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,286 INFO sqlalchemy.engine.base.Engine INSERT INTO
address (address) VALUES (?)
2011-06-06 14:56:37,286 INFO sqlalchemy.engine.base.Engine (u'Hong Kong',)
2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine INSERT INTO user
(name) VALUES (?)
2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine (u'alice',)
2011-06-06 14:56:37,287 INFO sqlalchemy.engine.base.Engine XA END
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine XA PREPARE
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine XA COMMIT
_sa_9121998e519e1b3edb13e0aa440ca7c7
2011-06-06 14:56:37,288 INFO sqlalchemy.engine.base.Engine ()
Here's the error I get:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, 'You have
an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near
\'_sa_7fd8e09924568e2e2a653185227c2929\' at line 1', None) 'XA BEGIN
_sa_7fd8e09924568e2e2a653185227c2929' ()
Am I doing something wrong or is this a bug?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
Re: [sqlalchemy] Secondary tables and deleting
On Jun 6, 2011, at 6:11 PM, Geoff wrote: I've searched for an answer to this but can't find one but I did find an old archived e-mail discussing this which refers to ticket #249. I have two tables in a many-many relationship. I do not want deletes to cascade between these two tables but I do want any delete to cascade to the secondary table in order to prevent the secondary table having rows that point to nowhere. A recent check on my (sqlite) database shows that SQLAlchemy does not do this automatically but the discussion I found seems to indicate that this should happen. Is it possible that this is a regression? Or maybe I used be doing something that I'm not? The table referenced by secondary= in a relationship() is managed entirely, regardless of cascade setting, but only from the perspective of the parent. Meaning if A references a collection of B, the row in a_to_b will be deleted if you 1. delete A, or 2. remove a B from A's collection. Deleting a B by itself as in session.delete(B) won't have an effect unless you apply a backref to the relationship so that each B also knows about its collection of A. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Secondary tables and deleting
On Jun 6, 11:32 pm, Michael Bayer mike...@zzzcomputing.com wrote:
The table referenced by secondary= in a relationship() is managed entirely,
regardless of cascade setting, but
only from the perspective of the parent. Meaning if A references a
collection of B, the row in a_to_b will be
deleted if you 1. delete A, or 2. remove a B from A's collection. Deleting
a B by itself as in session.delete(B)
won't have an effect unless you apply a backref to the relationship so that
each B also knows about its collection
of A.
I am indeed deleting B but in this case I do have the backref
specified in the parent table A e.g.
Class A(Base):
toB = relationship('B', secondary=a_b, backref='toA')
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Secondary tables and deleting
On Jun 6, 2011, at 7:13 PM, Geoff wrote:
On Jun 6, 11:32 pm, Michael Bayer mike...@zzzcomputing.com wrote:
The table referenced by secondary= in a relationship() is managed
entirely, regardless of cascade setting, but
only from the perspective of the parent. Meaning if A references a
collection of B, the row in a_to_b will be
deleted if you 1. delete A, or 2. remove a B from A's collection. Deleting
a B by itself as in session.delete(B)
won't have an effect unless you apply a backref to the relationship so that
each B also knows about its collection
of A.
I am indeed deleting B but in this case I do have the backref
specified in the parent table A e.g.
Class A(Base):
toB = relationship('B', secondary=a_b, backref='toA')
Below is a short test, can you figure out what you might be doing differently ?
from sqlalchemy import create_engine, Column, Integer, Table, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, relationship
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
related = relationship(B,
secondary=Table(
'secondary',
Base.metadata,
Column('aid', Integer, ForeignKey('a.id'),
primary_key=True),
Column('bid', Integer, ForeignKey('b.id'),
primary_key=True)),
backref=related_a
)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
engine = create_engine(sqlite://, echo=True)
Base.metadata.create_all(engine)
s = Session(engine)
b1, b2, b3 = B(), B(), B()
a1 = A(related=[b1, b2, b3])
s.add(a1)
s.commit()
assert s.query(Base.metadata.tables['secondary']).count() == 3
s.delete(b2)
s.commit()
assert s.query(Base.metadata.tables['secondary']).count() == 2
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
