[sqlalchemy] Re: Why isn't this Json column type working?
On Sep 18, 9:08 pm, Michael Bayer mike...@zzzcomputing.com wrote: You might after that also throw a pdb into the process_bind_param() method and ensure that its being called Sorry, I should have been more clear about this. If I add a print statement in process_bind_param it doesn't get executed. and as the ultimate sanity check info default.py do_execute(). Could you clarify what you mean by this? Perhaps I'm being a bit dense. :-/ I would also ensure you're on the latest production release of MySQLdb as this error does seem faintly familiar, like there might have been issues with the consumption of bind parameters on the MySQLdb side, or test with a different DBAPI driver. Yes, I'm presently running 1.2.3 which is the latest. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: internationalization of content
Hi,
Having seen Nil's elexir approach I had another go at this problem.
I wanted a solution which is usable from non Python tools accessing the
database, so I came up with:
e.g. for countries:
- Country/countries - class and table with the default language values
and everything else for countries
- Country_L/countries_l - class and table with the localized column
information
- countries_lp - a stored procedure which does the localization/default
value stuff (uses some Firebird SQL specific code, which could probably
be changed to be more generic)
- Country_LV/countries_lv - class and database view, the select of the
view is using the stored procedure
Using it looks like this:
# set localization to DE_de
session.execute(select rdb$set_context('USER_SESSION', 'LANG_CODE',
'DE_de')from rdb$database).fetchone()
session.commit()
print ' base table '
result = session.query(db.Country)
for item in result:
print item.name
print ' localize for DE_de '
result = session.query(db.Country_LV)
for item in result:
print item.name
# set localization to FR_fr
session.execute(select rdb$set_context('USER_SESSION', 'LANG_CODE',
'FR_fr')from rdb$database).fetchone()
session.commit()
print ' localize for FR_fr - which is getting default value for
e.g. France and Germany '
result = session.query(db.Country_LV)
for item in result:
print item.name
which results in:
base table
France
Switzerland
Germany
localize for DE_de
Frankreich
Schweiz
Deutschland
localize for FR_fr - which is getting default value for e.g.
France and Germany
France
Suisse
Germany
The SA model for this looks like this:
class Country(Base, CreateUpdateMixin):
__tablename__ = u'countries'
id = sa.Column(sa.BigInteger(), sa.Sequence('countries_id'),
primary_key=True, nullable=False)
name = sa.Column(sa.String(length=30, convert_unicode=False))
iso2 = sa.Column(sa.String(length=2, convert_unicode=False))
iso3 = sa.Column(sa.String(length=3, convert_unicode=False))
telcode = sa.Column(sa.SmallInteger())
__localize_columns__ = ['name', ]
class Country_L(Base):
__table__ = sautils.make_localize_table(Country(), 'countries_l',
metadata)
class Country_LV(Base):
__table__ = sautils.make_localize_view(Country(), 'countries_lv',
metadata)
I am sure that this could be done even nicer/better and that it could be
done in a way that would be compatible with meta.drop_all(engine) and
meta.create_all(engine), currently I need to create the _LV view and
the stored procedure outside of sa.
The make_localize_* functions are inspired from code I have seen in
wiki/UsageRecipes.
If there is interest to further enhance this and get it to SQLAlchemy
standard of code I would very much like to help but I am not good enough
a coder to actually do the work or I would definitely need a lot of hand
holding and coaching.
Werner
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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: internationalization of content
Hi, I must admit that I discovered the test I wrote to assert my approach works for polymorphic content is wrongly True. I'm working at making it reallty work but it is not that easy. Still for non polymorphic content it is quite ok of course, it is completely elixir oriented, but I'm sure it could be ported to pure sqla -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: Serializable txns not useful on sqlite because do_begin() does nothing
On Sep 18, 11:59 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Sep 18, 2010, at 6:52 PM, Randall Nortman wrote:
In testing my code for concurrency, I discovered that transactions are
not properly isolated on sqlite, even with
isolation_level='SERIALIZABLE'. It turns out that on the sqlite
dialect, do_begin() does nothing. As a result, transactions are not
isolated as expected.
DBAPI works on an always-transactionalized model. There is no begin() method
with DBAPI, only commit() and rollback(). By default, a DBAPI connection is
always in a transaction, so it is not appropriate to issue any kind of
BEGIN - the DBAPI does this transparently.
Seehttp://www.python.org/dev/peps/pep-0249/.
That said, SQLite has connection isolation options which modify how it
interprets the DBAPI connection, which affect whether or not there is
actually a transaction in progress. The isolation_level pysqlite2
argument, which is not the same thing as SQLAlchemy's isolation_level
parameter to create_engine(), affects this behavior - you can set it via
create_engine using connect_args={'isolation_level':level}.
Unfortunately, the isolation_level parameter to pysqlite2 only
controls the type of BEGIN issued, not when it is issued (I just
tested to be sure). It still waits until there is an INSERT/UPDATE/
DELETE to begin the transaction. I expect the semantics of
serializable transactions to mean the transaction begins also with a
SELECT. The vast majority of database operations, I'd wager, begin
with a SELECT.
Call this a bug in pysqlite2 if you like, though the DBAPI spec seems
to be silent on when transactions are started, so I think the
developers will defend their choice. (I don't see where the spec says
that a DBAPI connection is always in a transaction.) SA can provide
the behavior I want simply by implementing do_begin in SQLiteDialect
as a one-liner: connection.execute(BEGIN). Some people may not want
that behavior, I recognize, so perhaps a check to see if serializable
transaction isolation was requested in the create_engine call, and if
so, then issue the BEGIN?
At the very least, I beg you, don't have the echo code output BEGIN if
no BEGIN is being issued. That had me barking up the wrong tree for
many hours yesterday. The echo feature is great for troubleshooting,
but only if it doesn't lie!
To answer your question in your second email, yes Process A and
Process B are two different processes in my setup, with nothing shared
other than the database itself.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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: Pulling objects out of SQLAlchemy object
On Sep 19, 12:36 pm, Michael Bayer mike...@zzzcomputing.com wrote: If I reference order_by=Block_element.sorttree, paster crashes with the same error. order_by='somestr' with declarative means the string is a Python eval. So if you wanted to put the name of the Table here, its m_block_element.c.sorttree. Would be easier to just say order_by=Block_element.c.sorttree though. Now I understand what the .c. is for. Thank you. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] polymorphic single table inheritance with __mapper_args__ automagically set
Hello everyone:
I'm having some trouble with polymorphic single table inheritance. I
want my subclasses (Ferrari is a subclass of Car) to have the
__mapper_args__ automatically set. How could I do that. I've got this:
class Car(Base):
__tablename__ = 'car'
id = Column(Integer, primary_key=True)
class_name = Column(Unicode(128), nullable=False)
__mapper_args__ = {polymorphic_on:class_name,
polymorphic_identity:Car, with_polymorphic:*}
#...@classproperty
#def __mapper_args__(cls):
#if cls.__name__ == Car:
#return {polymorphic_on:cls.class_name,
polymorphic_identity:Car, with_polymorphic:*}
#else:
#return {polymorphic_identity:cls.__name__}
parent_id = Column(Integer, ForeignKey('car.id'))
children = relation(Car, backref=backref('parent', remote_side=id))
def __init__(self):
pass
def max_speed(self):
return 1000 km/h
class Ferrari(Car):
__mapper_args__ = {polymorphic_identity:Ferrari}
def __init__(self):
Car.__init__(self)
def max_speed(self):
return 320 km/h
I know that it doesn't make much sense for a car to have parent and
children cars, but that's what I need. So once the tables are created
in the db, I do something like:
parent_car = Car()
child_car = Ferrari()
child_car.parent = parent_car
orm.add(parent_car)
orm.add(child_car)
orm.commit()
print parent_car.children[0].max_speed()
print orm.query(Car).filter(Car.id ==
parent_car.children[0].id).one().max_speed()
And it works fine (prints 320 km/h twice)... but if I comment out
the __mapper_args__ lines in Car and Ferrari and uncomment the
@classproperty, it doesn't work fine because class_type doesn't get
properly set (i.e. its value is None) and I get a class_type cannot
be NULL exception. How could I fix it?
Thanks in advance,
Eduardo.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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] sqla 0.6.4, orm, problem with self-referring joins
when i apply multiple joins to orm query, one of them self-referring,
using (target, property) form sqla generates the wrong join criteria
by selecting aliased table for the left side of the join.
tried sqla 0.5.8 and 0.6.4, same result.
e.g. for sqla 0.6.4
from sqlalchemy import create_engine, Table, MetaData, orm, Column,
Integer, ForeignKey, String
from sqlalchemy.interfaces import PoolListener
class Listener(PoolListener):
def connect(self, dbapi_con, con_record):
dbapi_con.execute(PRAGMA foreign_keys=ON)
engine = create_engine(
sqlite:///:memory:,
listeners=[Listener()],
echo=True)
Session = orm.sessionmaker(bind=engine)
metadata = MetaData()
a_table = Table(
a, metadata,
Column(id, Integer, primary_key=True),
Column(a_data, String)
)
assoc_table = Table(
assoc, metadata,
Column(left_id, ForeignKey(a_table.c.id)),
Column(right_id, ForeignKey(a_table.c.id))
)
b_table = Table(
b, metadata,
Column(id, Integer, primary_key=True),
Column(a_id, ForeignKey(a_table.c.id)),
Column(b_data, String)
)
class B(object):
pass
orm.mapper(B, b_table)
class A(object):
pass
orm.mapper(
A, a_table,
properties={
some_a: orm.relation(
A,
primaryjoin=a_table.c.id ==
assoc_table.c.left_id,
secondary=assoc_table,
secondaryjoin=assoc_table.c.right_id ==
a_table.c.id),
some_b: orm.relation(
B,
primaryjoin=b_table.c.a_id == a_table.c.id)
}
)
sess = Session()
AliasedA = orm.aliased(A)
q = sess.query(A)
q = q.join((AliasedA, A.some_a)).options(orm.contains_eager(A.some_a,
alias=AliasedA))
q = q.join((B, A.some_b)).options(orm.contains_eager(A.some_b))
print q
--
SELECT
b.id AS b_id, b.a_id AS b_a_id, b.b_data AS b_b_data,
a_1.id AS a_1_id, a_1.a_data AS a_1_a_data,
a.id AS a_id, a.a_data AS a_a_data
FROM a
JOIN assoc AS assoc_1 ON a.id = assoc_1.left_id
JOIN a AS a_1 ON assoc_1.right_id = a_1.id
JOIN b ON b.a_id = a_1.id
here i expected JOIN b ON b.a_id = a.id not JOIN b ON b.a_id =
a_1.id.
looks like the whole query is anaylzed for a_1 alias based on
join_to_left=True set in 0.6.4/orm/query.py, ln 1341.
i can change order that joins are applied to avoid this OR replace
property A.bs with explicit join condition (i.e. A.id = B.a_id) but
for generated queries this is not always easy/convenient to do.
perhaps weight relations so that those that introduce alias are
applied in right order?
is this a bug or expected? is there a way to avoid this behavior when
using multiple (target, property) joins?
thanks.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
