Re: [sqlalchemy] Re: Can't upgrade SQLAlchemy any more
Hi, On 3/29/2015 19:29, Sibylle Koczian wrote: Am 29.03.2015 um 15:16 schrieb Michael Bayer: this is a pip issue; first ensure you’re on the latest version of pip. If the error persists, please report it to https://github.com/pypa/pip/issues. Will do, thank you. My pip version came with Python 3.4.3, so that should be the latest. I'll try first to update some other packages on that machine with pip. Had similar issue with wxPython-Phoenix and pip with 3.4.0 was very far behind, don't know about 3.4.3. pip 6.0.8 and setuptools 12.2 fixed it for me Werner -- 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] Duplicate constraint name
I am getting the following exception on
dbCurrent.metadata.create_all(engCurrent) for the Currency class.
It is due to the Constraint for 'home' and 'used' columns having the
same name.
Is this due to the BOOLEAN type and what is the correct way of uniquely
naming the Constraint?
Is this a recent change as I don't seem to get this on 0.9.3, below is
with Python 3.4 and 0.9.7.
Werner
class Currency(DeclarativeBase, mix.StandardColumnMixin):
__tablename__ = u'currency'
name = sa.Column(sa.Unicode(length=50), nullable=False, index=True)
code = sa.Column(sa.Unicode(length=3))
exchangerate = sa.Column(sa.Numeric(precision=15, scale=6))
home = sa.Column(sa.BOOLEAN(), default=False)
used = sa.Column(sa.BOOLEAN(), default=False)
sortorder = sa.Column(sa.Integer())
__mapper_args__ = {
'order_by': home DESC NULLS LAST, used DESC, sortorder DESC
NULLS LAST, UPPER(name)}
File d:\devMine\twcbv5\twcbsrc\controllers\app_cb.py, line 1271, in
module
app = ab.BaseApp(redirect=True, filename=appLog)
File c:\Python34\Lib\site-packages\wx\core.py, line 1864, in __init__
self._BootstrapApp()
File d:\devMine\twcbv5\twcbsrc\app_base.py, line 169, in OnInit
self.updateDB()
File d:\devMine\twcbv5\twcbsrc\app_base.py, line 502, in updateDB
if not dbupg.dbUpgrade(prefMinimal):
File d:\devMine\twcbv5\twcbsrc\dbupdatetools\dbupgrade.py, line 59, in
dbUpgrade
if not dbupgv50.doUpgrade(progdlg):
File d:\devMine\twcbv5\twcbsrc\dbupdatetools\dbupgradev50.py, line
382, in doUpgrade
dbCurrent.metadata.create_all(engCurrent)
File c:\Python34\Lib\site-packages\sqlalchemy\sql\schema.py, line
3352, in create_all
tables=tables)
File c:\Python34\Lib\site-packages\sqlalchemy\engine\base.py, line
1617, in _run_visitor
conn._run_visitor(visitorcallable, element, **kwargs)
File c:\Python34\Lib\site-packages\sqlalchemy\engine\base.py, line
1246, in _run_visitor
**kwargs).traverse_single(element)
File c:\Python34\Lib\site-packages\sqlalchemy\sql\visitors.py, line
120, in traverse_single
return meth(obj, **kw)
File c:\Python34\Lib\site-packages\sqlalchemy\sql\ddl.py, line 713, in
visit_metadata
self.traverse_single(table, create_ok=True)
File c:\Python34\Lib\site-packages\sqlalchemy\sql\visitors.py, line
120, in traverse_single
return meth(obj, **kw)
File c:\Python34\Lib\site-packages\sqlalchemy\sql\ddl.py, line 732, in
visit_table
self.connection.execute(CreateTable(table))
File c:\Python34\Lib\site-packages\sqlalchemy\engine\base.py, line
729, in execute
return meth(self, multiparams, params)
File c:\Python34\Lib\site-packages\sqlalchemy\sql\ddl.py, line 69, in
_execute_on_connection
return connection._execute_ddl(self, multiparams, params)
File c:\Python34\Lib\site-packages\sqlalchemy\engine\base.py, line
783, in _execute_ddl
compiled
File c:\Python34\Lib\site-packages\sqlalchemy\engine\base.py, line
958, in _execute_context
context)
File c:\Python34\Lib\site-packages\sqlalchemy\engine\base.py, line
1160, in _handle_dbapi_exception
exc_info
File c:\Python34\Lib\site-packages\sqlalchemy\util\compat.py, line
188, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
File c:\Python34\Lib\site-packages\sqlalchemy\util\compat.py, line
181, in reraise
raise value.with_traceback(tb)
File c:\Python34\Lib\site-packages\sqlalchemy\engine\base.py, line
951, in _execute_context
context)
File
c:\Python34\Lib\site-packages\sqlalchemy\dialects\firebird\kinterbasdb.py,
line 110, in do_execute
cursor.execute(statement, parameters or [])
File c:\Python34\Lib\site-packages\fdb\fbcore.py, line 3353, in execute
self._ps._execute(parameters)
File c:\Python34\Lib\site-packages\fdb\fbcore.py, line 3080, in _execute
Error while executing SQL statement:)
sqlalchemy.exc.DatabaseError: (DatabaseError) ('Error while executing
SQL statement:\n- SQLCODE: -607\n- unsuccessful metadata update\n- STORE
RDB$RELATION_CONSTRAINTS failed\n- attempt to store duplicate value
(visible to active transactions) in unique index RDB$INDEX_12', -607,
335544351) '\nCREATE TABLE currency (\n\tid KEYS NOT NULL, \n\tname
VARCHAR(50) NOT NULL, \n\tcode VARCHAR(3), \n\texchangerate NUMERIC(15,
6), \n\thome BOOLEAN, \n\tused BOOLEAN, \n\tsortorder INTEGER,
\n\tcreated_at TIMESTAMP, \n\tupdated_at TIMESTAMP, \n\tcreated_by KEYS,
\n\tupdated_by KEYS, \n\tCONSTRAINT pk_currency PRIMARY KEY (id),
\n\tCONSTRAINT ck_currency__unnamed_ CHECK (home IN (0, 1)),
\n\tCONSTRAINT ck_currency__unnamed_ CHECK (used IN (0, 1))\n)\n\n' ()
--
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
Re: [sqlalchemy] Duplicate constraint name
Hi Michael,
Yes, I am using the old naming recipe, adjusted it to check for
_unnamed_ as a temp fix until I can move to the new way of doing it.
Thanks for the fast answer
Werner
On 1/30/2015 18:27, Michael Bayer wrote:
Werner werner...@gmx.ch wrote:
I am getting the following exception on
dbCurrent.metadata.create_all(engCurrent) for the Currency class.
It is due to the Constraint for 'home' and 'used' columns having the same
name.
Is this due to the BOOLEAN type and what is the correct way of uniquely naming
the Constraint?
Is this a recent change as I don't seem to get this on 0.9.3, below is with
Python 3.4 and 0.9.7.
the _unnamed_ token is part of the mechanics of the default name given to the
CheckConstraint within the boolean. It should never be rendered, as the
compiler tests for the class of this string and skips if present, and
additionally it would not be made part of a naming convention (e.g. as
documented at
http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html?highlight=naming#configuring-constraint-naming-conventions),
because the token is a special class that is meant to instruct the naming
system to skip applying a convention.
I see that you have some naming convention with “ck_currency_existing_name”
coming in, I’m not sure what system you’re using to generate that. If its the
naming_convention feature, I need to see the naming_convention setup because that
would be a bug - the BOOLEAN type with no name given directly will bypass the naming
convention feature and I can confirm this works. There seems to already be a bug
here anyway, but not quite the one you’re seeing.
If the naming convention is something custom (perhaps the old recipe we had),
that custom thing has to be adjusted to look for this condition.
The BOOLEAN type allows the constraint name to be given a name by passing
“name=‘the name’” to the BOOLEAN constructor.
Werner
class Currency(DeclarativeBase, mix.StandardColumnMixin):
__tablename__ = u'currency'
name = sa.Column(sa.Unicode(length=50), nullable=False, index=True)
code = sa.Column(sa.Unicode(length=3))
exchangerate = sa.Column(sa.Numeric(precision=15, scale=6))
home = sa.Column(sa.BOOLEAN(), default=False)
used = sa.Column(sa.BOOLEAN(), default=False)
sortorder = sa.Column(sa.Integer())
__mapper_args__ = {
'order_by': home DESC NULLS LAST, used DESC, sortorder DESC NULLS LAST,
UPPER(name)}
File d:\devMine\twcbv5\twcbsrc\controllers\app_cb.py, line 1271, in module
app = ab.BaseApp(redirect=True, filename=appLog)
File c:\Python34\Lib\site-packages\wx\core.py, line 1864, in __init__
self._BootstrapApp()
File d:\devMine\twcbv5\twcbsrc\app_base.py, line 169, in OnInit
self.updateDB()
File d:\devMine\twcbv5\twcbsrc\app_base.py, line 502, in updateDB
if not dbupg.dbUpgrade(prefMinimal):
File d:\devMine\twcbv5\twcbsrc\dbupdatetools\dbupgrade.py, line 59, in
dbUpgrade
if not dbupgv50.doUpgrade(progdlg):
File d:\devMine\twcbv5\twcbsrc\dbupdatetools\dbupgradev50.py, line 382, in
doUpgrade
dbCurrent.metadata.create_all(engCurrent)
File c:\Python34\Lib\site-packages\sqlalchemy\sql\schema.py, line 3352, in
create_all
tables=tables)
File c:\Python34\Lib\site-packages\sqlalchemy\engine\base.py, line 1617, in
_run_visitor
conn._run_visitor(visitorcallable, element, **kwargs)
File c:\Python34\Lib\site-packages\sqlalchemy\engine\base.py, line 1246, in
_run_visitor
**kwargs).traverse_single(element)
File c:\Python34\Lib\site-packages\sqlalchemy\sql\visitors.py, line 120, in
traverse_single
return meth(obj, **kw)
File c:\Python34\Lib\site-packages\sqlalchemy\sql\ddl.py, line 713, in
visit_metadata
self.traverse_single(table, create_ok=True)
File c:\Python34\Lib\site-packages\sqlalchemy\sql\visitors.py, line 120, in
traverse_single
return meth(obj, **kw)
File c:\Python34\Lib\site-packages\sqlalchemy\sql\ddl.py, line 732, in
visit_table
self.connection.execute(CreateTable(table))
File c:\Python34\Lib\site-packages\sqlalchemy\engine\base.py, line 729, in
execute
return meth(self, multiparams, params)
File c:\Python34\Lib\site-packages\sqlalchemy\sql\ddl.py, line 69, in
_execute_on_connection
return connection._execute_ddl(self, multiparams, params)
File c:\Python34\Lib\site-packages\sqlalchemy\engine\base.py, line 783, in
_execute_ddl
compiled
File c:\Python34\Lib\site-packages\sqlalchemy\engine\base.py, line 958, in
_execute_context
context)
File c:\Python34\Lib\site-packages\sqlalchemy\engine\base.py, line 1160, in
_handle_dbapi_exception
exc_info
File c:\Python34\Lib\site-packages\sqlalchemy\util\compat.py, line 188, in
raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
File c:\Python34\Lib\site-packages\sqlalchemy\util\compat.py, line 181, in
reraise
raise value.with_traceback(tb)
File c:\Python34\Lib\site-packages\sqlalchemy\engine\base.py, line 951, in
_execute_context
context)
File
c:\Python34\Lib
Re: [sqlalchemy] Firebird SQL 'rdb$get_context eq' equivalent in PostGreSQL
Hi,
Just for the archive.
Got an answer on stackoverflow:
http://stackoverflow.com/questions/28047911/how-to-set-some-context-variable-for-a-user-connection/28061244#28061244
On 1/19/2015 16:59, Werner wrote:
Hi,
I like to move to eventually support PGSQL 9.3+ as an alternative engine.
With Firebird SQL I am using the following to set/get the language
used by a connection/user.
rdb$get_context('USER_SESSION', 'LANGUAGE_ID') -
http://www.firebirdsql.org/refdocs/langrefupd20-get-context.html
googling and looking at the PG documentation I can't find something
similar, probably just using the wrong terminology:-( .
Anyone can give me a tip of where to look?
Thanks
Werner
--
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] Firebird SQL 'rdb$get_context eq' equivalent in PostGreSQL
Hi,
I like to move to eventually support PGSQL 9.3+ as an alternative engine.
With Firebird SQL I am using the following to set/get the language used
by a connection/user.
rdb$get_context('USER_SESSION', 'LANGUAGE_ID') -
http://www.firebirdsql.org/refdocs/langrefupd20-get-context.html
googling and looking at the PG documentation I can't find something
similar, probably just using the wrong terminology:-( .
Anyone can give me a tip of where to look?
Thanks
Werner
--
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.
Re: [sqlalchemy] Setting column properties
Hi, no expert but hopefully following will help. On 12/19/2014 15:01, SF Markus Elfring wrote: Hello, I created another class after reading the object relational tutorial. Now I would like to apply more fine-tuning for corresponding attributes. How can I specify a default value for a column there? sqlalchemy.readthedocs.org/en/rel_0_9/core/defaults.html Is more documentation available for the class Column? http://sqlalchemy.readthedocs.org/en/rel_0_9/core/metadata.html#sqlalchemy.schema.Column Werner -- 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.
Re: [sqlalchemy] vacation...
Hi Mike, On 7/31/2014 0:33, Michael Bayer wrote: hey folks - I'm on vacation from thursday tomorrow through next thursday, so folks please hold down the fort! I won't be off the grid but I might not be able to get to my email as regularly. Have a good vacation, I hope you forgot to pack the laptop;-) . Werner -- 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.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
Hi Rich,
I don't like using 'name' columns as primary keys I would instead use an
'id' column and would set 'index=True' on the name column.
On the primary key also define a Sequence:
Column('id', Integer, Sequence('tablename_id_seq'), primary_key=True)
http://sqlalchemy.readthedocs.org/en/rel_0_9/dialects/postgresql.html?highlight=sequence
For name columns I would use Unicode instead of string.
You might want to read the following.
http://sqlalchemy.readthedocs.org/en/rel_0_9/core/metadata.html#sqlalchemy.schema.MetaData.params.naming_convention
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/NamingConventions
Werner
--
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.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
Hi Rich, On 7/30/2014 15:04, Rich Shepard wrote: On Wed, 30 Jul 2014, Werner wrote: I don't like using 'name' columns as primary keys I would instead use an 'id' column and would set 'index=True' on the name column. Werner, The use of natural keys (such as a vehicle VIN, the US's SSN, or equipment serial number) is prefered over an artificial, meaningless, integer key to prevent duplicate data. See any of Joe Celko's SQL books. His book looks very interesting. Over the past 30 or so years I've resorted to artificial keys only when absolutely necessary. Consider a table for water chemistry constituent concentrations. There can be no more than one row for the concentration of a specified constituent from a distinct location on a given day. The only way to ensure this uniqueness is with the compound primary key of (parameter, sampdate, site). An articial 'id' column fails to prevent duplication because someone could enter the same laboratory results more than once and each row would have a unique 'id' primary key but duplicate data. I can see the advantage these things, but not sure on 'agency_contacts'. In the early 1990s I was fired from a database consulting assignment with a medical resarch unit because I changed their flat-file database structure to a relational schema and turned up duplcate data for a number of patients. When you consider the effects on published analyses of data that contained duplicate entries, they had to pick a scapegoat and I was it. :-) Seriously, read Joe Celko's SQL for Smarties (I think the 4th edition is the latest) for robust DDL practices. The unicode vs string suggestion is interesting. I'm not sure of the advantages (or disadvantages) but if the change is neutral I'll run a global search-and-replace. I find it easier to deal with things like 'éüö' etc and IIUC there is no en/decoding if you use Unicode in the DB and Python. Werner -- 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] Inspect column
Hi, For my wxPython based application I like to get at things like the column length to e.g. limit the UI entry to a certain data length. I came up with: dbProName = db.sa.inspect(db.Profilec.name) print(dbProName.property.columns[0].type.length) 1. Is there a more direct way to get at the column length 2. when would 'property.columns' contain more then one element Thanks Werner -- 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.
Re: [sqlalchemy] just a picture
On 21/11/2013 15:02, Stefane Fermigier wrote: Hi Lars, Are you working on the tool, or on the application whose schema is depicted on the tool ? Assuming it is a tool this would be very nice. Can it read an existing SA model? Werner -- 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/groups/opt_out.
Re: [sqlalchemy] A record is dropped on add
Michael,
On 03/10/2013 23:05, Michael Bayer wrote:
On Oct 3, 2013, at 5:42 AM, Werner werner.bru...@sfr.fr wrote:
...
The relation setup for the classes involved, and I suspect
Vintage.drinkinfo/Cellarbook.drinkinfo ones:
Vintage.vintadd = sao.relationship('Vintadd', uselist=False,
cascade=all, delete, delete-orphan,
single_parent=True)
Vintage.cellarbook = sao.relationship('Cellarbook', uselist=False,
cascade=all, delete, delete-orphan,
backref='vintage')
my suspicion is newCB.vintage = oldCB.vintage means Vintage.cellarbook is set
to newCB, oldCB is no longer associated with Vintage.cellarbook due to
uselist=False, then delete-orphan deletes it.
Thanks a lot, I doubt that I would have figured this out.
Werner
--
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/groups/opt_out.
[sqlalchemy] A record is dropped on add
Hi,
I have a problem that when I add a record to 'cellarbook' using the
following code the 'old' cellarbook entry is dropped from the db.
oldCB = session.query(db.Cellarbook).get(7)
newCB = db.Cellarbook()
# set some data from existing dbitem
newCB.cellar = oldCB.cellar
newCB.authuser = oldCB.authuser
newCB.supplier = oldCB.supplier
newCB.vintage = oldCB.vintage
newBot = db.Bottle()
newCB.vintage.bottle.append(newBot)
newCB.bottle = newBot
newCB.drinkinfo = oldCB.drinkinfo
session.add(newCB)
session.commit()
After this I have newCB.id=10 in the database but oldCB.id=7 has gone.
I guess/think that I have a relation setup incorrectly which creates
this effect, but I can not put my finger on it.
Appreciate any hint.
Werner
The relation setup for the classes involved, and I suspect
Vintage.drinkinfo/Cellarbook.drinkinfo ones:
Vintage.vintadd = sao.relationship('Vintadd', uselist=False,
cascade=all, delete, delete-orphan,
single_parent=True)
Vintage.cellarbook = sao.relationship('Cellarbook', uselist=False,
cascade=all, delete, delete-orphan,
backref='vintage')
Vintage.drinkinfo = sao.relationship('Drinkinfo', backref='vintage',
cascade=all, delete, delete-orphan,
single_parent=True)
Bottle.cellarbook = sao.relationship('Cellarbook', uselist=False,
#cascade=all, delete, delete-orphan,
backref='bottle')
Bottle.vintage = sao.relationship('Vintage', backref='bottle',
#cascade=all, delete, delete-orphan,
single_parent=True)
Cellarbook.supplier = sao.relationship('Profilec',
primaryjoin=('Cellarbook.fk_supplier_id==Profilec.id'),
viewonly=True)
Cellarbook.cellar = sao.relationship('Cellar', backref='cellarbook')
Cellarbook.authuser = sao.relationship('Authuser')
Cellarbook.drinkinfo = sao.relationship('Drinkinfo', backref='cellarbook',
single_parent=True)
Cellarbook.purchase = sao.relationship('Purchase', backref='cellarbook',
cascade=all, delete,
delete-orphan)
Purchase.cellar = sao.relationship('Cellar')
Purchase.currency = sao.relationship('Currency')
Purchase.supplier = sao.relationship('Profilec',
primaryjoin=('Purchase.fk_supplier_id==Profilec.id'),
viewonly=True)
--
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/groups/opt_out.
Re: [sqlalchemy] A record is dropped on add
On 03/10/2013 11:42, Werner wrote:
Hi,
I have a problem that when I add a record to 'cellarbook' using the
following code the 'old' cellarbook entry is dropped from the db.
oldCB = session.query(db.Cellarbook).get(7)
newCB = db.Cellarbook()
# set some data from existing dbitem
newCB.cellar = oldCB.cellar
newCB.authuser = oldCB.authuser
newCB.supplier = oldCB.supplier
newCB.vintage = oldCB.vintage
newBot = db.Bottle()
newCB.vintage.bottle.append(newBot)
newCB.bottle = newBot
newCB.drinkinfo = oldCB.drinkinfo
session.add(newCB)
session.commit()
After this I have newCB.id=10 in the database but oldCB.id=7 has gone.
I guess/think that I have a relation setup incorrectly which creates
this effect, but I can not put my finger on it.
Appreciate any hint.
Werner
The relation setup for the classes involved, and I suspect
Vintage.drinkinfo/Cellarbook.drinkinfo ones:
Vintage.vintadd = sao.relationship('Vintadd', uselist=False,
cascade=all, delete, delete-orphan,
single_parent=True)
Vintage.cellarbook = sao.relationship('Cellarbook', uselist=False,
cascade=all, delete,
delete-orphan,
backref='vintage')
Vintage.drinkinfo = sao.relationship('Drinkinfo', backref='vintage',
cascade=all, delete,
delete-orphan,
single_parent=True)
Bottle.cellarbook = sao.relationship('Cellarbook', uselist=False,
#cascade=all, delete,
delete-orphan,
backref='bottle')
Bottle.vintage = sao.relationship('Vintage', backref='bottle',
#cascade=all, delete, delete-orphan,
single_parent=True)
Cellarbook.supplier = sao.relationship('Profilec',
primaryjoin=('Cellarbook.fk_supplier_id==Profilec.id'),
viewonly=True)
Cellarbook.cellar = sao.relationship('Cellar', backref='cellarbook')
Cellarbook.authuser = sao.relationship('Authuser')
Cellarbook.drinkinfo = sao.relationship('Drinkinfo',
backref='cellarbook',
single_parent=True)
Cellarbook.purchase = sao.relationship('Purchase', backref='cellarbook',
cascade=all, delete,
delete-orphan)
Purchase.cellar = sao.relationship('Cellar')
Purchase.currency = sao.relationship('Currency')
Purchase.supplier = sao.relationship('Profilec',
primaryjoin=('Purchase.fk_supplier_id==Profilec.id'),
viewonly=True)
If I create a new 'vintage' record then it works (see code below), but
that is another use case and I really would like to support both of them.
Werner
newCB = db.Cellarbook()
# set some data from existing dbitem
newCB.cellar = oldCB.cellar
newCB.authuser = oldCB.authuser
newCB.supplier = oldCB.supplier
newCB.drinkinfo = oldCB.drinkinfo
## use existing vintage
#newCB.vintage = oldCB.vintage
# create a new vintage
dbVintage = db.Vintage()
dbVintage.vintage = 2025
dbVintAdd = db.Vintadd()
dbVintage.vintadd = dbVintAdd
dbVintage.drinkinfo = newCB.drinkinfo
newCB.vintage = dbVintage
# create a bottle
newBot = db.Bottle()
newCB.vintage.bottle.append(newBot)
newCB.bottle = newBot
session.add(newCB)
session.flush()
session.commit()
--
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/groups/opt_out.
Re: [sqlalchemy] Strange bug when adding
Hi Mat, On 20/09/2013 01:07, Mat Leonard wrote: I'm having a problem adding class Session(Base): __tablename__ = 'sessions' id = sql.Column(sql.Integer, primary_key=True units = sql.orm.relationship(Unit, order_by=Unit.id, backref=session, cascade=all, delete, delete-orphan) And what would the bug be? Your id line is missing a closing ) but I wouldn't consider that a bug:-) You might want to provide little bit more detail about the problem you are having. Werner -- 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/groups/opt_out.
Re: [sqlalchemy] How to use classes derived from declarative_base for default columns
Hi Luke, On 15/08/2013 15:37, Luke wrote: Hey, how may i extend the declerative base to provide default things that are always available to any class/table that is derived from it (like primary keys, create timestamp columns ect) ? You might want to look at this. http://www.sqlalchemy.org/trac/wiki/UsageRecipes/NamingConventions Werner -- 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/groups/opt_out.
Re: [sqlalchemy] Mapping views as Table/ORM
On 09/08/2013 10:55, temp4...@gmail.com wrote: It seems that SQLAlchemy has no support for creating views by a View construct or something like that but you can map them as a Table or even an ORM class when applicable, and query from them, the problem is that SQLAlchemy will than try to create them as a new table when you issue metadata.create_all(), is there a convenient way around this, without having to pass a list of tables to create_all? Maybe this recipe will help. http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views Werner -- 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/groups/opt_out.
[sqlalchemy] SchemaDisplay
Hi Michael, Instead of always coming here with a problem I would just like to say thanks for the above. Was looking around on how to do some Schema documentation and as is often the case you have already been there done that. http://www.sqlalchemy.org/trac/wiki/UsageRecipes/SchemaDisplay Thanks this is really neat. Werner -- 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/groups/opt_out.
Re: [sqlalchemy] SQLAlchemy 0.8.2 released
Michael, On 03/07/2013 22:20, Michael Bayer wrote: Hey all - SQLAlchemy release 0.8.2 is now available. 0.8.2 includes several dozen bug fixes and new features, including refinement of some of the new features introduced in 0.8. Areas of improvement include Core, ORM, as well as specific fixes for dialects such as Postgresql, MySQL, Oracle, SQL Server, Firebird and Sybase. Users should carefully review the Changelog (http://docs.sqlalchemy.org/en/latest/changelog/changelog_08.html#change-0.8.2) to note which behaviors and issues are affected. We'd like to thank the many contributors who helped with this release. SQLAlchemy 0.8.2 is available on the Download Page: http://www.sqlalchemy.org/download.html Thanks for adding the retaining flag for Firebird - you are way to fast for me:) Werner -- 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/groups/opt_out.
Re: [sqlalchemy] The problem with Oldest transaction (Firebird)
Hi Michael, On 22/06/2013 16:39, Michael Bayer wrote: ... def do_rollback(self, dbapi_connection): # Use the retaining feature, that keeps the transaction going dbapi_connection.rollback(True) def do_commit(self, dbapi_connection): # Use the retaining feature, that keeps the transaction going dbapi_connection.commit(True) other users have urged me that this boolean is very necessary. Do you recall who pushed for this - did a quick search in the archive of this list but didn't find it. http://www.firebirdsql.org/file/documentation/papers_presentations/html/paper-fbent-impacting.html Do you want me to post a question on this on the FB list and see what current thinking/know how is? Why does Firebird have to be so ridiculously weird (and why do you all use it?) I still use as I had no time to move to PostGreSQL and chose it at the time as it is good fit for small desktop application due to its embedded engine and years ago PG didn't have a Windows build. Werner -- 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/groups/opt_out.
Re: [sqlalchemy] The problem with Oldest transaction (Firebird)
On 22/06/2013 01:03, graf wrote: sweep - sort of a garbage collector which removes unused versions of transaction. There is Database header page information, where you can see the the current transaction state, there are two parameters: Oldest transaction and Next transaction. The range from Oldest to Next - is the versions of transactions. The amount of versions slows down the database speed. So SQLAlchemy is looking somewhere this Oldest transaction and after the database works slowly. I am no expert but oldest transaction in Firebird only gets locked if a transaction is never released. Are you doing commit retaining in some way - not even sure you can do this via SQLalchemy. If you haven't already seen this it might help you find the real cause. http://www.ibphoenix.com/resources/documents/general/doc_67 Werner -- 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/groups/opt_out.
Re: [sqlalchemy] Dabo ReportDesigner/Writer
Hi, On 04/06/2013 08:26, Werner wrote: Hi, I am looking at Dabo's ReportDesigner/Writer to do reporting (looked at others like PythonReports and Geraldo) but both have problems with large text blobs. It wants the data as: Dabo's reportwriter wants dataset-like structures, which is a sequence (list, tuple) of mappings (dict), where each key in the mapping is a field name, and each item in the outer sequence is a record. I managed to figure this out: result = session.query(db.Cellarbook.avgpurchaseprice, db.Drinkinfo.name) result = result.join(db.Drinkinfo) print result for row in result.all(): print row.__dict__['name'] This won't work as if there are two columns with name, then only the last one can be accessed this way. E.g.: result = session.query(db.Cellarbook.id, db.Drinkinfo.name, db.Container_LV.name) for row in result.all(): print row.__dict__['name'] Will give me the column value of Container_LV.name, I tried using with_labels but it doesn't seem to affect the __dict__ keys. When I look at: row.keys() ['id', 'name', 'name'] This is with SA 0.8.1. Anyone has some tips on how I could make Dabo ReportWriter happy. Werner -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Dabo ReportDesigner/Writer
Hi Michael, On 12/06/2013 17:40, Michael Bayer wrote: ... Will give me the column value of Container_LV.name, I tried using with_labels but it doesn't seem to affect the __dict__ keys. When I look at: row.keys() ['id', 'name', 'name'] This is with SA 0.8.1. in that situation you need individual labels: s.query(A.id, B.name, C.name.label(c_name), ...) Great, thanks for the quick reply Werner -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Dabo ReportDesigner/Writer
Hi, I am looking at Dabo's ReportDesigner/Writer to do reporting (looked at others like PythonReports and Geraldo) but both have problems with large text blobs. It wants the data as: Dabo's reportwriter wants dataset-like structures, which is a sequence (list, tuple) of mappings (dict), where each key in the mapping is a field name, and each item in the outer sequence is a record. I managed to figure this out: result = session.query(db.Cellarbook.avgpurchaseprice, db.Drinkinfo.name) result = result.join(db.Drinkinfo) print result for row in result.all(): print row.__dict__['name'] Is using row.__dict__ the correct way or are there other ways? Best regards Werner -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] SA and Firebird RDB$SET/GET_CONTEXT
Hi,
I am using the above to set the user language and I am sure this worked
for me, but it doesn't any more, in the mean time I upgraded to 0.8.1
(was on 0.7.8) and I wonder where things changed.
In a simple test like this it still works:
session.execute(select rdb$set_context('USER_SESSION', 'LANGUAGE_ID',
3)from rdb$database).fetchone()
session.commit()
result = session.query(db.Drinktype_LV)
for item in result:
print item.name, item.language.name
I get the correct item and language name values back.
But when I try this in my application the context goes away and I am not
sure if this is SA doing this or Firebird.
During the app init (wxPython) I do this:
setcont = select rdb$set_context('USER_SESSION',
'LANGUAGE_ID', %s) \
from rdb$database % self.loggedInUser.language.id
print wx.GetApp().ds
x = wx.GetApp().ds.execute(setcont).fetchone()
y = wx.GetApp().ds.execute(getcont).fetchone()
assert y[0] == str(self.loggedInUser.language.id)
wx.GetApp().ds.commit()
Fine so far, now I try to use it e.g. like this:
app = app_base.BaseApp(redirect=False)
print app.ds
getcont = select rdb$get_context('USER_SESSION', 'LANGUAGE_ID') \
from rdb$database
y = app.ds.execute(getcont).fetchone()
assert y[0] == str(app.loggedInUser.language.id)
Here the assert fails as the fetch returns None as if the context was
not set.
the print app.ds both show:
sqlalchemy.orm.session.SessionMaker object at 0x06D41C90
app.ds is created with:
maker = sao.sessionmaker(autoflush=True, autocommit=False,
expire_on_commit=True)
DBSession = sao.scoped_session(maker)
app.ds = DBSession() # just checked again that I do this only in one
place of my code
Anyone has a hint on where I am going wrong with all this?
Werner
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] SA and Firebird RDB$SET/GET_CONTEXT
On 29/04/2013 13:29, Werner wrote:
Hi,
I am using the above to set the user language and I am sure this
worked for me, but it doesn't any more, in the mean time I upgraded to
0.8.1 (was on 0.7.8) and I wonder where things changed.
In a simple test like this it still works:
session.execute(select rdb$set_context('USER_SESSION', 'LANGUAGE_ID',
3)from rdb$database).fetchone()
session.commit()
result = session.query(db.Drinktype_LV)
for item in result:
print item.name, item.language.name
I get the correct item and language name values back.
But when I try this in my application the context goes away and I am
not sure if this is SA doing this or Firebird.
During the app init (wxPython) I do this:
setcont = select rdb$set_context('USER_SESSION',
'LANGUAGE_ID', %s) \
from rdb$database % self.loggedInUser.language.id
print wx.GetApp().ds
x = wx.GetApp().ds.execute(setcont).fetchone()
y = wx.GetApp().ds.execute(getcont).fetchone()
assert y[0] == str(self.loggedInUser.language.id)
wx.GetApp().ds.commit()
Fine so far, now I try to use it e.g. like this:
app = app_base.BaseApp(redirect=False)
print app.ds
getcont = select rdb$get_context('USER_SESSION', 'LANGUAGE_ID') \
from rdb$database
y = app.ds.execute(getcont).fetchone()
assert y[0] == str(app.loggedInUser.language.id)
Here the assert fails as the fetch returns None as if the context was
not set.
the print app.ds both show:
sqlalchemy.orm.session.SessionMaker object at 0x06D41C90
app.ds is created with:
maker = sao.sessionmaker(autoflush=True, autocommit=False,
expire_on_commit=True)
DBSession = sao.scoped_session(maker)
app.ds = DBSession() # just checked again that I do this only in one
place of my code
Anyone has a hint on where I am going wrong with all this?
Werner
I added:
print wx.GetApp().ds.execute(select current_connection from
rdb$database).fetchone()
just after the two other print statements and they both show the same
connection number on each run.
I guess this points the finger at Firebird SQL, no?
Werner
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] SA and Firebird RDB$SET/GET_CONTEXT
On 29/04/2013 15:05, Mauricio de Abreu Antunes wrote:
I know my question is very weird, but Have you ever tried the same SQL
string in the firebird console?
Not weird at all.
I use IBExpert for this and when I do it in a session all works fine.
Just to expand, all this is used by views for I18N enabled tables which
look like this:
CREATE OR ALTER VIEW WINESTYLE_LV(
ID,
CENTRALKEY,
NAME,
CODE,
FK_WINEFAMX_ID,
FK_WINESTYLET_ID,
FK_DRINKTYPE_ID,
FK_WINEGLASS_ID,
FK_LANGUAGE_ID,
FK_WINESTYLE_L_ID)
AS
SELECT
o.id,
o.centralkey,
COALESCE(t.name, o.name) COLLATE UNICODE_CI_AI,
o.code,
o.fk_winefamx_id,
o.fk_winestylet_id,
o.fk_drinktype_id,
o.fk_wineglass_id,
COALESCE(t.FK_LANGUAGE_ID, 1),
t.ID
from winestyle o
LEFT OUTER JOIN winestyle_l t on t.fk_winestyle_id=o.id
AND t.fk_language_id=rdb$get_context('USER_SESSION',
'LANGUAGE_ID')
;
If I then do in the console this:
select rdb$set_context('USER_SESSION', 'LANGUAGE_ID', 3) from rdb$database;
3 stands for French and then I look at the above view the name column
contains all the French texts unless it is not translated then it shows
the default, which is English.
Werner
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] SA and Firebird RDB$SET/GET_CONTEXT
Hi,
I think I fixed it but don't understand at all why my change would fix it.
getcont = select rdb$get_context('USER_SESSION',
'LANGUAGE_ID') \
from rdb$database
setcont = select rdb$set_context('USER_SESSION',
'LANGUAGE_ID', %s) \
from rdb$database % self.loggedInUser.language.id
# set language for this connection for localization stuff
x = wx.GetApp().ds.execute(setcont).fetchone()
y = wx.GetApp().ds.execute(getcont).fetchone()
assert y[0] == str(self.loggedInUser.language.id), \
User session lang problem y = %s, id = %s % (y,
self.loggedInUser.language.id)
wx.GetApp().ds.commit()
If I change the application init code above to NOT do the commit at the
end it works, even if later on in the application commits are issued it
still keeps working.
Can someone please explain why the above commit would someone cause the
context not to stick.
Werner
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] SA and Firebird RDB$SET/GET_CONTEXT
Hi Michael, On 29/04/2013 19:58, Michael Bayer wrote: I don't know what wx.GetApp().ds is, but a commit, assuming this is a Session, will return the current connection back to the connection pool, and any session changes stay with that connection. Using the Session again will pull up a potentially different connection. wx.GetApp().ds was created by: engine = db.sa.create_engine(dburl, encoding='utf8', echo=self.salog) DBSession.configure(bind=engine) maker = sao.sessionmaker(autoflush=True, autocommit=False, expire_on_commit=True) DBSession = sao.scoped_session(maker) app.ds = DBSession() # just checked again that I do this only in one place of my code app above == wx.GetApp().ds and this is what I use in the wxPython app to get always the same session and I think I also always get the same connection, but will check this again tomorrow. As always thanks for your time and explanation. Werner -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Are sqlalchemy queries a generator?
Hi, On 26/04/2013 16:41, alonn wrote: so not to load too much into memory I should do something like: for i in session.query(someobject).filter(idsomething) print i I'm guessing the answer is no, because of the nature of sql, but I'm not an expert so I'm asking. yes you can, check out the doc for querying, e.g. the following if you use the ORM. http://sqlalchemy.readthedocs.org/en/rel_0_8/orm/tutorial.html#querying Werner -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Are sqlalchemy queries a generator?
On 26/04/2013 17:07, Claudio Freire wrote: On Fri, Apr 26, 2013 at 12:06 PM, Werner werner.bru...@sfr.fr wrote: On 26/04/2013 16:41, alonn wrote: so not to load too much into memory I should do something like: for i in session.query(someobject).filter(idsomething) print i I'm guessing the answer is no, because of the nature of sql, but I'm not an expert so I'm asking. yes you can, check out the doc for querying, e.g. the following if you use the ORM. http://sqlalchemy.readthedocs.org/en/rel_0_8/orm/tutorial.html#querying Not entirely, if you don't use yield_per (as shown in the docs in fact, but worth mentioning). Seeing query: if self._yield_per: fetch = cursor.fetchmany(self._yield_per) if not fetch: break else: fetch = cursor.fetchall() Not only that, but also all rows are processed and saved to a local list, so all instances are built and populated way before you get the first row. That is, unless you specify yield_per. Oops, thanks for correcting me. Werner -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] ColanderAlchemy 0.2a1 released!
On 12/04/2013 00:01, Stefano Fontanelli wrote: Hi all, ColanderAlchemy 0.2a1 has been released on PyPi. We are waiting feedbacks to add/remove/change features before reach beta version. You can find changelog here: https://github.com/stefanofontanelli/ColanderAlchemy/blob/master/CHANGELOG.txt You can find new doc here: https://colanderalchemy.readthedocs.org/en/latest/ BTW, the colander link on the following page does not work. Werner -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Relationship setup problem
On 09/04/2013 14:29, Werner wrote:
...
Wineracku.combrack = sao.relationship('Wineracku',
remote_side=[Wineracku.id],
cascade=all, delete,
delete-orphan,
single_parent=True)
Wineracku.combrack = sao.relationship('Wineracku',
backref=sao.backref('winerackucomb',
cascade=all, delete, delete-orphan),
remote_side=[Wineracku.id])
I think this is it now, no more errors the Wineracku entries get
deleted when I delete a unit if they use combrack relationship or not.
Wouldn't mind a confirmation if I do things correctly now or if there is
a nicer, maybe even out of the box, solution to this.
Werner
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Relationship setup problem
Hi Michael, Didn't see this one before my last post. On 09/04/2013 16:19, Michael Bayer wrote: 1. is Winracku.combrack intended to be many-to-one or one-to-many ? One to many, in other words just to make sure that I don't mess up terminology, w1 can have many children but the children only have one parent. 2. Given w1, w2: w1 = Winracku() w2 = Winracku() w1.combrack = w2 which one are you deleting first, and what is the desired behavior as a result? w1 = Winracku() w2 = Winracku() w3 = Winracku() w1.combrack.append(w2) w1.combrack.append(w2) delete w1, currently I don't allow deletion of w2 or w3 Werner -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Relationship setup problem
Hi,
I have an ORM class:
class Wineracku(DeclarativeBase, mix.StandardColumnMixin):
__tablename__ = u'wineracku'
description = sa.Column(sa.Unicode(length=30))
shortdesc = sa.Column(sa.Unicode(length=10))
# only used with single bottle type units
maxcol = sa.Column(sa.Integer(), default=0)
maxrow = sa.Column(sa.Integer(), default=0)
fk_winerack_id = sautils.reference_col('winerack')
fk_combrack_id = sautils.reference_col('wineracku')
And I would like a relationship which relates to same table based on
fk_combrack_id.
I tried this but combrack is always an empty list:
Wineracku.combrack = sao.relationship('Wineracku')
I tried this but combrack is always an empty list:
Wineracku.combrack = sao.relationship('Wineracku')
and this with the same result:
Wineracku.combrack = sao.relationship('Wineracku',
primaryjoin=('Wineracku.fk_combrack_id==Wineracku.id'))
What am I doing wrong?
Werner
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Relationship setup problem
Hi, Found it in the doc, the Adjacency List Relationship is what I wanted. http://docs.sqlalchemy.org/en/latest/orm/relationships.html#adjacency-list-relationships Werner -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Storing and Retrieving BLOB in SqlAlchemy
Hi Dalia, On 26/02/2013 17:15, dalia wrote: self._get_file(content) ### This is a function which generates the file (.xls / .doc / .pdf) in the content What are you getting back from _get_file? Is it in a format you can store in the db column and then restore it to a file? Doing a bit of googling I found this on stackoverflow which looks relatively simple, i.e. he suggest to use open with the 'rb' and 'wb' flag for reading and writing. http://stackoverflow.com/questions/3379166/writing-blob-from-sqlite-to-file-using-python You should be able to just use the non sqlite3 part of this, i.e. something like: |with open(yourfilename, rb) as input_file: tbl.report_file= input_file.read() with open(Output.bin, wb) as output_file: output_file.write(|||tbl.report_file|)| I personally just store the file name/path and keep the data on the disk, but there are obviously reasons to put it in a db. You might want to read some of the other posts on stackoverflow, e.g.: http://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay Werner -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Docs in PDF are not being generated
On 30/01/2013 17:53, Michael Bayer wrote: On Jan 30, 2013, at 10:10 AM, Vraj Mohan wrote: Is there any interest in having this fixed? I'd love someone to take it on and fix it, sure.I've been asking around for help for years, as I still see people publishing entire books with LaTeX. What about rst2pdf instead of going via LaTex? http://techtonik.rainforce.org/2010/05/sphinx-pdf-with-rst2pdf.html I used on smallish docs, so don't know how it will do with SQLAlchemy. Werner -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Couple of questions about filtering...
On 23/01/2013 07:47, Alexey Vihorev wrote: Ok, I've set up a complete test case: http://pastebin.com/W08w6Hg6 That throws an exception: TypeError: getattr(): attribute name must be string File h:\devProjectsT\aaTests\aaMisc\AlexeyVihorev\sahybrid-org.py, line 67, in module res = s.query(Payable).filter(Payable.value==order).all() #this does not File c:\Python27\Lib\site-packages\sqlalchemy-0.7.9-py2.7.egg\sqlalchemy\ext\hybrid.py, line 681, in __get__ return self.expr(owner) File h:\devProjectsT\aaTests\aaMisc\AlexeyVihorev\sahybrid-org.py, line 46, in value return getattr(self, self.type) as you can see I am on 0.7.9. I am not an expert on these things but shouldn't you maybe use the following instead of hybrid_property: http://docs.sqlalchemy.org/en/latest/orm/inheritance.html Werner -- 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] sanitizing sql with sqlalchemy
On 31/12/2012 23:24, alonn wrote: I'm using sqlalchemy orm (with turbogears) to write data from a web application to an mssql 2005 Db (used by another application, not maintained by me). after dealing with a serious case of data corruption (basically because of user data including the . sign). Can you give more detail on how a . (point/full stop) in user data corrupted your database. A point is valid data in lots of situations, so should not cause you problems. Werner -- 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] sanitizing sql with sqlalchemy
On 01/01/2013 19:34, alonn wrote: Actually I don't know what's causing the corruption but the . looks like the only unvalid one in a varchar field. Why would a . in a varchar field not be valid? Just consider something like Firstname MidInitial. LastName, why would that not be valid in a varchar column? I am pretty sure that the . is not your problem. Provide more details and hopefully someone can help you identify your real problem. Werner -- 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] alembic handles no primary key table
On 01/12/2012 17:49, junepeach wrote:
I have 2 simple tables defined as below:
class Test1(Base):
__tablename__ = 'test1'
id = Column(Integer, primary_key = True)
name = Column(String(15))
class Test2(Base):
__tablename__ = 'test2'
fid = Column(Integer, ForeignKey('test1.id'))
tname = Column(String(15))
You can see that Test2 is a table without a primary key. When I run 'alembic
revision --autogenerate' to create migration script, and got below error:
File /usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/__init__.py,
line 1129, in mapper
return Mapper(class_, local_table, *args, **params)
File /usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/mapper.py, line
203, in __init__
self._configure_pks()
File /usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/mapper.py, line
767, in _configure_pks
(self, self.mapped_table.description))
sqlalchemy.exc.ArgumentError: Mapper Mapper|Test2|test2 could not assemble any
primary key columns for mapped table 'test2'
So I wonder how to create a migration script for a table without primary key.
Non-primary key is not good design, but acceptable in mysql, sqlite, postgresql.
Not an expert, but above is using the SA orm which requires a primary
key, you need to define the table using:
http://sqlalchemy.readthedocs.org/en/rel_0_8/core/schema.html#metadata-describing
Werner
--
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] relationship problem
Hi,
I don't understand why on one record I don't get the authuser relation.
My definition is:
class Cellar(DeclarativeBase, mix.StandardColumnMixin):
__tablename__ = u'cellar'
name = sa.Column(sa.Unicode(length=50), nullable=False)
fk_authuser_id = sautils.reference_col('authuser')
Cellar.authuser = sao.relationship('Authuser', backref='cellars',
primaryjoin=
('Cellar.id==Authuser.fk_cellar_id'), uselist=False)
In my authuser class I have:
class Authuser(DeclarativeBase, mix.StandardColumnMixin):
__tablename__ = u'authuser'
name = sa.Column(sa.Unicode(30), nullable=False, index=True)
# not using reference_col due to the use of Authuser in cellar
fk_cellar_id = sa.Column(sa.BigInteger(), sa.ForeignKey(u'cellar.id',
name='fk_cellar_id',
use_alter=True))
Authuser.cellar = sao.relationship('Cellar', primaryjoin=
('Authuser.fk_cellar_id==Cellar.id'))
With this query I don't get the authuser relation on the second record
even so the fk_authuser_id is set to 1, which is the same as on the
first record.
q = session.query(db.Cellar)
for i in q:
print i.name
print i.fk_authuser_id
print i.authuser.name
The output is:
Main Cellar
1
default
Special Reserve
1
--- attribute error NoneType object has no attribute 'name'
I am still on 0.7.9.
What am I doing wrong?
Werner
--
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] C Extension on Windows 7
Hi, I just upgraded to 0.7.9 and the message the build with C Extension failed motivated me to search a bit on what would be needed so it could succeed. I must be blind as I can't find it in the 0.7.9 docs, nor on the wiki, so a little googling helped me find these: http://docs.sqlalchemy.org/en/rel_0_7/intro.html http://www.linkapps.com/programming/19-python/42-sqlalchemy-with-c-extensions.html but not much help for a Windows user not working with C. Would appreciate pointers to link on what the C Extensions optimize and install instructions for Windows, for a C dummy please:-) . Werner -- 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] C Extension on Windows 7
Michael, Thanks for the quick reply. I am just about only using the ORM (and I like it a lot), so I won't bother with all this. Sorry for having wasted your time Werner On 20/10/2012 22:26, Michael Bayer wrote: You can google around for better info on this, as I've not done Windows C programming since the 90's, but the theme seems to be that you at least need to have MS Visual C++ installed (or maybe not, found some other links). this seems to be the definitive doc: http://docs.python.org/extending/windows.html then theres some here, including recipes using all free tools: http://stackoverflow.com/questions/101061/building-python-c-extension-modules-for-windows The C extensions with SQLAlchemy aren't really very dramatic IMHO, while it's not a bad thing for them to be running, and certain operations become much faster, you're probably not going to see any miraculous speedups with them enabled - especially if you're on the ORM there's a lot of other places that time is taken up. On Oct 20, 2012, at 7:30 AM, Werner wrote: Hi, I just upgraded to 0.7.9 and the message the build with C Extension failed motivated me to search a bit on what would be needed so it could succeed. I must be blind as I can't find it in the 0.7.9 docs, nor on the wiki, so a little googling helped me find these: http://docs.sqlalchemy.org/en/rel_0_7/intro.html http://www.linkapps.com/programming/19-python/42-sqlalchemy-with-c-extensions.html but not much help for a Windows user not working with C. Would appreciate pointers to link on what the C Extensions optimize and install instructions for Windows, for a C dummy please:-) . Werner -- 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] startswith column operator - db specific
I use Firebird SQL, and like has a pretty bad performance compared to
starting with, as the later uses an index if one is available.
When using someklass.somecolumn.startswith('value') SA uses like,
what would have to be done to use starting with instead for the
Firebird dialect?
Werner
--
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] startswith column operator - db specific
On 20/04/2012 10:36, Werner wrote:
I use Firebird SQL, and like has a pretty bad performance compared
to starting with, as the later uses an index if one is available.
When using someklass.somecolumn.startswith('value') SA uses like,
what would have to be done to use starting with instead for the
Firebird dialect?
Hhm,
The performance issue should be solved with 2.1+, so did some more
search and testing.
SA generates something like this when using startswith - correct?
select name from cepagesyn where NAME like 'merlot' || '%%';
If I run the above in IBExpert (the db tool I use to admin) it is very
slow as the plan is CEPAGESYN NATURAL
if I use:
select name from cepagesyn where NAME like 'merlot%';
PLAN (CEPAGESYN INDEX (IX_CEPAGESYN_NAME))
So, could the FB dialect be tweaked to do the concatenation in Python
instead of how it is done now?
Werner
--
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] startswith column operator - db specific
Michael,
On 20/04/2012 12:21, Michael Bayer wrote:
On Apr 20, 2012, at 4:51 AM, Werner wrote:
On 20/04/2012 10:36, Werner wrote:
I use Firebird SQL, and like has a pretty bad performance compared to starting
with, as the later uses an index if one is available.
When using someklass.somecolumn.startswith('value') SA uses like, what would have to
be done to use starting with instead for the Firebird dialect?
Hhm,
The performance issue should be solved with 2.1+, so did some more search and
testing.
SA generates something like this when using startswith - correct?
select name from cepagesyn where NAME like 'merlot' || '%%';
If I run the above in IBExpert (the db tool I use to admin) it is very slow as the plan
is CEPAGESYN NATURAL
if I use:
select name from cepagesyn where NAME like 'merlot%';
PLAN (CEPAGESYN INDEX (IX_CEPAGESYN_NAME))
So, could the FB dialect be tweaked to do the concatenation in Python instead
of how it is done now?
Werner
As always thanks for your quick feed back.
the concatenation in SQL is there to handle the case of the argument not being
a literal string, i.e. another SQL expression like a column. There also might
be some edges to it related to escape characters, not sure.
To really handle this we'd have to adjust the mechanism of startswith, endswith, contains
to move their evaluation out to the compiler, instead of hardwiring them to the
concatenation. This is probably a good idea in any case though might be a bit
destabilizing, might be better for 0.8.In the FB case it would render STARTING
WITH.
Would be great if that makes it into 0.8.
for the moment as a workaround I'd employ your own startswith() function, or
use mycol.op('starting with')(value).
That works great for the case I had a performance issue with.
Thanks a lot
Werner
--
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] OT - SQL report design
A little off topic, but who knows. Looking for a reporting tool for SQL db's, would be even nicer if it supports SQLAlchemy. Did quit a bit of googling but haven't really come across anything in the Python world. What am I looking for: - reports to be run from within a Python application (wxPython based and/or TurboGears) - some form of GUI to design report layouts (mostly listings, but also some labels) - needs to support images in the listings - support for barcode printing Something along the lines of JasperReport, CrystalReport or ReportManager but with nice support from within Python. reportlab/platypus looks promising, except that I can't find any GUI with which to generate the layout of a listing. Ideally it should be something a 'semi' technical end user could use too. Is there such a thing? Werner -- 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] Computed by columns
Is there a way in SA 0.7.6 to define computed columns as part of other DeclarativeBase columns? Currently I do manual DDL, but would like to get things setup to be able to easier move to another db backend. Most of my computed columns are calculations like the one on AVAILCAPACITY below, but also have a few simple selects like in the USEDCAPACITY column. CREATE TABLE WINERACKIT ( WINERACKITIDPKEYS NOT NULL, SPLIT INTEGER NOT NULL, CAPACITYINTEGER DEFAULT 0, FK_WINERACKBID PKEYS NOT NULL /* PKEYS = BIGINT */, USEDCAPACITYCOMPUTED BY ((SELECT COUNT(BOTTAG.TAGNO) FROM BOTTAG WHERE BOTTAG.FK_WINERACKIT_ID = WINERACKIT.ID)), AVAILCAPACITY COMPUTED BY (CAPACITY-USEDCAPACITY) Thanks in advance for some pointers on how to do this. Werner -- 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] Computed by columns
Good evening Michael, On 09/04/2012 20:05, Michael Bayer wrote: even google is not telling me which vendor has produced this expression. Because computed by is the most generic phrase. I see a brief mention in firebird's docs ? no clue. Sorry, should have mentioned that currently I use Firebird SQL 2.5. BTW, it is the first FB release which supports an alter column to change the computed by clause. I'd probably use @compiles on top of CreateTable for this, and use a regular expression to tokenize out the columns that have computed_by in their info field. To really support extensions like this fully we'd have to break out compiler to make usage of a CreateColumn directive, then you'd be able to put @compiles on top of CreateColumn and generate the columns directly. wouldn't be too terrible but 0.8 is quite backlogged (see http://www.sqlalchemy.org/trac/ticket/2463). Or why not do it at the type level ? Can you just make a UserDefinedType here ? Will look into these. 2463 might be nicer. and will keep watching this one in case it does make it into 0.8. Thanks Werner On Apr 9, 2012, at 1:36 PM, Werner wrote: Is there a way in SA 0.7.6 to define computed columns as part of other DeclarativeBase columns? Currently I do manual DDL, but would like to get things setup to be able to easier move to another db backend. Most of my computed columns are calculations like the one on AVAILCAPACITY below, but also have a few simple selects like in the USEDCAPACITY column. CREATE TABLE WINERACKIT ( WINERACKITIDPKEYS NOT NULL, SPLIT INTEGER NOT NULL, CAPACITYINTEGER DEFAULT 0, FK_WINERACKBID PKEYS NOT NULL /* PKEYS = BIGINT */, USEDCAPACITYCOMPUTED BY ((SELECT COUNT(BOTTAG.TAGNO) FROM BOTTAG WHERE BOTTAG.FK_WINERACKIT_ID = WINERACKIT.ID)), AVAILCAPACITY COMPUTED BY (CAPACITY-USEDCAPACITY) Thanks in advance for some pointers on how to do this. Werner -- 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] expire_on_commit and rollback
Hi, I sent this yesterday from my email client, but as it hasn't shown up I post it again directly. In my application whenever I issue a rollback on 'sub-transaction' I see (using logging.debug) that my main list is being reloaded from the database. In other words I do things like this: - get application wide db session - load the list of all the drinks - begin_nested - create/edit e.g. purchase item of one drink - commit - begin_nested - create/edit another purchase item of the same drink as above - rollback - commit (to finally commit first purchase) At this point all the drinks with all the relations etc are reloaded from the database. I tried with expire_on_commit but it doesn't seem to affect rollback. Is there a way to suppress the reloading of all the items and only load the one affected by the changes by using expire(instance) or refresh(instance)? Werner P.S. Python 2.7.2 and SA 0.7.2 on Win 7 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/WZ0HHOqitBQJ. 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] expire_on_commit and rollback
Hi, In my application whenever I issue a rollback on 'sub-transaction' I see (using logging.debug) that my main list is being reloaded from the database. In other words I do things like this: - get application wide db session - load the list of all the drinks - begin_nested - create/edit e.g. purchase item of one drink - commit - begin_nested - create/edit another purchase item of the same drink as above - rollback - commit (to finally commit first purchase) At this point all the drinks with all the relations etc are reloaded from the database. I tried with expire_on_commit but it doesn't seem to affect rollback. Is there way to suppress the reloading of all the items and only load the one affected by the changes by using expire(instance) or refresh(instance)? Werner -- 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] Google Summer of Code 2012
On 23/03/2012 05:53, Philip Jenvey wrote: Hey all, SQLAlchemy is looking to get involved in this year's Google Summer of Code[1]. We'd like to encourage students to visit our GSoC page on the wiki[2] and review some of the project ideas we've posted. We're also looking for more backup mentors, if anyone is interested, please contact me (we really could use at least one more)! Please feel free to suggest other GSoC project ideas if you happen to think of any. What about a dialect to support the new Firebird SQL driver which I believe is close to work for both Py 2.6+ and 3.x? https://github.com/pmakowski/fdb Copied Philip and Pavel as I am not sure that they read this list and they are would be much better qualified and responding to any technical questions. Werner -- 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] Google Summer of Code 2012
On 23/03/2012 18:59, Philip Jenvey wrote: On Mar 23, 2012, at 1:13 AM, werner wrote: On 23/03/2012 05:53, Philip Jenvey wrote: Hey all, SQLAlchemy is looking to get involved in this year's Google Summer of Code[1]. We'd like to encourage students to visit our GSoC page on the wiki[2] and review some of the project ideas we've posted. We're also looking for more backup mentors, if anyone is interested, please contact me (we really could use at least one more)! Please feel free to suggest other GSoC project ideas if you happen to think of any. What about a dialect to support the new Firebird SQL driver which I believe is close to work for both Py 2.6+ and 3.x? https://github.com/pmakowski/fdb Copied Philip and Pavel as I am not sure that they read this list and they are would be much better qualified and responding to any technical questions. I went ahead and added this idea to the wiki Thanks, you beat me to it! Werner -- 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] user defined type
Just for the archive, On 08/02/2012 16:47, Michael Bayer wrote: @hybrid_property def namesandvar(self): nVar = self.name if self.name2: nVar += ', ' + self.name2 if self.variety: nVar += ', ' + self.variety return nVar I must be doing something wrong when defining my NAMES40 custom type as when I change it to use Unicode the query works fine. those + signs should probably be concatenation operators.You'll get concatenation as long as the type of column includes types.Concatenable in its inheritance hierarchy. Went back to using straight sa.Unicode instead of my customtype and changed the hybrid_property to the following: @hybrid_property def namesandvar(self): if not self.variety in [None, u'']: return self.drinknames + u, + self.variety else: return self.drinknames @namesandvar.expression def namesandvar(cls): return case([ (cls.variety != None, cls.drinknames + u, + cls.variety), ], else_ = cls.drinknames) Which is based on what I found in the doc here: http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html#mapper-sql-expressions Werner -- 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] user defined type
Hi,
I am using some custom types which just about work, i.e. I just have
an issue when I do this type of query.
result = result.filter(db.Drinkinfo.namesandvar.startswith('cloudy'))
It throws the following exception, which is due to my custom type not
being correctly setup.
sqlalchemy.exc.ProgrammingError: (ProgrammingError) (-902,
'isc_dsql_prepare: \n Dynamic SQL Error\n expression evaluation not
supported\n Strings cannot be added or subtracted in dialect 3') SELECT
...
cellarbook.updated_by AS cellarbook_updated_by \nFROM cellarbook JOIN
vintage ON vintage.id = cellarbook.fk_vintage_id JOIN drinkinfo ON
drinkinfo.id = vintage.fk_drinkinfo_id \nWHERE vintage.avgscore BETWEEN
? AND ? AND drinkinfo.name + ? + drinkinfo.name2 + (? ||
drinkinfo.variety) LIKE ? || '%%' (80, 95, ', ', ', ', 'cloudy')
File c:\dev\twcbv4\twcbsrc\test3to4\saTest.py, line 57, in module
for item in result:
File
c:\Python27\Lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\orm\query.py,
line 1839, in __iter__
return self._execute_and_instances(context)
File
c:\Python27\Lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\orm\query.py,
line 1854, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File
c:\Python27\Lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engine\base.py,
line 1399, in execute
params)
File
c:\Python27\Lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engine\base.py,
line 1532, in _execute_clauseelement
compiled_sql, distilled_params
File
c:\Python27\Lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engine\base.py,
line 1640, in _execute_context
context)
File
c:\Python27\Lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engine\base.py,
line 1633, in _execute_context
context)
File
c:\Python27\Lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\dialects\firebird\base.py,
line 692, in do_execute
cursor.execute(statement, parameters or [])
The columns are defined like:
name = sa.Column(cts.NAMES40(), default=u'', index=True)
name2 = sa.Column(cts.NAMES40(), default=u'', index=True)
variety = sa.Column(sa.Unicode(length=40), default=u'')
@hybrid_property
def namesandvar(self):
nVar = self.name
if self.name2:
nVar += ', ' + self.name2
if self.variety:
nVar += ', ' + self.variety
return nVar
I must be doing something wrong when defining my NAMES40 custom type
as when I change it to use Unicode the query works fine.
I used custom types but maybe there are even easier/better ways to do
what I like to do (standard length and collation, would be nice to also
have index=True), anyhow this is how I define it.
In Firebird SQL I define a domain:
CREATE DOMAIN NAMES40 AS
VARCHAR(40) CHARACTER SET UTF8
COLLATE UNICODE_CI_AI;
and then have this as the custom type:
class NAMES40(sa.types.UserDefinedType):
impl = sa.Unicode
def get_col_spec(self):
return NAMES40
def bind_processor(self, dialect):
def process(value):
return value
return process
def result_processor(self, dialect, coltype):
def process(value):
return value
return process
Can anyone point me to more samples using UserDefinedType and
TypeDecorator, looked at the UsageRecipes and the documentation but
obviously can't quit put it together for my use.
Werner
--
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] column_property and Firebird
I would like to do this type of thing: namesandvar = column_property(names + + variety) and use it e.g. like: query(db.Cellarbook).filter(db.Drinkinfo.namesandvar.contains(u'menu')) But Firebird SQL doesn't like the + operator, it throws an error Strings cannot be added or subtracted in dialect 3. Is there an alternative which would work for both FB and Postgresql? Werner P.S. The above query generates the following: 2012-01-16 17:41:56,696 INFO sqlalchemy.engine.base.Engine SELECT cellarbook.id AS cellarbook_id, cellarbook.maturityfirst AS cellarbook_maturityfirst, cellarbook.maturitybest AS cellarbook_maturitybest, cellarbook.maturitypast AS cellarbook_maturitypast, cellarbook.storagelocation AS cellarbook_storagelocation, cellarbook.quantitypurchased AS cellarbook_quantitypurchased, cellarbook.quantityconsumed AS cellarbook_quantityconsumed, cellarbook.quantityonhand AS cellarbook_quantityonhand, cellarbook.lastpurchaseprice AS cellarbook_lastpurchaseprice, cellarbook.avgpurchaseprice AS cellarbook_avgpurchaseprice, cellarbook.currentvalue AS cellarbook_currentvalue, cellarbook.isactive AS cellarbook_isactive, cellarbook.printlabel AS cellarbook_printlabel, cellarbook.minqoh AS cellarbook_minqoh, cellarbook.avgscore AS cellarbook_avgscore, cellarbook.avgscore2 AS cellarbook_avgscore2, cellarbook.fk_cellar_id AS cellarbook_fk_cellar_id, cellarbook.fk_authuser_id AS cellarbook_fk_authuser_id, cell arbook.fk_bottle_id AS cellarbook_fk_bottle_id, cellarbook.fk_vintage_id AS cellarbook_fk_vintage_id, cellarbook.fk_drinkinfo_id AS cellarbook_fk_drinkinfo_id, cellarbook.fk_cbbottleid AS cellarbook_fk_cbbottleid, cellarbook.created_at AS cellarbook_created_at, cellarbook.updated_at AS cellarbook_updated_at, cellarbook.created_by AS cellarbook_created_by, cellarbook.updated_by AS cellarbook_updated_by, cellar_1.id AS cellar_1_id, cellar_1.name AS cellar_1_name, cellar_1.fk_authuser_id AS cellar_1_fk_authuser_id, cellar_1.created_at AS cellar_1_created_at, cellar_1.updated_at AS cellar_1_updated_at, cellar_1.created_by AS cellar_1_created_by, cellar_1.updated_by AS cellar_1_updated_by, authuser_1.password AS authuser_1_password, authuser_1.id AS authuser_1_id, authuser_1.name AS authuser_1_name, authuser_1.email AS authuser_1_email, authuser_1.displayname AS authuser_1_displayname, authuser_1.fk_language_id AS authuser_1_fk_language_id, authuser_1.fk_cellar_id AS authuser_1_fk_cellar_ id, authuser_1.created_at AS authuser_1_created_at, authuser_1.updated_at AS authuser_1_updated_at, authuser_1.created_by AS authuser_1_created_by, authuser_1.updated_by AS authuser_1_updated_by, bottle_1.id AS bottle_1_id, bottle_1.remarks AS bottle_1_remarks, bottle_1.barcode AS bottle_1_barcode, bottle_1.inetrefcode AS bottle_1_inetrefcode, bottle_1.inetrefsource AS bottle_1_inetrefsource, bottle_1.quantitypurchased AS bottle_1_quantitypurchased, bottle_1.quantityconsumed AS bottle_1_quantityconsumed, bottle_1.lastpurchaseprice AS bottle_1_lastpurchaseprice, bottle_1.avgpurchaseprice AS bottle_1_avgpurchaseprice, bottle_1.currentvalue AS bottle_1_currentvalue, bottle_1.fk_vintage_id AS bottle_1_fk_vintage_id, bottle_1.fk_container_id AS bottle_1_fk_container_id, bottle_1.fk_sealtype_id AS bottle_1_fk_sealtype_id, bottle_1.created_at AS bottle_1_created_at, bottle_1.updated_at AS bottle_1_updated_at, bottle_1.created_by AS bottle_1_created_by, bottle_1.updated_by AS bottle_1_updated_ by, vintage_1.id AS vintage_1_id, vintage_1.vintage AS vintage_1_vintage, vintage_1.notes AS vintage_1_notes, vintage_1.alcohol AS vintage_1_alcohol, vintage_1.avgscore AS vintage_1_avgscore, vintage_1.avgscore2 AS vintage_1_avgscore2, vintage_1.locurl AS vintage_1_locurl, vintage_1.url AS vintage_1_url, vintage_1.fk_drinkinfo_id AS vintage_1_fk_drinkinfo_id, vintage_1.fk_wineinfo_id AS vintage_1_fk_wineinfo_id, vintage_1.fk_spirinfo_id AS vintage_1_fk_spirinfo_id, vintage_1.created_at AS vintage_1_created_at, vintage_1.updated_at AS vintage_1_updated_at, vintage_1.created_by AS vintage_1_created_by, vintage_1.updated_by AS vintage_1_updated_by, drinkinfo_1.id AS drinkinfo_1_id, drinkinfo_1.name AS drinkinfo_1_name, drinkinfo_1.name2 AS drinkinfo_1_name2, drinkinfo_1.barrique AS drinkinfo_1_barrique, drinkinfo_1.externalref AS drinkinfo_1_externalref, drinkinfo_1.notes AS drinkinfo_1_notes, drinkinfo_1.variety AS drinkinfo_1_variety, drinkinfo_1.fk_country_id AS drinkinfo_1_fk_country_ id, drinkinfo_1.fk_region_id AS drinkinfo_1_fk_region_id, drinkinfo_1.fk_subregion_id AS drinkinfo_1_fk_subregion_id, drinkinfo_1.fk_minregion_id AS drinkinfo_1_fk_minregion_id, drinkinfo_1.fk_supplier_id AS drinkinfo_1_fk_supplier_id, drinkinfo_1.fk_producer_id AS drinkinfo_1_fk_producer_id, drinkinfo_1.fk_distiller_id AS drinkinfo_1_fk_distiller_id, drinkinfo_1.fk_bottler_id AS drinkinfo_1_fk_bottler_id, drinkinfo_1.fk_quality_id AS drinkinfo_1_fk_quality_id, drinkinfo_1.fk_drinktype_id
Re: [sqlalchemy] column_property and Firebird
Michael, On 01/16/2012 05:53 PM, Michael Bayer wrote: On Jan 16, 2012, at 11:46 AM, werner wrote: I would like to do this type of thing: namesandvar = column_property(names + + variety) and use it e.g. like: query(db.Cellarbook).filter(db.Drinkinfo.namesandvar.contains(u'menu')) But Firebird SQL doesn't like the + operator, it throws an error Strings cannot be added or subtracted in dialect 3. Is there an alternative which would work for both FB and Postgresql? the + should be coming out as || if names and variety are both of String type. Hhm, yeap it works if I use sa.Unicode. So I must have messed up with my custom type. Thanks for your quick reply Werner -- 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] Tea Rota
Hi Calum,
On 01/13/2012 01:31 PM, Calum MacLeod wrote:
Thanks, Ian,
I appreciate your advice and have removed the first conditional (if
name not in duties).
Have now changed that to:
duty = Duty(date=date)
duties[date] = duty
if name not in volunteers:
volunteer = Volunteer(fore=fore, surn=surn, name=name)
volunteers[name] = volunteer
volunteer.duties.append(duty)
Another hobbiest here (so take the following with a grain of salt), I
think you would want this:
else:
volunteers[name].duties.append(duty)
Or
if name not in volunteers:
volunteer = Volunteer(fore=fore, surn=surn, name=name)
volunteers[name] = volunteer
volunteers[name].duties.append(duty)
Would also change your Volunteer definition from:
class Volunteer(Base):
__tablename__ = 'volunteers'
id = Column(Integer, primary_key=True)
fore = Column(String)
surn = Column(String)
name = Column(String)
dates = association_proxy('duties', 'date')
to:
class Volunteer(Base):
__tablename__ = 'volunteers'
id = Column(Integer, primary_key=True)
fore = Column(String)
surn = Column(String)
dates = association_proxy('duties', 'date')
# read only columns
@hybrid_property
def name(self):
return self.fore + ', ' + self.surn
I.e. don't store the same information in two different columns - why not
is better explained on e.g. the wiki -
http://en.wikipedia.org/wiki/Database_normalization
hybrid_property is described here
http://www.sqlalchemy.org/docs/orm/extensions/hybrid.html?highlight=hybrid_property#sqlalchemy.ext.hybrid.hybrid_property
If you do this you would need to change this:
volunteer = Volunteer(fore=fore, surn=surn, name=name)
to:
volunteer = Volunteer(fore=fore, surn=surn)
Hope this helps
Werner
--
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] joinedload/subqueryload and friends
In my application I have a user configured list control which is a bit slow, so I started experimenting with joinedload and friends. Looked at: http://www.sqlalchemy.org/docs/orm/loading.html This is working and cuts query time just about in half. drinks = drinks.options(db.sao.joinedload_all( 'drinkinfo', 'vintage', 'bottle'), db.sao.joinedload( 'drinkinfo.country_lv'), db.sao.joinedload( 'drinkinfo.drinktype_lv'), db.sao.joinedload( 'bottle.container_lv')) As the control is user configured I like to dynamically build the joinedload and/or subqueryload, so is it possible to do the above in a form similar to this: drinks = drinks.options(db.sao.joinedload_all( 'drinkinfo', 'drinkinfo.country_lv', 'drinkinfo.drinktype_lv', 'vintage', 'bottle', 'bottle.container_lv')) I tried a few combinations but I always get exceptions. Appreciate any hints on this, even further pointers on what else to read in the doc. Werner -- 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] joinedload/subqueryload and friends
Michael, On 11/16/2011 05:25 PM, Michael Bayer wrote: ... It shouldn't be too hard to roll a Python function that does what you want here, though.Then I looked and saw this is like a programming interview question - basically an unusual traversal. If i were asked this on an interview, I'd choke and freeze and they'd think I'm a moron, which is annoying since absent of that pressure it took me about ten minutes to come up with it! You are amazing, it took you 10 minutes, I wouldn't have figured out how to start for a few hours - lucky I don't have to interview:) Now I just have to sit down and benchmark different combinations. Thanks a lot Werner -- 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] select count(*)
On 11/04/2011 03:11 AM, Mark Erbaugh wrote: On Nov 3, 2011, at 3:31 PM, werner wrote: Mark, On 11/03/2011 07:18 PM, Mark Erbaugh wrote: Using the query object count() method generates a sub-query (as per the docs). The docs say to use func.count to avoid the subquery. func.count seems to require a field object. Is there a way to generate a query that essentially becomes 'select count(*) from table' using the ORM, not the SQL generator? Just the other day I thought I needed the same, initially I just used the id column which all my tables had, but as count(anything) is pretty expensive (using Firebird SQL - so might be different for other dbs) I wanted to find a way without using count(). In my case I needed at some point to get all the id values of that table (to build a virtual listctrl in wxPython), so instead of doing the count and starting feeling the list I got the id and did a len(onresult) to get my count. Point I am trying to make with a lot of words, maybe there is a solution which doesn't need count() at all:-) . I never considered that a count(*) was that expensive especially if there is no where clause. I think it depends a lot on the SQL one uses. I know it is recommended not to use count on Firebird SQL unless there is no other solution. Just did a google search on count() sql slow and there seem to be problems with other db engines too. I would think that it would be less expensive than actually retrieving all the rows and counting them. Just did a little test with one of my larger tables (over 16,000 rows) and doing: qTable = db.Cepagesyn idCol = qTable.id resultT = session.query(qTable).all() # about 1 sec resultC = session.query(idCol).all() # about .25 sec count = session.query(db.sa.func.count(idCol)).all() # about .01 sec So, you are absolutely right count() is faster. In my case I need resultC any way, so doing count and then sometimes later resultC could return a different number and I really don't need it. What if there are millions of rows? The result set could fill up memory. In my case, I just need to know how many rows. Don't deal in millions (Euros or rows) :-) . I don't care about any other details. In one case, I'm checking to see if there are zero rows, in which case, I populate the table with initial rows. In another case, I'm just unittesting some code and I want to make sure that there are the proper number of rows in the table as one of the test conditions. I believe the count test line is what you were looking for (btw db.Cepagesyn is the class of my table cepagesyn). Werner -- 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] select count(*)
Mark, On 11/03/2011 07:18 PM, Mark Erbaugh wrote: Using the query object count() method generates a sub-query (as per the docs). The docs say to use func.count to avoid the subquery. func.count seems to require a field object. Is there a way to generate a query that essentially becomes 'select count(*) from table' using the ORM, not the SQL generator? Just the other day I thought I needed the same, initially I just used the id column which all my tables had, but as count(anything) is pretty expensive (using Firebird SQL - so might be different for other dbs) I wanted to find a way without using count(). In my case I needed at some point to get all the id values of that table (to build a virtual listctrl in wxPython), so instead of doing the count and starting feeling the list I got the id and did a len(onresult) to get my count. Point I am trying to make with a lot of words, maybe there is a solution which doesn't need count() at all:-) . Werner -- 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] Enh. request: more details on Unicode type warning
On 09/09/2011 05:07 PM, Michael Bayer wrote: On Sep 9, 2011, at 11:01 AM, werner wrote: Tracking down where one forgot to use u'' e.g.: somemodelinstance.somecol = 'somevalue' instead of: somemodelinstance.somecol = u'somevalue' is sometimes not that easy to track down as the warning doesn't provide any hints. C:\python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engine\default.py:448: SAWarning: Unicode type received non-unicode bind param value param.append(processors[key](compiled_params[key])) Tried to come up with a patch but can't figure out how/where this warning is generated. Could it include e.g. the model class and column name? This warning is emitted by the datatype and has no awareness of the column to which it is attached (it could be associated with many) nor is the column in question passed in, nor is there even necessarily a column in use. The column itself then has no awareness of the ORM or any kind of model objects, etc. To track the warning, have the warning raise an exception: http://docs.python.org/library/warnings.html Thanks for the detailed explanation and the solution. Werner -- 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] Enh. request: more details on Unicode type warning
Tracking down where one forgot to use u'' e.g.: somemodelinstance.somecol = 'somevalue' instead of: somemodelinstance.somecol = u'somevalue' is sometimes not that easy to track down as the warning doesn't provide any hints. C:\python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engine\default.py:448: SAWarning: Unicode type received non-unicode bind param value param.append(processors[key](compiled_params[key])) Tried to come up with a patch but can't figure out how/where this warning is generated. Could it include e.g. the model class and column name? Werner -- 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] relationship trouble when using UsageRecipes/Views
I am working on my localization stuff and run into a problem when I want
to add an relationship to a class.
I based my code on
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views , but maybe I
messed something up.
The following works:
r1 = session.query(db.Region_LV).get(175)
print type(result.country_lv)
print r1
print r1.country_lv.name
with this output:
Region_LV(centralkey=175, country_lv=Country_LV(centralkey=83,
created_at=datetime.datetime(2011, 9, 7, 9, 39, 10, 702000),
created_by=None, dialcode=30, fk_language_id=2, id=83, is2code=u'GR',
iswinecountry=1, name=u'Gr\xe8ce', shortname=None, un3code=u'GRC',
updated_at=datetime.datetime(2011, 9, 7, 9, 39, 10, 702000),
updated_by=None, website1=None, website2=None),
created_at=datetime.datetime(2011, 9, 7, 9, 39, 11, 452000),
created_by=None, fk_country_id=83, fk_language_id=2, id=175,
name=u'Sud-Ouest', shortname=u'Sud-Ouest',
updated_at=datetime.datetime(2011, 9, 7, 9, 39, 11, 452000),
updated_by=None)
Grèce
The model for all this is:
class Region(DeclarativeBase, mix.StandardColumnMixin):
__tablename__ = u'region'
centralkey = sa.Column(sa.BigInteger())
name = sa.Column(sa.Unicode(length=50), nullable=False)
shortname = sa.Column(sa.Unicode(length=10))
fk_country_id = sautils.reference_col('country')
__localize_columns__ = ['name', 'shortname']
Region_L, Region_LV = sautils.CreateLocalized(Region())
Region_LV.country_lv = sao.relationship('Country_LV')
CreateLocalized creates the Region_LV based on the usage recipe.
Now if I add:
Region_LV.language = sao.relationship('Language')
I get:
sqlalchemy.exc.ArgumentError: Could not determine join condition between
parent/child tables on relationship Region_LV.language. Specify a
'primaryjoin' expression. If 'secondary' is present, 'secondaryjoin' is
needed as well.
or:
Region_LV.language = sao.relationship('Language',
primaryjoin=Region_LV.fk_language_id==Language.id)
I get:
sqlalchemy.exc.ArgumentError: Could not determine relationship direction
for primaryjoin condition 'country_lv.fk_language_id = language.id', on
relationship Region_LV.language. Ensure that the referencing Column
objects have a ForeignKey present, or are otherwise part of a
ForeignKeyConstraint on their parent Table, or specify the foreign_keys
parameter to this relationship.
or:
Region_LV.language = sao.relationship('Language',
primaryjoin=Country_LV.fk_language_id==Language.id,
foreign_keys=[Country_LV.__table__.c.fk_language_id])
I get:
sqlalchemy.exc.ArgumentError: Column-based expression object expected
for argument 'foreign_keys'; got: 'Country_LV.fk_language_id', type
type 'str'
sqlalchemy.exc.ArgumentError: Could not locate any foreign-key-equated,
locally mapped column pairs for primaryjoin condition
'country_lv.fk_language_id = language.id' on relationship
Region_LV.language. For more relaxed rules on join conditions, the
relationship may be marked as viewonly=True.
Tried with viewonly but couldn't make that work either.
I guess/think my problem is that I don't define a ForeignKeyConstraint
for the fk_language_id column but I haven't found how this is done as
the Country_LV view is created using from sqlalchemy.sql import table.
I'd appreciate any tips on how to get this to work.
Werner
--
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] relationship trouble when using UsageRecipes/Views
Michael,
On 09/07/2011 05:10 PM, Michael Bayer wrote:
On Sep 7, 2011, at 4:40 AM, werner wrote:
or:
Region_LV.language = sao.relationship('Language',
primaryjoin=Country_LV.fk_language_id==Language.id,
foreign_keys=[Country_LV.__table__.c.fk_language_id])
I get:
sqlalchemy.exc.ArgumentError: Column-based expression object expected for argument
'foreign_keys'; got: 'Country_LV.fk_language_id', typetype 'str'
this one above is the one that doesn't make any sense (also its probably how
the relationship should be set up).
Sorry that was a copy/paste error on my part.
Clearly x.__table__.c.somename is a Column object, not a string.
Something is up with what you're sending it. I've tried to reproduce with
no luck. See attached. Also I don't even need the primaryjoin/foreignkeys if
the originating Table upon which the view is based has foreign keys to the
parent.
The additional column is in my case added in to the view as the base
table doesn't have it and it is filled by the stored procedure (which
combines the base table plus the localized table ...) , i.e.:
def view(name, metadata, selectable):
t = table(name)
for c in selectable.c:
c._make_proxy(t)
lc = sasql.column(stuff_id, sa.Integer)
t.append_column(lc)
But what I had overlooked/forgotten to include was the __mapper_args__
= {primary_key:__table__.c.id} line.
After adding this the relationship as shown at the beginning it works.
Thanks a lot for your answer and your patience.
Werner
--
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] relationship trouble when using UsageRecipes/Views
On 09/07/2011 05:58 PM, Michael Bayer wrote:
On Sep 7, 2011, at 11:53 AM, werner wrote:
Michael,
On 09/07/2011 05:10 PM, Michael Bayer wrote:
On Sep 7, 2011, at 4:40 AM, werner wrote:
or:
Region_LV.language = sao.relationship('Language',
primaryjoin=Country_LV.fk_language_id==Language.id,
foreign_keys=[Country_LV.__table__.c.fk_language_id])
I get:
sqlalchemy.exc.ArgumentError: Column-based expression object expected for argument
'foreign_keys'; got: 'Country_LV.fk_language_id', typetype 'str'
this one above is the one that doesn't make any sense (also its probably how
the relationship should be set up).
Sorry that was a copy/paste error on my part.
Clearly x.__table__.c.somename is a Column object, not a string.
Something is up with what you're sending it. I've tried to reproduce with
no luck. See attached. Also I don't even need the primaryjoin/foreignkeys if
the originating Table upon which the view is based has foreign keys to the
parent.
The additional column is in my case added in to the view as the base table
doesn't have it and it is filled by the stored procedure (which combines the
base table plus the localized table ...) , i.e.:
def view(name, metadata, selectable):
t = table(name)
for c in selectable.c:
c._make_proxy(t)
lc = sasql.column(stuff_id, sa.Integer)
t.append_column(lc)
But what I had overlooked/forgotten to include was the __mapper_args__ =
{primary_key:__table__.c.id} line.
After adding this the relationship as shown at the beginning it works.
well that's also strange. If the mapper is not able to find a primary key,
the error is very clear:
sqlalchemy.exc.ArgumentError: Mapper Mapper|MSView|msview could not assemble
any primary key columns for mapped table 'msview'
not sure why that isn't what you were seeing.
Just tried to step back and see what other change I have done which
would explain my problem, but I missed to commit to source control
(didn't bother as it didn't work - should have known better).
Werner
Werner
--
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] Instance is not persistent within this Session
thanks for the clarification. there's nothing I can see in your practices here that causes that error, it has to do with how you're getting at Session objects in relation to your wx environment. I'd add assertions at every point to ensure that the Session you're getting is the one that you think it is, same with objects being dealt with.You're looking to reveal the problem much farther up the chain of events basically. Thanks Michael Werner -- 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] Instance is not persistent within this Session
A few of my user run into this exception when they use a dialog to create a new wine purchase. Fri Aug 05 10:24:56 2011 Version: 3.2.182.9 wxV: 2.8.11.0 (msw-unicode) * Traceback (most recent call last): File dialognewwine.pyo, line 779, in OnSaveButton File dialognewwine.pyo, line 797, in SaveData File sqlalchemy\orm\session.pyo, line 901, in refresh File sqlalchemy\orm\session.pyo, line 970, in _expire_state File sqlalchemy\orm\session.pyo, line 1261, in _validate_persistent InvalidRequestError: Instance 'Purchase at 0xc1c7a30' is not persistent within this Session I can't figure out what I am doing wrong which very very rarely causes this exception, I actually could never recreate it yet, nor one of my testers, but I had reports from two different users. Line 797 is the refresh line in the following code snippet which is done when saving the purchase: # commit it all self.Getds().commit() self.Getds().refresh(self.dbItemPurchase) currencyDBItem = self.dbItemPurchase.currency I am creating the session like this using SA 0.6.7: self.engine = db.sa.create_engine(dburl, encoding='utf8', echo=False) self.engine.connect() # for a connection now self.Session = db.sao.sessionmaker() self.Session.configure(bind=self.engine) self.ds = self.Session() The self.dbItemPurchase I create during the startup of the dialog, as shown below. self.dbItem = db.Cellarbook() self.Getds().add(self.dbItem) self.dbItemVintage = db.Cbvintage() self.dbItemVintage.cellarbook = self.dbItem self.dbItem.cbvintage.append(self.dbItemVintage) self.Getds().add(self.dbItemVintage) self.dbItemVintageW = db.Wineinfo() self.dbItemVintage.wineinfo = self.dbItemVintageW self.Getds().add(self.dbItemVintageW) self.dbItemBottle = db.Cbbottle() self.dbItemBottle.cbvintage = self.dbItemVintage self.dbItemVintage.cbbottle.append(self.dbItemBottle) self.Getds().add(self.dbItemBottle) self.dbItemPurchase = db.Purchase() self.dbItemPurchase.quantity = 1 # dummy self.dbItemPurchase.cbbottle = self.dbItemBottle self.dbItemBottle.purchase.append(self.dbItemPurchase) self.Getds().add(self.dbItemPurchase) self.Getds().flush() Anyone can give me some pointers of what I could possibly do wrong which would cause the instance not being persistent. Many thanks in advance as I am hitting my head on trying to recreate this problem for some time now. Werner -- 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] Instance is not persistent within this Session
Hi Michael, One thing that stands out here is the usage of self., implying the scope at which the various objects are declared is all the same. Engine, a connection, sessionmaker, session, then actual objects, etc. all at the exact same scope on self, and what's self?Scope is the important thing here. Engines and sessionmakers are usually module scope, not local to an operation or any actual objects. Especially an Engine which is meant to be a registry shared among threads - it isn't cheap to create by any means and should never be a per-operation object (though we see it often and have ensured that its at least supported, there's some difficult memory management I've had to implement to make sure unlimited-Engine apps don't run out of memory). That they're all stuck on the same self as the objects you're using for an operation, it's not the cause of the issue here but its a little suspect about the bigger picture. To track down issues like this you basically need to put assertions everywhere. Check that self.ds and the session used by self.Getds() isn't changing for some reason, check that self.dbItemPurchase remains in that same session (obj in session) at various points of interaction. If it *isnt* in the session, see if its in some *other* session, which would suggest some kind of concurrency issue (keeping in mind concurrency issues can exist in just one thread as well). So many more questions than answers here especially the self thing ! I will do some more testing on all this but I should be fine as self in the below code snippets is not alwasy the same, sorry for not making this clear - should have been clear even to me that it is important to define/explain this when asking for help on this. See below for clarification. On Aug 14, 2011, at 3:54 AM, werner wrote: A few of my user run into this exception when they use a dialog to create a new wine purchase. Fri Aug 05 10:24:56 2011 Version: 3.2.182.9 wxV: 2.8.11.0 (msw-unicode) * Traceback (most recent call last): File dialognewwine.pyo, line 779, in OnSaveButton File dialognewwine.pyo, line 797, in SaveData File sqlalchemy\orm\session.pyo, line 901, in refresh File sqlalchemy\orm\session.pyo, line 970, in _expire_state File sqlalchemy\orm\session.pyo, line 1261, in _validate_persistent InvalidRequestError: Instance 'Purchase at 0xc1c7a30' is not persistent within this Session I can't figure out what I am doing wrong which very very rarely causes this exception, I actually could never recreate it yet, nor one of my testers, but I had reports from two different users. Line 797 is the refresh line in the following code snippet which is done when saving the purchase: The application is a wxPython app. The following code is within my dialog (which is modal) and self is the dialog. # commit it all self.Getds().commit() self.Getds().refresh(self.dbItemPurchase) currencyDBItem = self.dbItemPurchase.currency then in the dialog self.Getds() is: def Getds(self): return wx.GetApp().Getds() I am creating the session like this using SA 0.6.7: self.engine = db.sa.create_engine(dburl, encoding='utf8', echo=False) self.engine.connect() # for a connection now self.Session = db.sao.sessionmaker() self.Session.configure(bind=self.engine) self.ds = self.Session() Now here is where I caused the big confusion as all the above session creation stuff is at application level and I should have shown that further down and made it clear - sorry for wasting your time. In wxPython I get to the app with the call wx.GetApp() and the .Getds() at the application level is this: def Getds(self): return self.ds The following is again in the dialog. Sorry again for badly explaining my problem. Werner The self.dbItemPurchase I create during the startup of the dialog, as shown below. self.dbItem = db.Cellarbook() self.Getds().add(self.dbItem) self.dbItemVintage = db.Cbvintage() self.dbItemVintage.cellarbook = self.dbItem self.dbItem.cbvintage.append(self.dbItemVintage) self.Getds().add(self.dbItemVintage) self.dbItemVintageW = db.Wineinfo() self.dbItemVintage.wineinfo = self.dbItemVintageW self.Getds().add(self.dbItemVintageW) self.dbItemBottle = db.Cbbottle() self.dbItemBottle.cbvintage = self.dbItemVintage self.dbItemVintage.cbbottle.append(self.dbItemBottle) self.Getds().add(self.dbItemBottle) self.dbItemPurchase = db.Purchase() self.dbItemPurchase.quantity = 1 # dummy self.dbItemPurchase.cbbottle = self.dbItemBottle self.dbItemBottle.purchase.append(self.dbItemPurchase) self.Getds().add(self.dbItemPurchase) self.Getds().flush() Anyone can give me some pointers of what I could possibly do wrong
[sqlalchemy] Can pk_col function be adapted for Firebird?
I can't figure out how I could adapt the pk_col function on this page http://www.sqlalchemy.org/trac/wiki/UsageRecipes/NamingConventions to handle the Sequence definition needed for e.g. Firebird. At the point when Column is instantiated I don't have access to table.name and I can't figure it out either how to do it in on_table_attach. Would appreciate any tips on this. Werner -- 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] Can pk_col function be adapted for Firebird?
On 08/12/2011 02:18 PM, werner wrote:
I can't figure out how I could adapt the pk_col function on this page
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/NamingConventions to
handle the Sequence definition needed for e.g. Firebird.
At the point when Column is instantiated I don't have access to
table.name and I can't figure it out either how to do it in
on_table_attach.
Would appreciate any tips on this.
Did a bit more searching and trying and came up with this:
def pk_col(cls, **kw):
Produce a primary key column for a table.
e.g.::
pk_col()
is equivalent to::
Column(id, sa.BigInteger,
doc = Primary key column for tablename,
primary_key=True,
sequence=sa.Sequence('tablename_id')
)
kw['primary_key'] = True
c = sa.Column(sa.BigInteger(), sa.Sequence('seq_%s_%s' %
(cls.__tablename__,
dbg.pkId)), **kw)
@sa.event.listens_for(c, before_parent_attach)
def on_table_attach(column, table):
column.name = column.key = dbg.pkId
column.doc = Primary key column for %r % table.name
c._creation_order = 0 # forces it to the top when using declarative
return c
I.e. pass cls in so I can get to __tablename__.
Is this an o.k. way of doing it or is there a better/cleaner way?
Werner
--
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] Can pk_col function be adapted for Firebird?
On 08/12/2011 04:46 PM, Michael Bayer wrote:
On Aug 12, 2011, at 9:04 AM, werner wrote:
On 08/12/2011 02:18 PM, werner wrote:
I can't figure out how I could adapt the pk_col function on this page
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/NamingConventions to handle
the Sequence definition needed for e.g. Firebird.
At the point when Column is instantiated I don't have access to table.name
and I can't figure it out either how to do it in on_table_attach.
Would appreciate any tips on this.
Did a bit more searching and trying and came up with this:
def pk_col(cls, **kw):
Produce a primary key column for a table.
e.g.::
pk_col()
is equivalent to::
Column(id, sa.BigInteger,
doc = Primary key column fortablename,
primary_key=True,
sequence=sa.Sequence('tablename_id')
)
kw['primary_key'] = True
c = sa.Column(sa.BigInteger(), sa.Sequence('seq_%s_%s' % (cls.__tablename__,
dbg.pkId)), **kw)
@sa.event.listens_for(c, before_parent_attach)
def on_table_attach(column, table):
column.name = column.key = dbg.pkId
column.doc = Primary key column for %r % table.name
c._creation_order = 0 # forces it to the top when using declarative
return c
I.e. pass cls in so I can get to __tablename__.
Is this an o.k. way of doing it or is there a better/cleaner way?
you should be able to set the Sequence name directly in the attach event
(starting with a fake name). not sure how you're using pk_col() above (how it
gets at 'cls').
I had this as part of the declarative base.
@sad.declared_attr
def id(cls):
# use a method so that the pk_col() returned
# here is the one used instead of a copy
return pk_col(cls)
Its also possible to create + attach the Sequence to the Column after the fact
but I don't know that the public API is there for that quite yet. (i think
calling seq._set_parent(column) would be sufficient ...)
Yeap, great.
got this now:
@sa.event.listens_for(c, before_parent_attach)
def on_table_attach(column, table):
column.name = column.key = dbg.pkId
column.doc = Primary key column for %r % table.name
seq = sa.Sequence('seq_%s_%s' % (table.name,
dbg.pkId))
seq._set_parent(column)
I guess I just have to watch out for whenever you provide a public API
for _set_parent.
Thanks
Werner
--
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] storedprocedure with input parameter
Conor, On 07/27/2011 05:54 PM, Conor wrote: On 07/27/2011 10:42 AM, werner wrote: I like to use a stored procure which needs a input parameter in something like this: seltest = db.sa.select([id, name]).select_from(db.sa.func.someStoredProc(2)).alias() seltestm = db.sao.mapper(ATest, seltest, primary_key=[seltest.c.id]) result = session.query(seltestm).get(73) above works, but I would really need to replace the hardcoded 2 with a function, i.e.: seltest = db.sa.select([id, name]).select_from(db.sa.func.someStoredProc(getSomeUserValue())).alias() seltestm = db.sao.mapper(ATest, seltest, primary_key=[seltest.c.id]) # set the SomeUserValue here and then do result = session.query(seltestm).get(73) tried using functools.partial but I get a InterfaceError exception. Werner I believe you want to replace getSomeUserValue() with sa.bindparam(callable_=getSomeUserValue). See the docs at http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.expression.bindparam. As an aside, do you really want to map a class against a dynamic query? I'm not sure how well the ORM deals with that. At the very least, I think you need to ensure that SomeUserValue does not change while using the session. Thanks that works great. Werner -- 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] storedprocedure with input parameter
I like to use a stored procure which needs a input parameter in something like this: seltest = db.sa.select([id, name]).select_from(db.sa.func.someStoredProc(2)).alias() seltestm = db.sao.mapper(ATest, seltest, primary_key=[seltest.c.id]) result = session.query(seltestm).get(73) above works, but I would really need to replace the hardcoded 2 with a function, i.e.: seltest = db.sa.select([id, name]).select_from(db.sa.func.someStoredProc(getSomeUserValue())).alias() seltestm = db.sao.mapper(ATest, seltest, primary_key=[seltest.c.id]) # set the SomeUserValue here and then do result = session.query(seltestm).get(73) tried using functools.partial but I get a InterfaceError exception. Werner -- 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] Ubuntu install
On 05/07/2011 07:27 PM, Michael Trier wrote: On Sat, May 7, 2011 at 11:11 AM, werner wbru...@free.fr mailto:wbru...@free.fr wrote: Just FYI, I am installing things on Ubuntu 10.10 (Maverick) and when I do this: easy_install.main(['-Zmad', sitePKG, sqlalchemy==0.6.8]) Maybe I'm confusing the issue but the latest version is 0.6.7. http://www.sqlalchemy.org/docs/ shows 0.6.8. Anyhow, if it is 0.6.7 I should get it if I use easy_install.main(['-Zmad', sitePKG, sqlalchemy]) But this gets me 0.6.6. Werner -- 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] Ubuntu install
Just FYI, On 05/08/2011 09:46 AM, werner wrote: ... easy_install.main(['-Zmad', sitePKG, sqlalchemy]) But this gets me 0.6.6. Gets 0.6.6 if I do the above on Ubuntu 10.10 Maverick, just did the same in a VirtualBox/Win 7 machine and I get 0.6.7. Werner -- 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] Ubuntu install
Just FYI,
I am installing things on Ubuntu 10.10 (Maverick) and when I do this:
easy_install.main(['-Zmad', sitePKG, sqlalchemy==0.6.8])
I get:
error: Could not find suitable distribution for
Requirement.parse('sqlalchemy==0.6.8') (--always-copy skips system and
development eggs)
If I use sqlalchemy==0.6, I get 0.6.3
With sqlalchemy==0.6.6 I did get 0.6.6 which is fine for what I am
doing at the moment.
Werner
--
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: Side by side versions on one machine.
On 04/26/2011 03:24 PM, Mike Conley wrote: If you only want to change the SQLAlchemy version and use easy install tools, I have done it by altering the sqlalchemy path in site_packages/easy-install.pth Or do this before you import SQLAlchemy: I find that easier than setting up virtual environments. If you need to change a lot of things including maybe the Python version, then virtualenv might be the way to go. -- Mike Conley -- 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: Side by side versions on one machine.
On 04/26/2011 03:24 PM, Mike Conley wrote: If you only want to change the SQLAlchemy version and use easy install tools, I have done it by altering the sqlalchemy path in site_packages/easy-install.pth Sorry clicked wrong button to fast Use the multi version install, e.g.: # MULTI or SINGLE?!!! # # m = multi # Z = always unzip # a = always copy # x = exclude scripts # d = install directory # to install - Multiversion WITH SCRIPTS easy_install.main(['-Zmad', sitePKG, egg]) And then before you import SQLAlchemy do this to select the version: import pkg_resources pkg_resources.require(sqlalchemy) # get latest version ##pkg_resources.require(sqlalchemy==0.5.8) # get specific version Werner -- 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+Kinterbasdb installation error
On 28/02/2011 16:31, Massi wrote: Hi everyone, I'm trying to access a Firebird (version 2.5) database through Kinterbasdb under windows 7 with python 2.6. I'have download the file: kinterbasdb-3.3.0.win32-py2.6.msi which can be found at this link: http://www.firebirdsql.org/index.php?op=develsub=python, but when I try to install it I get the following errors: - Could not create: kinterbasdb-py2.6 - Could not set key value: python 2.6 kinterbas-3.3.0 - Could not set key value:C:\Python26\removekinterbasdb.exe - Could not set key value:C:\Python26\kinterbasdb-wininst.log Sounds like you are not running is as admin. I'm currently running python 2.6 win32 on a 64 bit CPU. The installed Firebird version is 64bit too. Maybe is it a compatibility issue? I also tried kinterbasdb-3.3.0.win-amd64-py2.6.msi, but it didn't work. I am on Win 7 64 bit but all my Python stuff and FB is 32 bit - so don't know if it would work. Anyhow you should better ask kinterbasdb questions on the FB driver list. Werner -- 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] Multilingual Model
Hi Madhu, On 02/02/2011 17:46, Madhu Alagu wrote: Hi I am looking best model for the following tables: - ---Table : groups_t - CREATE TABLE groups_t ( id BIGINT NOT NULL, code VARCHAR NOT NULL, version BIGINT NOT NULL ); - ---Table : groups_i18n_t - CREATE TABLE groups_i18n_t ( group_id BIGINT NOT NULL, lang_id BIGINT NOT NULL, description TEXT NOT NULL, version BIGINT NOT NULL ); - ---Table : groups_lookup_t - CREATE TABLE groups_lookup_t ( group_id BIGINT NOT NULL, lang_id BIGINT NOT NULL, code VARCHAR NOT NULL, description TEXT NOT NULL, lookup_text TEXT NOT NULL ); You might want to look at these threads. i10n of data internationalization of content A few different approaches to this problem were discussed in them. Werner -- 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] does one always have to call session.add ?
Hi, I sometimes see this type of error: Traceback (most recent call last): File dialognewwine.pyo, line 750, in OnSaveButton File dialognewwine.pyo, line 768, in SaveData File sqlalchemy\orm\session.pyo, line 924, in refresh File sqlalchemy\orm\session.pyo, line 1234, in _validate_persistent InvalidRequestError: Instance 'Purchase at 0xdac9df0' is not persistent within this Session Inspecting my code I see that I forgot to call session.add, however the same code works most of the time without complaining. Are there situations where session.add doesn't need to be called? Werner P.S. Using SA 0.6.5 -- 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] sqla and firebird
On 09/11/2010 01:34, Michael Bayer wrote: not sure if anyone knows. I have 2.1 running here for my own tests. On Nov 8, 2010, at 5:05 PM, Domingo Aguilera wrote: Is firebird 2.5 working with sqla. ? I am in the midst of moving to FB 2.5 and SA 0.6.5 and have not encountered any problems yet. You are on kinterbasdb 3.3.0? If not you can get it from here: http://www.firebirdsql.org/index.php?op=develsub=python 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] Link problem on : http://www.sqlalchemy.org/features.html
the View Current DBAPI Support link on the above page gives a page not found error. 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] Suggestion - Firebird engine to default type_conv=300
As of version 3.3 of kinterbasdb and with Firebird 2.1+ blob fields can be treated the same as other string type fields when one is using type_conv=300. New users will be on FB 2.1 (or soon 2.5) and it would make things easier/more consistent when dealing with blobs containing text - i.e. they won't have to search why sa.String will not return the same thing for a varchar or a blob column. Doc from - http://www.firebirdsql.org/devel/python/docs/3.3.0/beyond-python-db-api.html#parameter-conversion Quote: 300 (the ideal for Firebird 2.1 and later) New in v3.3 This translator configuration is identical to 200, but textual blobs are handled in the same way as other textual types, so unicode encoding/decoding is performed automagically. When converting in the input direction, this doesn’t work with any Firebird version prior to 2.1, because the Firebird API doesn’t make the blob’s character set ID available. 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.
Re: [sqlalchemy] get to dialect specific column type
I tried with a hack to get to this, but still no luck. I am doing: from sqlalchemy.dialects.firebird import dialect ... fbDialect = dialect() ... if str(col.type) == 'DATETIME': print col.type.dialect_impl(fbDialect) print col.type.get_dbapi_type(fbDialect) The first one gives me DATETIME and the second throws this exception. Traceback (most recent call last): File saCreateDb.py, line 5, in module import model as db File C:\dev\aaTests\sqla\i18nFB\model.py, line 116, in module class Country_LV(Base): File C:\dev\aaTests\sqla\i18nFB\model.py, line 117, in Country_LV __table__ = sautils.make_localize_view(Country(), Country_L(), Language(), metadata) File C:\dev\aaTests\sqla\i18nFB\sautils.py, line 181, in make_localize_view storedProc = doCreateLocaleStoredProc(baseinst, baseTable, localeTable, localeLangCol, localeFK, procName) File C:\dev\aaTests\sqla\i18nFB\sautils.py, line 31, in doCreateLocaleStoredProc print col.type.get_dbapi_type(fbDialect) File c:\python26\lib\site-packages\sqlalchemy-0.6.4-py2.6.egg\sqlalchemy\types.py, line 1191, in get_dbapi_type return dbapi.DATETIME AttributeError: 'FBDialect_kinterbasdb' object has no attribute 'DATETIME' What am I doing wrong here? And is there a cleaner way of doing this, i.e. get the dialect currently used instead of using a hard coded dialect. On 20/09/2010 23:52, werner wrote: I am trying to automatically generate the stored procedure I need for the localize stuff. So, would like to do something like this: aninst.__table__.c['created_at'].type.get_dbapi_type(dbapi) - to get e.g. TIMESTAMP for a DateTime column with Firebird SQL. What is the most efficient/easy way to get at dbapi from e.g. an instance? Isn't there some more elegant way then doing connection.engine.dialect.dbapi? 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.
Re: [sqlalchemy] get to dialect specific column type
I try to explain in more detail what I am trying to do.
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',
Language, metadata)
language = sao.relation(Language, backref='country_l')
country = sao.relation(Country, backref='country_l')
class Country_LV(Base):
__table__ = sautils.make_localize_view(Country(), Country_L(),
Language(), metadata)
Witin make_localize_view I need to generate a stored procedure which
gets information such as columns etc from Country and Country_L ,
the generated code looks like this:
CREATE OR ALTER PROCEDURE countries_lp
returns (created_at DATE, updated_at TIMESTAMP, id BIGINT, name
VARCHAR(30), iso2 VARCHAR(2), iso3 VARCHAR(3), telcode SMALLINT) as
declare variable locale_name VARCHAR(30);
begin
for select created_at, updated_at, id, name, iso2, iso3, telcode
from countries
into :created_at, :updated_at, :id, :name, :iso2, :iso3, :telcode
do
begin
begin
locale_name = Null;
select name from countries_l
where :id = countries_l.fk_countries_id and
countries_l.fk_languages_code5 = rdb$get_context('USER_SESSION',
'LANG_CODE')
into :name;
end
if (:locale_name is not Null) then
begin
name = :locale_name;
end
suspend;
end
end
part of the code to generate the above is the following:
for col in basetable.c:
if str(col.type) == 'DATETIME':
# hack as I can't figure out a nicer/cleaner way
colType = 'TIMESTAMP'
basetable = Country.__table__
What I like to do is replace the check for DATETIME with
similar/same code I assume meta.create_all(engine) is using to
generate create table (can't yet figure out where/how this is all
done) and ideally this should work not only for Firebird engine.
Hope this is clearer.
Thanks for looking at all this.
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] get to dialect specific column type
I am trying to automatically generate the stored procedure I need for the localize stuff. So, would like to do something like this: aninst.__table__.c['created_at'].type.get_dbapi_type(dbapi) - to get e.g. TIMESTAMP for a DateTime column with Firebird SQL. What is the most efficient/easy way to get at dbapi from e.g. an instance? Isn't there some more elegant way then doing connection.engine.dialect.dbapi? 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.
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] possible doc error in UsageRecipe/NamingConventions
Shouldn't this (on page: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/NamingConventions) : # elsewhere, in main model code: from mymodel import make_table, id_column, ref_column, Base be: # elsewhere, in main model code: from mymodel import make_table, id_column, reference_column, Base i.e. change ref_column to reference_column 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] Views recipe - is it usable with a stored procedure as a select
Looking through the different recipes I noted the one on views (http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views). Would like to use this with the ORM but some of my views have a stored procedure as a select. Is it possible with SA to create a selectable as is needed for the Views recipe from/with a stored procedure? The view definition in Firebird SQL is: CREATE OR ALTER VIEW COUNTRIES_LV( ID, NAME, ISO2, ISO3, TELCODE, CREATED_AT, UPDATED_AT) AS select * from countries_lp; countries_lp being the stored procedure. 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.
Re: [sqlalchemy] Re: internationalization of content
Hi Nil, On 14/09/2010 21:58, NiL wrote: Hi Werner, many thanks for your rich reply. I'm going to try an elixir implementation for now. If you want follow the thread of the same title in the elixir mailing list. Thanks for letting me know. One of the things which disturb me about SA or Elixir approach is that other means to access the database will now have access to the translations, e.g. Report Writer accessing the db directly, tools like Excel using the db via ODBC etc etc. I am surprised that db's at this point in time don't have some support for something like this. 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.
Re: [sqlalchemy] internationalization of content
Hi Nil, On 13/09/2010 23:37, NiL wrote: Hi all, I'm lookin for a good solution to internationalize the content of my application. that is provide many translations for the database content (as opposed to the translation of the application itself with babel/gettext for template and code messages). Has anyone tried ti implement this ? a working solution ? willing to participate in a effort to provide a solution ? Very interested in this too. Some time ago I looked into this too. At the time I came across the following: - a gettext type solution implemented in SQL stored procedures - done by Karsten Hilbert for gnumed - http://cvs.savannah.gnu.org/viewvc/gnumed/gnumed/server/sql/gmI18N.sql?root=gnumedview=log I also had a go at it using SA's dynamic_loader and/or query enabled properties. see the thread in January 2010 on this list with a subject of dynamic_loader Got some test code on this using Firebird SQL, but never really finalized anything as I got a bit side tracked, and the code is probably pretty ugly as I am not that good a programmer. Just lately I also saw the following, which sounded interesting but it uses PostgreSQL - which is not an option for me at the moment. - http://rwec.co.uk/blog/2009/11/atomic-translations-part-1/ - http://rwec.co.uk/blog/2009/12/atomic-translations-part-2/ Hope some of this is useful to you 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.
Re: [sqlalchemy] meta.sorted_tables exception with 0.6.3
Michael,
On 06/09/2010 19:42, Michael Bayer wrote:
On Sep 6, 2010, at 1:23 PM, werner wrote:
Michael,
Thanks for the fast reply.
On 06/09/2010 15:53, Michael Bayer wrote:
engine = create_engine(...)
m = MetaData()
t = Table('foo', m, autoload=True, autoload_with=engine) #--- boom
The table which errors has the following definition. It seems to have to do with the two columns
MINWITH and MAXWIDTH which have default -1. FYI, when I change this to '0' and then
back to '-1' it becomes DEFAULT -1 and doesn't cause the exception anymore.
OK the bug is the SQL has default -1 and not DEFAULT -1 and apparently that
comes back to us as lower case. So that's your workaround if possible. Or you can download the
latest default tip where its fixed in r443b974a8013 . I also get the same error using 0.5.
Thanks again for your quick reply.
Don't understand why I didn't get it in 0.5, but anyhow I fix/work
around it by fixing the db.
Werner
Werner
/**/
/* Generated by IBExpert 06/09/2010 19:05:50 */
/**/
/**/
/*Following SET SQL DIALECT is just for the Database Comparer */
/**/
SET SQL DIALECT 3;
/**/
/* Tables */
/**/
CREATE GENERATOR GEN_LISTCTRL_LISTCTRLID;
CREATE TABLE LISTCTRL (
LISTCTRLID PKEYS NOT NULL /* PKEYS = BIGINT */,
LISTNAMEVARCHAR(20) NOT NULL,
COLNUM INTEGER,
DBCOLNAME VARCHAR(100),
COLSIZE INTEGER,
CREATED DATE,
UPDATED DATE,
MINWIDTHINTEGER default -1 NOT NULL,
MAXWIDTHINTEGER default -1 NOT NULL,
FILLSPACE BOOLEAN /* BOOLEAN = SMALLINT DEFAULT 1 */,
GROUPCOLBOOLEAN /* BOOLEAN = SMALLINT DEFAULT 1 */
);
/**/
/*Primary Keys*/
/**/
ALTER TABLE LISTCTRL ADD CONSTRAINT FK_LISTCTRL PRIMARY KEY (LISTCTRLID);
/**/
/* Triggers */
/**/
SET TERM ^ ;
/**/
/*Triggers for tables */
/**/
/* Trigger: LISTCTRL_BI0 */
CREATE OR ALTER TRIGGER LISTCTRL_BI0 FOR LISTCTRL
ACTIVE BEFORE INSERT POSITION 0
AS BEGIN
IF(NEW.CREATED IS NULL) THEN NEW.CREATED = current_date;
IF(NEW.UPDATED IS NULL) THEN NEW.UPDATED = current_date;
IF(NEW.LISTCTRLID IS NULL) THEN NEW.LISTCTRLID =
GEN_ID(GEN_LISTCTRL_LISTCTRLID,1);
END
^
/* Trigger: LISTCTRL_BU0 */
CREATE OR ALTER TRIGGER LISTCTRL_BU0 FOR LISTCTRL
ACTIVE BEFORE UPDATE POSITION 0
AS BEGIN
NEW.UPDATED = current_date;
END
^
SET TERM ; ^
/**/
/* Privileges */
/**/
/* Privileges of users */
GRANT ALL ON LISTCTRL TO MYWINECB;
GRANT SELECT ON LISTCTRL TO MYWINECBREAD;
--
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.
--
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] meta.sorted_tables exception with 0.6.3
Michael,
Thanks for the fast reply.
On 06/09/2010 15:53, Michael Bayer wrote:
engine = create_engine(...)
m = MetaData()
t = Table('foo', m, autoload=True, autoload_with=engine) #--- boom
The table which errors has the following definition. It seems to have
to do with the two columns MINWITH and MAXWIDTH which have default
-1. FYI, when I change this to '0' and then back to '-1' it becomes
DEFAULT -1 and doesn't cause the exception anymore.
Werner
/**/
/* Generated by IBExpert 06/09/2010
19:05:50 */
/**/
/**/
/*Following SET SQL DIALECT is just for the Database
Comparer */
/**/
SET SQL DIALECT 3;
/**/
/*
Tables */
/**/
CREATE GENERATOR GEN_LISTCTRL_LISTCTRLID;
CREATE TABLE LISTCTRL (
LISTCTRLID PKEYS NOT NULL /* PKEYS = BIGINT */,
LISTNAMEVARCHAR(20) NOT NULL,
COLNUM INTEGER,
DBCOLNAME VARCHAR(100),
COLSIZE INTEGER,
CREATED DATE,
UPDATED DATE,
MINWIDTHINTEGER default -1 NOT NULL,
MAXWIDTHINTEGER default -1 NOT NULL,
FILLSPACE BOOLEAN /* BOOLEAN = SMALLINT DEFAULT 1 */,
GROUPCOLBOOLEAN /* BOOLEAN = SMALLINT DEFAULT 1 */
);
/**/
/*Primary
Keys*/
/**/
ALTER TABLE LISTCTRL ADD CONSTRAINT FK_LISTCTRL PRIMARY KEY (LISTCTRLID);
/**/
/*
Triggers */
/**/
SET TERM ^ ;
/**/
/*Triggers for
tables */
/**/
/* Trigger: LISTCTRL_BI0 */
CREATE OR ALTER TRIGGER LISTCTRL_BI0 FOR LISTCTRL
ACTIVE BEFORE INSERT POSITION 0
AS BEGIN
IF(NEW.CREATED IS NULL) THEN NEW.CREATED = current_date;
IF(NEW.UPDATED IS NULL) THEN NEW.UPDATED = current_date;
IF(NEW.LISTCTRLID IS NULL) THEN NEW.LISTCTRLID =
GEN_ID(GEN_LISTCTRL_LISTCTRLID,1);
END
^
/* Trigger: LISTCTRL_BU0 */
CREATE OR ALTER TRIGGER LISTCTRL_BU0 FOR LISTCTRL
ACTIVE BEFORE UPDATE POSITION 0
AS BEGIN
NEW.UPDATED = current_date;
END
^
SET TERM ; ^
/**/
/*
Privileges */
/**/
/* Privileges of users */
GRANT ALL ON LISTCTRL TO MYWINECB;
GRANT SELECT ON LISTCTRL TO MYWINECBREAD;
--
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] begginers query question
Hi,
On 24/08/2010 10:14, Dobrysmak wrote:
Hi guys.
I've got a little problem with the sqlalchemy syntax.
I've got two tables with relations:
Table_Goups
id int(4) PrimaryKey not null,
name varchar(50) not null;
and
Table_User
id int(4) Primary Key not null,
login varchar(50) not null,
id_group int(4) Foreign Key not null;
i would like to build a query that would gets the user data from
Table_User and the id_group but insted od showing the id_group number
i want to show the Table_Groups.name
Can anyone help?
I use SA declarative, so you would define something like this in your model:
class Group(Base):
__table__ = sa.Table(u'groups', metadata,
sa.Column(u'id', sa.Integer(), sa.Sequence('gen_contact_id'),
primary_key=True, nullable=False),
sa.Column(u'name', sa.String(length=70, convert_unicode=False),
nullable=False),
)
class User(Base):
__table__ = sa.Table(u'users', metadata,
sa.Column(u'id', sa.Integer(), sa.Sequence('gen_contact_id'),
primary_key=True, nullable=False),
sa.Column(u'name', sa.String(length=70, convert_unicode=False),
nullable=False),
sa.Column(u'fk_group', sa.Integer(), sa.ForeignKey(u'groups.id'),
nullable=False),
)
group = sao.relation('Group', backref='user')
And then to query you could do e.g. this:
for usr in session.query(db.User).all():
print 'user: %s, group name: %s' % (usr.name, usr.group.name)
Check out the SA doc, especially the tutorials:
http://www.sqlalchemy.org/docs/ormtutorial.html
http://www.sqlalchemy.org/docs/ormtutorial.html#creating-table-class-and-mapper-all-at-once-declaratively
Hope this helps
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.
Re: [sqlalchemy] begginers query question
On 24/08/2010 15:53, werner wrote:
Hi,
On 24/08/2010 10:14, Dobrysmak wrote:
Hi guys.
I've got a little problem with the sqlalchemy syntax.
I've got two tables with relations:
Table_Goups
id int(4) PrimaryKey not null,
name varchar(50) not null;
and
Table_User
id int(4) Primary Key not null,
login varchar(50) not null,
id_group int(4) Foreign Key not null;
i would like to build a query that would gets the user data from
Table_User and the id_group but insted od showing the id_group number
i want to show the Table_Groups.name
Can anyone help?
I use SA declarative, so you would define something like this in your
model:
class Group(Base):
__table__ = sa.Table(u'groups', metadata,
sa.Column(u'id', sa.Integer(), sa.Sequence('gen_contact_id'),
primary_key=True, nullable=False),
sa.Column(u'name', sa.String(length=70, convert_unicode=False),
nullable=False),
)
Oops, a copy/paste error, should be: sa.Sequence('gen_group_id')
class User(Base):
__table__ = sa.Table(u'users', metadata,
sa.Column(u'id', sa.Integer(), sa.Sequence('gen_contact_id'),
primary_key=True, nullable=False),
and another one, a copy/paste error, should be: sa.Sequence('gen_user_id')
sa.Column(u'name', sa.String(length=70, convert_unicode=False),
nullable=False),
sa.Column(u'fk_group', sa.Integer(), sa.ForeignKey(u'groups.id'),
nullable=False),
)
group = sao.relation('Group', backref='user')
And then to query you could do e.g. this:
for usr in session.query(db.User).all():
print 'user: %s, group name: %s' % (usr.name, usr.group.name)
Check out the SA doc, especially the tutorials:
http://www.sqlalchemy.org/docs/ormtutorial.html
http://www.sqlalchemy.org/docs/ormtutorial.html#creating-table-class-and-mapper-all-at-once-declaratively
Hope this helps
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.
