[sqlalchemy] best way of connecting to sqlserver from linux using SA?
Hi All, What's now the best way to connect to a Microsoft SQL Server instance from a linux box? What's the recommended driver? cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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
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 as an ExtColumn but would
store it's value in JSid and not mess with the functionality of SA and or the
database as jsid would NOT interfere with Id.
Solving it this way, the overhead is only on ExtColumn based Columns which
saves a lot. Again this would be unusual use of SA for as far as I can see.
reading the docs for the Column object:
The name of this column as represented in the database. This argument may be
the first positional argument, or specified via keyword.
Names which contain no upper case characters will be treated as case
insensitive names, and will not be quoted unless they are a reserved word.
Names with any number of upper case characters will be quoted and sent exactly.
Note that this behavior
Re: [sqlalchemy] best way of connecting to sqlserver from linux using SA?
On 05-03-2012 11:29, Chris Withers wrote:
Hi All,
What's now the best way to connect to a Microsoft SQL Server instance
from a linux box? What's the recommended driver?
cheers,
Chris
I'm using pyodbc and freetds packages to connect to existing mssql
server 2008 running on windows.
here are my configurations:
FreeTDS config:
-
tkizilay@tkizilay-linux:~$ cat /etc/freetds/freetds.conf
[TDS]
Description = FreeTDS Driver for Linux MSSQL on Win32
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
[mssqlserver]
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
host = 192.168.0.10
port = 1433
tds version = 8.0
client charset = UTF-8
---
and this is /etc/odbc.ini:
--
tkizilay@tkizilay-linux:~$ cat /etc/odbc.ini
[MSSQLDB]
Driver = FreeTDS
Description = mssql server
Trace = No
Servername = mssqlserver
convert_unicode=True
client charset = utf8
---
and this is the connection setup in python:
--
engine=create_engine('mssql+pyodbc://SQLUSER:SQLPASSWORD@MSSQLDB?Database=TEST_DB')
DeclarativeBase = declarative_base()
metadata = DeclarativeBase.metadata
metadata.bind = engine
---
--
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
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 as an
Re: [sqlalchemy] Id and id
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). 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:
[sqlalchemy] Proxy objects and SA joined inheritance
Is is possible to set the polymorphic_on attribute on an object that
is not directly tied to a db table, but has access to the db attribute
via delegation?
I have a generic Product class that processes an XML to obtain its
generic attributes (uuid, type, etc). Afterwards, the product is
encapsulated within one of several proxy objects that perform
additional processing based on the enclosed product type. Each of the
various proxies are derived from a BaseProxy and multiple types can
use the same proxy. Due to this, the BaseProxy has a poly_type to
specify which of the proxies was used for additional processing. This
is a slimmed down version of what I'm trying to do:
Base = declarative_base()
class Product(Base):
__tablename__ = 'products'
id = Column(Integer, Sequence('id_seq'), primary_key=True)
type = Column(String(16))
class BaseProxy(Base):
__tablename__ = 'products'
__table_args__ = {'extend_existing': True}
poly_type = Column(String(16))
__mapper_args__ = {'polymorphic_on': poly_type}
product = relationship(Product, uselist=False)
def __init__(self, product):
self.product = product
def __getattr__(self, attrib):
return getattr(self.product, attrib)
class HardwareProduct(BaseProxy, Base):
__tablename__ = 'hardware'
__mapper_args__ = {'polymorphic_identity': 'hardware'}
id = Column(Integer, ForeignKey('products.id'), primary_key=True)
serial = Column(String(16))
class SoftwareProduct(BaseProxy, Base):
__tablename__ = 'software'
__mapper_args__ = {'polymorphic_identity': 'software'}
id = Column(Integer, ForeignKey('products.id'), primary_key=True)
product_key = Column(String(16))
However:
p = Product()
sqlalchemy.exc.ArgumentError: Could not determine join condition
between parent/child tables on relationship ProductProxy.product.
Specify a 'primaryjoin' expression. If 'secondary' is present,
'secondaryjoin' is needed as well.
While not directly related to SA, the reason that I didnt have
Hardware and SoftwareProduct inherit directly from Product was because
the product's type wasn't known until after product creation and, due
to the number of products created, I didn't want to waste the time
recreating the underlying product again and again. However, I'm
willing to change it if there is a better approach. Product creation
looks something like:
p = Product('product.xml')
p = select_proxy_based_on_type(p.type)(p)
I realize this is a drawn out question, but any help is appreciated.
--
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] One to Many relationship of the same class as the parent class
I have been struggeling for a few days with this now and trying to see
if I maybe can get some help here. I'm using SQLAlchemy with Flask
This is what I have tried so far:
I got a user class defined like this:
association_table = db.Table('association',
db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
db.Column('friend_id', db.Integer, db.ForeignKey('friend.id'))
)
class Friend(db.Model):
id = db.Column(db.Integer, primary_key=True)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(20), unique=True)
password = db.Column(db.String(30))
friends = db.relationship(Friend,
secondary=association_table)
Basically I want to have a relationship to other objects of class User
in the field User.friends
What am I doing wrong here?
Thanks,
Stefan
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: One to Many relationship of the same class as the parent class
I managed to figure this one out my self :)
association_table = db.Table('association',
db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
db.Column('friend_id', db.Integer, db.ForeignKey('user.id'))
)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(20), unique=True)
password = db.Column(db.String(30))
email = db.Column(db.String(45), unique=True)
friends = db.relationship(User,
secondary=association_table,
backref='added_by',
primaryjoin=id == association_table.c.user_id,
secondaryjoin=id == association_table.c.friend_id)
With this I can now do following:
user1 = User.query.filter_by(id=1).first()
user1.friends
[]
user2 = User.query.filter_by(id=2).first()
user1.friends.append(user2)
user1.friends
[User('user1','us...@admin.com','2')]
user1.friends[0].added_by
[User('admin','ad...@admin.com','1')]
On Monday, 5 March 2012 15:54:27 UTC, Stefan wrote:
I have been struggeling for a few days with this now and trying to see
if I maybe can get some help here. I'm using SQLAlchemy with Flask
This is what I have tried so far:
I got a user class defined like this:
association_table = db.Table('association',
db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
db.Column('friend_id', db.Integer, db.ForeignKey('friend.id'))
)
class Friend(db.Model):
id = db.Column(db.Integer, primary_key=True)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(20), unique=True)
password = db.Column(db.String(30))
friends = db.relationship(Friend,
secondary=association_table)
Basically I want to have a relationship to other objects of class User
in the field User.friends
What am I doing wrong here?
Thanks,
Stefan
On Monday, 5 March 2012 15:54:27 UTC, Stefan wrote:
I have been struggeling for a few days with this now and trying to see
if I maybe can get some help here. I'm using SQLAlchemy with Flask
This is what I have tried so far:
I got a user class defined like this:
association_table = db.Table('association',
db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
db.Column('friend_id', db.Integer, db.ForeignKey('friend.id'))
)
class Friend(db.Model):
id = db.Column(db.Integer, primary_key=True)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(20), unique=True)
password = db.Column(db.String(30))
friends = db.relationship(Friend,
secondary=association_table)
Basically I want to have a relationship to other objects of class User
in the field User.friends
What am I doing wrong here?
Thanks,
Stefan
--
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/-/5uqgtl5-9k0J.
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] Object delegation and SA inheritance
Is is possible to set the polymorphic_on attribute on an object that is
not directly tied to a db table, but has access to the db attribute via
delegation?
I have a generic Product class that processes an XML to obtain its
generic attributes (uuid, type, etc). Afterwards, the product is
encapsulated within one of several proxy objects that perform additional
processing based on the enclosed product type. Each of the various
proxies are derived from a BaseProxy and multiple types can use
the same proxy. Due to this, the BaseProxy has a poly_type to specify
which of the proxies was used for additional processing.
Base = declarative_base()
class Product(Base):
__tablename__ = 'products'
id = Column(Integer, Sequence('id_seq'), primary_key=True)
type = Column(String(16))
class BaseProxy(Base):
__tablename__ = 'products'
__table_args__ = {'extend_existing': True}
poly_type = Column(String(16))
__mapper_args__ = {'polymorphic_on': poly_type}
product = relationship(Product, uselist=False)
def __init__(self, product):
self.product = product
def __getattr__(self, attrib):
return getattr(self.product, attrib)
class HardwareProduct(BaseProxy, Base):
__tablename__ = 'hardware'
__mapper_args__ = {'polymorphic_identity': 'hardware'}
id = Column(Integer, ForeignKey('products.id'), primary_key=True)
serial = Column(String(16))
class SoftwareProduct(BaseProxy, Base):
__tablename__ = 'software'
__mapper_args__ = {'polymorphic_identity': 'software'}
id = Column(Integer, ForeignKey('products.id'), primary_key=True)
product_key = Column(String(16))
However:
p = Product()
sqlalchemy.exc.ArgumentError: Could not determine join condition between
parent/child tables on relationship ProductProxy.product. Specify a
'primaryjoin' expression. If 'secondary' is present, 'secondaryjoin' is
needed as well.
In anticipation of one response, the reason that I didnt have Hardware
and SoftwareProduct inherit directly from Product was because the
product's type wasn't known until after product creation and, due to the
number of products created, I didn't want to waste the time recreating
the underlying product again and again. However, I'm willing to change
it if there is a better approach. Product creation looks something like:
p = Product('product.xml')
p = select_proxy_based_on_type(p.type)(p)
I realize this is a drawn out question, but any help is appreciated.
--
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
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). and
Re: [sqlalchemy] Object delegation and SA inheritance
On Mar 3, 2012, at 10:03 AM, Peter Erickson wrote:
Is is possible to set the polymorphic_on attribute on an object that is
not directly tied to a db table, but has access to the db attribute via
delegation?
I have a generic Product class that processes an XML to obtain its
generic attributes (uuid, type, etc). Afterwards, the product is
encapsulated within one of several proxy objects that perform additional
processing based on the enclosed product type. Each of the various
proxies are derived from a BaseProxy and multiple types can use
the same proxy. Due to this, the BaseProxy has a poly_type to specify
which of the proxies was used for additional processing.
Base = declarative_base()
class Product(Base):
__tablename__ = 'products'
class BaseProxy(Base):
__tablename__ = 'products'
product = relationship(Product, uselist=False)
class HardwareProduct(BaseProxy, Base):
__tablename__ = 'hardware'
class SoftwareProduct(BaseProxy, Base):
__tablename__ = 'software'
__mapper_args__ = {'polymorphic_identity': 'software'}
In anticipation of one response, the reason that I didnt have Hardware
and SoftwareProduct inherit directly from Product was because the
product's type wasn't known until after product creation and, due to the
number of products created, I didn't want to waste the time recreating
the underlying product again and again. However, I'm willing to change
it if there is a better approach. Product creation looks something like:
p = Product('product.xml')
p = select_proxy_based_on_type(p.type)(p)
There's a few ways I can answer this question.There's addressing the
mapping itself, and ways to have a product attribute on both HardwareProduct
and SoftwareProduct. But I think first let's see about the rationale, which
is that you want to create a Product, which doesn't know its real type yet,
then using composition (that is, Product points to HardwareProduct or
SoftwareProduct), you'd add extra attributes to Product.
So right off I think trying to define the type of Product using composition
is leading to complexity and I'd want to try to do it more simply. The
normal way to do this is just to make Product, then have some method
Product.coerce_to_type() that copies itself over to a new SoftwareProduct or
HardwareProduct.If I were writing this app, I would probably do it that
way, as it is the simplest and most straightforward approach. But note
there is a key assumption here, which is that you don't need to actually
*persist* Product until its type is known. If you do actually need to persist
Product with a generic type, then change the type in the database, we need to
take that into account.
Let's first assume the main rationale is performance concerns about generating
a new Product plus a new XYZProduct, that is generating two objects instead of
one. I'd first make sure that the performance overhead of creating Product,
then XYZProduct, is definitely prohibitive. So one way to cut down on that,
while maintaining a very simple approach, is to just collect the data for the
XYZProduct into a lighter weight structure, such as a dictionary, or a
ProductInfo(object) type of object that isn't actually mapped and therefore has
no instrumentation overhead.
The other way to do this, which is a bit more hacky, is to change the class of
Product once constructed:
p = Product('product.xml')
p.__class__ = SoftwareProduct
p.type = software
It's this second approach that can conceivably be used with a Product that's
already persisted in the database, too.SQLA doesn't support the changing
the class operation directly though, so you'd need to do an INSERT statement
manually on the related table, then re-add a new XYZProduct object in.
Anyway, I'd go with Product(xyz).cast_as(software), returning me a new
SoftwareProduct object, just because that would work the most simply.
If you want to explore the Product.product approach, we can talk about that
also though I think it would work with BaseProxy being mapped as a concrete
class to hardware and software, at the moment it seems like pulling in
products into BaseProxy makes this more complicated.
I realize this is a drawn out question, but any help is appreciated.
--
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
Re: [sqlalchemy] Reducing instrumentation overhead for read-only entities
Hello, just for the record, I've built a quick and dirty hack[1] which simplifies state-management for immutable domain models (can create but can't modify). Achieved 2-2.5x speedup in pickle/unpickle (see tests module). I'm not just suggesting anyone to use this in production (though I already do :-)) so this is just FYI. [1]: https://github.com/andreypopp/saimmutable -- 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] Reducing instrumentation overhead for read-only entities
On Sun, Feb 19, 2012 at 6:27 AM, Andrey Popp 8may...@gmail.com wrote: I've managed quite efficient inlining with bytecode magic. It's especially effective with SQLA code, since it also specializes the inlined function, removing a lot of dead code (in the call context). That's pretty interesting! I could share the code if you're interested, but it is rather hard to maintain (it's tied to the bytecode, which is version-specific in CPython) and it's experimental code, so it's rather ugly. Yeah, that would be great, thanks! Finally, I got access to it. So, this[0] is the core code. The easiest way to invoke it is to install it as an import hook (see the function install), that will process all imports from then on. The code is only tested in 2.6, and has issues with 2.7, not to mention 3.x. Might work with 2.5/2.4 though. [0] http://pastebin.com/9TN6zJKc -- 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] Reducing instrumentation overhead for read-only entities
On Mon, Mar 05, 2012 at 06:20:33PM -0300, Claudio Freire wrote: On Sun, Feb 19, 2012 at 6:27 AM, Andrey Popp 8may...@gmail.com wrote: I've managed quite efficient inlining with bytecode magic. It's especially effective with SQLA code, since it also specializes the inlined function, removing a lot of dead code (in the call context). That's pretty interesting! I could share the code if you're interested, but it is rather hard to maintain (it's tied to the bytecode, which is version-specific in CPython) and it's experimental code, so it's rather ugly. Yeah, that would be great, thanks! Finally, I got access to it. Thanks a lot! -- 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] Reducing instrumentation overhead for read-only entities
Wow, OK great, you were able to make something work while maintaining the ClassManager approach. So the first thing is, you can skip the redefinition of Mapper(). You can put your custom ClassManager class right on a base or mixin class like this: class MySpecialMixin(object): __sa_instrumentation_manager__ = MySpecialClassManager The next thing is, you could also instead subclass InstrumentationManager instead of ClassManager, which provides hooks that are a bit more public. I'd probably need to add some more hooks to it in order to fulfill this use case, though. If you look through instrumentation.py _ClassInstrumentationAdapter you can see what that adaption looks like. You can see examples of custom instrumentation in examples/custom_attributes/custom_management.py. This would be *really* neat if it actually works all the way using public API. This would probably go right into the examples/custom_attributes/ directory as a recipe to use for lower-latency read-only objects. On Mar 5, 2012, at 4:14 PM, Andrey Popp wrote: Hello, just for the record, I've built a quick and dirty hack[1] which simplifies state-management for immutable domain models (can create but can't modify). Achieved 2-2.5x speedup in pickle/unpickle (see tests module). I'm not just suggesting anyone to use this in production (though I already do :-)) so this is just FYI. [1]: https://github.com/andreypopp/saimmutable -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Object delegation and SA inheritance
Thanks for the response, however sorry that I posted the question
twice. I had some computer problems over the weekend and didn't think
that my original email made it through.
On Mar 5, 2:52 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Mar 3, 2012, at 10:03 AM, Peter Erickson wrote:
I have a generic Product class that processes an XML to obtain its
generic attributes (uuid, type, etc). Afterwards, the product is
encapsulated within one of several proxy objects that perform additional
processing based on the enclosed product type. Each of the various
proxies are derived from a BaseProxy and multiple types can use
the same proxy. Due to this, the BaseProxy has a poly_type to specify
which of the proxies was used for additional processing.
Base = declarative_base()
class Product(Base):
__tablename__ = 'products'
class BaseProxy(Base):
__tablename__ = 'products'
product = relationship(Product, uselist=False)
class HardwareProduct(BaseProxy, Base):
__tablename__ = 'hardware'
class SoftwareProduct(BaseProxy, Base):
__tablename__ = 'software'
__mapper_args__ = {'polymorphic_identity': 'software'}
In anticipation of one response, the reason that I didnt have Hardware
and SoftwareProduct inherit directly from Product was because the
product's type wasn't known until after product creation and, due to the
number of products created, I didn't want to waste the time recreating
the underlying product again and again. However, I'm willing to change
it if there is a better approach. Product creation looks something like:
p = Product('product.xml')
p = select_proxy_based_on_type(p.type)(p)
There's a few ways I can answer this question. There's addressing the
mapping itself, and ways to have a product attribute on both
HardwareProduct and SoftwareProduct. But I think first let's see about the
rationale, which is that you want to create a Product, which doesn't know
its real type yet, then using composition (that is, Product points to
HardwareProduct or SoftwareProduct), you'd add extra attributes to Product.
So right off I think trying to define the type of Product using composition
is leading to complexity and I'd want to try to do it more simply. The
normal way to do this is just to make Product, then have some method
Product.coerce_to_type() that copies itself over to a new SoftwareProduct
or HardwareProduct. If I were writing this app, I would probably do it
that way, as it is the simplest and most straightforward approach. But
note there is a key assumption here, which is that you don't need to actually
*persist* Product until its type is known. If you do actually need to
persist Product with a generic type, then change the type in the database,
we need to take that into account.
Let's first assume the main rationale is performance concerns about
generating a new Product plus a new XYZProduct, that is generating two
objects instead of one. I'd first make sure that the performance overhead
of creating Product, then XYZProduct, is definitely prohibitive. So one
way to cut down on that, while maintaining a very simple approach, is to just
collect the data for the XYZProduct into a lighter weight structure, such as
a dictionary, or a ProductInfo(object) type of object that isn't actually
mapped and therefore has no instrumentation overhead.
Given the problems with dealing with a delegation/proxy type setup, it
seems that it'll be a lot easier to follow your recommendation by
creating a ProductInfo(object) (or dict) first that reads an XML file
to collect the generic product attributes. From there, I can create a
Product(Base) that is mapped and create my joined inheritance from
there. Assuming that I'm understanding you correctly, that makes the
most amount of sense. However, going this route leads to another
question. As previously mentioned, the XML file contains generic
product information including a type. Depending on the product's type,
additional information might be available by reading another XML file.
The specified type follows the format x/y/z, where x, y, and z vary
based on how the XML creator felt that day (Unfortunately I have no
control over the creation of the XML files and I'm stuck with what I'm
given -- *huge* headache and source of frustration). For the sake of
an example, we'll say that x is the type of product, hardware or
software. If the product is a hardware product, I want to create a
HardwareProduct and same goes for software. This is easy to deal with,
but what happens if down the road a new type is created, let's say a
book, and there is no BookProduct. That being the case, I'd still like
the generic Product to persist, but it just won't have additional
attributes specific to a book. Once the BookProduct is created, I can
go back and fix it... so the question is, can I do the following and
when I query the db I get HardwareProducts,
Re: [sqlalchemy] Re: Object delegation and SA inheritance
On Mar 5, 2012, at 9:33 PM, Pete Erickson wrote:
book, and there is no BookProduct. That being the case, I'd still like
the generic Product to persist, but it just won't have additional
attributes specific to a book. Once the BookProduct is created, I can
go back and fix it... so the question is, can I do the following and
when I query the db I get HardwareProducts, SoftwareProducts, and
Products?
class Product(Base):
__tablename__ = 'products'
id = Column(Integer, Sequence('product_id_seq'), primary_key=True)
# read from the xml file
type = Column(String(16))
# used for SA's polymorphic ability
poly_type = Column(String(16))
__mapper_args__ = {'polymorphic_on': poly_type}
class HardwareProduct(Base):
__tablename__ = 'hardware'
__mapper_args__ = {'polymorphic_identity': 'hardware'}
id = Column(Integer, ForeignKey('products.id'), primary_key=True)
...
pi = ProductInfo('xml_file')
if pi.type == 'hardware':
p = HardwareProduct(pi.__dict__)
elif pi.type == 'software':
p = SoftwareProduct(pi.__dict__)
else:
p = Product(pi.__dict__)
session.add(p)
One thing to keep in mind is if bookproduct is added, then you load more XML
files in treating bookproduct as plain Product, then later you want to fix
it, it means you have to migrate data into a new book table.
Anyway the approach you have above is fine, with Product as a fallback, just
give it a polymorphic identity (like 'product') in the __mapper_args__ dict.
--
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] orm query that returns millions of rows
On 02/03/2012 15:59, Michael Bayer wrote: the other recipe is the windowed range query which I normally use for this at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowedRangeQuery, So, this would be the way to go for Microsoft SQL Server? cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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.
