Re: [sqlalchemy] how to get into PG database, is the url the right way? newbie question
I have made this for my project and I do not see why you are using Openlayers
for this.
This has nothing to do with sqlalchemy.
off topic: Ext.form.Panel has no 'protocol' so I think you should extend
FormPanel to include that functionality. Ext.direct is the way to go for stuff
like this.
On Aug 14, 2012, at 19:05 , Gery geryherb...@gmail.com wrote:
any ideas?? basically the idea is how to search inside a database being
outside the database, especifically through SqlAlchemy from OpenLayers?
On Monday, August 13, 2012 9:23:43 PM UTC+2, Gery wrote:
Hello, I'm new around here and I've been using SQLalchemy (SA) for a while. I
work with PostGis (PG), OpenLayers (OL), ExtJS, GeoExtJS and now with the
great SA and GeoAlchemy. I have one problem, I created a model where I
defined one table of my PG database, it has a url like this: url =
'postgresql://postgres:password@localhost:5432/pgdb'. In my HTML, I have some
maps displayed with OL and a toolbar built with ExtJS and GeoExtJS. I put a
search button there using ExtJS, and the OL protocol code to get the data.
This protocol has an url option, in this way:
var searchformPanel = new Ext.form.FormPanel(
{
width: 250,
bodyStyle: 'padding:5px',
labelAlign: 'top',
defaults:
{
anchor: '100%'
},
protocol: new OpenLayers.Protocol.HTTP(
{
url: 'http://localhost/mop/py/dbmodel.py',
format: new OpenLayers.Format.GeoJSON()
}
),
items:
etc,etc..
my problem is that in this url I wrote the whole path where my model script
is located, but after pressing the button I got nothing. I think I need
something else rather than only pointing the whole path and the python script
in the url mentioned above, is that correct? I've searched how to connect
sqlalchemy to extjs in google but didn't find any that solved this doubt.
Any support is very welcome, thanks in advance.
Best regards,
Gery
--
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/-/06H8WaCTV7cJ.
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] how to get into PG database, is the url the right way? newbie question
or take a look at this:
var store = new GeoExt.data.FeatureStore({
layer: sundials,
proxy: new GeoExt.data.ProtocolProxy({
protocol: new OpenLayers.Protocol.HTTP({
url: sundials.kml,
format: new OpenLayers.Format.KML()
})
}),
fields: [
{name: 'title', type: 'string'},
{name: 'description', type: 'string'}
],
autoLoad: true
});
which looks way different than your code….
use mod_python (better not)
use mod_wsgi (Good!)
use CGI of FastCGI (not that good)
GeoExt uses ext 3.3 which is a hazzard in its own rights since 4.x is so much
better (consistant) , faster and more stable, I guess GeoExt is over a year
behind reality. If you use that try if you can wait for GeoExt2
On Aug 18, 2012, at 02:24 , Martijn Moeling mart...@xs4us.nu wrote:
I have made this for my project and I do not see why you are using Openlayers
for this.
This has nothing to do with sqlalchemy.
off topic: Ext.form.Panel has no 'protocol' so I think you should extend
FormPanel to include that functionality. Ext.direct is the way to go for
stuff like this.
On Aug 14, 2012, at 19:05 , Gery geryherb...@gmail.com wrote:
any ideas?? basically the idea is how to search inside a database being
outside the database, especifically through SqlAlchemy from OpenLayers?
On Monday, August 13, 2012 9:23:43 PM UTC+2, Gery wrote:
Hello, I'm new around here and I've been using SQLalchemy (SA) for a while.
I work with PostGis (PG), OpenLayers (OL), ExtJS, GeoExtJS and now with the
great SA and GeoAlchemy. I have one problem, I created a model where I
defined one table of my PG database, it has a url like this: url =
'postgresql://postgres:password@localhost:5432/pgdb'. In my HTML, I have
some maps displayed with OL and a toolbar built with ExtJS and GeoExtJS. I
put a search button there using ExtJS, and the OL protocol code to get the
data. This protocol has an url option, in this way:
var searchformPanel = new Ext.form.FormPanel(
{
width: 250,
bodyStyle: 'padding:5px',
labelAlign: 'top',
defaults:
{
anchor: '100%'
},
protocol: new OpenLayers.Protocol.HTTP(
{
url: 'http://localhost/mop/py/dbmodel.py',
format: new OpenLayers.Format.GeoJSON()
}
),
items:
etc,etc..
my problem is that in this url I wrote the whole path where my model script
is located, but after pressing the button I got nothing. I think I need
something else rather than only pointing the whole path and the python
script in the url mentioned above, is that correct? I've searched how to
connect sqlalchemy to extjs in google but didn't find any that solved this
doubt.
Any support is very welcome, thanks in advance.
Best regards,
Gery
--
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/-/06H8WaCTV7cJ.
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.
--
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] Id and id
applies even for databases which standardize upper case
names as case insensitive such as Oracle.
The name field may be omitted at construction time and applied later, at any
time before the Column is associated with a Table. This is to support
convenient usage within the declarative extension.
the last paragraph is the most interesting. testing however makes me unsure
where to put the name. and even more interesting, how to test for the name in
the __init__ ?
for some reason I do not seem to get no reference to name, even after calling
Column.__init__, self.name == None, Am I thinking in the wrong direction?
Martijn
On Mar 5, 2012, at 00:06 , Michael Bayer wrote:
On Mar 4, 2012, at 2:18 PM, Martijn Moeling wrote:
sqlalchemy.exc.DataError: (DataError) invalid input syntax for integer:
Blablabla
LINE 3: WHERE ide_applicationcontroller.Application = E'Blablabla...
^
'SELECT ide_controller.Id AS ide_controller_Id, ide_controller.Name
AS ide_controller_Name, ide_controller.onLaunch AS
ide_controller_onLaunch, ide_controller.id AS ide_controller_id \nFROM
ide_controller, ide_applicationcontroller \nWHERE
ide_applicationcontroller.Application = %(param_1)s AND
ide_applicationcontroller.Controller = ide_controller.Id' {'param_1':
u'Blablabla'}
I do not understand where the E is coming from, I've check and checked and
never reference to id anywhere by mistake
the E is part of how psycopg2/libpq renders bound parameters before passing
off to the backend, and is normal.
I can not freely choose my columnnames
OK well I can confirm the use case is not supported at all on SQLAlchemy -
SQLA is case insensitive in how it deals with result set column names. It's
been that way since the beginning and nobody has ever asked for it to be
changed, much less even noticed it, most likely. Had I been making this
decision today, I would not have chosen the lower() option, as it adds
performance overhead in any case and is for the vast majority of cases
completely unnecessary.However, I don't recall if I was coming up against
DBAPIs that were not giving me the correct casing within cursor.description,
and I'm not sure if all DBAPIs handle this correctly, so there is some risk
to changing the behavior.
So I'd point out that this use case you have here is exceedingly unusual.
Many databases don't support it, not even SQLite, which won't let me create
such a table, even if I quote both names:
duplicate column name: Id u'\nCREATE TABLE a (\n\tid INTEGER NOT
NULL, \n\tId INTEGER, \n\tPRIMARY KEY (id)\n)\n\n' ()
nor will the current release of MySQL:
(1060, Duplicate column name 'Id') '\nCREATE TABLE a (\n\t`id`
INTEGER NOT NULL AUTO_INCREMENT, \n\t`Id` INTEGER, \n\tPRIMARY KEY (`id`),
\n\tUNIQUE (`Id`)\n)\n\n' ()
So generally, mixing overlapping names based on case within relational
databases is a really bad idea, and is just asking for trouble at every
stage.
and do not really want to run code for every parameter just to see if there
is an id field and put the value in whatever other storage name, that will
add unwanted load to my system.
So if your system is truly generic and you can't anticipate what existing
names are present, I'd point out that right off, your approach will not work
for SQLite or MySQL, which will not allow such a naming convention in any
case.
The logic we're talking about here would take place at configuration time in
any case, and would be a completely minuscule check that runs just once per
class at import time, so there's no load issue.Also, what if you were
given a class that actually had the lowercase name id on it already? If
you have no control over column names, don't you need to check for that name
already existing? Or are you publishing a restriction that this name is
reserved, and in which case why not say that all casing conventions of id
are reserved as well ?
Is this a bug? or do I hit a Feature of SA where it does not matter if id or
Id is used and somewhere a .lower() takes care of that. Can I switch it off
if so?
The model for the statement compiler and result proxy works on a case
insensitive model right now as far as how columns are located in result sets,
I can't even get a plain row back using such a casing convention.
Ticket #2423 (http://www.sqlalchemy.org/trac/ticket/2423) illustrates a patch
that would allow the behavior to be configurable. As anticipated, it adds
7% method call overhead to key performance tests, when the case insensitive
behavior is turned on as it doubles the function call overhead for each
lower(). Which leads me to want to release it in 0.8 with the default
behavior reversed, thereby avoiding the double-function call for the vast
majority of cases that aren't attempting to refer to columns
Re: [sqlalchemy] Id and id
I think I've got it working correctly.
in my mixin I now do:
@declared_attr
def id(self):
return ExtColumn('JSid',Unicode(255), default = None)
so the id property is actually stored in the DB Column 'JSid'
Since my introspection looks at the python class, it takes the name from the
class definition and put's 'id' in the json.
the only change is 'JSid' in the definition and no extra overhead is added.
This is nice since whilst solving this I found out that I have a few more of
these (i.e. Name and name)
Since I use PostgreSQL I got away with this….
Martijn
On Mar 5, 2012, at 11:55 , Martijn Moeling wrote:
Michael,
Thank you for confirming my worries and adding reason to it.
I'm not sure if you remember al my questions in the past but with my project
I'm constantly hitting the impossible, although its fun, it can be
frustrating sometimes.
What I'm doing is something bigger than just an application, I'm building an
web based operating system, with Desktop in a browser and with a development
system for applications.
It is based on Open Source products and build from absolute scratch, there is
much more to it but as I signed a non disclosure agreement with my self, I
must stop here.
Since I Love Python and absolutely hate Javascript, HTML, CSS, I Have put a
lot of effort in doing away with those and replace them with Python,
Basically what I have is an IDE to develop web appellations which in itself
is a web application. Within that you can write Python which is Compiled
into all the parts needed by a browser.
It resembles something which is very close to Visual Basic 6.0 if you
remember that one from a developer point of view.
Since I use the Id field for SA in the usual fashion, I struggle where I
reflect Javascript objects with an id field.
Everything is generated with Introspection, I subclassed the SA Column to add
properties to the colums. These are non functioning in run mode but are
used when compiling to javascript. (the _constuctor returns the real Column,
not the extended one).
the id column is coming from Python classes generated from the sources of
ExtJs, the Id column is tightly bound to communication between browser and
back-end (Since its the recordId).
at the end the Id column will be/is hidden from the developer, but I have
tons and tons of code relying on Id.
Within the IDE a user would and should be able to instance an object with an
id doing so in the creation is no problem since python differs between id
and Id (and Id is never set by code)
so :
id= ExtendedSAColumn(Unicode(25), default = ….)
will end up ad {xtype : 'textfield' , id : 'the id' ……}
For compile time I have no Issues with overhead
I can rename id to JS_id (or something) but I need to be able to do
Someclass.id = 'the id'
and print Someclass.id (or use the value in some other way)
Since an Application and its components are reflected in the database, almost
all classes which are compiled into javascript will have an id column. adding
a @declared_attr to my mixin en a setter function and map id to JSid adds an
id column to everything and that is not what I want (and messes with my
introspection routines to reflect SA Database object classes)
I must find a way where the id field can be used in the IDE without adding
any special code there, it should be transparent to users of my system.
Thing is, on some objects (maybe most, at least the ones which have to do
with stores and models) I Introspect a Class and not an instance of that
Class. While during runtime these python objects are used to query the
database and converted to json to fit into the generated stores and models
(as defined by Extjs). and during runtime all overhead is just not there,
so translating JSid into id will be needed on each record, if it has an id
property or not.
Is there a way to use declarative and map the id property to a different name?
like:
id= Column(integer, name='JSid'……)
I have something similair to this:
I've left out the filtering of SA Colum arguments as I did with the
processing of NON SA column arguments to __init__
class ExtColum(Column):
def __init__(self, type, *arg ,**kwarg):
#filter out the SA Column properties into filteredoptions and:
Column.__init__(self, type_, *arg,**filteredoptions)
def _constructor(self, name, type_ *a, **kw):
column= Column(type,*a, **kw)
column.name = name
return column
in the _constructor I can mess with the name:
def _constructor(self,name, type_ *a,**kw):
column= Column(type,*a, **kw)
if name == JSid:
column.name = id
else:
column.name = name
I think this would fix runtime, as _constructor is called on record load
but how to set the Column name in the __init__
My introspection routines would see the id column
Re: [sqlalchemy] Id and id
I have seen it in the docs and that is where the solution came from.
When not interested in a property I tend to remember it is there but not read
into it, which is normal I guess..
I was looking at some commented out code in that bit and saw I had tried
name='JSid' but since name was used in Extending SA.Column for my use.
Interested in why I tried that I looked into the docs to find out the First
Property option. This saved my ass!! as name='something' would have worked
normally but not in this particular case….
On Mar 5, 2012, at 15:45 , Michael Bayer wrote:
On Mar 5, 2012, at 6:52 AM, Martijn Moeling wrote:
I think I've got it working correctly.
in my mixin I now do:
@declared_attr
def id(self):
return ExtColumn('JSid',Unicode(255), default = None)
so the id property is actually stored in the DB Column 'JSid'
Since my introspection looks at the python class, it takes the name from the
class definition and put's 'id' in the json.
the only change is 'JSid' in the definition and no extra overhead is added.
This is nice since whilst solving this I found out that I have a few more of
these (i.e. Name and name)
Since I use PostgreSQL I got away with this….
yeah I'm surprised you didn't know about the attribute =
Column(someothername, ) calling form ? I point it out in several places
in the docs, it has its own section:
http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html#naming-columns-distinctly-from-attribute-names
and additionally
http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative.html#defining-attributes
.
Glad you got it working but also I will be looking into removing that lower()
logic by default in 0.8 since it is usually wasteful.
Martijn
On Mar 5, 2012, at 11:55 , Martijn Moeling wrote:
Michael,
Thank you for confirming my worries and adding reason to it.
I'm not sure if you remember al my questions in the past but with my
project I'm constantly hitting the impossible, although its fun, it can
be frustrating sometimes.
What I'm doing is something bigger than just an application, I'm building
an web based operating system, with Desktop in a browser and with a
development system for applications.
It is based on Open Source products and build from absolute scratch, there
is much more to it but as I signed a non disclosure agreement with my
self, I must stop here.
Since I Love Python and absolutely hate Javascript, HTML, CSS, I Have put a
lot of effort in doing away with those and replace them with Python,
Basically what I have is an IDE to develop web appellations which in itself
is a web application. Within that you can write Python which is Compiled
into all the parts needed by a browser.
It resembles something which is very close to Visual Basic 6.0 if you
remember that one from a developer point of view.
Since I use the Id field for SA in the usual fashion, I struggle where I
reflect Javascript objects with an id field.
Everything is generated with Introspection, I subclassed the SA Column to
add properties to the colums. These are non functioning in run mode but
are used when compiling to javascript. (the _constuctor returns the real
Column, not the extended one).
the id column is coming from Python classes generated from the sources of
ExtJs, the Id column is tightly bound to communication between browser and
back-end (Since its the recordId).
at the end the Id column will be/is hidden from the developer, but I have
tons and tons of code relying on Id.
Within the IDE a user would and should be able to instance an object with
an id doing so in the creation is no problem since python differs between
id and Id (and Id is never set by code)
so :
id = ExtendedSAColumn(Unicode(25), default = ….)
will end up ad {xtype : 'textfield' , id : 'the id' ……}
For compile time I have no Issues with overhead
I can rename id to JS_id (or something) but I need to be able to do
Someclass.id = 'the id'
and print Someclass.id (or use the value in some other way)
Since an Application and its components are reflected in the database,
almost all classes which are compiled into javascript will have an id
column. adding a @declared_attr to my mixin en a setter function and map id
to JSid adds an id column to everything and that is not what I want (and
messes with my introspection routines to reflect SA Database object classes)
I must find a way where the id field can be used in the IDE without adding
any special code there, it should be transparent to users of my system.
Thing is, on some objects (maybe most, at least the ones which have to do
with stores and models) I Introspect a Class and not an instance of that
Class. While during runtime these python objects are used to query the
database and converted to json to fit into the generated stores and models
(as defined by Extjs
[sqlalchemy] Id and id
Hi
I have some weird behavior due to a class wit both Id and id columns.
Id is the primary key like on every declarative class.
id is just a data column
class Application()
__tablename….
Id = Column(Integer, primary_key=True)
id = Column(Unicode(…
controllers = relation('Controller',primaryjoin
=ApplicationController.Application==Application.Id,single_parent =
True,secondary='ide_applicationcontroller',secondaryjoin=ApplicationController.Controller==Controller.Id,
cascade='all')#, delete-orphan' )
x = A(Id=1,id='Blablabla'
It feels like a missing check for a PK or a .lower() where it should not be, on
the other hand it might very well be expected behavior on my setup (Pg 9.0,
prostgresql+psycopg, SA 7.3)
There are Id and id colums and Camelcase my columnames so that is not the issue
On a session.merge the querybuilder seems to favour id over the PK column Id
and inserts the data from the instances id field.
I can Add fine and understand why.
Some background on the relation:
there are two classes Application and Controller with a many-to many relation
ApplicationController (With two columns, Application and controller, both are
PK and FK (to the corresponding table.Id
tablenames are according to 'ide_'+self.name.lower()
the failing query (see the primaryjoin of the controllers relation of the
Application class above):
2012-03-04 19:10:59,255 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2012-03-04 19:10:59,257 INFO sqlalchemy.engine.base.Engine SELECT
ide_application.Id AS ide_application_Id, ide_application.appFolder AS
ide_application_appFolder, ide_application.autoCreateViewport AS
ide_application_autoCreateViewport, ide_application.defaultUrl AS
ide_application_defaultUrl, ide_application.enableQuickTips AS
ide_application_enableQuickTips, ide_application.name AS
ide_application_name, ide_application.launch AS ide_application_launch,
ide_application.onLaunch AS ide_application_onLaunch, ide_application.id AS
ide_application_id
FROM ide_application
WHERE ide_application.Id = %(param_1)s
2012-03-04 19:10:59,257 INFO sqlalchemy.engine.base.Engine {'param_1': 1}
2012-03-04 19:10:59,261 INFO sqlalchemy.engine.base.Engine SELECT
ide_controller.Id AS ide_controller_Id, ide_controller.Name AS
ide_controller_Name, ide_controller.onLaunch AS ide_controller_onLaunch,
ide_controller.id AS ide_controller_id
FROM ide_controller, ide_applicationcontroller
WHERE ide_applicationcontroller.Application = %(param_1)s AND
ide_applicationcontroller.Controller = ide_controller.Id
2012-03-04 19:10:59,261 INFO sqlalchemy.engine.base.Engine {'param_1':
u'Blablabla'}
gives, param_1: should be the value from Id (Interger, PK) …{'param_1' : 1}…
db.merge(x)
File build/bdist.macosx-10.7-intel/egg/sqlalchemy/orm/session.py, line
1301, in merge
File build/bdist.macosx-10.7-intel/egg/sqlalchemy/orm/session.py, line
1393, in _merge
File build/bdist.macosx-10.7-intel/egg/sqlalchemy/orm/properties.py, line
767, in merge
File build/bdist.macosx-10.7-intel/egg/sqlalchemy/orm/attributes.py, line
453, in get
File build/bdist.macosx-10.7-intel/egg/sqlalchemy/orm/strategies.py, line
563, in _load_for_state
File build/bdist.macosx-10.7-intel/egg/sqlalchemy/orm/query.py, line 1947,
in all
File build/bdist.macosx-10.7-intel/egg/sqlalchemy/orm/query.py, line 2057,
in __iter__
File build/bdist.macosx-10.7-intel/egg/sqlalchemy/orm/query.py, line 2072,
in _execute_and_instances
File build/bdist.macosx-10.7-intel/egg/sqlalchemy/engine/base.py, line
1405, in execute
File build/bdist.macosx-10.7-intel/egg/sqlalchemy/engine/base.py, line
1538, in _execute_clauseelement
File build/bdist.macosx-10.7-intel/egg/sqlalchemy/engine/base.py, line
1646, in _execute_context
File build/bdist.macosx-10.7-intel/egg/sqlalchemy/engine/base.py, line
1639, in _execute_context
File build/bdist.macosx-10.7-intel/egg/sqlalchemy/engine/default.py, line
330, in do_execute
sqlalchemy.exc.DataError: (DataError) invalid input syntax for integer:
Blablabla
LINE 3: WHERE ide_applicationcontroller.Application = E'Blablabla...
^
'SELECT ide_controller.Id AS ide_controller_Id, ide_controller.Name AS
ide_controller_Name, ide_controller.onLaunch AS ide_controller_onLaunch,
ide_controller.id AS ide_controller_id \nFROM ide_controller,
ide_applicationcontroller \nWHERE ide_applicationcontroller.Application =
%(param_1)s AND ide_applicationcontroller.Controller = ide_controller.Id'
{'param_1': u'Blablabla'}
I do not understand where the E is coming from, I've check and checked and
never reference to id anywhere by mistake
I can not freely choose my columnnames and do not really want to run code for
every parameter just to see if there is an id field and put the value in
whatever other storage name, that will add unwanted load to my system. Is
Re: [sqlalchemy] Id and id
Correction: SA version is 0.7.4 not 0.7.3 -- 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] Extending sqlalchemy.schema.Column
Michael (Or anyone else),
sorry I have to get back on this. I renamed all Columns in my application
definitions to MyColumn a while back. and everything worked.
Now that I'm starting to use functionality of MyColumn. (The reason I needed
this) I run into some trouble.
What do I want:
I want to add properties to the Column definition, properties which vane
NOTHING to do with SA, but should not break the inner workings of SA.
The Properties should be initialized and dealt with by MyColumn
Michael pointed at setting _constructor to Column (See mail history below)
class MyColumn(Column):
_constructor = Column
X = 0
Y = 0
class Test(Base, GeneratorClass):
__tablename__ = …..
etc...
Name = MyColumn(Uncicode(100), Index=True,
Last_of_SQLAlchemy_properties, X = 100, Y = 200)
Note the X and Y (Which will be the position on screen, as I will generate
screens directly using the GeneratorClass.__subclasses__ and a lots of
Introspection from within SA (reflection.Inspector.from_engine(engine) )and
python.
Te above, you mentioned is not working for me. The X and Y are passed to the
_contructor.__init__ directly and class property Test.Name looses track of them
I need something like:
class MyColumn(Column)
def __init__(self, Type, **options)
#filter all options to set to this column and have nothing to
do with SA (Like X and Y)
FilteredOptions = {}
For option in options:
if hasattr(self, option.name)
#init self
else:
#add to Filteredoptions
#Now Init the Superclass:Column with the correct properties
Column.__init__(self,Type, *Filteredoptions)
At the other end, I might need to introspect the objects, I have tried but I
have trouble in relating to X or Y since the Name Column is an
InstrumentedAttribute.
One other thing. I can get Columns by iterating over self.__table__.Columns. I
can get Foreign keys using:
inspector.get_foreign_keys(SomeClass.__tablename__)
But Now I need to access the relation objects defined in my classes and
Introspect them.
There just does not seem to be a Class.__table__.relations, neither see I
something in the documentation but I might overlook something, not sure…
Martijn
On Feb 28, 2011, at 18:21 , Michael Bayer wrote:
Column can be subclassed but because they are intensively used in complex
expression transformations, your custom class may be used in more scenarios
than you first anticipate.
There are two scenarios where Column objects are copied, and in one case
copied into an altered class, so the copying of Column uses an attribute
called _constructor to point to which class should be used when creating this
copy. Usually setting that to Column:
class MyColumn(Column):
_constructor = Column
# go nuts
is all you need.
On Feb 28, 2011, at 10:17 AM, Martijn Moeling wrote:
Hi,
I know this is an OLD threat but I was searching the group to see If I was
not the first one doing this.
I am not sure I understand very well what this threat is all about, but I
want to extend the Column class for a different reason.
I want to add extra functionality to the Column class which is absolutely
NOT SA related. SA functionality should not be effected though.
say I want to add a config value and some methods for rendering and
validating screens:
def MyColumn(Column):
def __init():
dosomething to init
def ExtraInfo(self):
do_something_not_sa_related
validation = 'someregex'
and use MyColumn in places where I normally use Column(..)
What do I need to take into account, I've done some tests and Error hell
broke loose, where the errors are hidden deep inside SA so hard to overcome.
Martijn
On Dec 11, 2008, at 16:20 , Michael Bayer wrote:
--
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] Extending sqlalchemy.schema.Column
Michael,
Interesting stuff, The first part I had almost covered, I did not have the
_constructor part.
It wil be part of something more complex…
Thankx,
Martijn
On Jan 30, 2012, at 17:46 , Michael Bayer wrote:
On Jan 30, 2012, at 9:49 AM, Martijn Moeling wrote:
sorry I have to get back on this. I renamed all Columns in my application
definitions to MyColumn a while back. and everything worked.
Now that I'm starting to use functionality of MyColumn. (The reason I needed
this) I run into some trouble.
so we can skip the easy _constructor thing and implement the rest of
Column. If you read the first error message you get with a straight subclass:
TypeError: Could not create a copy of this class '__main__.MySpecialColumn'
object. Ensure the class includes a _constructor() attribute or method which
accepts the standard Column constructor arguments, or references the Column
class itself. Original error: __init__() takes exactly 2 arguments (8 given)
So Column goes through a lot of trouble to try to diagnose what's going on.
It's telling us to create a method called _constructor(), that accepts the
standard arguments that Column does. The return value is our modified column:
class MySpecialColumn(Column):
x = 0
y = 0
def __init__(self, type_, **options):
filtered_options = {}
for name, option in options.items():
if hasattr(self, name):
setattr(self, name, option)
else:
filtered_options[name] = option
Column.__init__(self, type_, **filtered_options)
def _constructor(self, name, type_, **kw):
kw['x'] = self.x
kw['y'] = self.y
col = MySpecialColumn(type_, **kw)
col.name = name
return col
At the other end, I might need to introspect the objects, I have tried but I
have trouble in relating to X or Y since the Name Column is an
InstrumentedAttribute.
Yeah to get at them directly you'd need to say:
assert MyClass.value.property.columns[0].x == 5
if you want MyClass.value.x == 5, you need to tack that on when it gets
instrumented:
@event.listens_for(Base, 'attribute_instrument')
def configure_listener(class_, key, inst):
if isinstance(inst.property, ColumnProperty) and \
isinstance(inst.property.columns[0], MySpecialColumn):
inst.x = inst.property.columns[0].x
inst.y = inst.property.columns[0].y
One other thing. I can get Columns by iterating over self.__table__.Columns.
I can get Foreign keys using:
inspector.get_foreign_keys(SomeClass.__tablename__)
I'd note those are not at all equivalent operations, in that inspector is
going to go out to the database in order to get the FK information.
Assuming your table metadata has it configured, you can get it locally by
iterating through SomeClass.__table__.foreign_keys:
for fk in MyClass.__table__.foreign_keys:
print fk.parent, fk.column
if you want the full constraint object, which I'd recommend if you have
composite FKs in use:
for const in MyClass.__table__.constraints:
if isinstance(const, ForeignKeyConstraint):
for element in const.elements:
print element.parent, element.column
But Now I need to access the relation objects defined in my classes and
Introspect them.
relationship is not the same as a foreign key, see below...
There's a ticket for 0.8 that would attempt to provide more accessors for
these things, including things like mapper.relationships and stuff like that.
Feel free to add in things we should consider on
http://www.sqlalchemy.org/trac/ticket/2208.
an example with everything happening, including in a tricky from_self() query:
from sqlalchemy import create_engine, Column, Integer, ForeignKey,
ForeignKeyConstraint
from sqlalchemy.orm import Session, configure_mappers, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import event
from sqlalchemy.orm.properties import ColumnProperty, RelationshipProperty
class MySpecialColumn(Column):
x = 0
y = 0
def __init__(self, type_, **options):
filtered_options = {}
for name, option in options.items():
if hasattr(self, name):
setattr(self, name, option)
else:
filtered_options[name] = option
Column.__init__(self, type_, **filtered_options)
def _constructor(self, name, type_, **kw):
kw['x'] = self.x
kw['y'] = self.y
col = MySpecialColumn(type_, **kw)
col.name = name
return col
Base= declarative_base()
@event.listens_for(Base, 'attribute_instrument')
def configure_listener(class_, key, inst):
if isinstance(inst.property, ColumnProperty) and \
isinstance(inst.property.columns[0], MySpecialColumn):
inst.x = inst.property.columns[0].x
inst.y = inst.property.columns[0].y
Re: [sqlalchemy] Extending sqlalchemy.schema.Column
The below example works, except if a Foreign key is given. On those columns
-e.g. orderId = MySpecialColumn(Integer, ForeignKey('Order.Id'))-
To fix this I have added stuff, see comments in code below:
As long as the Non SA Column arguments are named it is OK.
On Jan 30, 2012, at 17:46 , Michael Bayer wrote:
On Jan 30, 2012, at 9:49 AM, Martijn Moeling wrote:
sorry I have to get back on this. I renamed all Columns in my application
definitions to MyColumn a while back. and everything worked.
Now that I'm starting to use functionality of MyColumn. (The reason I needed
this) I run into some trouble.
so we can skip the easy _constructor thing and implement the rest of
Column. If you read the first error message you get with a straight subclass:
TypeError: Could not create a copy of this class '__main__.MySpecialColumn'
object. Ensure the class includes a _constructor() attribute or method which
accepts the standard Column constructor arguments, or references the Column
class itself. Original error: __init__() takes exactly 2 arguments (8 given)
So Column goes through a lot of trouble to try to diagnose what's going on.
It's telling us to create a method called _constructor(), that accepts the
standard arguments that Column does. The return value is our modified column:
class MySpecialColumn(Column):
x = 0
y = 0
def __init__(self, type_, **options):
def __init__(self, type_, *arg, **options):
filtered_options = {}
for name, option in options.items():
if hasattr(self, name):
setattr(self, name, option)
else:
filtered_options[name] = option
Column.__init__(self, type_, **filtered_options)
Column.__init__(self, type_, *arg, **filtered_options)
def _constructor(self, name, type_, **kw):
def _constructor(self, name, type_,*arg, **kw):
kw['x'] = self.x
kw['y'] = self.y
col = MySpecialColumn(type_, **kw)
col = MySpecialColumn(type_, *arg, **kw)
col.name = name
return col
At the other end, I might need to introspect the objects, I have tried but I
have trouble in relating to X or Y since the Name Column is an
InstrumentedAttribute.
Yeah to get at them directly you'd need to say:
assert MyClass.value.property.columns[0].x == 5
if you want MyClass.value.x == 5, you need to tack that on when it gets
instrumented:
@event.listens_for(Base, 'attribute_instrument')
def configure_listener(class_, key, inst):
if isinstance(inst.property, ColumnProperty) and \
isinstance(inst.property.columns[0], MySpecialColumn):
inst.x = inst.property.columns[0].x
inst.y = inst.property.columns[0].y
One other thing. I can get Columns by iterating over self.__table__.Columns.
I can get Foreign keys using:
inspector.get_foreign_keys(SomeClass.__tablename__)
I'd note those are not at all equivalent operations, in that inspector is
going to go out to the database in order to get the FK information.
Assuming your table metadata has it configured, you can get it locally by
iterating through SomeClass.__table__.foreign_keys:
for fk in MyClass.__table__.foreign_keys:
print fk.parent, fk.column
if you want the full constraint object, which I'd recommend if you have
composite FKs in use:
for const in MyClass.__table__.constraints:
if isinstance(const, ForeignKeyConstraint):
for element in const.elements:
print element.parent, element.column
But Now I need to access the relation objects defined in my classes and
Introspect them.
relationship is not the same as a foreign key, see below...
There's a ticket for 0.8 that would attempt to provide more accessors for
these things, including things like mapper.relationships and stuff like that.
Feel free to add in things we should consider on
http://www.sqlalchemy.org/trac/ticket/2208.
an example with everything happening, including in a tricky from_self() query:
from sqlalchemy import create_engine, Column, Integer, ForeignKey,
ForeignKeyConstraint
from sqlalchemy.orm import Session, configure_mappers, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import event
from sqlalchemy.orm.properties import ColumnProperty, RelationshipProperty
class MySpecialColumn(Column):
x = 0
y = 0
def __init__(self, type_, **options):
filtered_options = {}
for name, option in options.items():
if hasattr(self, name):
setattr(self, name, option)
else:
filtered_options[name] = option
Column.__init__(self, type_, **filtered_options)
def _constructor(self, name, type_, **kw):
kw['x'] = self.x
kw['y'] = self.y
col = MySpecialColumn(type_, **kw)
col.name = name
return col
Base= declarative_base
Re: [sqlalchemy] PG Sequence on non primary_key column and ForeignKey
I had to put checkfirst=True in the calling parameters of create_all
After I set echo=True on the engine I found out that SA was creating tables in
a different order as I expected.
it started with one of the polymorphic child tables….. that does not work when
the Integrity is checked upon creation.
I thought about specifying tables to see if that forces the creation order.
Anyway it works now
Martijn
On Jan 18, 2012, at 21:14 , Martijn Moeling wrote:
I managed to get PG (9.0) installed and I am in the process over moving over
from MySQL,
I allready have stuff working and am now trying to get the main reason for
the Quick move: Sequence.
I have a object like:
seq=Sequence('serialnumber', metadata=Base.metadata)
Class Object1(Base):
Id = Column(Integer, primary_key=True)
SerialNumber= Column(Integer, seq) # Or whatever I try here
Class Object(Base):
Id = Column(Integer, primary_key=True)
SerialNumber= Column(Integer, ForeignKey('object1.SerialNumber')
with a create_all I get this:
(ProgrammingError) there is no unique constraint matching given keys for
referenced table object1
I might be looking in the wrong direction here, the documentation only talks
about Sequence with primary_key set to True
Martijn
--
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] Moving from Mysql to postgresql and Case Insensitive querys
Hi, I am use to setup the collation in MySQL to ci_utf8 (Case Insensitive) I would like the same behaviour on PostgreSQL. I use Unicode (Or UnicodeText) as columntype If I leave the Collation empty it defaults to C which means it looks at bytecodes so it will NOT be case insensitive. Postgress supports (Since 8.4) the citext columntype. Is there any simple way of getting my queries to work in Case Insensitive mode Kind Regards, Martijn -- 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] joined table inheritance
That is not going to work. let me explain: Polymorphic inheritance uses tables and discriminators and some logic somewhere in the system. (As far as I can tell both SA and databases can do the job, i have to look into that) For each object there will be a table, including the base object. simple: class x(BAse) Id discriminator some_column_x class y(x) id …. some_column_y class z(x) id … some_column_y will result in the following table structure (assume the name of the object is the name of the table and discriminator: x: Id discriminator some_column_x y: Id some_column_y z Id some_column_z now if you create a record in y or z two records are created, one in y or z and one in x. Take note that the Id's will be the same and that the discriminator in x will be y or z. the Id are a sequence of the x table. If you however create an x record, only one record will be created. You are planning on changing from x to y or z later in the process. you can do so but there is little more work to do here. what you have to compensate for is the missing record in y or z so if you have an x object you want to convert into an y object (Or Eployee to Engineer) Load the Employee Object. create a new Engineer object copy the shared values from the Employee, including Id and EXCLUDING discriminator!!! now you have converted the Employee into an Engineer to make it persistant, remeber that we miss the record in Engineer. Saving Engineer by Session.add(EngineerInstance) would do the trick. You should test if it is needed to Session.delete(EmployeeInstance), and Session.commit() first. Session.add(Engineer) might very well overwrite but It might throw up a duplicate key error. This will help you out I think. Martijn On Jan 18, 2012, at 19:08 , Thierry wrote: Hi I'm trying to mimick the example from the documentation (employee/ manager/engineer) I've been able to add employees, and engineers, and all works as expected now my next step would be, creating an 'Employee' object, and some time later, decide that he's in fact an engineer. so my first attempt would read something like person = session.query(Employee).filter(..) - retrieve the instance person.type='engineer' session.commit() and I would have expected at that point, when retrieving again person = session.query(Engineer).filter(..) to now have an instance of Engineer but that's not what I am seeing, I still get an instance of Employee I haven't found this issue addressed in the doc, sorry if I missed it What should be the right way to achieve this ? Am I missing something obvious here ? Many thanks in advance --- I can provide a complete working code of course, I just would like to make a sanity check before I go through the trouble of extracting the relevant part -- 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] PG Sequence on non primary_key column and ForeignKey
I managed to get PG (9.0) installed and I am in the process over moving over
from MySQL,
I allready have stuff working and am now trying to get the main reason for the
Quick move: Sequence.
I have a object like:
seq=Sequence('serialnumber', metadata=Base.metadata)
Class Object1(Base):
Id = Column(Integer, primary_key=True)
SerialNumber= Column(Integer, seq) # Or whatever I try here
Class Object(Base):
Id = Column(Integer, primary_key=True)
SerialNumber= Column(Integer, ForeignKey('object1.SerialNumber')
with a create_all I get this:
(ProgrammingError) there is no unique constraint matching given keys for
referenced table object1
I might be looking in the wrong direction here, the documentation only talks
about Sequence with primary_key set to True
Martijn
--
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: Re: [sqlalchemy] joined table inheritance
You are welcome,
I get a lot of help from here, taking some load of Michael's back is the least
I can do.
I got the idea from your story that you needed to understand how the mechanics
are.
I'm blessed in having very complex polymorphic self relating setups with lots
of mixins and Object functions.
I remember the days I had issues like you and those can be very frustrating
I used capital letters to make clear you had to include the Id and you wrote
code to exclude it ;-)
I would do something different on the continue, I do not like that code wise.
I would do domething like:
for column in [column for column in rec.__table.columns if c.name not in
['discriminator']]:
setattr(…...
with that you can easily add columns to exclude as your objects get more
complex.
I use the above in a __iter__ method like this to create a dict which to
convert to json and return to the browser
class ...
skiplist = [] # or ['discriminator','somecolumn', 'somecolumn']
def __iter__(self):
for c in [c for c in self.__table__.columns] + [c for c in
BaseObject.__table__.columns if c.key not in self.__table__.columns] and c.key
not in self.skiplist]:
if c.name not in ['ACLLinkId']:
if isinstance(c.type, DateTime) and getattr(self, c.name) !=
None:
value = getattr(self, c.name).strftime('%Y-%m-%d %H:%M:%S')
elif isinstance(c.type, Date) and getattr(self, c.name) != None:
value = getattr(self, c.name).strftime('%Y-%m-%d')
elif isinstance(c.type, Time)and getattr(self, c.name) != None:
value = getattr(self, c.name).strftime('%H:%M:%S')
else:
value = getattr(self, c.name)
yield(c.name, value)
In fact I have Overridden the SA Column and added my own parameters to generate
javascript.
Great it helped you
Martijn
On Jan 18, 2012, at 22:06 , Thierry wrote:
Hi !
Thanks for the prompt, and very helpful answer
I do now understand the logic a bit better
I've been able to get somewhere, so here's a status in case this is
helpful
in my own example, Record is the ancestor/common class (x in your
msg), and User is the specialization (y)
--- attempt #1
the code below kind of works - [[I've naively tried to do the copy in
some generic way, feel free to comment that part]]
# locate the object to be promoted to specialized class
rec=session.query(Record).filter_by(hrn=hrn2).first()
user=User(None,None)
# copying the ancestor fields except primary_key and disciminator
mapper=object_mapper(rec)
for column in mapper.columns:
cname=column.name
if cname == mapper.polymorphic_on.name: continue
if column.primary_key: continue
setattr(user,cname,getattr(rec,cname))
# add new instance, trash previous one
# set a user-specific column
user.email=u...@gmail.com
session.add(user)
session.delete(rec)
session.commit()
however the side effect is that the new object - of course - has a
different primary key, which in my case can work at this early stage,
but I'm concerned about any possible relationship that would have a
hard time with that
--- attempt #2
and if now I comment out this line here, I'm getting a runtime error
#if column.primary_key: continue
in order to try and reuse the same primary_key
Traceback (most recent call last):
...
File tuto.py, line 79, in add2 this is the commit line
mentioned above
session.commit()
...
File /usr/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py,
line 1867, in _save_obj
(table.description, len(update), rows))
sqlalchemy.orm.exc.StaleDataError: UPDATE statement on table
'tuto_users' expected to update 1 row(s); 0 were matched.
--- attempt #3
eventually I got this to do what I wanted by doing; just had to trash
commit the deprecated instance, before I could add and commit the
new one
# locate the object to be promoted to specialized class
rec=session.query(Record).filter_by(hrn=hrn2).first()
user=User(None,None)
# copying the ancestor fields except primary_key and disciminator
mapper=object_mapper(rec)
for column in mapper.columns:
cname=column.name
if cname == mapper.polymorphic_on.name: continue
#if column.primary_key: continue
setattr(user,cname,getattr(rec,cname))
# trash previous one
session.delete(rec)
session.commit()
# set a user-specific column
user.email=u...@gmail.com
# add new one
session.add(user)
session.commit()
--- and for the record
# rpm -q python python-sqlalchemy postgresql
python-2.7-8.fc14.1.i686
python-sqlalchemy-0.6.8-1.fc14.i686
postgresql-8.4.9-1.fc14.i686
-- Thanks again -- Thierry
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to
Re: [sqlalchemy] PG Sequence on non primary_key column and ForeignKey
That is what I thought,
I removed it after it did give the same error, to be sure, I just tested that
again.
I also must say it might be something else I might need an relation since many
Objects can reference a single SerialNumber
Not sure what to do
relation ('….…?? I have to think about that, it has to work the other way
around too
Thanks
Martijn
On Jan 18, 2012, at 22:39 , Conor wrote:
On 01/18/2012 02:14 PM, Martijn Moeling wrote:
I managed to get PG (9.0) installed and I am in the process over moving over
from MySQL,
I allready have stuff working and am now trying to get the main reason for
the Quick move: Sequence.
I have a object like:
seq=Sequence('serialnumber', metadata=Base.metadata)
Class Object1(Base):
Id = Column(Integer, primary_key=True)
SerialNumber = Column(Integer, seq) # Or whatever I try here
Class Object(Base):
Id = Column(Integer, primary_key=True)
SerialNumber = Column(Integer, ForeignKey('object1.SerialNumber')
with a create_all I get this:
(ProgrammingError) there is no unique constraint matching given keys for
referenced table object1
I might be looking in the wrong direction here, the documentation only talks
about Sequence with primary_key set to True
Martijn
PostgreSQL requires the target of a foreign key (Object1.SerialNumber in your
case) to have a unique constraint on it. Adding unique=True to the column
definition would do it.
-Conor
--
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] Sequence on non primary_key and import data
This is one of the reasons I'm dropping MySQL, at the end it just generates trouble.. I was hoping to get an easy two platform fix for this, but there just is not. So moving to PG needs to done quickly here. Thing is I want PG to be installed on my Workstation, exactly like on Lion Server…. more work Thank you Michael! Martijn On Jan 17, 2012, at 02:29 , Michael Bayer wrote: On Jan 16, 2012, at 5:14 PM, Martijn Moeling wrote: I am planning on using PG for production but for now I'm stuck on Mysql. Mysql does not seem to support Sequence.. can't I just do x = Column(Integer, autoincreament=True) Would that work on both? I cannot test PG just jet. MySQL's autoincrement feature only works on a single integer primary key column and only when INSERT happens. mysql doesn't have a lot of option here, you can use a sequence table, a table with just one row/column that you increment, or the dreaded select max(n) from table - neither of these work very well in a high concurrency situation. If you wanted to go crazy, MySQL supports stored procedures, maybe you could make a stored procedure that uses the sequence table and acts like a sequence.Maybe there's one on the web somewhere. On Jan 16, 2012, at 5:07 PM, Michael B ayer wrote: On Jan 16, 2012, at 6:29 AM, Martijn Moeling wrote: Now I need to import data from the current production system. This data already has Serialnumbers generated. What should I do to make this work? Do I need the sequence created after the Import and set the Start value to the last imported SerialNumber+1 ? I would prefer creating the sequence before the import and Update the Sequence after the import. you can create the sequence with a start value if you pass start=X to Sequence(). Or you can just bump it up with nextval(). Or PG allows you to call setval() on it.You can pretty much set it to anything at at any time. http://www.postgresql.org/docs/9.1/static/functions-sequence.html If you want to have things created before you deal with import data, then just bump up the sequence as you go through your data. Assigning to SerialNr on the Order will have the effect of not using the Sequence on insert. Also the create_all() step will create the Sequence construct in the DB also. -- 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. -- 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] Sequence on non primary_key and import data
Bump.
On Jan 10, 2012, at 12:06 , Martijn Moeling wrote:
Hi,
I am running into something
I am using MySQL but am moving to Postgress so I'm looking for something
compatible with the two.
I have to generate an unique number for each record created into a column
separate from the Id, call it SerialNumber
I have found the Sequence object.
Say I make the following class:
class Order(Base):
__tablename__ = 'product'
Id = Column(Integer, primary_Key=True)
SerialNr= Column(Integer,
Sequence('SerialNumber'))
after the create_all()
CreateSequence('SerialNumber')
All fine.
Now I need to import data from the current production system. This data
already has Serialnumbers generated.
What should I do to make this work? Do I need the sequence created after the
Import and set the Start value to the last imported SerialNumber+1 ?
I would prefer creating the sequence before the import and Update the
Sequence after the import.
I can also Drop and Recreate after the import. The Import will be done
several times during the test period. After the code is in production, I'll
never ever need to modify the seqence anymore.
Adding another table with just one column and setting SerialNr as a
ForeignKey might be a solution too (As SerialNr will also be a coded as a 4
character string which needs to be in the database for compatability reasons)
I prefer the sequence though.
My real code is much more complex, the above example class is simplified. In
fact Order is a polymorphic base table and the sequence Column is in the
polymorphic child tables only where Orders are in fact Items to be
produced. The simplified version above is just to make things clear.
Martijn
--
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] Sequence on non primary_key and import data
I am planning on using PG for production but for now I'm stuck on Mysql. Mysql does not seem to support Sequence.. can't I just do x = Column(Integer, autoincreament=True) Would that work on both? I cannot test PG just jet. On Jan 16, 2012, at 5:07 PM, Michael B ayer wrote: On Jan 16, 2012, at 6:29 AM, Martijn Moeling wrote: Now I need to import data from the current production system. This data already has Serialnumbers generated. What should I do to make this work? Do I need the sequence created after the Import and set the Start value to the last imported SerialNumber+1 ? I would prefer creating the sequence before the import and Update the Sequence after the import. you can create the sequence with a start value if you pass start=X to Sequence(). Or you can just bump it up with nextval(). Or PG allows you to call setval() on it.You can pretty much set it to anything at at any time. http://www.postgresql.org/docs/9.1/static/functions-sequence.html If you want to have things created before you deal with import data, then just bump up the sequence as you go through your data. Assigning to SerialNr on the Order will have the effect of not using the Sequence on insert. Also the create_all() step will create the Sequence construct in the DB also. -- 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] Sequence on non primary_key and import data
Hi,
I am running into something
I am using MySQL but am moving to Postgress so I'm looking for something
compatible with the two.
I have to generate an unique number for each record created into a column
separate from the Id, call it SerialNumber
I have found the Sequence object.
Say I make the following class:
class Order(Base):
__tablename__ = 'product'
Id = Column(Integer, primary_Key=True)
SerialNr= Column(Integer,
Sequence('SerialNumber'))
after the create_all()
CreateSequence('SerialNumber')
All fine.
Now I need to import data from the current production system. This data already
has Serialnumbers generated.
What should I do to make this work? Do I need the sequence created after the
Import and set the Start value to the last imported SerialNumber+1 ?
I would prefer creating the sequence before the import and Update the
Sequence after the import.
I can also Drop and Recreate after the import. The Import will be done
several times during the test period. After the code is in production, I'll
never ever need to modify the seqence anymore.
Adding another table with just one column and setting SerialNr as a ForeignKey
might be a solution too (As SerialNr will also be a coded as a 4 character
string which needs to be in the database for compatability reasons)
I prefer the sequence though.
My real code is much more complex, the above example class is simplified. In
fact Order is a polymorphic base table and the sequence Column is in the
polymorphic child tables only where Orders are in fact Items to be produced.
The simplified version above is just to make things clear.
Martijn
--
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] money type for Postgresql
I use Float for money at the moment. I am moving from Mysql to Postgres and have not had any issues but i'm not sure if Float actually works correctly. Floats are being used for both broken number values and for money values. should I change to numeric for Postgres as I do not see that Column Type mentioned in this tread. Martijn On Dec 27, 2011, at 20:39 , dgardner wrote: Quick hack, figured I would share since there seemed to be other people asking about it. I couldn't get it to work with autoload=True for table reflection. --- from sqlalchemy import types from decimal import Decimal class Money(types.UserDefinedType): def get_col_spec(self): return 'money' def result_processor(self, dialect, coltype): def process(value): # Strip off the currency symbol return Decimal(value[1:]) return process -- 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] Querying number column as if it is a unicode column
Kinda lame I could not find that I should have known Thanks! On Dec 9, 2011, at 16:01 , Michael Bayer wrote: On Dec 9, 2011, at 5:28 AM, Martijn Moeling wrote: Hi, I'm puzzled on how to get this working: class User(base): Id = Column (Integer, primarykey=true) Name= Column(Unicode(100)) Session.query(User).filter(User.Name.like(query+%)).all() is all fine. now I want to add an extension to the query, which does the same within Id and returns all results where the Id field is searched as a string too like Session.query(User).filter(or_(User.Name.like(%+query+%), str(User.Id).like(query+%)).all() example data: 1, martijn 1 2, martijn 2 …. 100,martijn 100 searching for 1 will find all records with Id's - 1,11,12…., 19, 100 and records with a 1 somewhere in the name. there must be a way, right? you'd use cast() for this, representing the SQL CAST expression: http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=cast#sqlalchemy.sql.expression.cast Martijn -- 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. -- 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] Querying number column as if it is a unicode column
Hi, I'm puzzled on how to get this working: class User(base): Id = Column (Integer, primarykey=true) Name= Column(Unicode(100)) Session.query(User).filter(User.Name.like(query+%)).all() is all fine. now I want to add an extension to the query, which does the same within Id and returns all results where the Id field is searched as a string too like Session.query(User).filter(or_(User.Name.like(%+query+%), str(User.Id).like(query+%)).all() example data: 1, martijn 1 2, martijn 2 …. 100,martijn 100 searching for 1 will find all records with Id's - 1,11,12…., 19, 100 and records with a 1 somewhere in the name. there must be a way, right? Martijn -- 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] weird error in import of sqla
Hi, Something rendered my sqlalchemy on OSX lion unusable Traceback (most recent call last): File /Users/martijn/Documents/workspace/UCO/src/ConvertData.py, line 9, in module from sqlalchemy import * File /Library/Python/2.7/site-packages/SQLAlchemy-0.7.2-py2.7-macosx-10.7-intel.egg/sqlalchemy/__init__.py, line 52, in module from sqlalchemy.types import ( File /Library/Python/2.7/site-packages/SQLAlchemy-0.7.2-py2.7-macosx-10.7-intel.egg/sqlalchemy/types.py, line 33, in module from sqlalchemy import processors, events File /Library/Python/2.7/site-packages/SQLAlchemy-0.7.2-py2.7-macosx-10.7-intel.egg/sqlalchemy/processors.py, line 37, in module from sqlalchemy.cprocessors import UnicodeResultProcessor, \ AttributeError: PyCapsule_Import datetime.datetime_CAPI is not valid Searching google for the AttributeError gives some sort of Python bug but very little info. Anyone else have this? I just do not know where to start Martijn -- 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] weird error in import of sqla
Never mind… I still had library definitions in eclipse set to python 2.6…. Thanks for reading Martijn On Oct 6, 2011, at 13:02 , Martijn Moeling wrote: Hi, Something rendered my sqlalchemy on OSX lion unusable Traceback (most recent call last): File /Users/martijn/Documents/workspace/UCO/src/ConvertData.py, line 9, in module from sqlalchemy import * File /Library/Python/2.7/site-packages/SQLAlchemy-0.7.2-py2.7-macosx-10.7-intel.egg/sqlalchemy/__init__.py, line 52, in module from sqlalchemy.types import ( File /Library/Python/2.7/site-packages/SQLAlchemy-0.7.2-py2.7-macosx-10.7-intel.egg/sqlalchemy/types.py, line 33, in module from sqlalchemy import processors, events File /Library/Python/2.7/site-packages/SQLAlchemy-0.7.2-py2.7-macosx-10.7-intel.egg/sqlalchemy/processors.py, line 37, in module from sqlalchemy.cprocessors import UnicodeResultProcessor, \ AttributeError: PyCapsule_Import datetime.datetime_CAPI is not valid Searching google for the AttributeError gives some sort of Python bug but very little info. Anyone else have this? I just do not know where to start Martijn -- 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] Best practice for changing record structure
Hi! As a big fan of SQLA I am looking for a way to implement something which would in fact be something like phpmysqladmin but based on SQLA and not as big. I know about migrate but that is not the way to go for me. Basically I would like to generate: databases tables Python objects (reflecting those tables) In my own (web) framework I have the ability to load python objects dynamically from a database so that is not the problem. I could use the generated SQLAlchemy objects as a base class for the programmer to extend (which can be done from the web-interface). Something like class Customer(Customer_SQLABase)…. The problem I have is in updating the record structure in the database. To keep things in sync with SQLA development I'm puzzled which approach to take. Should I customize DDL? as described in /docs/core/schema.html? Any thoughts, hint or tips would be very nice… Martijn -- 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] Best practice for changing record structure
Michael, Looks promising but I need some time to get clues about the workings. I lost my password for bitbucket, I have a project there too, mp2mwsgi to run mod_python code on top of mod_wsgi (or any other wsgi but not tested) I might very well extend alembic and put an frond-end on it using extjs which I use for my project. I'll be in touch! Martijn On Sep 6, 2011, at 16:09 , Michael Bayer wrote: On Sep 6, 2011, at 9:38 AM, Martijn Moeling wrote: Hi! As a big fan of SQLA I am looking for a way to implement something which would in fact be something like phpmysqladmin but based on SQLA and not as big. I know about migrate but that is not the way to go for me. Basically I would like to generate: databases tables Python objects (reflecting those tables) In my own (web) framework I have the ability to load python objects dynamically from a database so that is not the problem. I could use the generated SQLAlchemy objects as a base class for the programmer to extend (which can be done from the web-interface). Something like class Customer(Customer_SQLABase)…. The problem I have is in updating the record structure in the database. To keep things in sync with SQLA development I'm puzzled which approach to take. Should I customize DDL? as described in /docs/core/schema.html? Any thoughts, hint or tips would be very nice… I have a library that serves as the base for a bunch of new ALTER constructs and such called Alembic: https://bitbucket.org/zzzeek/alembic/overview . It uses the @compiled system to achieve this. I wrote most of it over a year ago and its basically a project needing some more work to write basic documentation and flesh it out some more (though I have used it on the job to a minimal extent). It's a migration tool, but also can be used just for the DDL constructs which you can see in alembic.ddl (i.e. same philosophy as SQLAlchemy - provide more rudimental tools as well as functionality on top). You can check it out to see some of the ALTERs, and also any code/documentation/beta tester contributions are entirely welcome as well (it just would require some source code reading since there's no docs yet). -- 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] SQLAlchemy 0.7.2 Released
Thank you for all the effort you put in. SQLAlchemy has been a proven tool for me and as it seems for many others. On Aug 1, 2011, at 02:17 , Michael Bayer wrote: SQLAlchemy version 0.7.2 is now available. A lot has been going on leading up to this release, and there was actually a bunch more I've wanted to do; but as we went about six weeks since the last release we've accumulated at least twenty five bug fixes, and it's time for them to go out.Work continues towards the next release. This release features a relatively big change to the mechanics of joined and subquery eager loading, which is that when invoked from a Query (as opposed to from a lazy load), the eager loader will traverse the graph of objects fully regardless of collections and attributes that are already loaded, populating any expired or not-yet-loaded attributes all the way down the hierarchy. Previously it tried to save time by not descending into already loaded subtrees. This is to better support the use case of using eager loading in order to fully populate a tree, such that it can be detached and sent to a cache in a fully loaded state. It is also behaviorally closer to the spirit of I asked for X, I should get X, i.e. if you say subqueryload(), you'll get your subquery no matter what. Other than that there were a *lot* of ORM fixes, most of which have been also applied to the 0.6 branch and will be in 0.6.9. Also some additional 0.6-0.7 regressions fixed, and some fixes to the new Mutable extension including one which was kind of a show stopper. Download SQLAlchemy 0.7.2 at: http://www.sqlalchemy.org/download.html Changelog follows. 0.7.2 = - orm - Feature enhancement: joined and subquery loading will now traverse already-present related objects and collections in search of unpopulated attributes throughout the scope of the eager load being defined, so that the eager loading that is specified via mappings or query options unconditionally takes place for the full depth, populating whatever is not already populated. Previously, this traversal would stop if a related object or collection were already present leading to inconsistent behavior (though would save on loads/cycles for an already-loaded graph). For a subqueryload, this means that the additional SELECT statements emitted by subqueryload will invoke unconditionally, no matter how much of the existing graph is already present (hence the controversy). The previous behavior of stopping is still in effect when a query is the result of an attribute-initiated lazyload, as otherwise an N+1 style of collection iteration can become needlessly expensive when the same related object is encountered repeatedly. There's also an as-yet-not-public generative Query method _with_invoke_all_eagers() which selects old/new behavior [ticket:2213] - A rework of replacement traversal within the ORM as it alters selectables to be against aliases of things (i.e. clause adaption) includes a fix for multiply-nested any()/has() constructs against a joined table structure. [ticket:2195] - Fixed bug where query.join() + aliased=True from a joined-inh structure to itself on relationship() with join condition on the child table would convert the lead entity into the joined one inappropriately. [ticket:2234] Also in 0.6.9. - Fixed regression from 0.6 where Session.add() against an object which contained None in a collection would raise an internal exception. Reverted this to 0.6's behavior which is to accept the None but obviously nothing is persisted. Ideally, collections with None present or on append() should at least emit a warning, which is being considered for 0.8. [ticket:2205] - Load of a deferred() attribute on an object where row can't be located raises ObjectDeletedError instead of failing later on; improved the message in ObjectDeletedError to include other conditions besides a simple delete. [ticket:2191] - Fixed regression from 0.6 where a get history operation on some relationship() based attributes would fail when a lazyload would emit; this could trigger within a flush() under certain conditions. [ticket:2224] Thanks to the user who submitted the great test for this. - Fixed bug apparent only in Python 3 whereby sorting of persistent + pending objects during flush would produce an illegal comparison, if the persistent object primary key is not a single integer. [ticket:2228] Also in 0.6.9 - Fixed bug whereby the source clause used by query.join() would be inconsistent if against a column expression that combined multiple entities together. [ticket:2197] Also in 0.6.9 - Fixed bug whereby if a mapped class redefined __hash__() or
Re: [sqlalchemy] Re: Extending sqlalchemy.schema.Column and metaprogramming traps
Hi, I know this is an OLD threat but I was searching the group to see If I was not the first one doing this. I am not sure I understand very well what this threat is all about, but I want to extend the Column class for a different reason. I want to add extra functionality to the Column class which is absolutely NOT SA related. SA functionality should not be effected though. say I want to add a config value and some methods for rendering and validating screens: def MyColumn(Column): def __init(): dosomething to init def ExtraInfo(self): do_something_not_sa_related validation = 'someregex' and use MyColumn in places where I normally use Column(..) What do I need to take into account, I've done some tests and Error hell broke loose, where the errors are hidden deep inside SA so hard to overcome. Martijn On Dec 11, 2008, at 16:20 , Michael Bayer wrote: On Dec 11, 2008, at 3:37 AM, Angri wrote: Here it is: http://www.sqlalchemy.org/trac/ticket/1244 Maybe it is good idea to drop some new lines in faq? Something like this: Q: How should I extend sqlalchemy.schema.Column? A: You surely dont need it. Recommended way to achive your possible needs is to write instance-factory function which decorates creation of sqlalchemy.schema.Column instances. Q: But I'm really need it! A: Ok. To subclass Column, this is the current recipe: from sqlalchemy.sql.util import Annotated, annotated_classes class MyColumn(Column): ... class AnnotatedMyColumn(Annotated, MyColumn): pass annotated_classes[MyColumn] = AnnotatedMyColumn Do not forget to put AnnotatedMyColumn in the module namespace, or your schema will not be pickleable! Correct me please if I am wrong somewhere and excuse me for my English. Well the AnnotatedMyColumn part is less than ideal since its an internal. the way that works could very likely change. Creating an AnnotatedXXX class *can* be automated. the pickle thing just might be a caveat we'd document or arrange for an exception to occur (like putting a throw in a __getstate__ method). --~--~-~--~~~---~--~~ 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: Extending sqlalchemy.schema.Column and metaprogramming traps
On Feb 28, 2011, at 18:21 , Michael Bayer wrote: Column can be subclassed but because they are intensively used in complex expression transformations, your custom class may be used in more scenarios than you first anticipate. There are two scenarios where Column objects are copied, and in one case copied into an altered class, so the copying of Column uses an attribute called _constructor to point to which class should be used when creating this copy. Usually setting that to Column: class MyColumn(Column): _constructor = Column # go nuts LOL!! Thanks is all you need. On Feb 28, 2011, at 10:17 AM, Martijn Moeling wrote: Hi, I know this is an OLD threat but I was searching the group to see If I was not the first one doing this. I am not sure I understand very well what this threat is all about, but I want to extend the Column class for a different reason. I want to add extra functionality to the Column class which is absolutely NOT SA related. SA functionality should not be effected though. say I want to add a config value and some methods for rendering and validating screens: def MyColumn(Column): def __init(): dosomething to init def ExtraInfo(self): do_something_not_sa_related validation = 'someregex' and use MyColumn in places where I normally use Column(..) What do I need to take into account, I've done some tests and Error hell broke loose, where the errors are hidden deep inside SA so hard to overcome. Martijn On Dec 11, 2008, at 16:20 , Michael Bayer wrote: On Dec 11, 2008, at 3:37 AM, Angri wrote: Here it is: http://www.sqlalchemy.org/trac/ticket/1244 Maybe it is good idea to drop some new lines in faq? Something like this: Q: How should I extend sqlalchemy.schema.Column? A: You surely dont need it. Recommended way to achive your possible needs is to write instance-factory function which decorates creation of sqlalchemy.schema.Column instances. Q: But I'm really need it! A: Ok. To subclass Column, this is the current recipe: from sqlalchemy.sql.util import Annotated, annotated_classes class MyColumn(Column): ... class AnnotatedMyColumn(Annotated, MyColumn): pass annotated_classes[MyColumn] = AnnotatedMyColumn Do not forget to put AnnotatedMyColumn in the module namespace, or your schema will not be pickleable! Correct me please if I am wrong somewhere and excuse me for my English. Well the AnnotatedMyColumn part is less than ideal since its an internal. the way that works could very likely change. Creating an AnnotatedXXX class *can* be automated. the pickle thing just might be a caveat we'd document or arrange for an exception to occur (like putting a throw in a __getstate__ method). --~--~-~--~~~---~--~~ 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. -- 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] UTC DateTimes
Hi, I would like to store all time values in my database as UTC values (automatically). I already have the proper conversion routines in my own CALDAV server but I have many more places where I would like to store dates. what would be a logical place to hook in those conversion routines so that DateTime and Time values are stored as UTC in the database automatically. The front end code will display the local time as it will consider all values from the database as UTC. Martijn -- 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] UTC DateTimes
Cool that is what I thought,
On Feb 19, 2011, at 17:32 , Michael Bayer wrote:
On Feb 19, 2011, at 6:40 AM, Martijn Moeling wrote:
Hi,
I would like to store all time values in my database as UTC values
(automatically). I already have the proper conversion routines in my own
CALDAV server but I have many more places where I would like to store dates.
what would be a logical place to hook in those conversion routines so that
DateTime and Time values are stored as UTC in the database automatically.
The front end code will display the local time as it will consider all
values from the database as UTC.
The application itself should coerce all dates into UTC as soon as possible.
Its best to assume that local time is a moving target only decided at the
very endpoints of the UX, i.e. rendering, data handling.In my experience
this means the date conversions tend to occur in Python.Any date logic or
arithmetic that occurs in Python can proceed normally without any timezone
mismatch.An alternative approach is to use timezone-aware datetimes, but
the effect is similar, as you still need to coerce incoming data into
timezone aware constructs.
So I've got a web application, in my templates I display dates like:
${someobject.created_at | n, formatters.format_datetime_local}
where the function is:
def format_datetime_local(k, format=%Y-%m-%d %H:%M:%S %Z, tz=None):
if k is None:
return
if tz is None:
tz = c.tz
k = pytz.utc.localize(k)
k = k.astimezone(tz)
return k.strftime(format)
When I receive user input, I'm using a formencode validator that handles it.
If the scenario is that there's literally no space between your front end
and your database routines (which is usually unlikely), TypeDecorator is the
standard construct for data coercion at the point of database interaction -
still an in-python routine.
Martijn
--
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.
--
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] Polymorhic tables and __table__.columns
Hi,
I am running into something weird results I do not really understand. SA 0.6.6
I have a Polymorhic table setup, where I need to get the columnlist for
outputting the ExtJs 4 Model.
Basically it should output
( 'User' : { fields : [
{'field1' : 'int'},
{'field2' : 'string'},
{'field3' : 'boolean'}
]
}
)
to output the data to the browser in json I do
dict(Userinstance)
to get this working I have added to the BaseClass of the polymorphic setup
def __iter__(self):
for c in self.__table__.columns + BaseClass.__table__.columns:
if c.name not in ['discriminator']:
if isinstance(c.type, DateTime):
value = getattr(self, c.name).strftime(%Y-%m-%d %H:%M:%S)
else:
value = getattr(self, c.name)
yield(c.name, value)
This works fine but a few things has to be taken in consideration:
Both self.__table__.columns and BaseClass.__table__.columns__ have columns
defined in the BaseClass
In the above, this is no real problem since the dict keys are overwritten and
they have the same value anyway.
if I do the same for generating a model:
def ExtModel(self):
Result = {'fields': []}
for c in self.__table__.columns + BaseClass.__table__.columns:
if c.name not in ['discriminator']:
if isinstance(c.type, Unicode):
Result['fields'].append({'name': c.name, 'type': 'string'})
elif isinstance(c.type, Integer):
Result['fields'].append({'name': c.name, 'type': 'int'})
elif isinstance(c.type, Boolean):
Result['fields'].append({'name': c.name, 'type': 'boolean'})
elif isinstance(c.type, Float):
Result['fields'].append({'name': c.name, 'type': 'float'})
elif isinstance(c.type, DateTime):
Result['fields'].append({'name': c.name, 'type': 'date'})
return self.__class__.__name__, Result
I get the Id field twice. in the fieldlist
again they have the same value, but here it alters the result.
I need something like:
def ExtModel(self):
ColumnList = self.__table__.columns
for c in BaseClass.__table__.columns:
if c not in self.__table__.columns:
ColumnList.append(c)
Result = {'fields': []}
for c in self.__table__.columns + Affiliation.__table__.columns:
.. etc
which gives:
if c not in self.__table__.columns:
File
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/sql/expression.py,
line 2064, in __contains__
raise exc.ArgumentError(__contains__ requires a string argument)
sqlalchemy.exc.ArgumentError: __contains__ requires a string argument
One other thing i noted which is really odd
I have multiple classes inheriting from BaseClass, which is why I have set up
polymorphic inheritance in the foist place.
When I do:
alldata = s.query(BaseClass).all()
for a in alldata:
print a.ExtModel()
I notice that BaseClass.__table__.columns sometimes holds the Id in others it
doesnt.
most of the classes (new project) are empty anyway and are no more than:
SomeClass(BaseClass):
Id = Column(Integer, ForeignKey('baseclass.Id'), primary_key=True)
SomeClass2(BaseClass):
Id = Column(Integer, ForeignKey('baseclass.Id'), primary_key=True)
That is really weird! It solves my problem automatically on some instances
but not on others and only for the Id column ...
Is there a way I can make a new ColumnList based on the self.__table__.columns
and BaseClass.__tablename__ excluding the overlap. In this example I 'only'
talk about the Id but in my real world there are more Columns in BaseClass
Martijn
--
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: Dynamic relations...
Michael, I have it all working now!!! What I had to do was to filter out records in the Mapperextension with the data in the Extra record. Next to that I got a lot of errors since the relations where pointing to non-existant records not added in the append_result. So I set up a MapperExtension on the relation records to filter out the relation records and so the run-time references. This is perfect for reading. I still have to test adding relations but in that case I Know something is going to be written and I can skip the file mapper tests. (If I only could somehow tell query to pass parameters to the MapperExtension.) I know 0.7 adds MapperExtension functionality in the form of Events and once 0.7 becomes Alpha/Released I might just change that bit of code. What by the way will happen if: a is instance of Class A b is instance of Class B(A) c is instance of Class C(A) d is an instance of class D and has a relation which (Remember polymorphic self referencing) basically it has: d.REL = [a,b,c] if no filtering is done. if filtering is done: d.REL = [a] next i do REL.append(e) commit() what will be the result of the next unfiltered Query? d.REL = [a,b,c,e] or d.REL = [a,e] (and relation proxy records b and c are removed from the database) If you have interesting comments on this please tell me, if not do not bother since I will test that (It is just not so easy to to test this quickly in a test program with all the code in the MappersExtension) Although my project has to do with chemistry and this whole effort has been made to be able to define possible molecules based on which atoms and molecules etc. can be combined. I used ACL filesystem like (Access Control Lists) to describe what I wanted. I have a scheme now which is perfectly able to define Who can access What no matter if classed (tables) are self referencing or not. By setting a parameter the results of the same query are filtered. All this has given me some ideas I have to play with when this project is done and I happen to have some spare time. Anyway again a BIG THANK YOU for all your help Martijn On Feb 14, 2011, at 14:04 , Martijn Moeling wrote: Eric (and Michael), Thank you for your comments, I agree with you totally. I am not much of a database guy and never have been. During my education I did not pay much attention to those lessons either, I was just interested in positive grades. I dropped the database stuff as quickly as possible and concentrated on (OS) kernel and protocol related technologies. The case I am working on now requires a very (To me) complex datamodel with complex relations. Right now I have multiple polymorphic classes with both self reference and references to each other. Those references are polymorphic too!! Now I had to make a class which had to reference all of the above with the right cascading integrity. To be honest this turned out way over my head not in the first place because the SQLAlchemy learning curve gets steeper and steeper when technologies have to be mixed to get stuff working. Me being not really interested in Databases during the first 33 years of my experience in software development is certainly a drawback in terms of understanding the SQLAlchemy documentation. If I do not know what a AssociationProxy is, I do not search for it but what If I need it to get my problem out of the way? Michael has been a great help and I have been looking over this mailing list to help him out answering the simple questions. It turned out not that easy and I need a lot more knowledge about the SQLALchemy internals. I have been an Open Source guy for a very long time and did work on many things, I still remember the good old Linux-Kernel days and (although I am the only user) I have made an adapter for running MOD_PYTHON code on top of MOD_WSGI (as an alternative MP package). It is running my production code made for MP on top of MOD_WSGI very well I personally like the way of the Request object. If I can contribute to SA I will be happy to do so, I have the feeling I have lightened up something which could be improved. That is a first step... (not a big one :-) I have the feeling that SA is mainly a German project. Although I get confused by the working hours of Michael, He never responded to my question if he ever sleeps... The German names of people seem to make me think that, I am not sure though. German product turn out be be great and I love Germany for many reasons (I even Worked in Germany). If I cannot afford a German made car, I'll walk!! as I said I am thinking about contributing in some manner to pay back for being able to use SA and for the great help the users get from Michael and others. On Feb 14, 2011, at 05:11 , Eric Ongerth wrote: Polymorphic associations pop up a lot around here, don't they! I suppose it's partly because they would
Re: [sqlalchemy] Re: Dynamic relations...
Eric (and Michael), Thank you for your comments, I agree with you totally. I am not much of a database guy and never have been. During my education I did not pay much attention to those lessons either, I was just interested in positive grades. I dropped the database stuff as quickly as possible and concentrated on (OS) kernel and protocol related technologies. The case I am working on now requires a very (To me) complex datamodel with complex relations. Right now I have multiple polymorphic classes with both self reference and references to each other. Those references are polymorphic too!! Now I had to make a class which had to reference all of the above with the right cascading integrity. To be honest this turned out way over my head not in the first place because the SQLAlchemy learning curve gets steeper and steeper when technologies have to be mixed to get stuff working. Me being not really interested in Databases during the first 33 years of my experience in software development is certainly a drawback in terms of understanding the SQLAlchemy documentation. If I do not know what a AssociationProxy is, I do not search for it but what If I need it to get my problem out of the way? Michael has been a great help and I have been looking over this mailing list to help him out answering the simple questions. It turned out not that easy and I need a lot more knowledge about the SQLALchemy internals. I have been an Open Source guy for a very long time and did work on many things, I still remember the good old Linux-Kernel days and (although I am the only user) I have made an adapter for running MOD_PYTHON code on top of MOD_WSGI (as an alternative MP package). It is running my production code made for MP on top of MOD_WSGI very well I personally like the way of the Request object. If I can contribute to SA I will be happy to do so, I have the feeling I have lightened up something which could be improved. That is a first step... (not a big one :-) I have the feeling that SA is mainly a German project. Although I get confused by the working hours of Michael, He never responded to my question if he ever sleeps... The German names of people seem to make me think that, I am not sure though. German product turn out be be great and I love Germany for many reasons (I even Worked in Germany). If I cannot afford a German made car, I'll walk!! as I said I am thinking about contributing in some manner to pay back for being able to use SA and for the great help the users get from Michael and others. On Feb 14, 2011, at 05:11 , Eric Ongerth wrote: Polymorphic associations pop up a lot around here, don't they! I suppose it's partly because they would be so much more difficult to handle, or even come close to handling, conveniently, with most other ORM packages. Martijn, after running into the wall on polymorphic associations approximately once a year since Michael wrote that blog article, I finally got it all straight in my mind, and I realized there are two fundamental tricks to seeing the PA phenomenon clearly. One is learn to sense when it's around; it's almost like a certain smell in your code. There is a particular feeling that distinguishes it from other data modeling problems. Two, when you notice a polymorphic association pattern beginning to appear, attempt to think about the relations involved in the exact reverse direction. This sounds too trivial to be a real piece of advice, but it works every time for me. Each time I perceive a problem with a polymorphic association but then I reverse my perspective, the perceived problem vanishes. I could probably do just as well by binding a rug around my head or something, but that's just me. And I'm not going to find out. - Eric On Feb 13, 1:57 pm, Michael Bayer mike...@zzzcomputing.com wrote: a polymorphic association is hard. that's why I have three examples of them and soon a fourth.Though they are a subset of a larger batch of tricks that I've been using in my own work with declarative for the past year to automate lots of different kinds of patterns, perhaps there's a learning curve but once three or four techniques are mastered they come pretty easily. On Feb 13, 2011, at 4:35 PM, Martijn Moeling wrote: Michael, I looked at the code and I can not say more than that its very interesting, I have to see how it works and more importantly how It fits into my objects but it seems clear enough to do so. I really appreciate your work on SQLAlchemy and all the time you spend to help us users out. Your solution is definitively one I could not have put together myself. Although I have tried. SQLAlchemy is so powerful that it is hard to find the right options for the job. Thank you again! Martijn On Feb 13, 2011, at 21:19 , Michael Bayer wrote: On Feb 13, 2011, at 6:14 AM, Martijn Moeling wrote: You are right in the misunderstood
Re: [sqlalchemy] Re: Dynamic relations... !!
Michael,
I have implemented your example into my code and although it sort of works, It
does not fit into the whole system as expected, somehow I think it is way to
complex for what I want.
It should be much more simple
In the basis I want a class Extra, in this example I'll use queries to
define/explain the relationships
Class User(Base): This class is global and loaded
Id = 0
groups = []
Me = User(Id=5, Groups = [1,2,3,6])
class Extra(Base):
__tablename__ = 'Extra'
Id = Column(Integer,
primary_key=True) # this is only to identify the EXTRA record
Table = Column(Unicode(20))
TableId = Column(Integer)
#ForeignKey to self.Table+'.Id' !!THIS IS MY REAL
PROBLEM!
# can @validates help out? I do not
understand the use very well.
OwnerID = Column(Integer,
ForeignKey('UsersAndGroup.Id'))
SomeRecord =
Session.Query('self.Table').filter(str(self.Table.Id)+' ==
'+str(Self.TableId)).one() (Or this be a backref, seems even better)
Read= Column(Boolean)
Write = Column(Boolean)
... = Column(what_ever_type)
Class ExtraProperties(object):
@declared_attr
__table_args__(self):
return
(ForeignKeyConstaint(['Id',self.__tablename__],['Extra.TableId','Extra.Table']),{})
def Extras(self, Me)
should be @declared_attr, # Not sure how to
handle the reference to Me.. yet
def Extras(self),
return relation
The Query.
return Session.query(Extra).filter(
and_(
Extra.Table == self.__tablename__,
Extra.Id == self.Id,
in_(Extra.OwnerId, Me.groups.append(Me.Id))
, Extra.Read==True).all()# in this case [1,2,3,6,5] , This
can be done in the MapperExtension before_append
def __del__(self)
session.delete(Extra).filter(
and_(
Extra.Table == self.__tablename__,
Extra.TableId == self.Id)
# defines the cascade for deletion nothing more ,just Every Extra to this
record
Class SomeTable(Base, ExtraProperties)
__tablename__ = 'SomeTable'
Id = Column(Integer,
primary_key=True)
I have tried doing this but can not seem to set the relationship right. If
manage to get the relation not to moan about determing primarykeys, I get NULL
Identity errors on flush() in the commit
The SomeTable could be a polymorphic inheritance or any basic table.
As you might remember I even tried this with MapperExtension in the
before_append and before_insert etc. but then I run into instance of whatever
is deleted. amongst others
I might be on the wrong foot but spending 7 days to get such a stupid thing
. It should not be hard.
I could very well make wrapper functions for the Queries, but that does not
help with Integrity of Extra records I want them to be deleted on SomeClass
deletion...
I still like the MapperExtrention approach where I can pythonically check the
Extras.Read etc.
SomeClass being Polymorphic or not does not seem to be relevant.
Martijn
On Feb 14, 2011, at 14:04 , Martijn Moeling wrote:
Eric (and Michael),
Thank you for your comments, I agree with you totally.
I am not much of a database guy and never have been.
During my education I did not pay much attention to those
lessons either, I was just interested in positive grades.
I dropped the database stuff as quickly as possible and concentrated on
(OS) kernel and protocol related technologies.
The case I am working on now
Re: [sqlalchemy] Dynamic relations...
You are right in the misunderstood relation.
I see the primary key in extra to be wrong, extra should have it's own I'd
column being an auto number. In extra it should be possible to have many
records pointing to 1 ext variant. Sorry for that.
The extra, should also work with tables without a discriminator, there the link
should be made to table name which is in my case always class.__name__ ..
On those tables, the relation needs to be different since on of the local
columns, discriminator is not present and it somehow should be linked to
__table name__
It is all part of the ACL examples it talked about walker, where extra must be
seen as the ACL. That is where the mapper extension comes in
It is getting a all technologies mixed in situation
Verstuurd vanaf mijn iPad
Op Feb 12, 2011 om 17:05 heeft Michael Bayer mike...@zzzcomputing.com het
volgende geschreven:
OK I can show you the version of your code that does most of this but there
are some fundamental relational misunderstandings in this schema if I am
interpreting correctly.
Extra:
tableid tablename
--- -
1 ext1
2 ext1
3 ext2
4 ext2
5 ext3
ext1:
id discriminator (- FK to Extra.tableid, Extra.tablename)
-- -
1ext1
2ext1
3ext2
4ext2
5ext3
ext2:
id
--
3
4
ext3:
id
--
5
given ext1 ID #3, discriminator ext2 - how can more than one Extra row be
referenced? Why is extras assumed to be one-to-many when it can only be
many-to-one ?
On Feb 12, 2011, at 9:57 AM, Martijn Moeling wrote:
This whole thing is driving me crazy, What I want:
class Extra(Base):
__tablename__= extra
# Primary key consists of two different columns !!!
tableId= Column(Integer, primary_key=true)
tablename= Column(Unicode(20), primary_key=True)
info= Column() #Not relevant
class ex1(Base):
Id = Column(Integer, primary_key=True)
discriminator = Column(Unicode(20))
@declared_attr
def __tablename__(self):
return self.__name__.lower()
@declared_attr
def __mapper_args__(self):
if self.__name__ == 'ext1':
return {'polymorphic_on': self.discriminator,
'polymorphic_identity':unicode(self.__name__.lower()),
'extension': FilePropertiesMapperExtension(),
'batch' : False}
else:
return {'polymorphic_identity':unicode(self.__name__.lower()),
'inherit_condition': self.Id == extra.Id,
#needed for something else in this config (multiple self reference)
'extension': FilePropertiesMapperExtension(),
#Needed for something else, not relevant for this sample
'batch' : False}
# ,,,,,,,,
# Set up foreignkey and relation to Extra
__table_args__ = (ForeignKeyConstraint(['discriminator', 'Id'],
['extra.Table','extra.TableId']),{})
extras= relation('Extra', cascade=all, lazy=dynamic
backref=owner)
class ext2(ext1):
Id = Column(Integer,ForeignKey('ext1.Id'),
primary_key=True)
..
class ext3(ext1):
Id = Column(Integer,ForeignKey('ext1.Id'),
primary_key=True)
.
Now I want:
Ext2 = ext2()
Extra_info = extra()
Ext2.extras.append(Extra_Info)
Ext2.discriminator should be ext2
Ext2.Id should be 1 for the first record
Extra_Info should be created in the database, with its columns : id set to
the Ext2.id and tablename to Ext.discriminator ..
Extra_Info.owner would point to Ext2
If Ext2 is deleted, all related extrainfo record would be delete too
if one Extra_Info is deleted, Extra_Info.owner should stay in place as well
as all other related
Extra is many to one polymorhic version of ext1
I hope this clarifies more what I want
I really need the @declared_attr way of doing stuff and that is not related
to this question but might influence this question so I left it in..
Martijn
On Feb 10, 2011, at 18:13 , Michael Bayer wrote:
On Feb 10, 2011, at 4:20 AM, Martijn Moeling wrote:
Another small thing:
I took a look at:
ForeignKeyConstraint(['invoice_id', 'ref_num'], ['invoice.invoice_id',
'invoice.ref_num'])
Now for Polymorphic tables:
in baseclass:
baseclass.discriminator happens to be the __tablename__ of the polymorphic
ForeignKeyConstraint('['baseclass.disciminator', baseclass.Id'],
['someotherclass.tablename','someotherclass.tableId']
relationship('someotherclass', backref=baseclass, cascade=all,
lazy
Re: [sqlalchemy] Dynamic relations...
Michael,
I looked at the code and I can not say more than that its very interesting, I
have to see how it works and more importantly how It fits into my objects but
it seems clear enough to do so.
I really appreciate your work on SQLAlchemy and all the time you spend to help
us users out.
Your solution is definitively one I could not have put together myself.
Although I have tried. SQLAlchemy is so powerful that it is hard to find the
right options for the job.
Thank you again!
Martijn
On Feb 13, 2011, at 21:19 , Michael Bayer wrote:
On Feb 13, 2011, at 6:14 AM, Martijn Moeling wrote:
You are right in the misunderstood relation.
I see the primary key in extra to be wrong, extra should have it's own I'd
column being an auto number. In extra it should be possible to have many
records pointing to 1 ext variant. Sorry for that.
The extra, should also work with tables without a discriminator, there the
link should be made to table name which is in my case always class.__name__
.. On those tables, the relation needs to be different since on of the
local columns, discriminator is not present and it somehow should be
linked to __table name__
OK what you are trying to do is exactly a polymorphic association. The
technique of placing tablename in the table of related records, then
using that tablename to indicate which parent table should be matched at
query time, is a common, but IMHO relationally incorrect pattern. I blogged
extensively about the Ruby on Rails approach, how to duplicate Rails'
approach in SQLAlchemy, and then an alternate system which maintains
referential integrity, four years ago at
http://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-sqlalchemy/
.
That's a really old example and we're into 0.7 now, so I've created a new
version of the poly assoc example that uses declarative techniques and the
association proxy, which is attached. I'm going to further fix up this
example and add it to the distribution as a fourth example of polymorphic
association, which is in examples/poly_assoc/.
Additionally I'd like the association proxy to work more smoothly in queries
so I've added ticket #2054 for some of those issues which weren't accounted
for when we first added any(), contains() operators to the association proxy.
It is all part of the ACL examples it talked about walker, where extra must
be seen as the ACL. That is where the mapper extension comes in
It is getting a all technologies mixed in situation
Verstuurd vanaf mijn iPad
Op Feb 12, 2011 om 17:05 heeft Michael Bayer mike...@zzzcomputing.com
het volgende geschreven:
OK I can show you the version of your code that does most of this but there
are some fundamental relational misunderstandings in this schema if I am
interpreting correctly.
Extra:
tableid tablename
--- -
1 ext1
2 ext1
3 ext2
4 ext2
5 ext3
ext1:
id discriminator (- FK to Extra.tableid, Extra.tablename)
-- -
1ext1
2ext1
3ext2
4ext2
5ext3
ext2:
id
--
3
4
ext3:
id
--
5
given ext1 ID #3, discriminator ext2 - how can more than one Extra row be
referenced? Why is extras assumed to be one-to-many when it can only be
many-to-one ?
On Feb 12, 2011, at 9:57 AM, Martijn Moeling wrote:
This whole thing is driving me crazy, What I want:
class Extra(Base):
__tablename__= extra
# Primary key consists of two different columns !!!
tableId= Column(Integer, primary_key=true)
tablename= Column(Unicode(20), primary_key=True)
info= Column() #Not relevant
class ex1(Base):
Id = Column(Integer, primary_key=True)
discriminator = Column(Unicode(20))
@declared_attr
def __tablename__(self):
return self.__name__.lower()
@declared_attr
def __mapper_args__(self):
if self.__name__ == 'ext1':
return {'polymorphic_on': self.discriminator,
'polymorphic_identity':unicode(self.__name__.lower()),
'extension': FilePropertiesMapperExtension(),
'batch' : False}
else:
return {'polymorphic_identity':unicode(self.__name__.lower()),
'inherit_condition': self.Id == extra.Id,
#needed for something else in this config (multiple self reference)
'extension': FilePropertiesMapperExtension(),
#Needed for something else, not relevant for this sample
'batch' : False}
# ,,,,,,,,
# Set up foreignkey and relation to Extra
__table_args__ = (ForeignKeyConstraint
Re: [sqlalchemy] Dynamic relations...
This whole thing is driving me crazy, What I want:
class Extra(Base):
__tablename__ = extra
# Primary key consists of two different columns !!!
tableId = Column(Integer,
primary_key=true)
tablename = Column(Unicode(20),
primary_key=True)
info= Column() #Not
relevant
class ex1(Base):
Id = Column(Integer, primary_key=True)
discriminator = Column(Unicode(20))
@declared_attr
def __tablename__(self):
return self.__name__.lower()
@declared_attr
def __mapper_args__(self):
if self.__name__ == 'ext1':
return {'polymorphic_on': self.discriminator,
'polymorphic_identity':unicode(self.__name__.lower()),
'extension': FilePropertiesMapperExtension(),
'batch' : False}
else:
return {'polymorphic_identity':unicode(self.__name__.lower()),
'inherit_condition': self.Id == extra.Id,
#needed for something else in this config (multiple
self reference)
'extension': FilePropertiesMapperExtension(),
#Needed for something else, not relevant for this sample
'batch' : False}
# ,,,,
,, ,,
# Set up foreignkey and relation to Extra
__table_args__ = (ForeignKeyConstraint(['discriminator', 'Id'],
['extra.Table','extra.TableId']),{})
extras= relation('Extra', cascade=all, lazy=dynamic
backref=owner)
class ext2(ext1):
Id = Column(Integer,ForeignKey('ext1.Id'),
primary_key=True)
..
class ext3(ext1):
Id = Column(Integer,ForeignKey('ext1.Id'),
primary_key=True)
.
Now I want:
Ext2 = ext2()
Extra_info = extra()
Ext2.extras.append(Extra_Info)
Ext2.discriminator should be ext2
Ext2.Id should be 1 for the first record
Extra_Info should be created in the database, with its columns : id set to the
Ext2.id and tablename to Ext.discriminator ..
Extra_Info.owner would point to Ext2
If Ext2 is deleted, all related extrainfo record would be delete too
if one Extra_Info is deleted, Extra_Info.owner should stay in place as well as
all other related
Extra is many to one polymorhic version of ext1
I hope this clarifies more what I want
I really need the @declared_attr way of doing stuff and that is not related to
this question but might influence this question so I left it in..
Martijn
On Feb 10, 2011, at 18:13 , Michael Bayer wrote:
On Feb 10, 2011, at 4:20 AM, Martijn Moeling wrote:
Another small thing:
I took a look at:
ForeignKeyConstraint(['invoice_id', 'ref_num'], ['invoice.invoice_id',
'invoice.ref_num'])
Now for Polymorphic tables:
in baseclass:
baseclass.discriminator happens to be the __tablename__ of the polymorphic
ForeignKeyConstraint('['baseclass.disciminator', baseclass.Id'],
['someotherclass.tablename','someotherclass.tableId']
relationship('someotherclass', backref=baseclass, cascade=all,
lazy=dynamic)
in someotheclass:
tablename = column(Unicode(20), primary_key=True)
tableId = column(Integer, primary_key=True)
seems Ok to me.
Now I need to make someotherclass work with non-polymorphic tables too!!
anotherclass:
Id = column(Integer, primary_key=True)
ForeignKeyConstaint('[anotherclass.__tablename__,'anotherclass.Id'],['someotherclass.tablename','someotherclass.tableId'])
relation('someotherclass', backref=baseclass, cascade=all,
lazy=dynamic)
Is there any way to get this working without configuring it as polymorphic
an do no Inhiritance, I do not want each anotherclass record to have a
column discriminator with its own tablename!
or can I use anotherclass.__tablename__ in the ForeignKeyConstaint?
This has to do with the someotherclass being the ACL I talked about in a
previous post if that gives extra info. I am trying to implement the
MapperExtension.before_append where I need to refer to the ACL records in
a way like:
for A in instance.ACL:
.
yeah, sorry, this use case continues to be 98% opaque to me. I don't
understand what you mean by make someotherclass work with non-polymorphic
tables, a class is mapped in just one way, either with or without a
discriminator column. A single class can't be mapped in both ways.If
there's no discriminator, there's just one class that can be used for
returned rows.
If you could create a small test that illustrates a mapping and an expected
result, perhaps I can attempt to find
Re: [sqlalchemy] Dynamic relations...
One more thing
Extra should also be related to other classes, not only ones setup polymorphic
Like:
class dummy(Base):
ID = Column (Integer,Primary_key=True)
# Set up foreignkey and relation to Extra
__table_args__ = (ForeignKeyConstraint([This tables
tablename, 'Id'], ['extra.Table','extra.TableId']),{})
#
^^^
extras= relation('Extra', cascade=all, lazy=dynamic
backref=owner)
I do not really want to add a discriminator to the Dummy table for this
relation to work
This to explain:
yeah, sorry, this use case continues to be 98% opaque to me. I don't
understand what you mean by make someotherclass work with non-polymorphic
tables, a class is mapped in just one way, either with or without a
discriminator column. A single class can't be mapped in both ways.If
there's no discriminator, there's just one class that can be used for
returned rows.
Martijn
On Feb 12, 2011, at 15:57 , Martijn Moeling wrote:
This whole thing is driving me crazy, What I want:
class Extra(Base):
__tablename__ = extra
# Primary key consists of two different columns !!!
tableId = Column(Integer,
primary_key=true)
tablename = Column(Unicode(20),
primary_key=True)
info= Column() #Not
relevant
class ex1(Base):
Id = Column(Integer, primary_key=True)
discriminator = Column(Unicode(20))
@declared_attr
def __tablename__(self):
return self.__name__.lower()
@declared_attr
def __mapper_args__(self):
if self.__name__ == 'ext1':
return {'polymorphic_on': self.discriminator,
'polymorphic_identity':unicode(self.__name__.lower()),
'extension': FilePropertiesMapperExtension(),
'batch' : False}
else:
return {'polymorphic_identity':unicode(self.__name__.lower()),
'inherit_condition': self.Id == extra.Id,
#needed for something else in this config (multiple
self reference)
'extension': FilePropertiesMapperExtension(),
#Needed for something else, not relevant for this sample
'batch' : False}
# ,,,,
,, ,,
# Set up foreignkey and relation to Extra
__table_args__ = (ForeignKeyConstraint(['discriminator', 'Id'],
['extra.Table','extra.TableId']),{})
extras= relation('Extra', cascade=all, lazy=dynamic
backref=owner)
class ext2(ext1):
Id = Column(Integer,ForeignKey('ext1.Id'),
primary_key=True)
..
class ext3(ext1):
Id = Column(Integer,ForeignKey('ext1.Id'),
primary_key=True)
.
Now I want:
Ext2 = ext2()
Extra_info = extra()
Ext2.extras.append(Extra_Info)
Ext2.discriminator should be ext2
Ext2.Id should be 1 for the first record
Extra_Info should be created in the database, with its columns : id set to
the Ext2.id and tablename to Ext.discriminator ..
Extra_Info.owner would point to Ext2
If Ext2 is deleted, all related extrainfo record would be delete too
if one Extra_Info is deleted, Extra_Info.owner should stay in place as well
as all other related
Extra is many to one polymorhic version of ext1
I hope this clarifies more what I want
I really need the @declared_attr way of doing stuff and that is not related
to this question but might influence this question so I left it in..
Martijn
On Feb 10, 2011, at 18:13 , Michael Bayer wrote:
On Feb 10, 2011, at 4:20 AM, Martijn Moeling wrote:
Another small thing:
I took a look at:
ForeignKeyConstraint(['invoice_id', 'ref_num'], ['invoice.invoice_id',
'invoice.ref_num'])
Now for Polymorphic tables:
in baseclass:
baseclass.discriminator happens to be the __tablename__ of the polymorphic
ForeignKeyConstraint('['baseclass.disciminator', baseclass.Id'],
['someotherclass.tablename','someotherclass.tableId']
relationship('someotherclass', backref=baseclass, cascade=all,
lazy=dynamic)
in someotheclass:
tablename = column(Unicode(20), primary_key=True)
tableId = column(Integer, primary_key=True)
seems Ok to me.
Now I need to make someotherclass work with non-polymorphic tables too!!
anotherclass:
Id = column(Integer, primary_key=True)
ForeignKeyConstaint('[anotherclass.__tablename__,'anotherclass.Id
[sqlalchemy] Dynamic relations...
Another small thing:
I took a look at:
ForeignKeyConstraint(['invoice_id', 'ref_num'], ['invoice.invoice_id',
'invoice.ref_num'])
Now for Polymorphic tables:
in baseclass:
baseclass.discriminator happens to be the __tablename__ of the polymorphic
ForeignKeyConstraint('['baseclass.disciminator', baseclass.Id'],
['someotherclass.tablename','someotherclass.tableId']
relationship('someotherclass', backref=baseclass, cascade=all, lazy=dynamic)
in someotheclass:
tablename = column(Unicode(20), primary_key=True)
tableId = column(Integer, primary_key=True)
seems Ok to me.
Now I need to make someotherclass work with non-polymorphic tables too!!
anotherclass:
Id = column(Integer, primary_key=True)
ForeignKeyConstaint('[anotherclass.__tablename__,'anotherclass.Id'],['someotherclass.tablename','someotherclass.tableId'])
relation('someotherclass', backref=baseclass, cascade=all,
lazy=dynamic)
Is there any way to get this working without configuring it as polymorphic an
do no Inhiritance, I do not want each anotherclass record to have a column
discriminator with its own tablename!
or can I use anotherclass.__tablename__ in the ForeignKeyConstaint?
This has to do with the someotherclass being the ACL I talked about in a
previous post if that gives extra info. I am trying to implement the
MapperExtension.before_append where I need to refer to the ACL records in a
way like:
for A in instance.ACL:
.
Martijn
--
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] MapperExtension.append_result ....
Michael,
I took a look at the recipe you indicated, it looks promising but the check
should be constructed from database results. Another issue is that this project
is implemented in my web based desktop/Os which uses SQLAlchemy from the bottem
up. So modifiing the session object globally is with a PreFilteredQuery is not
a real option. Creating a session for this program only might be an option
but I am not sure how that will turn out.
Being it a web based (and so Handle request and die), Persistence is (to me)
not very usefull and I need to reload everything for every action.
the @reconstructor hook seems too outdated. I moved to 0.6.6 last week, and
only will upgrade to stable/production versions since in my case there is a lot
to it.
I need to transparently add being queried functionality to mapped objects.
This functionality is will be mixed in and should be able to limit the results
when being queried. Since my class definitions are so complex I would like to
make a (not functional) example on what I am in search of. and I will not
bother you with chemistry stuff...
Class ACL(Base):
Id = Column(Integer, primary_key=True)
tablename = Column(Unicode(...
tableId= Column(Integer
RecordId = ForeignKeyContruct( / ForeignKey (not sure yet)
Record = relation( self.tablename
User_Group = relation to Person, group
Bool columns..
MayRead
MayWrite
MayCreate
Class Mixinstuff(Object)
Rights = {} # Rights[MayRead] etc. will be set upon load
Class Person(Base,Mixinstuff)
Id = Column(Integer, primary_key=True)
ACLs = relation('ACL' All ACL records which have tablename =
'person' and tableID = Person.Id, cascade=delete and ACL record for me ) #
ACL's work on many tables
I might not define the relation here but backref from the acl record
depending on how to build what I want
addresses = relation(
Class Address(Base, ACLMixinstuff)
Id = Column(Integer, primary_key=True)
ACLs = relation('ACL' All ACL records which have tablename =
'person' and tableID = Person.Id, cascade=delete) # ACL's work on many tables
I might not define the relation here but backref from the acl record
depending on how to build what I want
class ME()
userId = 1 (foreignkey to Person)
groups = [1,2,3,4] (relationship with groups (same polymorhic
baseclass)
Now consider ME being a member of Everyone not guest
ACLS for Person
ME | table = person | Id = 1| MayRead = F
Everyone| table = person | Id = 1 | MayRead = T
Guest | table = person | Id = 1 | MayRead = F
user = ME, GROUPS = [Everyone]
A query for Session.query(Persons).all() should NOT return Person.Id although
Everyone says True, personal Permissions overrule group permissions , simple
boolean operations. If no ACLs are found It all defaults to false or true not
sure yet on how this will work on my real data model, since this will be the
model on which atoms and molecule connections are Allowed
If However the ACL's turn out that ME.MayRead = T, I will only get related
addresses I actually may read. This should work automatically for each class
with Mixedinstuff inherited
This is whilst I do not want the Users of this model to be bothered with
this, the should add data to their model and query to generate list of possible
new molecules.
I am some sort of clueless on how to do this properly
the MapperExtention.append_result still seems the best way...
if calculate_ACLs(Session = object_session(self), tablename =
instance.__tablename__, TableId = instance.__=TableId__, CheckFor = ME, Right =
MayRead ):
EXT_CONTINUE
else:
EXT_STOP
Dont you?
One other thing, the CalculateACLs query should be as light as possible It will
only need to return True or False if possible using database functions and if
possible be database independant.
Can you help me on that one too?
def calculate-ACLs(...):
BOOLGROUP = Session.query(ACL).filter(and_(tablename= .., tableId
=...,USER_GROUP in me.literal_colum(..?..?..?))..
BOOLME = the same but now for ME, is easy no boolean calculation needed
in query
if BoolME:
return BOOLME
else:
return BOOLGROUP
Martijn
On Feb 7, 2011, at 5:55 PM, Michael Bayer wrote:
On Feb 7, 2011, at 11:42 AM, Martijn Moeling wrote:
I think, I might be helped with the create_instance event
Assuming you're talking about when the ORM establishes an instance from a
newly fetched row, you can use the @reconstructor hook for that. 0.7
publishes this event additionally as the load event.
I will never ever stop a class from being saved/persistent,
it is the other way around. I
Re: [sqlalchemy] MapperExtension.append_result ....
Michael, Thank you, The final solution has nothing to do with ACL's or addresses and security for others getting results by querying is a none issue. As mentioned before I am building a database and tools to help chemists selecting molecule structures. It is all way more complex than you might think since the ACL records have ACL records assosiated to them to. Setting up relations and queries is a total nightmare because almost all relations end up to be circular over multiple tables. controlling the eager loading where possible for convenience and where impossible has been a huge job although SQLAlchemy is a huge help. I only use this as a understandable data structure since I know how hard it was to understand the terminology. I do not want to bring that to this group and more importantly since I search the mailinglist myself a lot it can help others finding a solution to their needs. I find that the deeper I dive into SA, the less examples are available, the harder it is to test functionality and sometimes documentation gets more sparse. Thank you again... Martijn On Feb 8, 2011, at 4:21 PM, Michael Bayer wrote: On Feb 8, 2011, at 6:05 AM, Martijn Moeling wrote: Michael, I took a look at the recipe you indicated, it looks promising but the check should be constructed from database results. Another issue is that this project is implemented in my web based desktop/Os which uses SQLAlchemy from the bottem up. So modifiing the session object globally is with a PreFilteredQuery is not a real option. Creating a session for this program only might be an option but I am not sure how that will turn out. Well a MapperExtension is also global to that class.Subclassing Query with rules for a specific mapper is fairly easy to isolate to those use cases. Being it a web based (and so Handle request and die), Persistence is (to me) not very usefull and I need to reload everything for every action. That is typical for a web application. the @reconstructor hook seems too outdated. I moved to 0.6.6 last week, and only will upgrade to stable/production versions since in my case there is a lot to it. @reconstructor is a standard feature since 0.5 and continues to be. I need to transparently add being queried functionality to mapped objects. This functionality is will be mixed in and should be able to limit the results when being queried. Since my class definitions are so complex I would like to make a (not functional) example on what I am in search of. and I will not bother you with chemistry stuff... user = ME, GROUPS = [Everyone] A query for Session.query(Persons).all() should NOT return Person.Id although Everyone says True, personal Permissions overrule group permissions , simple boolean operations. If no ACLs are found It all defaults to false or true not sure yet on how this will work on my real data model, since this will be the model on which atoms and molecule connections are Allowed If However the ACL's turn out that ME.MayRead = T, I will only get related addresses I actually may read. This should work automatically for each class with Mixedinstuff inherited This is whilst I do not want the Users of this model to be bothered with this, the should add data to their model and query to generate list of possible new molecules. I am some sort of clueless on how to do this properly the MapperExtention.append_result still seems the best way... if calculate_ACLs(Session = object_session(self), tablename = instance.__tablename__, TableId = instance.__=TableId__, CheckFor = ME, Right = MayRead ): EXT_CONTINUE else: EXT_STOP Dont you? I guess what you're expressing is that your ACL rules need to fire off using Python code, not SQL expressions.The whole thing seems quite awkward to me since there's nothing to stop someone from saying Query(MyACLObject.id, MyACLObject.name, ...), etc., they get all the data from the ACL row anyway, or similarly if they were to say Query(SomeClass, SomeOtherClass, MyACLObject) using a join, again the append_result() hook isn't used.If it were me I'd be using some filter function around query() in an explicit sense to do it, but this is just a matter of style. The hook will work fine if its limitations are OK with you. -- 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
[sqlalchemy] Declarative, Imports and Base
Hi, I am having a small issue with multiple python modules and declarative... I might miss something but Consider: a.py: 8--- Base = declarative_base() class A(Base): ... 8--- b.py Base = declarative_base() class B(Base): ... 8--- c.py Base = declarative_base() class C(Base): ... 8--- d.py Base = declarative_base() from A import * # imports base from B import * # imports base from C import * # imports base Class D1(Base) ... Class D2(A) ... in d.py I want to create: def create_tables(engine): metadata= Base.metadata metadata.create_all(engine) Is there any way to properly add the metadata from the imported modules in d.py to the Base.metadata during the import..? Think of modules a,b,c and d are together in a package and d is imported with similar packages into something bigger Martijn -- 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] Declarative, Imports and Base
Michael, Do you ever sleep? I am not sure I get your point. How do I set up a common Base. I could do Base= Declarative_base() from a import A (or * not sure how this differs in this case) from b import B (or *) If I do not declare Base in module a I get an Import Error on class A(Base), the same for importing b. This gets even more complicated when Base should be build from classes defined across modules. at the end there is one main.py importing all modules and this should be able to define the Main Base. Any suggestions on how to tackle this. I know have multiple modules and am glueing everything together. This even gets more problematic with Inheritance. one solution could be.. from a import A, BaseA from b import B, BaseB Base = declarative_base() metadata - BaseA.metadata + BaseB.metadata metadata.create_all(engine) What I do not get is how the mapper is configured. Normally with declarative Base it is not used in Production fase for as far as I can see. The mapper is part of the Class right? and session does not use Base at all? but gets it when needed - Session.query(A). ? Or am I totally wrong on this? Can I something like this: from a import * (imports class A and the declarative_base BaseA) Base = declarative_base() class C(Base): ... BaseForB = declarative_base(metadata=BaseA) class B(BaseForB) Mixin classes are of type object so there is no issue since @declared_attr etc works class D(Base,mixinclass) works without a Base at all so there is no Issue Am I right? in understanding your comments on my first mail in this topic? Martijn On Feb 8, 2011, at 7:46 PM, Michael Bayer wrote: On Feb 8, 2011, at 1:19 PM, Martijn Moeling wrote: Hi, I am having a small issue with multiple python modules and declarative... I might miss something but Consider: a.py: 8--- Base = declarative_base() class A(Base): ... 8--- b.py Base = declarative_base() class B(Base): ... 8--- c.py Base = declarative_base() class C(Base): ... 8--- d.py Base = declarative_base() from A import * # imports base from B import * # imports base from C import * # imports base Class D1(Base) ... Class D2(A) ... in d.py I want to create: def create_tables(engine): metadata= Base.metadata metadata.create_all(engine) Is there any way to properly add the metadata from the imported modules in d.py to the Base.metadata during the import..? Think of modules a,b,c and d are together in a package and d is imported with similar packages into something bigger Usually the convention is that all modules in an application share the same declarative base object (i.e. Base). If you wanted multiple Base objects but have them share a common MetaData, you can declare the MetaData up front, then create each Base using declarative_base(metadata=my_metadata). Otherwise if you're really looking to merge together multiple MetaData objects, that's not really in the API right now in a clean way, you'd perhaps call .tometadata() on each Table object but that's really not what I would do here - it copies the whole Table object and isn't really for a use case like this. If you can at least have a common MetaData object, or better a common Base object, that would be the best way to go. -- 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] Declarative, Imports and Base
Clear, if all packages are in the same project that is.. and what if in a.py I want to inherit some class mapped with b.py mixin does not allways work as a solution subclassing (DeclarativeMeta) is an option, not sure Once I do a base=declarative_base(metadata=BaseB) every class x(Base) gets the Columns in BaseB to it ..? I have been setting up some tests and one way or the other I need to know how Session is using Base, if I can have multiple declarative_base instances and how Session relates to that. Consider this: Base = Declarative_base() class a(Base): def create_table(engine): b=a() metadata = b.metadata metadata.create_all(engine) Base = None # IMPORTANT IS Sessionmaker using intropsection to find out an instance of Declarative base? engine = Session = SessionMaker(bind=engine) would this or a similar approach work? On Feb 8, 2011, at 9:12 PM, Michael Bayer wrote: the idea is like this: myproject/ myproject/__init__.py myproject/meta.py myproject/somepackage/__init__.py myproject/somepackage/a.py myproject/someotherpackage/__init__.py myproject/someotherpackage/b.py myproject/__init__.py: from myproject.somepackage import a from myproject.someotherpackage import b meta.py: Base = declarative_base() a.py: from myproject.meta import Base b.py: from myproject.meta import Base On Feb 8, 2011, at 2:25 PM, Martijn Moeling wrote: Michael, Do you ever sleep? I am not sure I get your point. How do I set up a common Base. I could do Base= Declarative_base() from a import A (or * not sure how this differs in this case) from b import B (or *) If I do not declare Base in module a I get an Import Error on class A(Base), the same for importing b. This gets even more complicated when Base should be build from classes defined across modules. at the end there is one main.py importing all modules and this should be able to define the Main Base. Any suggestions on how to tackle this. I know have multiple modules and am glueing everything together. This even gets more problematic with Inheritance. one solution could be.. from a import A, BaseA from b import B, BaseB Base = declarative_base() metadata - BaseA.metadata + BaseB.metadata metadata.create_all(engine) What I do not get is how the mapper is configured. Normally with declarative Base it is not used in Production fase for as far as I can see. The mapper is part of the Class right? and session does not use Base at all? but gets it when needed - Session.query(A). ? Or am I totally wrong on this? Can I something like this: from a import * (imports class A and the declarative_base BaseA) Base = declarative_base() class C(Base): ... BaseForB = declarative_base(metadata=BaseA) class B(BaseForB) Mixin classes are of type object so there is no issue since @declared_attr etc works class D(Base,mixinclass) works without a Base at all so there is no Issue Am I right? in understanding your comments on my first mail in this topic? Martijn On Feb 8, 2011, at 7:46 PM, Michael Bayer wrote: On Feb 8, 2011, at 1:19 PM, Martijn Moeling wrote: Hi, I am having a small issue with multiple python modules and declarative... I might miss something but Consider: a.py: 8--- Base = declarative_base() class A(Base): ... 8--- b.py Base = declarative_base() class B(Base): ... 8--- c.py Base = declarative_base() class C(Base): ... 8--- d.py Base = declarative_base() from A import * # imports base from B import * # imports base from C import * # imports base Class D1(Base) ... Class D2(A) ... in d.py I want to create: def create_tables(engine): metadata= Base.metadata metadata.create_all(engine) Is there any way to properly add the metadata from the imported modules in d.py to the Base.metadata during the import..? Think of modules a,b,c and d are together in a package and d is imported with similar packages into something bigger Usually the convention is that all modules in an application share the same declarative base object (i.e. Base). If you wanted multiple Base objects but have them share a common MetaData, you can declare the MetaData up front, then create each Base using declarative_base(metadata=my_metadata). Otherwise if you're really looking to merge together multiple MetaData objects, that's not really in the API right now in a clean way, you'd perhaps call .tometadata() on each Table object but that's really not what I would do here - it copies the whole Table object and isn't really for a use case like this. If you can
Re: [sqlalchemy] Declarative, Imports and Base
Clear!
On Feb 8, 2011, at 10:21 PM, Michael Bayer wrote:
On Feb 8, 2011, at 3:57 PM, Martijn Moeling wrote:
Clear, if all packages are in the same project that is..
and what if in a.py I want to inherit some class mapped with b.py
mixin does not allways work as a solution subclassing (DeclarativeMeta) is
an option, not sure
Once I do a base=declarative_base(metadata=BaseB)
every class x(Base) gets the Columns in BaseB to it ..?
I have been setting up some tests and one way or the other I need to know
how Session is using Base, if I can have multiple declarative_base instances
and how Session relates to that.
Consider this:
Base = Declarative_base()
class a(Base):
def create_table(engine):
b=a()
metadata = b.metadata
metadata.create_all(engine)
Base = None # IMPORTANT IS Sessionmaker using intropsection to find out
an instance of Declarative base?
engine =
Session = SessionMaker(bind=engine)
would this or a similar approach work?
Session does not care about Base, nor does MetaData.
The Base gives you this:
class MyObject(Base):
...
related = relationship(Related)
the string Related is looked up in a dictionary inside of Base. The
dictionary is called _decl_class_registry.
MetaData gives you this:
class MyObject(Base):
...
related_id = Column(Integer, ForeignKey('related.id'))
the string 'related.id' is broken into 'related' and 'id' and is looked up
inside a dictionary inside of MetaData(). The dictionary is called
tables.There's some extra lookup helper mechanics surrounding this
dictionary in 0.7 which is why we don't want you manipulating .tables
directly.
Those two registries are the *only* thing you get from a Base and a MetaData
that is dependent on how many of them are in use. Neither is strictly
needed. relationship() accepts the real class itself, i.e.
relationship(Related). ForeignKey accepts a real column object, i.e.
ForeignKey(related_table.c.id).The registries are strictly for the
purpose of making it *easier* to organize table metadata and declarative
classes without worrying about order of dependencies, allowing specification
of related constructs via string name. Otherwise feel free to declare every
single class and Table on its own Base and MetaData, it makes no difference.
If you have multiple projects each with their own set of unrelated tables,
there is no need to merge any Base or MetaData objects together. Simply call
create_all() on each MetaData() object as needed. The namespaces remain
entirely separate, as they should.
If OTOH the multiple projects are linking to each other's tables and classes,
then these projects have a dependency on each other. You should change them
such that a common MetaData, and optionally a Base, can be specified from
which they all make usage of - unless you can get away with not declaring any
inter-package relationships or foreign keys with string names.
On Feb 8, 2011, at 9:12 PM, Michael Bayer wrote:
the idea is like this:
myproject/
myproject/__init__.py
myproject/meta.py
myproject/somepackage/__init__.py
myproject/somepackage/a.py
myproject/someotherpackage/__init__.py
myproject/someotherpackage/b.py
myproject/__init__.py:
from myproject.somepackage import a
from myproject.someotherpackage import b
meta.py:
Base = declarative_base()
a.py:
from myproject.meta import Base
b.py:
from myproject.meta import Base
On Feb 8, 2011, at 2:25 PM, Martijn Moeling wrote:
Michael,
Do you ever sleep?
I am not sure I get your point. How do I set up a common Base.
I could do Base= Declarative_base()
from a import A (or * not sure how this differs in this case)
from b import B (or *)
If I do not declare Base in module a I get an Import Error on class
A(Base), the same for importing b.
This gets even more complicated when Base should be build from classes
defined across modules.
at the end there is one main.py importing all modules and this should be
able to define the Main Base.
Any suggestions on how to tackle this. I know have multiple modules and am
glueing everything together. This even gets more problematic with
Inheritance.
one solution could be..
from a import A, BaseA
from b import B, BaseB
Base = declarative_base()
metadata - BaseA.metadata + BaseB.metadata
metadata.create_all(engine)
What I do not get is how the mapper is configured. Normally with
declarative Base it is not used in Production fase for as far as I can
see.
The mapper is part of the Class right? and session does not use Base at
all? but gets it when needed - Session.query(A). ?
Or am I totally wrong on this?
Can I something like this:
from a import * (imports class A and the declarative_base BaseA
[sqlalchemy] MapperExtension.append_result ....
Hi, It is me again with an interesting thing, I've searched the net, this group etc. Not a lot of people seem interested in append_result, I AM!! I am looking for a way to implement the following: I have many tables, a lot with polymorphic inheritance and self and cross references. In order to control available data I have set up a system similar to ACL (Access Control Lists) Depending on Who I am I can get data from the database. I want to do so within the MapperExtension I already have set up to do some before update and before insert def append_result(self, mapper, selectcontext, row, instance, result, **flags): if instance.__tablename__ == 'he': return EXT_STOP else: return EXT_CONTINUE would do such a thing, but I want (for the sake of the code behind that) to continue with a heavily modified instance. To avoid making this long code (a lot of different object types pass through here, remember the polymorhic bit) Does anyone have an interesting approach to this? basically I need to do something like instance= instance_class_type(new, configuration, based, on, the, ACL) Any help would be wonderfull, Martijn -- 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] MapperExtension.append_result ....
I think, I might be helped with the create_instance event I will never ever stop a class from being saved/persistent, it is the other way around. I thought I was able to use joins and or relations to limit for allowed results from a query. With all the polymorphic and self references I have got and the fact that I need to do so for multiple Polymorphic colums I came up with the ACL idea. Im not sure it if will perform, but in the create_instance I will look up the ACL and set additional properties on the instance or create an empty one. This whole system is getting very complex now and limiting returned data involves modifying relationsships a lot. I'm really glad I got that working. Many classes are base on top of that and I the only IT guy working on this together with programmers with a chemistry degree. Who will need the API I am working on to do their stuff without knowing anything about Databases To have that I Inherit polymorphicly, have many-to-many self references, use mixins. I'll have a look at the PreFilteredQuery example you gave me, Any thoughts are helpful, I'll see If I can make up an example with persons and addresses again since the molecule stuff makes it even more confusing.. Will take me some time though Martijn On Feb 7, 2011, at 5:18 PM, Michael Bayer wrote: On Feb 7, 2011, at 10:55 AM, Martijn Moeling wrote: Hi, It is me again with an interesting thing, I've searched the net, this group etc. Not a lot of people seem interested in append_result, I AM!! I am looking for a way to implement the following: I have many tables, a lot with polymorphic inheritance and self and cross references. In order to control available data I have set up a system similar to ACL (Access Control Lists) Depending on Who I am I can get data from the database. I want to do so within the MapperExtension I already have set up to do some before update and before insert Limitations on inserts, updates and queries are best done outside of the Mapper. By the time the mapper is dealing with instructions to persist or load a row, its usually too late, unless you're looking to raise an exception upon certain conditions. For example there's no way to stop the insert from happening inside of a before insert operation, short of raising an exception (maybe that's what you're doing). A SessionExtension.before_flush() OTOH allows you to modify everything that's going to happen before any flush plans are made. Regarding append_result(), its a very old hook from 0.1 that's never had any real use. In this case I would instead be ensure that the undesired rows are not in the result set to start with. The recipe at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PreFilteredQuery is a decent starting point for such a recipe. def append_result(self, mapper, selectcontext, row, instance, result, **flags): if instance.__tablename__ == 'he': return EXT_STOP else: return EXT_CONTINUE would do such a thing, but I want (for the sake of the code behind that) to continue with a heavily modified instance. To avoid making this long code (a lot of different object types pass through here, remember the polymorhic bit) Does anyone have an interesting approach to this? basically I need to do something like instance= instance_class_type(new, configuration, based, on, the, ACL) Any help would be wonderfull, Martijn -- 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. -- 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] nested Polymorphic tables..
Michael,
Thank you for your efforts!
I do not completely understand how it works yet, but I will when I implement
this for my use, I'll have to change it into my real structure.
I'll have to find out which of the technologies suits my situation best, the
Concrete version looks nice and clean but the last one seems more elegant for
future use
Martijn
On Jan 31, 2011, at 7:40 PM, Michael Bayer wrote:
here we are, I was just doing it wrong. This approach is pure declarative,
then sets up specialized polymorphic attributes on Address, PostalAddress
after the fact.
--
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.
On Jan 31, 2011, at 1:11 PM, Michael Bayer wrote:
So I have two approaches here, besides the basic postal_nl in the
discriminator column approach. One just puts a concrete wall at the bottom
of PostalAddress. This is pretty easy, if you don't mind breaking the
chain of inheritance on the DB side.This is postal_as_concrete.py.
The other illustrates the polymorphic-on-concatenation approach. As of yet,
it needs Table metadata to be configured before the classes, so that the
selectable can be constructed and configured with the Address mapper at
creation time. The semi-declarative approach is in
postal_as_joined_semi_decl.py.This is a style of configuration that I
used to think would be more common than it is in the earlier days, so uses
well established mapper configurational patterns, even though they are not
widely used.
If I have time, I may try to improve the polymorphic_on attribute so that
it can be set on an existing mapper - right now the internal state does not
configure itself correctly if you set it after the fact. This would allow
a straight declarative config, with a second step of setting
polymorphic_on, with_polymorphic on the already-existing Address mapper.
These are tested on 0.7 but should work in 0.6, and most probably work more
or less in 0.5.8 (the Session import might need to be altered).
--
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.
postal_as_concrete.pypostal_as_joined_semi_decl.py
On Jan 31, 2011, at 10:58 AM, Michael Bayer wrote:
On Jan 31, 2011, at 4:27 AM, Martijn Moeling wrote:
Hi,
First I need to let you know I do everything declarative..
To make things understandable I have chosen to use objects a bit more
close to real-life.
There is a base object Person with a one-to-many relationship to the table
addresses:
class Person(Base):
__tablename__ = person
Id = Column(Integer, primary_key=True)
.
Addresses = relation('Address',primaryjoin='Address.Person_Id ==
Person.Id', cascade=all)
Address is a polymorphic base type:
class Address(Base):
__tablename__ = adresses
Id = Column(Integer,
primary_key=True)
discriminator = Column(Unicode(20))
__mapper_args__ = {'Polymorphic_on': discriminator}
...
for a lot of address types to be appended to Person.Addresses like:
class EmailAddress(Address):
__tablename__ = emailaddresses
__mapper_args__ = {'polymorphic_identity' : u'emailaddress'}
Id = Column(Integer,ForeignKey('addresses.Id'),
primary_key=True)
Value = Column(Unicode(100))
class MSNAddress(Address):
__tablename__ = msnaddresses
__mapper_args__ = {'polymorphic_identity' : u'msnaddress'}
Id = Column(Integer,ForeignKey('addresses.Id'),
primary_key=True)
class PostalAddress(Address):
__tablename__ = postaladdresses
Country = Column(Unicode(2),primary_key=True)
# Should hold the polymorphic Identity for
subclass like NL,D,UK, US
__mapper_args__ = {'polymorphic_identity' : u'postaladdress'}
# cannot add {'Polymorphic_on' : Counrty} to
this see below in text what happens if I do
Id = Column(Integer,ForeignKey('addresses.Id'),
primary_key=True)
Now I want to make say a Dutch postaladdress, a german postaladdress.
The obvious way to do so is make the base class Polymorphic again but it
will overwrite
[sqlalchemy] nested Polymorphic tables..
Hi,
First I need to let you know I do everything declarative..
To make things understandable I have chosen to use objects a bit more close to
real-life.
There is a base object Person with a one-to-many relationship to the table
addresses:
class Person(Base):
__tablename__ = person
Id = Column(Integer, primary_key=True)
.
Addresses = relation('Address',primaryjoin='Address.Person_Id ==
Person.Id', cascade=all)
Address is a polymorphic base type:
class Address(Base):
__tablename__ = adresses
Id = Column(Integer,
primary_key=True)
discriminator = Column(Unicode(20))
__mapper_args__ = {'Polymorphic_on': discriminator}
...
for a lot of address types to be appended to Person.Addresses like:
class EmailAddress(Address):
__tablename__ = emailaddresses
__mapper_args__ = {'polymorphic_identity' : u'emailaddress'}
Id = Column(Integer,ForeignKey('addresses.Id'),
primary_key=True)
Value = Column(Unicode(100))
class MSNAddress(Address):
__tablename__ = msnaddresses
__mapper_args__ = {'polymorphic_identity' : u'msnaddress'}
Id = Column(Integer,ForeignKey('addresses.Id'),
primary_key=True)
class PostalAddress(Address):
__tablename__ = postaladdresses
Country = Column(Unicode(2),primary_key=True)
# Should hold the polymorphic Identity for
subclass like NL,D,UK, US
__mapper_args__ = {'polymorphic_identity' : u'postaladdress'}
# cannot add {'Polymorphic_on' : Counrty} to
this see below in text what happens if I do
Id = Column(Integer,ForeignKey('addresses.Id'),
primary_key=True)
Now I want to make say a Dutch postaladdress, a german postaladdress.
The obvious way to do so is make the base class Polymorphic again but it will
overwrite the __mapper_args__ of the base class Address
class NLPostalAddress(PostalAddress):
__tablename__ = nlpostaladdress
__mapper_args__ = {'polymorphic_identity' : u'NL'} # will overwrite
the polymorphic Identity of PostalAddress base class
Id = Column(Integer,ForeignKey('postaladdresses.Id'),
primary_key=True)
Street = Column(Unicode(100))
HouseNumber = Column(Unicode(10))
...
...
Person.Addresses.append(NLPostalAddress(Street = somestreet, HouseNum
..)) works but with: Address. discriminator stays empty. and
PostallAddress. Country will become 'NL'.
Which is logical (look at the comments in the code) since the polymorphic_on
: Country makes the mapper forget it was polymorphic on
Address.discriminator
PostalAddress.Id makes the ID part work well..
Is there a way to nest polymorph classes in some way so the above works...
One solution might be Concrete tables but I have not been able to get that
working either? since that would involve a polymorphic union I might be
missing something there... but I do not really understand the documentation
(I'm still using 0.5.8, upgrading is possible)
Again this is a fake data model to make my point
Any suggestions?
Martijn
--
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.
UPDATE PLS HELP [sqlalchemy] Define a relation table
Update, (Still need help, I'm really puzzled on how to do this)
since both Person and Company are polymorphic from Affiliation the relations
are not really self referencing,
So I need help defining the relation and a reference table. The reference table
should however be something like this:
TablenameLLId TablenameR Rid
person 1 company 2
company 2 person 3
So the reference table relations should have a compound key person:1 to
connect to company:2
at the same time company:2 should connect to person:3
I would like to add something like this to my Affiliation class:
relationships =
relation(Relation,primaryjoin=or_(and_(Relation.LTable==discriminator,Relation.LId
==Id),and_(Relation.RTable==discriminator,Relation.RId==Id)))
I know this is wrong but the reference table should also work backwards. There
will also be an Relationtype indicating
Company-employee-Person,
Company-customer-Person
Person-friend-Person
etc. (one Company can have many relations to the same Person, like Companies
can have multiple relations with other companies (like supplier, customer,
partner (like in a project) etc. etc)
also Person1-Father-Person2 will be Person2-Son-Person1 when lookup the
other way around. the Relationtype will be a ForeignKey to another table, the
Order will be dependent on the side of the (current object) Person so there
might be two relations in the Affiliation object . One working on the Left
side of the reference table and the other one from the Right
This is just a small piece, in total there are many Classes based on the
Affiliation object so doing it all there would be nice
Thanks for ANY thoughts, doing this right from the beginning helps me a lot.
The definition of Affiliation, Person and Company can be found below.
Martijn
On Jan 4, 2011, at 9:55 AM, Martijn Moeling wrote:
Hi
I have done the following:
class Affiliation(Base):
__tablename__ = affiliations
Id = Column(Integer, primary_key=True)
FullName= Column(Unicode(255), index = True)
discriminator = Column('type', Unicode(20))
__mapper_args__ = {'polymorphic_on': discriminator}
class Person(Affiliation):
__tablename__ = 'persons'
__mapper_args__ = {'polymorphic_identity' : u'person'}
Id = Column(Integer,ForeignKey('affiliations.Id'),
primary_key=True)
class Company(Affiliation):
__tablename__ = 'companies'
__mapper_args__ = {'polymorphic_identity' : u'company'}
Id = Column(Integer,ForeignKey('affiliations.Id'),
primary_key=True)
which is straight foreward.
What I want do do now is a bit more troublesome, I have been trying many
different things but
class relation(Base):
Parent= reference to one of the Affiliations
Child = reference to one of the Affiliations
Relation_type = column(Integer)(like two
persons can be Father and Son, Two companies can be supplier and customer)
It would be very nice if I can change the Affiliation class to self reference
M:N
Problem with this is that both Parent and child (or left and right side)
have to be checked and updated
Martijn
--
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: UPDATE PLS HELP [sqlalchemy] Define a relation table
Michael,
Thank you,
reading trough your example (and running it)
made me understand a bit more how SQLA works.
I managed to get it all working,
Since I was on my way to something a bit slightly more complex ...one last
Question
What if I would like to:
Class RelationType(Base):
__tablename__ = 'relationtypes'
Id = Column(Integer,
primary_key=True)
ChildRelationType = Column(Unicode(20))
ParentRelationType = Column(Unicode(20))
Some data for information:
Id ChildRelationType
ParentRelationType
1 Parent
Child
2 Supplier
Customer
I will put additional logic to print Mother - Son, in Fact this is not really
what I am doing but I feel the need to make it a bit more understandable for
the MailingList
At the end it will be something with molecules, not really interesting and easy
to understand.
relation_table = Table('relation', Base.metadata,
Column('rid', Integer, ForeignKey('affiliations.id'), primary_key=True),
Column('RelationType', integer, ForeignKey('relationtypes.Id')),
Column('lid', Integer, ForeignKey('affiliations.id'), primary_key=True),
)
Adding this column to the relation_table does not make a difference but
Somehow I need to get a reference to the type of relation
Setting the relation_type MUST be done whilst Appending it. i.e. every
relation between Affiliates must have a reason
Is this possible or is there another way to do such a thing, I feel logically
it should be part of the relation_table.
Thank you Again for your great help. I'm Not really good in Database stuff, but
did/do a lot on the mod_python/mod_wsgi lists so I know about the huge amounts
of time spent
Martijn
On Jan 4, 2011, at 7:20 PM, Michael Bayer wrote:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
relation_table = Table('relation', Base.metadata,
Column('rid', Integer, ForeignKey('affiliations.id'), primary_key=True),
Column('lid', Integer, ForeignKey('affiliations.id'), primary_key=True),
)
class Affiliation(Base):
__tablename__ = affiliations
id = Column(Integer, primary_key=True)
discriminator = Column('type', Unicode(20))
__mapper_args__ = {'polymorphic_on': discriminator}
ParentRelation = relation(
'Affiliation',
primaryjoin=relation_table.c.rid==id,
secondaryjoin=relation_table.c.lid==id,
secondary=relation_table,
backref=ChildRelation)
class Person(Affiliation):
__tablename__ = 'persons'
id = Column(Integer, ForeignKey('affiliations.id'), primary_key=True)
__mapper_args__ = {'polymorphic_identity':'person'}
class Company(Affiliation):
__tablename__ = 'companies'
id = Column(Integer, ForeignKey('affiliations.id'), primary_key=True)
__mapper_args__ = {'polymorphic_identity':'company'}
e = create_engine('sqlite://', echo =True)
Base.metadata.create_all(e)
sess = Session(e)
p1, p2, c1, c2 = Person(), Person(), Company(), Company()
p1.ParentRelation.append(c1)
p1.ChildRelation.append(c2)
c1.ParentRelation.append(p2)
c2.ChildRelation.append(p1)
sess.add_all([p1, p2, c1, c2])
sess.commit()
--
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] Create_all() - Create_JustThese(engine, [Table1,Table2,....])?
Simon (and the others), thank you! the someClass.__table__works... Kind regards, Martijn On Oct 27, 2010, at 5:02 PM, King Simon-NFHD78 wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling Sent: 27 October 2010 15:47 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Create_all() - Create_JustThese(engine, [Table1,Table2,])? Mike, I have checked the docs but somehow overlooked it multiple times. I now looked at the index and found it as an MetaData method and found it. Using it gives me all sorts of errors now, I need to get some reference to the Table object hidden in the Internals somewhere when using declarative. I am an extremely experienced (Technical low level) programmer but I have always been able to let others do the database stuff (Since I have allways hated it sooo much, Personally I think Databases are pure torture), now that I am in the need of databases I am suddenly facing my inexperience with the technical language. The SQLAlchemy docs look like chinese to me and often I just do not know how you DBA's call things and therefore searching the documentation is a problem if you do not know what to search for. Again I have searched the Docs, can anyone tell me how to get a reference to a Table object when it is defined the declarative way? Martijn The Synopsis for the declarative extension says the following: -- The resulting table and mapper are accessible via __table__ and __mapper__ attributes on the SomeClass class: # access the mapped Table SomeClass.__table__ # access the Mapper SomeClass.__mapper__ -- http://www.sqlalchemy.org/docs/orm/extensions/declarative.html#synopsis Hope that helps, Simon -- 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] Working with mapper objects without saving them
I did this in one of my previous projects. what I did was to make a python module, with the right SQLAlchemy includes and all the mapper objects. what I did not do is connecting to the database (it makes the class module database independent) What it does is it implements the iCal standard, it can read iCal files and export iCal files. in one occasion it is being used by a CalDav server (completely written in python) and of course it needs to be interacting with the database. in the other occasion it is being used as a man-in-the-middle between an CRM application (with its own calendar) and Microsoft Exchange, in that case the class module is being used as a conversion utility and the data is actually never ever put into a database. It has both relationships and backrefs and works fine in both occasions. Let me know if you have any trouble, I use SQLalchemy a lot for handling data without saving it to a database. SQLAlchemy works fine when no database connection is present Martijn On Oct 27, 2010, at 5:41 PM, Michael Bayer wrote: On Oct 27, 2010, at 11:31 AM, Michael Elsdörfer wrote: I have a mapper-based data model that uses relationships() extensively. In one particular instance, to implement a sort of preview feature, I'd like to work with a set of instances of my model class without saving them to the database, i.e. without adding them to the session. So I'm not calling session.add() for the objects I newly create, but if such a preview object as a relationship with another, existing object (i.e. with a session state of Persistent), then when the new and the existing objects are connected through that relationship, the latter is marked as dirty, causing both to be saved. Solutions that I've come up with so far: * Manually call expunge() on the preview objects * Set the proper cascade-settings for the relationships. Both I'm not entirely fond of; in particular, the latter prevents me from using the cascades I really want in all other cases. Is there a better way to deal with this? there's a new flag on relationship() called cascade_backrefs. It prevents save-update cascade from occurring for backrefs - in other words, the save-update cascade moves only left to right. So if you were to say my_transient_object.some_related = some_related, if some_related were in the Session, it would not cascade my_transient_object in. We are possibly going to make this the default in 0.7. An example of the flag is at http://www.sqlalchemy.org/docs/orm/session.html#cascades . -- 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. -- 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] Create_all() - Create_JustThese(engine, [Table1,Table2,....])?
Hi, I have a huge definition module where I create Python objects and use declarative. Since not all databases (Multiple for different customers) need all tables I do not like to use create_all is there any way to create just the tables I really need (according to some config list or so) say: class C1(Base): __tablename__ = C1 .. class C2(Base): __tablename__ = C2 .. class C3(Base): __tablename__ = C3 .. create_JustThese(engine, [C1,C3]) - table C2 is NOT created Please do not reply with why I would want this, I just want to know if it is possible and it would help me out big time if it is Martijn -- 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] create_all() trows table already exist, is not there and is not created....
Hi, I have a weird problem. (Mysql) When I do a create_all(), i get the error : File /Library/Python/2.6/site-packages/SQLAlchemy-0.5.8-py2.6.egg/sqlalchemy/engine/base.py, line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exc.OperationalError: (OperationalError) (1050, Table 'calendarevents' already exists) '\nCREATE TABLE `CalendarEvents` (\n\t`Id` INTEGER NOT NULL AUTO_INCREMENT, \n\t`CalendarId` INTEGER, \n\t`Allday` BOOL, \n\t`DtStart` DATETIME, \n\t`DtEnd` DATETIME, \n\t`DTStamp` DATETIME, \n\t`Class` VARCHAR(20), \n\t`Created` DATETIME, \n\t`Description` TEXT, \n\t`Duration` DATETIME, \n\t`LastModified` DATETIME, \n\t`Location` TEXT, \n\t`Priority` VARCHAR(10), \n\t`RecurId` DATETIME, \n\t`Sequence` INTEGER, \n\t`Status` TEXT, \n\t`Summary` TEXT, \n\t`Transparent` VARCHAR(15), \n\tuid TEXT, \n\turl TEXT, \n\t`Organizer` VARCHAR(100), \n\t`OrganizerCN` VARCHAR(100), \n\t`OrganizerDIR` VARCHAR(50), \n\t`OrganizerSendBy` VARCHAR(50), \n\t`OrganizerLanguage` VARCHAR(50), \n\tPRIMARY KEY (`Id`), \n\t FOREIGN KEY(`CalendarId`) REFERENCES `Calendars` (`Id`)\n)\n\n' () even though the table is not there!, Only the Calendars table is created Any suggestions? Martijn -- 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] Declerative Relation trouble
Hi All, I am having a very bad day (or two to be honest), spending time reading error messages. I am sorry to say but the SQLALCHEMY documentation is not very helpful when using declarative_base when it is about relations.. It should be very easy for you database guy's (which I'm not, I'm more of a protocoll/low level programmer) The problem. I am implementing a calendaring system based on the ical specification. Consider the following ( I'll keep it as simple as possible): I am using MySQL 5.+ SQLAlchemy 0.5.6 Class | __tablename__ +-- Calendar| Calendars Event | CalendarEvents Todo| CalendarTodos Alarm | CalendarAlarms XProp | CalendarXProps (Tablenames do not reflect Objectnames, sorry for that, it is a requirement) so: class XProp(Base): __tablename__ = CalendarXProps Id = Column(Integer, primary_key=True) EventId = the Id of the event this XProp belongs to AlarmId = the Id of the Alarm this Xprop belongs to CalendarId = The Id of the Calendar .. Name= Column(... Value = Column(.. (either EventId or AlarmId or CalendarId is used) class Event(Base): __tablename__ =CalendarEvents Id = Column(Integer, primary_key=True) CalendarId = the ID of the Calendar this Object belongs to XProps = relation zero or more XProp (I would like to do: for XProp in self.XProps: .. ) X = Column( Y = Column(... def __init__(self): self.Xprops = [ ] self.X = X self.Y = 576234 class Alarm(Base): __tablename__ =CalendarAlarms Id = Column(Integer, primary_key=True) CalendarId = the ID of the Calendar this Object belongs to XProps = relation zero or more XProp X = Column( Y = Column(... def __init__(self): self.Xprops = [ ] self.X = X klsdjkladsjkd ddsa self.Y = 5 class Calendar(Base): __tablename__ = Calendars Id = Column(Integer, primairy_key=True) Events = relation One Calendar zero or more Events (i.e. [ ], [Event,Events,Event,] Alarms = relation One Calendar zero or more Alarms XProps = relation, One Calendar zero or more XProp X = Column( Y = Column(... def __init__(self): self.Events = [ ] self.Alarms = [ ] self.Xprops = [ ] self.X = X self.Y = 576234 cal = Calendar() the X, Y, Name and Value columns indicate example record data) The Errormessages are allways on the line with cal = Calendar(), saying the original errormessage has probaby been lost due to hasattr. Not very helpfull since over 40 objecttypes are trying to do the same thing. Whatever I do with ForeignKey, backref, relation keeps popping up errors whenever I try to make a Calendar Instance (e.g. cal = Calendar () ) One or mappers failed to compile. The big problem is that the tablenames are not equal to the Object names and that seems to confuse SQLAlchemy . Many errors (every time I run my program It generates a different error) Like table 'Calendar' not found (I never said there is a table Calendar, that is the objectname, I specify Calendars.Id) Can someone please help me with the setting up the relations. in the example above to give me a starting point. (there are about 40 XProp alike Objects and some Alarm/Even alike objects, I have reduced the above example to the minimum possible) So what I need is help with the relation and Foreign Key columns including the backrefs (I need to search CalendarEvents and find the corresponding Calendar) Thank you very mutch Martijn --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Declerative Relation trouble
Hi Simon,
(I do things a little different on the import side)
Working example (very minimised):
from sqlalchemy import
Integer,Column,DateTime,Unicode,UnicodeText,Boolean,ForeignKey,Interval
from sqlalchemy.ext.declarative import declarative_base
import sqlalchemy.orm as orm
Base = declarative_base()
class Event(Base):
__tablename__ = CalendarEvents
Id = Column(Integer, primary_key=True)
CalendarId = Column(ForeignKey('Calendars.Id'))
class Calendar(Base):
__tablename__ = Calendars
Id = Column(Integer, primary_key=True)
UserId = Column(Integer, index=True)
ProdId = Column(Unicode(255))
Version = Column(Unicode(5))
CalScale= Column(Unicode(20))
Method = Column(Unicode(10))
Events = orm.relation(Event,backref='Calendar')#,
cascade=all)
if __name__ == '__main__':
engine = create_engine('sqlite:///')
Base.metadata.create_all(bind=engine)
Session = orm.sessionmaker(bind=engine)()
cal = Calendar()
when I run this I get:
Traceback (most recent call last):
File /var/www/PyWebOs/caltst.py, line 28, in module
cal = Calendar()
File string, line 4, in __init__
File /usr/local/lib/python2.6/site-packages/sqlalchemy/orm/
state.py, line 71, in initialize_instance
fn(self, instance, args, kwargs)
File /usr/local/lib/python2.6/site-packages/sqlalchemy/orm/
mapper.py, line 1810, in _event_on_init
instrumenting_mapper.compile()
File /usr/local/lib/python2.6/site-packages/sqlalchemy/orm/
mapper.py, line 666, in compile
Message was: %s % mapper._compile_failed)
InvalidRequestError: One or more mappers failed to compile. Exception
was probably suppressed within a hasattr() call. Message was: One or
more mappers failed to compile. Exception was probably suppressed
within a hasattr() call. Message was: One or more mappers failed to
compile. Exception was probably suppressed within a hasattr() call.
Message was: Could not find table 'Calendar' with which to generate a
foreign key
which is excacly the same as I got.
I have done so mutch in python/sqlalchemy that I feel extremely stupid
not to get this working, it might be just a case of overreading the
problem
Martijn
On Oct 15, 2009, at 4:10 PM, King Simon-NFHD78 wrote:
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling
Sent: 15 October 2009 14:42
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Declerative Relation trouble
Hi All,
I am having a very bad day (or two to be honest), spending
time reading error messages.
I am sorry to say but the SQLALCHEMY documentation is not very
helpful
when using declarative_base when it is about relations..
Without a runnable example which actually shows your problem, it's
very
difficult to debug. Here's something I cobbled together based on your
description. It may not be exactly right, but it seems to work:
import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class XProp(Base):
__tablename__ = CalendarXProps
Id = sa.Column(sa.Integer, primary_key=True)
EventId = sa.Column(sa.ForeignKey('CalendarEvents.Id'))
AlarmId = sa.Column(sa.ForeignKey('CalendarAlarms.Id'))
CalendarId = sa.Column(sa.ForeignKey('Calendars.Id'))
Name = sa.Column(sa.String(20))
Value = sa.Column(sa.String(20))
class Event(Base):
__tablename__ = CalendarEvents
Id = sa.Column(sa.Integer, primary_key=True)
CalendarId = sa.Column(sa.ForeignKey('Calendars.Id'))
XProps = orm.relation(XProp, backref='Events')
class Alarm(Base):
__tablename__ = CalendarAlarms
Id = sa.Column(sa.Integer, primary_key=True)
CalendarId = sa.Column(sa.ForeignKey('Calendars.Id'))
XProps = orm.relation(XProp, backref='Alarms')
class Calendar(Base):
__tablename__ = Calendars
Id = sa.Column(sa.Integer, primary_key=True)
Events = orm.relation(Event, backref='Calendar')
Alarms = orm.relation(Alarm, backref='Calendar')
XProps = orm.relation(XProp, backref='Calendar')
if __name__ == '__main__':
engine = sa.create_engine('sqlite:///')
Base.metadata.create_all(bind=engine)
Session = orm.sessionmaker(bind=engine)()
cal = Calendar()
cal.Events.append(Event(XProps=[XProp(Name='Hello', Value='World'),
XProp(Name='foo', Value='bar')]))
Session.add(cal)
Session.flush()
print cal
for event in cal.Events:
print event
for prop in event.XProps:
print prop
Hope that helps,
Simon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post
[sqlalchemy] Re: Declerative Relation trouble
I downgraded SQLA to version 0.5.5, no luck, (Simon) what version of
python are you using?
On Oct 15, 2009, at 4:45 PM, Martijn Moeling wrote:
Hi Simon/all,
When I run your example i get:
Traceback (most recent call last):
File /var/www/PyWebOs/caltst.py, line 41, in module
cal = Calendar()
File string, line 4, in __init__
File /usr/local/lib/python2.6/site-packages/sqlalchemy/orm/
state.py, line 71, in initialize_instance
fn(self, instance, args, kwargs)
File /usr/local/lib/python2.6/site-packages/sqlalchemy/orm/
mapper.py, line 1810, in _event_on_init
instrumenting_mapper.compile()
File /usr/local/lib/python2.6/site-packages/sqlalchemy/orm/
mapper.py, line 666, in compile
Message was: %s % mapper._compile_failed)
InvalidRequestError: One or more mappers failed to compile. Exception
was probably suppressed within a hasattr() call. Message was: One or
more mappers failed to compile. Exception was probably suppressed
within a hasattr() call. Message was: One or more mappers failed to
compile. Exception was probably suppressed within a hasattr() call.
Message was: Could not find table 'Calendar' with which to generate a
foreign key
So I am starting to get the impression there is something wrong beyond
my code.
Your and my example are ruling out MySQL (or the Mysql part of
SQLAlchemy)
Now there is python 2.6.2 and SQLA version 0.5.6 on a Linux box
Anyone having trouble with these versions?
Martijn
On Oct 15, 2009, at 4:10 PM, King Simon-NFHD78 wrote:
import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class XProp(Base):
__tablename__ = CalendarXProps
Id = sa.Column(sa.Integer, primary_key=True)
EventId = sa.Column(sa.ForeignKey('CalendarEvents.Id'))
AlarmId = sa.Column(sa.ForeignKey('CalendarAlarms.Id'))
CalendarId = sa.Column(sa.ForeignKey('Calendars.Id'))
Name = sa.Column(sa.String(20))
Value = sa.Column(sa.String(20))
class Event(Base):
__tablename__ = CalendarEvents
Id = sa.Column(sa.Integer, primary_key=True)
CalendarId = sa.Column(sa.ForeignKey('Calendars.Id'))
XProps = orm.relation(XProp, backref='Events')
class Alarm(Base):
__tablename__ = CalendarAlarms
Id = sa.Column(sa.Integer, primary_key=True)
CalendarId = sa.Column(sa.ForeignKey('Calendars.Id'))
XProps = orm.relation(XProp, backref='Alarms')
class Calendar(Base):
__tablename__ = Calendars
Id = sa.Column(sa.Integer, primary_key=True)
Events = orm.relation(Event, backref='Calendar')
Alarms = orm.relation(Alarm, backref='Calendar')
XProps = orm.relation(XProp, backref='Calendar')
if __name__ == '__main__':
engine = sa.create_engine('sqlite:///')
Base.metadata.create_all(bind=engine)
Session = orm.sessionmaker(bind=engine)()
cal = Calendar()
cal.Events.append(Event(XProps=[XProp(Name='Hello', Value='World'),
XProp(Name='foo', Value='bar')]))
Session.add(cal)
Session.flush()
print cal
for event in cal.Events:
print event
for prop in event.XProps:
print prop
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re:[runs one one installation not on the other] Declerative Relation trouble
Hi All, bad problem I have trouble making relations in SQLA. My code runs on Simons computer but his (and mine) not. I downgraded SLQA from 0.5.6. to 0.5.5 to have the same version, still no luck. I run Python 2.6.2 and Simon 2.5.1 Downgrading Python is a hassle at the moment (Mod_python) so is there anything I can try? (Database changes give me the same results on both mysql and sqllite so that seems no problem) Martijn On Oct 15, 2009, at 4:59 PM, King Simon-NFHD78 wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling Sent: 15 October 2009 15:55 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: Declerative Relation trouble I downgraded SQLA to version 0.5.5, no luck, (Simon) what version of python are you using? 2.5.1 on Linux Simon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: [runs one one installation not on the other] Declerative Relation trouble
Mod_python has nothing to do with this project, so I run it from idle within X On Oct 15, 2009, at 5:23 PM, King Simon-NFHD78 wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling Sent: 15 October 2009 16:21 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re:[runs one one installation not on the other] Declerative Relation trouble Hi All, bad problem I have trouble making relations in SQLA. My code runs on Simons computer but his (and mine) not. I downgraded SLQA from 0.5.6. to 0.5.5 to have the same version, still no luck. I run Python 2.6.2 and Simon 2.5.1 Downgrading Python is a hassle at the moment (Mod_python) so is there anything I can try? (Database changes give me the same results on both mysql and sqllite so that seems no problem) Martijn Are you running the test script from mod_python, or from the command line? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: [runs one one installation not on the other] Declerative Relation trouble
(in the mean time I drove home. dinner soon) Indeed, running it from command line changes things, Strange since I even rebooted the machine in the process, but since I was messing with the code It could well have been really broken. Your sample works from the command line, mine still gives the error but needs changing. I will investigate some more, I have been using Idle with SQLA for more than a year now. but now I'm thinking about it, I use mod_python to actually run the code. Any suggestions for a alternative? I do not like eclipse very mutch (same with Aptana) I'll keep you posted! Martijn On Oct 15, 2009, at 5:33 PM, King Simon-NFHD78 wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling Sent: 15 October 2009 16:27 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: [runs one one installation not on the other] Declerative Relation trouble Mod_python has nothing to do with this project, so I run it from idle within X I have a feeling that Idle doesn't necessarily spawn a separate process to run your code, so you may have old definitions of your objects in memory. Try running it directly from the command line. --~--~-~--~~~---~--~~ 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] Do not use the idle -n command when using SQLA
Ok here is the deal Since I sometimes need to run multiple instances of idle in the same shell,so I use the -n switch by default: 8 [removed~u...@removed~host ~]# idle --help Error: option --help not recognized USAGE: idle [-deins] [-t title] [file]* idle [-dns] [-t title] (-c cmd | -r file) [arg]* idle [-dns] [-t title] - [arg]* -h print this help message and exit -n run IDLE without a subprocess (see Help/IDLE Help for details) 8 So To complete Simons suggestion about idle, Do not use the -n switch on idle when playing with SQLAlchemy. On Oct 15, 2009, at 6:55 PM, Martijn Moeling wrote: (in the mean time I drove home. dinner soon) Indeed, running it from command line changes things, Strange since I even rebooted the machine in the process, but since I was messing with the code It could well have been really broken. Your sample works from the command line, mine still gives the error but needs changing. I will investigate some more, I have been using Idle with SQLA for more than a year now. but now I'm thinking about it, I use mod_python to actually run the code. Any suggestions for a alternative? I do not like eclipse very mutch (same with Aptana) I'll keep you posted! Martijn On Oct 15, 2009, at 5:33 PM, King Simon-NFHD78 wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Martijn Moeling Sent: 15 October 2009 16:27 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Re: [runs one one installation not on the other] Declerative Relation trouble Mod_python has nothing to do with this project, so I run it from idle within X I have a feeling that Idle doesn't necessarily spawn a separate process to run your code, so you may have old definitions of your objects in memory. Try running it directly from the command line. --~--~-~--~~~---~--~~ 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] Create Table errors on mysql...
Hi, I have a python module where I am implementing several classes. When I do a metadata.create_all(engine) every time Mysql trows an exception (1064, PROGRAMMING ERROR), but on a different table, I think SQLAlchemy is behaving different every time I run the program. ie (one of the definitions failing): class Journal(Base): __tablename__ = CalendarJournals Id = Column(Integer(), primary_key=True,quote=True) Attendees = relation(Attendee, cascade=all) Attachments = relation(Attachment, cascade=all) Catagories = relation(Catagorie, cascade=all) Comments= relation(Comment, cascade=all) Contacts= relation(Contact, cascade=all) ExDates = relation(ExDate, cascade=all) ExRules = relation(ExRule, cascade=all) RDates = relation(RDate, cascade=all) Related = relation(Relate, cascade=all) RRules = relation(RRule, cascade=all) RStatusses = relation(RStatus, cascade=all) XProps = relation(XProp, cascade=all) Class = Column(Unicode(20),quote=True) Created = Column(DateTime(),quote=True) Description = Column(UnicodeText(),quote=True) DTStamp = Column(DateTime(),quote=True) DtStart = Column(DateTime(),quote=True) LastModified= Column(DateTime(),quote=True) RecurId = Column(Unicode(),quote=True) Sequence= Column(Integer(),quote=True) Status = Column(Unicode(),quote=True) Summary = Column(Unicode(),quote=True) uid = Column(Unicode(),quote=True) url = Column(Unicode(),quote=True) ProgrammingError: (ProgrammingError) (1064, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' \n\t`Sequence` INTEGER, \n \t`Status` VARCHAR, \n\t`Summary` VARCHAR, \n\t`uid` VARCHAR,' at line 9) '\nCREATE TABLE `CalendarJournals` (\n\t`Id` INTEGER NOT NULL AUTO_INCREMENT, \n\t`Class` VARCHAR(20), \n\t`Created` DATETIME, \n \t`Description` TEXT, \n\t`DTStamp` DATETIME, \n\t`DtStart` DATETIME, \n\t`LastModified` DATETIME, \n\t`RecurId` VARCHAR, \n\t`Sequence` INTEGER, \n\t`Status` VARCHAR, \n\t`Summary` VARCHAR, \n\t`uid` VARCHAR, \n\t`url` VARCHAR, \n\tPRIMARY KEY (`Id`)\n)\n\n' () CREATE TABLE `CalendarJournals` ( `Id` INTEGER NOT NULL AUTO_INCREMENT, `Class` VARCHAR(20), `Created` DATETIME, `Description` TEXT, `DTStamp` DATETIME, `DtStart` DATETIME, `LastModified` DATETIME, `RecurId` VARCHAR, `Sequence` INTEGER, `Status` VARCHAR, `Summary` VARCHAR, `uid` VARCHAR, `url` VARCHAR, PRIMARY KEY (`Id`) ) The Error: ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' `Sequence` INTEGER, `Status` VARCHAR, `Summary` VARCHAR, `uid` VARCHAR, `url` V' Since the syntax seems to be correct and Sequence is Quoted with BackQuotes (`). I am puzzled what to do to fix this. Please help, Martijn --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Create Table errors on mysql...
Thanks Guy's! Kinda stupid, but that happens with the use of examples. Martijn On Oct 14, 2009, at 3:34 PM, limodou wrote: On Wed, Oct 14, 2009 at 9:03 PM, Martijn Moeling mart...@xs4us.nu wrote: Hi, I have a python module where I am implementing several classes. When I do a metadata.create_all(engine) every time Mysql trows an exception (1064, PROGRAMMING ERROR), but on a different table, I think SQLAlchemy is behaving different every time I run the program. ie (one of the definitions failing): class Journal(Base): __tablename__ = CalendarJournals Id = Column(Integer(), primary_key=True,quote=True) Attendees = relation(Attendee, cascade=all) Attachments = relation(Attachment, cascade=all) Catagories = relation(Catagorie, cascade=all) Comments= relation(Comment, cascade=all) Contacts= relation(Contact, cascade=all) ExDates = relation(ExDate, cascade=all) ExRules = relation(ExRule, cascade=all) RDates = relation(RDate, cascade=all) Related = relation(Relate, cascade=all) RRules = relation(RRule, cascade=all) RStatusses = relation(RStatus, cascade=all) XProps = relation(XProp, cascade=all) Class = Column(Unicode(20),quote=True) Created = Column(DateTime(),quote=True) Description = Column(UnicodeText(),quote=True) DTStamp = Column(DateTime(),quote=True) DtStart = Column(DateTime(),quote=True) LastModified= Column(DateTime(),quote=True) RecurId = Column(Unicode(),quote=True) Sequence= Column(Integer(),quote=True) Status = Column(Unicode(),quote=True) Summary = Column(Unicode(),quote=True) uid = Column(Unicode(),quote=True) url = Column(Unicode(),quote=True) ProgrammingError: (ProgrammingError) (1064, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' \n\t`Sequence` INTEGER, \n\t`Status` VARCHAR, \n\t`Summary` VARCHAR, \n\t`uid` VARCHAR,' at line 9) '\nCREATE TABLE `CalendarJournals` (\n\t`Id` INTEGER NOT NULL AUTO_INCREMENT, \n \t`Class` VARCHAR(20), \n\t`Created` DATETIME, \n\t`Description` TEXT, \n \t`DTStamp` DATETIME, \n\t`DtStart` DATETIME, \n\t`LastModified` DATETIME, \n \t`RecurId` VARCHAR, \n\t`Sequence` INTEGER, \n\t`Status` VARCHAR, \n\t`Summary` VARCHAR, \n\t`uid` VARCHAR, \n\t`url` VARCHAR, \n\tPRIMARY KEY (`Id`)\n)\n\n' () CREATE TABLE `CalendarJournals` ( `Id` INTEGER NOT NULL AUTO_INCREMENT, `Class` VARCHAR(20), `Created` DATETIME, `Description` TEXT, `DTStamp` DATETIME, `DtStart` DATETIME, `LastModified` DATETIME, `RecurId` VARCHAR, `Sequence` INTEGER, `Status` VARCHAR, `Summary` VARCHAR, `uid` VARCHAR, `url` VARCHAR, PRIMARY KEY (`Id`) ) The Error: ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' `Sequence` INTEGER, `Status` VARCHAR, `Summary` VARCHAR, `uid` VARCHAR, `url` V' Since the syntax seems to be correct and Sequence is Quoted with BackQuotes (`). I am puzzled what to do to fix this. Please help, Martijn I think VARCHAR need a length, but most of your table field has no length. -- I like python! UliPad The Python Editor: http://code.google.com/p/ulipad/ UliWeb simple web framework: http://uliwebproject.appspot.com My Blog: http://hi.baidu.com/limodou --~--~-~--~~~---~--~~ 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] SA 0.5 rc1 - Mysql Unicode(1) decode error
Hi I needed a Unicode(1) Column in one of my tables. It was translated into a char(1) column in MySQL. When querying the table, I get a: AttributeError: 'Set' object has no attribute 'decode' Which disappears if I make the column a Unicode(2), so there might be a small bug in the MySQL code translating Unicode(1) to char(1) ??? Martijn Van: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED] Namens Heston James - Cold Beans Verzonden: Thursday, October 09, 2008 5:33 PM Aan: sqlalchemy@googlegroups.com Onderwerp: [sqlalchemy] Can't connect to local MySQL server Hello Guys, I'm receiving errors in my application on a fairly regular basis now and I'm not sure how to begin solving it. Please find attached a backtrace for the error. It seems that its struggling to connect to the MySQL server, however I get this after the application has been running and querying the database for some time. Any ideas what might be causing this? I'd appreciate your thoughts. The code which throws the error is a very simple query(some_object).get(id) Cheers all, Heston --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mysql utf8 encoding problem
I had similar problems, part of it turned out to be the encoding send
from the browser.
on forms you can set the encoding (in the formtag, check
http://www.w3schools.com
if the page is other than UTF-8 (check your browser view-
encoding menu).
I changed everything to utf-8 by setting headers and configuring the
webserver (apache in my case)
I'm not sure if you are developing web stuff though,
MySQLdb behaves odd when leaving the out the encoding though
so adding it into SA is a help which saves me from patching every SA
release as I do!
Martijn
On Oct 9, 2008, at 2:54 AM, jason kirtland wrote:
joelanman wrote:
Hi,
Firstly - I'm hugely impressed with SQLAlchemy - it's really helped
me
a lot with my new project.
I'm having problems storing international characters in mysql using
SQLAlchemy. For example:
école—school
looks like this in mysql:
école—school
I'm using the following engine call:
engine = create_engine(config.db, encoding='utf-8')
and using Unicode as the column type:
Column('content', UnicodeText),
and utf8 for the table:
mysql_charset='utf8'
I'm pretty sure all my mySQL options are set to utf8. This looks
really similar to a 'double encoding' issue I found while searching
the group, but it seems that was fixed in python-mysql 1.2.2, which
is
what I'm using.
Any help would be much appreciated.
Most likely you just need to configure the db-api's client encoding by
adding ?charset=utf8 onto your connection URL.
Enough folks have hit this recently that I'm (again) considering
passing
through the engine encoding= parameter to the MySQLdb connection
setup.
I've resisted the urge for a while because we don't to my knowledge
re-configure any db-apis in any of the backends. But this keeps
coming
up despite being documented in the mysql section of the docs, and last
time I traced through it, it seemed like MySQLdb was ignoring the
server's configured connection_encoding so a little assist from the SA
side would probably be useful.
I'll look at sneaking that into the upcoming rc2 unless the
implementation is untenable for some reason or there's an outcry.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] limit the set of returned columns
Hi, Yesterday I was searching this group and the SA 5.0 doc to get something working. Somewere I came across an option to specify the columnames to return (and getting a tuple or so) Let me explain: Keep the following in mind: I have a table which stores files defined with Declarative_base: Class file(Base): __tablename__ = sometable Id = Column(Integer, primary_key=True) Filename = Column(String(255), index=True) Permissions = Column(Integer) Store= Column(Someblobtype) Now I need to build a query which results in the following information in any form: Filename, permissions and the size of Store I think I need func from sqlalchemy.sql but whatever I try I cannot find any sizeof or alike, I know MySQL has something like that. To build a list of files (in realality it is a store for a webdav server and I greatly simplified this example, I need this since listing a directory causes Martijn --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: limit the set of returned columns
Thanx! In Mysql it seems to be func.octet_length(OBJ.columname) though All the documentation is very confusing, I started out with essential SQLAlchemy which is completely outdated by now... The online docs are not very helpful if you don't know what yu're lokking for. The column specification increases my performance a lot Martijn Moeling -Oorspronkelijk bericht- Van: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED] Namens Empty Verzonden: Tuesday, October 07, 2008 3:18 PM Aan: sqlalchemy@googlegroups.com Onderwerp: [sqlalchemy] Re: limit the set of returned columns Hi, Yesterday I was searching this group and the SA 5.0 doc to get something working. Somewere I came across an option to specify the columnames to return You can specify the column names as part of the query, like session.query(User.name, User.phone). Filename, permissions and the size of Store I think I need func from sqlalchemy.sql but whatever I try I cannot find any sizeof or alike, I know MySQL has something like that. func.length() Michael --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: limit the set of returned columns
If found that, but that is exactly the opposite of what I want, since with webdav first is looked for the files, a user might choose one of them to open/edit en than the blob needs to be loaded On Oct 7, 2008, at 4:27 PM, Michael Bayer wrote: another option for blob columns and similar is the deferred() property, which will only load that column on the instance when first accessed. this is also in the docs (and probably is in the book too). On Oct 7, 10:03 am, Martijn Moeling [EMAIL PROTECTED] wrote: Thanx! In Mysql it seems to be func.octet_length(OBJ.columname) though All the documentation is very confusing, I started out with essential SQLAlchemy which is completely outdated by now... The online docs are not very helpful if you don't know what yu're lokking for. The column specification increases my performance a lot Martijn Moeling -Oorspronkelijk bericht- Van: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED] Namens Empty Verzonden: Tuesday, October 07, 2008 3:18 PM Aan: sqlalchemy@googlegroups.com Onderwerp: [sqlalchemy] Re: limit the set of returned columns Hi, Yesterday I was searching this group and the SA 5.0 doc to get something working. Somewere I came across an option to specify the columnames to return You can specify the column names as part of the query, like session.query(User.name, User.phone). Filename, permissions and the size of Store I think I need func from sqlalchemy.sql but whatever I try I cannot find any sizeof or alike, I know MySQL has something like that. func.length() Michael --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: inhirit columnnames from another class
I accedently posted the message premature, an was in the process of completing it. The answer seems suitable.. Thanks!! -Oorspronkelijk bericht- Van: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED] Namens Michael Bayer Verzonden: Monday, October 06, 2008 5:19 PM Aan: sqlalchemy Onderwerp: [sqlalchemy] Re: inhirit columnnames from another class not sure if this is what youre asking but it sounds like this: http://groups.google.com/group/sqlalchemy/browse_thread/thread/5c629ce3e27916c7# On Oct 6, 11:02 am, JASH [EMAIL PROTECTED] wrote: Hi ! I am a huge fan of declerative base but now I run into a problem... Say I have a class that stores files in a database: Base = decclarative_base() class Document(Base): Id = Column(Integer, primary_key=True) Filename = Column(String(255), Unique = True) Contents = Column(someblob .) Now I want to add a class davproperties to inhirit some Columns needed to get it working with my pythonic webdav server I wrote for use with mod_python class davproperties() getcontentype Column --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: problems with quoted Columnnames
I have been investigating some more and solved the issue
by changing the unbound Unicode to UnicodeText
I am using SA 0.5r1 and mysql 5.x
the unbound Unicode worked with 0.4, I had to upgrade to 0.5 for some
other stuff I used and this confused me.
Nevertheless the Fields defined in the DavBaseClass are still not
Quoted and the ones defined in the Javascript class are...
Maybe something in the DeclerativeMeta left from 0.4? the quoting
seems to be changed
Thanks!
On Oct 6, 2008, at 8:18 PM, Michael Bayer wrote:
test case:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import DeclarativeMeta,
declarative_base
class DavBaseClass(DeclarativeMeta):
def __init__(self,ClassName,Bases,dict_):
dict_['displayname']=
Column(Unicode(255),quote=True)
return DeclarativeMeta.__init__(self,ClassName,Bases,dict_)
Base = declarative_base(metaclass=DavBaseClass)
engine = create_engine('postgres://scott:[EMAIL PROTECTED]/test',
echo=True)
class Javascript(Base):
__tablename__ = Javascript
Id = Column(Integer, primary_key = True)
Source = Column(Unicode())
Minified= Column(Unicode())
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
output (showing just the CREATE TABLE portion):
CREATE TABLE Javascript (
Id SERIAL NOT NULL,
Source VARCHAR,
Minified VARCHAR,
displayname VARCHAR(255),
PRIMARY KEY (Id)
)
note that quote=True will not have this effect if you're using 0.4.
Also the quoting style you're using there seems to indicate the usage
of MySQL, but a datatype of VARCHAR on that platform would not be
accepted, so questions persist...
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
