[sqlalchemy] Re: how to remove table from an engine
Michael Bayer wrote: create a new, empty MetaData instance. redefine=True didnt work so great since tables have dependencies on each other. thanks for the answer, I just saw that redefine is still in the docstring for 0.3s Table class. robert --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- begin:vcard fn:robert rottermann n:rottermann;robert email;internet:[EMAIL PROTECTED] tel;work:031 333 10 20 tel;fax:031 333 10 23 tel;home:031 333 36 03 x-mozilla-html:FALSE version:2.1 end:vcard
[sqlalchemy] Re: Microsoft Jet Database Engine
On 11/4/06, Rick Morrison [EMAIL PROTECTED] wrote: Wow, Jet? There's a blast from the past. I would be amazed if you could get 100% of the unit tests to pass, as some of Jet's SQL syntax can vary quite a bit from ANSI standards. Did you use an ODBC connector, DAO or ADO? A general-purpose ODBC connector for SA would be a really nice addition. that will be very nice (the ODBC) think Rick On 11/2/06, hjr [EMAIL PROTECTED] wrote: Hi, I've started on a Jet database engine for sqlalchemy. I've only just started so at the moment it passes 60% of the unit tests. I am doing this because I am limited to a locked down windows machine with only the Jet engine at my disposal! Anyway, if I can get the unit test a bit higher would there be any chance / interest in including this in the SVN code? Thanks Hugh --~--~-~--~~~---~--~~ 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: Thread issue?
OK, i had the impression you were switching the mapper inside of a
relation() somehow, but it seems all youre doing is sticking a mapper
on a property (i dont quite understand how youd use it ? )
if i understand properly, id just do it this way:
class dbPeople(object):
def fixRace(self, race):
self._race_id = race.id
def _in_race(self):
return object_session(self).query(dbRace).select(
and_(tblRaceParticipant.c.Race_id==self._race_id,tblPeople.c.id==tblRaceParticipant.c.Racer_id))
inRace = property(_in_race)
François Wautier wrote:
Hi Michael,
Thanks for your reply.
First an apology, my program is now working It was a silly mistake...
Second, I agree that what I am doing is not the most elegant thing I've ever
done...
.to put it mildly... Yet, in most cases, the fixRace function will only be
run once at startup .
In all but one case you only deal with one race. So that's not as bad as it
sounds...
Still the application that input data into the database does need to deal
with multiple races.
In that case I only keep one secondary mapper attached to the class and only
create a new
one when needed (i.e. when the race changes) (I probably need to delete the
old mapper if present)
At the bottom you will see the actual definition/mapping I use.
I guess that I may be able to map the various attributes of dbPeople and
dbRace to attributes of dbRaceParticipant
and deal with that object when needed, but I still see no elegant way of
fixing the race, either I
create a secondary mapper (same as now essentially) or I need to pass the
race as an argument
to all my queries... which is exactly what I am trying to avoid.
Cheers,
François
Here is an excerpt of my definitions draft in progress
=+%%
tblPeople=Table(People,
Column(id, Integer, primary_key = True),
Column(Nickname,Unicode(32),nullable=False,index=True),
Column(Firstname,Unicode(32),nullable=False),
Column(Lastname,Unicode(32),nullable=False,index=True),
Column(Email,VARCHAR(64),index=True),
Column(Birthdate,Date),
Column(Gender,Enum([Male,Female]),nullable=False),
Column(Nationality,Country,nullable=False),
Column(Address,Unicode(256)),
Column(Zip,Unicode(32)),
Column(Country,Country),
Column(Tel,String(16)),
Column(Tel_Ext,String(4)),
Column(Mobile,String(16)),
Column(Picture_id,Integer,ForeignKey(ADM_Files.id), nullable=True),
Column(Tag,String(32)),
Column(Active,Boolean,default=True))
tbbPeopleidx=Index('OnlyOne', tblPeople.c.Nickname,tblPeople.c.Firstname,
tblPeople.c.Lastname, unique=True)
class dbPeople(object):
def __str__(self):
return self.Fullname()
def Fullname(self):
return unicode(self.Firstname)+u +unicode(self.Lastname)
def age(self,adate=None):
Compute the age of a person. If adate is set the age is computed
at the given date
if adate is None:
adate=datetime.date.today()
myage=adate.year-self.Birthdate.year
if adate.monthself.Birthdate.month:
myage -=1
elif adate.month==self.Birthdate.month:
if adate.dayself.Birthdate.day:
myage -=1
return myage
# Mapping a Racer with a Race
tblRaceParticipant=Table(Race_Participant,
Column(id, Integer, primary_key = True),
Column(Race_id,Integer,ForeignKey(Race.id), nullable=False),
Column(Racer_id,Integer,ForeignKey('People.id'), nullable=False),
Column(Team_id,Integer,ForeignKey('Team.id'), nullable=True),
Column(Weight,DECIMAL(4,1), nullable=True),
Column(Age,Integer, nullable=True),
Column(Height,Integer, nullable=True),
#Column(Categories,String, nullable=True),
Column(isActive,Boolean, nullable=False,default=True),
Column(Retired,TIMESTAMP, nullable=True),
Column(Comment,Unicode))
tbbParticipantidx=Index('OnlyOne',
tblRaceParticipant.c.Race_id,tblRaceParticipant.c.Racer_id, unique=True)
class dbRaceParticipant(object):
def __str__(self):
return str(self.Racer)+u during +str(self.Race)
#Defining races
tblRace=Table(Race,
Column(id, Integer, primary_key = True),
Column(Name,Unicode(64),nullable=False),
Column(Type,Enum([Team,Individual]),nullable=False),
Column(Vehicle,Enum([None,One,Multiple,Individual]),nullable=False,default=One),
Column(Organiser_id,Integer,ForeignKey('People.id'),nullable=True),
Column(Description,Unicode),
Column(Logo_id,Integer,ForeignKey(ADM_Files.id), nullable=True),
Column(Standing,Unicode(32)))
class dbRace(object):
def __str__(self):
return self.Name
def getRegistrationRecord(self,people):
There must be a session here
for rec in self.Registration:
if rec.Racer==people:
return rec
#
[sqlalchemy] Re: Thread issue?
Thanks for your reply,
OK, i had the impression you were switching the mapper inside of a
relation() somehow, but it seems all youre doing is sticking a mapper
on a property (i dont quite understand how youd use it ? )
I want to use it like this
fixRace(dbPeople, myrace)
listofgreeks=session.query(dbPeople.inRace).select_by(Nationality=Greece)
listofresident=session.query(dbPeople.inRace).select_by(Country=Germany)
So I need to have the inRace mapper an attribute of the class itself.
I'll stick to what I have right now, it seems to work fine.
Cheers,
François
if i understand properly, id just do it this way:
class dbPeople(object):
def fixRace(self, race):
self._race_id = race.id
def _in_race(self):
return object_session(self).query(dbRace).select(
and_(tblRaceParticipant.c.Race_id==self._race_id,tblPeople.c.id==tblRacePar
ticipant.c.Racer_id)) inRace = property(_in_race)
François Wautier wrote:
Hi Michael,
Thanks for your reply.
First an apology, my program is now working It was a silly mistake...
Second, I agree that what I am doing is not the most elegant thing I've
ever done... .to put it mildly... Yet, in most cases, the fixRace
function will only be run once at startup . In all but one case you
only deal with one race. So that's not as bad as it sounds... Still the
application that input data into the database does need to deal with
multiple races. In that case I only keep one secondary mapper attached to
the class and only create a new one when needed (i.e. when the race
changes) (I probably need to delete the old mapper if present)
At the bottom you will see the actual definition/mapping I use.
I guess that I may be able to map the various attributes of dbPeople and
dbRace to attributes of dbRaceParticipant and deal with that object when
needed, but I still see no elegant way of fixing the race, either I
create a secondary mapper (same as now essentially) or I need to pass the
race as an argument to all my queries... which is exactly what I am
trying to avoid.
Cheers,
François
Here is an excerpt of my definitions draft in progress
=+%%
tblPeople=Table(People,
Column(id, Integer, primary_key = True),
Column(Nickname,Unicode(32),nullable=False,index=True),
Column(Firstname,Unicode(32),nullable=False),
Column(Lastname,Unicode(32),nullable=False,index=True),
Column(Email,VARCHAR(64),index=True),
Column(Birthdate,Date),
Column(Gender,Enum([Male,Female]),nullable=False),
Column(Nationality,Country,nullable=False),
Column(Address,Unicode(256)),
Column(Zip,Unicode(32)),
Column(Country,Country),
Column(Tel,String(16)),
Column(Tel_Ext,String(4)),
Column(Mobile,String(16)),
Column(Picture_id,Integer,ForeignKey(ADM_Files.id),
nullable=True), Column(Tag,String(32)),
Column(Active,Boolean,default=True))
tbbPeopleidx=Index('OnlyOne', tblPeople.c.Nickname,tblPeople.c.Firstname,
tblPeople.c.Lastname, unique=True)
class dbPeople(object):
def __str__(self):
return self.Fullname()
def Fullname(self):
return unicode(self.Firstname)+u +unicode(self.Lastname)
def age(self,adate=None):
Compute the age of a person. If adate is set the age is
computed at the given date
if adate is None:
adate=datetime.date.today()
myage=adate.year-self.Birthdate.year
if adate.monthself.Birthdate.month:
myage -=1
elif adate.month==self.Birthdate.month:
if adate.dayself.Birthdate.day:
myage -=1
return myage
# Mapping a Racer with a Race
tblRaceParticipant=Table(Race_Participant,
Column(id, Integer, primary_key = True),
Column(Race_id,Integer,ForeignKey(Race.id), nullable=False),
Column(Racer_id,Integer,ForeignKey('People.id'), nullable=False),
Column(Team_id,Integer,ForeignKey('Team.id'), nullable=True),
Column(Weight,DECIMAL(4,1), nullable=True),
Column(Age,Integer, nullable=True),
Column(Height,Integer, nullable=True),
#Column(Categories,String, nullable=True),
Column(isActive,Boolean, nullable=False,default=True),
Column(Retired,TIMESTAMP, nullable=True),
Column(Comment,Unicode))
tbbParticipantidx=Index('OnlyOne',
tblRaceParticipant.c.Race_id,tblRaceParticipant.c.Racer_id, unique=True)
class dbRaceParticipant(object):
def __str__(self):
return str(self.Racer)+u during +str(self.Race)
#Defining races
tblRace=Table(Race,
Column(id, Integer, primary_key = True),
Column(Name,Unicode(64),nullable=False),
Column(Type,Enum([Team,Individual]),nullable=False),
Column(Vehicle,Enum([None,One,Multiple,Individual]),nullable=Fa
lse,default=One),
[sqlalchemy] Inheritance and relation in primary table
Hi,
I'm still experimenting SA features and, once again, I'm stucked with a
relation definition problem.
This is the deal.
Considering this tables definition :
USERS = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(255), nullable=False),
)
BASES = Table('bases', metadata,
Column('id', Integer, primary_key=True),
Column('owner_id', Integer, ForeignKey('users.id'), primary_key=True),
Column('creation_date', DateTime),
Column('modification_date', DateTime),
Column('type', String(255), nullable=False),
Column('title', String(255)),
)
FOLDERS = Table('folders', metadata,
Column('id', Integer, ForeignKey('bases.id'), primary_key=True),
Column('description', String()),
)
This class definitions :
#
# Class definition
#
class User(object):
pass
class Base(object):
pass
class Folder(Base):
pass
And this mappers :
base_join = polymorphic_union(
{
'folder':BASES.join(FOLDERS),
'base':BASES.select(BASES.c.type=='base'),
}, None, 'pjoin')
base_mapper = mapper(Base, BASES, select_table=base_join,
polymorphic_on=base_join.c.type, polymorphic_identity='base',
properties={'owner': relation(User,
primaryjoin=BASES.c.owner_id==USERS.c.id, backref='objects')})
mapper(Folder, FOLDERS, inherits=base_mapper,
polymorphic_identity='folder',)
mapper(User, USERS)
I guess my relation defined in the base_mapper has something wrong
because I'm not able to add an owner to my Bases objects.
Any idea ??
Regards,
Laurent.
--~--~-~--~~~---~--~~
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: Inheritance and relation in primary table
it all looks fine to me, youd have to show me something more specific. --~--~-~--~~~---~--~~ 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: Inheritance and relation in primary table
Here is an attached sample of my problem.
You can create a User, u, and a Folder, f. Assign ownership via a
simple f.owner = u.
You can do an :
objectstore.save(u)
objectstore.save(f)
You can see relations via
print f.owner
print u.objects
But this can not be saved into the database. An
objectstore.flush() will fail with a :
SQLError: (IntegrityError) bases.id may not be NULL 'INSERT INTO bases
(owner_id, creation_date, modification_date, type, title) VALUES (?, ?,
?, ?, ?)' [1, None, None, 'folder', Le titre de l'object]
Everything works fine as soon as you remove the owner relation and the
owner_id in the BASES table declaration.
Michael Bayer a écrit :
it all looks fine to me, youd have to show me something more specific.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
# -*- coding: UTF-8 -*-
# * BEGIN LICENSE BLOCK *
# This file is part of NOMDUPRODUIT.
# Copyright (c) 2004 laurent Rahuel and contributors. All rights
# reserved.
#
# NOMDUPRODUIT is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# NOMDUPRODUIT is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with NOMDUPRODUIT; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
#
# * END LICENSE BLOCK *
from sqlalchemy import mapper, relation, polymorphic_union, create_session
from sqlalchemy import BoundMetaData, Table, Column, Integer, DateTime, Date, Time, String, Boolean, Float, ForeignKey
from datetime import datetime
metadata = BoundMetaData(sqlite:///:memory:)
objectstore = create_session()
#
# Tables definition
#
USERS = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(255), nullable=False),
)
BASES = Table('bases', metadata,
Column('id', Integer, primary_key=True),
Column('owner_id', Integer, ForeignKey('users.id'), primary_key=True),
Column('creation_date', DateTime),
Column('modification_date', DateTime),
Column('type', String(255), nullable=False),
Column('title', String(255), nullable=False),
)
FOLDERS = Table('folders', metadata,
Column('id', Integer, ForeignKey('bases.id'), primary_key=True),
Column('description', String()),
)
#
# Class definition
#
class User(object):
pass
class Base(object):
pass
class Folder(Base):
pass
mapper(User, USERS)
base_join = polymorphic_union(
{
'folder':BASES.join(FOLDERS),
'base':BASES.select(BASES.c.type=='base'),
}, None, 'pjoin')
base_mapper = mapper(Base, BASES, select_table=base_join, polymorphic_on=base_join.c.type, polymorphic_identity='base',properties={'owner': relation(User, primaryjoin=BASES.c.owner_id==USERS.c.id, backref='objects')})
mapper(Folder, FOLDERS, inherits=base_mapper, polymorphic_identity='folder',)
metadata.drop_all()
metadata.create_all()
u = User()
u.name = Laurent
f = Folder()
f.title = Le titre de l'object
f.owner = u
[sqlalchemy] how to select a database with MySQL
hi there, i want to create a database in mysql and then use it. this is what I do: in the __init__ def __init__( self, connectionstr ): self.connectionstr = connectionstr def _checkEngine(self): if self._db_engine is None: self._db_engine = create_engine(self.connectionstr) self._metadata = BoundMetaData(self._db_engine) self._connection = self._db_engine.connect() later I do: self._checkEngine() self._connection.execute(use %s % dbname so long everything seems to work ok. however, when I try to execute a statement (like creating a table) like this: table = Table(tblname, self._metadata, *columns) table.create() I get an error claiming that no database is selected.: table.create() File ../../lib/sqlalchemy/schema.py, line 284, in create File ../../lib/sqlalchemy/schema.py, line 862, in create_all File ../../lib/sqlalchemy/engine/base.py, line 413, in create File ../../lib/sqlalchemy/engine/base.py, line 437, in _run_visitor File ../../lib/sqlalchemy/schema.py, line 882, in accept_schema_visitor File ../../lib/sqlalchemy/ansisql.py, line 637, in visit_metadata File ../../lib/sqlalchemy/schema.py, line 268, in accept_schema_visitor File ../../lib/sqlalchemy/ansisql.py, line 668, in visit_table File ../../lib/sqlalchemy/engine/base.py, line 763, in execute File ../../lib/sqlalchemy/engine/base.py, line 365, in proxy File ../../lib/sqlalchemy/engine/base.py, line 329, in _execute_raw File ../../lib/sqlalchemy/engine/base.py, line 348, in _execute SQLError: (OperationalError) (1046, 'No database selected') how can I avoid this ?? thanks robert --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- begin:vcard fn:robert rottermann n:rottermann;robert email;internet:[EMAIL PROTECTED] tel;work:031 333 10 20 tel;fax:031 333 10 23 tel;home:031 333 36 03 x-mozilla-html:FALSE version:2.1 end:vcard
[sqlalchemy] Re: polymorphic_identity determination
I've attached a file which is a variant to the employees example with
two objectives.
1. Base polymorphic_identity on select criteria (no type column).
2. Use two levels of inheritance.
The first objective seems to be met, but the second is not working
properly. I put in two Managers, two Generic Engineers and two Chemical
Engineers (which inherit from Engineer). When I select from employees,
I get eight records. The Chemical Engineers are included once as
Chemical Engineers and once and Generic Engineers.
How might this be better written to meet these objectives?
Randall
Michael Bayer wrote:
just FYI, the type column idea is taken from Hibernate, and that's
all Hibernate supports as far as polymorphic loading.
But for polymorphic loading in SA, you are free to make any kind of
polymorphic_union you like that can add in a functionally-generated
type column, and specify it into select_table. im pretty sure this
should work completely right now, such as:
import sqlalchemy.sql as sql
person_join = polymorphic_union(
{
'engineer':sql.select([people.join(engineers),
sql.column('engineer').label('type')]),
'manager':sql.select([people.join(managers),
sql.column('manager').label('type')]),
}
)
etc.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
from sqlalchemy import *
metadata = DynamicMetaData('testdata')
employees = Table('employees', metadata,
Column('employee_id', Integer, primary_key=True),
Column('name', String(50)),
Column('manager_data', String(50)),
Column('engineer_info', String(50)),
Column('cheme_info', String(50)),
)
managers = select([employees], employees.c.manager_data !=
None).alias('managers')
engineers = select([employees], employees.c.engineer_info !=
None).alias('engineers')
chemical_engineers = select([employees],
and_(employees.c.engineer_info != None,
employees.c.cheme_info != None)).alias(
'chemical_engineers')
class Employee(object):
def __init__(self, name):
self.name = name
def __repr__(self):
return self.__class__.__name__ + + self.name
class Manager(Employee):
def __init__(self, name, manager_data):
self.name = name
self.manager_data = manager_data
def __repr__(self):
return self.__class__.__name__ + + self.name + + self.manager_data
class Engineer(Employee):
def __init__(self, name, engineer_info):
self.name = name
self.engineer_info = engineer_info
def __repr__(self):
return self.__class__.__name__ + + self.name + + self.engineer_info
class ChemicalEngineer(Engineer):
def __init__(self, name, engineer_info, cheme_info):
self.name = name
self.engineer_info = engineer_info
self.cheme_info = cheme_info
def __repr__(self):
return self.__class__.__name__ + + self.name + + self.engineer_info
p_union = polymorphic_union(
{
'engineer': engineers,
'manager': managers,
'chemical_engineer': chemical_engineers
},
'type'
)
employee_mapper = mapper(Employee, p_union, polymorphic_on=p_union.c.type)
manager_mapper = mapper(Manager, managers, inherits=employee_mapper,
concrete=True, polymorphic_identity='manager')
engineer_mapper = mapper(Engineer, engineers, inherits=employee_mapper,
concrete=True, polymorphic_identity='engineer')
mapper(ChemicalEngineer, chemical_engineers, inherits=engineer_mapper,
concrete=True, polymorphic_identity='chemical_engineer')
def populate(session):
m1 = Manager('manager1', 'manager1')
m2 = Manager('manager2', 'manager2')
e1 = Engineer('engineer1', 'engineer1')
e2 = Engineer('engineer2', 'engineer2')
ce1 = ChemicalEngineer('cengineer1', 'cengineer1', 'cengineer1')
ce2 = ChemicalEngineer('cengineer2', 'cengineer2', 'cengineer2')
for o in (m1, m2, e1, e2, ce1, ce2):
session.save(o)
session.flush()
if __name__ == '__main__':
engine = create_engine('sqlite:///test.db')
engine.echo = True
metadata.connect(engine)
metadata.drop_all()
metadata.create_all()
session = create_session(engine)
populate(session)
print session.query(Employee).select()
[sqlalchemy] Re: Inheritance and relation in primary table
sqlite's autoincrement feature does not work when you define a composite primary key. youll have to set the id attribute on each instance manually before flushing. --~--~-~--~~~---~--~~ 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: polymorphic_identity determination
if you change your echo to 'debug', or just select straight from your
p_union selectable, youll see these rows:
(5, u'cengineer1', u'cengineer1', u'cengineer1', None,
u'chemical_engineer')
(6, u'cengineer2', u'cengineer2', u'cengineer2', None,
u'chemical_engineer')
(1, u'manager1', None, None, u'manager1', u'manager')
(2, u'manager2', None, None, u'manager2', u'manager')
(3, u'engineer1', u'engineer1', None, None, u'engineer')
(4, u'engineer2', u'engineer2', None, None, u'engineer')
(5, u'cengineer1', u'cengineer1', u'cengineer1', None, u'engineer')
(6, u'cengineer2', u'cengineer2', u'cengineer2', None, u'engineer')
Where you can see that the chemical engineers are coming out twice with
inconsistent types. the query has to be tuned to be more specific:
managers = select([employees, column('manager').label('type')],
employees.c.manager_data !=
None).alias('managers')
engineers = select([employees, column('engineer').label('type')],
and_(employees.c.engineer_info !=
None,
employees.c.cheme_info==None)).alias('engineers')
chemical_engineers = select([employees,
column('chemical_engineer').label('type')],
and_(employees.c.engineer_info != None,
employees.c.cheme_info !=
None)).alias(
'chemical_engineers')
p_union = polymorphic_union(
{
'engineer': engineers,
'manager': managers,
'chemical_engineer': chemical_engineers
},
None,
)
--~--~-~--~~~---~--~~
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: Inheritance and relation in primary table
Tried with a postgresql database and I get another error right in my metadata.create_all(). I get a SQLError: (ProgrammingError) from postgres telling me I got something wrong while creating constraint on unexisting key into table bases. CREATE TABLE folders ( id INTEGER NOT NULL, description TEXT, FOREIGN KEY(id) REFERENCES bases (id), PRIMARY KEY (id) ) But if I remove owner_id from BASES and I remove the relation in base_mapper, I get all tables created. Any idea ?? Michael Bayer a écrit : sqlite's autoincrement feature does not work when you define a composite primary key. youll have to set the id attribute on each instance manually before flushing. --~--~-~--~~~---~--~~ 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] Disregard the last message please
I cannot reply to it because I'm having a 8 hour lag on my posts in Google groups (no idea why) I was having problems getting the defaults to the database structure, after checking get_column_default_string from the ANSI schema creator I realized that it was wrong to skip the PassiveDefault explanation on the docs. Sometimes I don't have to trust my instinct. -- Claudio --~--~-~--~~~---~--~~ 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] arbitary sql queries (or how to select a bool from postgres)
Hello, I'm using sqlalchemy.mod.threadlocal with object mappers and sessions, works greate sofar;). Now, for the first time, I have the desire to do something completely different. Select a bool from the database, like SELECT %s in (SELECT ); % some_id What is the most straight forward approach to do so. Thanks, sb. --~--~-~--~~~---~--~~ 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] indexu-style category tree of arbitrary depth
I need to implement an indexu-style category tree of arbitrary depth: News Media (64) Arts and Humanities, Automotive, Business Recreation Sports (234) Amusement and Theme Parks, Automotive, Aviation Reference (32) Acronyms and Abbreviations, Almanacs, Arts and Humanities Product items with arbitrary properties will be associated with multiple product categories. The eventual rendering will be in TurboGears or Django, the database postgresql. Being new to both sqlalchemy and SQL tree models, I'd like to start by learning the proper nomenclature: What is this data structure called, particularly in sqlalchemy parlance? I'd imagine this is a fairly common pattern in applications built on top of sqlalchemy. Can anyone direct me to a python/sqlalchemy open-source project using this kind of category directory, so that I can study their implementation? I'm hoping to properly utilize sqlalchemy to manage parent-child relationships (especially for moving nodes), so that I don't end up parsing a stored string path field or anything naive. Thanks for any suggestions. --~--~-~--~~~---~--~~ 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] Column __repr__ with sqlalchemy types instead of db-specific object instances?
I have a versioned repository of SQL DDL that I'd like to convert to
versioned sqlalchemy models, using the Table(...,autoload=True)
feature.
I suspect it may be a lossy operation to do so, but in the interest of
table documentation, is there a way to render the Table.__repr__() with
for example the sqlalchemy class Integer in place of the object
instance sqlalchemy.databases.postgres.PGInteger object at ...
Table('product_items',
BoundMetaData(),
Column('id',
sqlalchemy.databases.postgres.PGInteger object at 0xb75eaf6c,
key='id',
primary_key=False,
nullable=True,
default=PassiveDefault(sqlalchemy.sql._TextClause object at
0xb75eaf8c),
onupdate=None),
(...)
I can see from this Column's example that there would be a similar
issue with the _TextClause __repr__(). I'd need to render the python
necessary to create the column to achieve my objective.
Thanks for any advice.
--~--~-~--~~~---~--~~
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: Column __repr__ with sqlalchemy types instead of db-specific object instances?
__repr__() really annoys me because no matter what i do with it, people tell me im using it incorrectly. technically, __repr__() is supposed to return a string that when eval'ed would return the object instance. which is not realistic for an object like Table since its an enormous construction. if you want to take Tables and produce some kind of string representation, I recommend you create yourself a SchemaVisitor...since __repr__() is not something id write code against in this case. --~--~-~--~~~---~--~~ 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: polymorphic_identity determination
Randall Smith wrote: For discussion, consider the Employee, Manager, Engineer example from the docs. If I were designing the tables, I would not normally have a type field. I would use the null status of engineer_info or manager_data to determine the employee type. Or if that data was in separate tables, I would use the existence of records in those tables. And these are simple cases. I can think of some real life cases where the values of multiple fields would determine the class. IMO you always need to keep your people/place/things data separate from your roles data. If you put the 2 together you end up developing a system that is highly inflexible and the code will start to look ugly. After all a user can wear many hats. He can be an Employee, Manager, Engineer, etc. He can even be an Employee of 2 different companies, a manager of multiple groups, etc. So I would keep user tables/classes separate from role tables/classes. For the database design you would have 2 basic choices. First option: User Table userID ... Role Table userID roleType roleData ... Where roleData would likely contain serialized (pickled) data. Second option: User Table id ... Role Table id userID roleType roleDetailsID ... Role1Details Table id ... Role2Details Table id ... other role details tables I would only go with the second option if you actually needed to do ad hoc queries of the Role#Details tables. Otherwise, the first option is far quicker to code and provides more flexibility. John --~--~-~--~~~---~--~~ 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: polymorphic_identity determination
John, Thanks for the feedback. The code I put up is not part of any real system. I'm just playing off of the existing examples in the docs and trying to get comfortable with SA inheritance. Randall John M Camara wrote: Randall Smith wrote: For discussion, consider the Employee, Manager, Engineer example from the docs. If I were designing the tables, I would not normally have a type field. I would use the null status of engineer_info or manager_data to determine the employee type. Or if that data was in separate tables, I would use the existence of records in those tables. And these are simple cases. I can think of some real life cases where the values of multiple fields would determine the class. IMO you always need to keep your people/place/things data separate from your roles data. If you put the 2 together you end up developing a system that is highly inflexible and the code will start to look ugly. After all a user can wear many hats. He can be an Employee, Manager, Engineer, etc. He can even be an Employee of 2 different companies, a manager of multiple groups, etc. So I would keep user tables/classes separate from role tables/classes. For the database design you would have 2 basic choices. First option: User Table userID ... Role Table userID roleType roleData ... Where roleData would likely contain serialized (pickled) data. Second option: User Table id ... Role Table id userID roleType roleDetailsID ... Role1Details Table id ... Role2Details Table id ... other role details tables I would only go with the second option if you actually needed to do ad hoc queries of the Role#Details tables. Otherwise, the first option is far quicker to code and provides more flexibility. John --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
