[sqlalchemy] Re: How to disable Foreign Keys to clear database
There is a recipe for dropping FK constraints: http://www.sqlalchemy.org/trac/wiki/UsageRecipes did you find that? In addition, if you wanted to keep the FKs enabled, there is an example here using Metadata.sorted_tables, to get the table list in dependency order. http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html?highlight=sorted_tables#reflecting-all-tables-at-once On Friday, August 17, 2012 4:48:10 AM UTC+2, Greg wrote: Hi, I was naively trying to clear a db earlier in the day and I ran into this problem: 'Cannot delete or update a parent row: a foreign key constraint fails' So as anyone would do I've been searching online and through the documentation on how to turn the foreign keys off, on delete = Cascade, delete orphan-cascade what have you. So many hours and many stack traces later I'd like to ask your help in this matter def cleanMapping(self, dbName): connection = self.dbEngines[dbName].connect() trans = connection.begin() for my_table in reversed(self.dbMetaData[dbName].tables.values()): keys= my_table.foreign_keys for column in my_table.columns: for key in keys: my_table = Table(my_table, self.dbMetaData[dbName], Column(column, Integer, ForeignKey(key, onupdate=CASCADE, ondelete = CASCADE) ,primary_key=True)) connection.execute(my_table.delete()) trans.commit() So I'm fresh out of ideas; Everytime I try using this code I get sqlalchemy.exc.ArgumentError: Column object already assigned to Table col -- 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/-/6EiRPIEPIHAJ. 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: Retrive datetime attributes
Here is code that works for me:
from datetime import datetime
from sqlalchemy import Column, DateTime, Integer, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite://', echo=True)
Base = declarative_base(engine)
Session = sessionmaker()
session = Session()
class LogEntry(Base):
Log class
__tablename__ = 'log'
#common data
id = Column(Integer, primary_key=True)
timestamp = Column(DateTime)
def __init__(self):
self.timestamp = datetime.now()
log = LogEntry()
Base.metadata.create_all()
session.add(log)
session.flush()
log = session.query(LogEntry).one()
print type(log.timestamp)
On Thursday, June 21, 2012 2:35:24 PM UTC+2, Fabien Ribes wrote:
Hi all,
I'm using Python 2.6.5 and SQLAlchemy-0.7.8 over sqlite3 to store and
retrieve logs with in table like this :
class LogEntry(Base):
Log class
__tablename__ = 'log'
#common data
id = Column(Integer, primary_key=True)
timestamp = Column(DateTime())
When querying back object, how comes I get unicode string in timestamp
attribute ? Isn't SA supposed to convert ISO formatted string stored in
sqlite back to python datetime object ?
--
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/-/N6IlGbBTzyUJ.
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: The Minimalists' strategy for SQLAlchemy.
You can reflect once, then cache the metadata (e.g. pickle it) then I think you can use autoload with usexisting On Apr 30, 12:13 am, Kuze kuze.to...@gmail.com wrote: I'm aware SQLAlchemy provides a comprehensive package for creating database objects (tables, indexes, etc.) with a simple `create_all` but I'm finding for my purpose, I'd rather manage object creation with old fashion SQL scripts due to requirements both during development and in production. I'm also aware of `reflection` capability provided. However, it'd be hitting the database with a query to grab the necessary data points for generating the schema. For production, hitting the db when using reflection does not sound compelling. With my assumption that reflection isn't for production. What's the bare minimum required for data access? Note: I'll be using declaritive extension for any required definitions. I'll be making use of the ORM (Query, Events, etc). I'll be using Mixins and Metaclasses where needed to cut down boilerplate code. I've a hunch that perhaps it's only indexes which would not need to be defined with SQLAlchemy cause they need not be created, but willing to see what others have to add. -- 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: Side by side versions on one machine.
Try virtualenv http://pypi.python.org/pypi/virtualenv On Apr 26, 2:31 pm, Mathieu Tozer math...@madebysofa.com wrote: Is it possible to have multiple installation versions on the one machine? I don't want to screw with my dev environment too much but want to try migrating up. -- 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 a one-to-many relationship using association object with two foreign key primary keys
Looks like you need to specify a composite ForeignKey
http://www.sqlalchemy.org/docs/core/schema.html?highlight=foreign_keys#sqlalchemy.schema.ForeignKeyConstraint
comment_table = Table('comment',metadata,
Column('id',Integer,primary_key=True),
Column('package_id', Integer),
Column('repo_id', Integer),
Column('msg',String),
ForeignKeyConstraint(['package_id', 'repo_id'],
['package_repo_table.package_id', 'package_repo_table.repo_id'])
)
On Apr 14, 3:41 pm, frankentux cfarrell1...@googlemail.com wrote:
package_table = Table('package',metadata,
Column('id',Integer,primary_key=True),
Column('name',String))
repo_table = Table('repo',metadata,
Column('id',Integer,primary_key=True),
Column('name',String))
comment_table = Table('comment',metadata,
Column('id',Integer,primary_key=True),
### PROBLEM - HOW TO CREATE RELATIONSHIP TO package_repo ###
# Column('packagerepo_id', Integer, ForeignKey(### how to declare
this ###)),
Column('msg',String))
package_repo_table = Table('package_repo', metadata,
Column('package_id',Integer,ForeignKey('package.id'),primary_key=True),
Column('repo_id',Integer,ForeignKey('repo.id'), primary_key=True),
Column('status',String,default='builds'))
mapper(Package, package_table, properties={
'repos':relationship(PackRepo)
})
mapper(PackRepo, pack_repo_table, properties={
'repo':relationship(Repo),
'comments': relationship(Comment)
})
mapper(Comment,comment_table)
mapper(Repo, repo_table)
--
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: Setting column value that changes everyday
Hi, Do you need to store expiry_code? seeing as it is a function of last_con and the current date. class PhNumber(Base): __tablename__ = 'ph_numbers' ph_no = Column(Integer, nullable=False) last_con = Column(DateTime, nullable=False) @property def expiry_code(self): msg = 'Expired 3 months' now = datetime.datetime.now() if now (self.last_con - 90): return msg return 'Not Applicable' If the column needs to be queried from outside sqlalchemy, then you could put the logic in a database function (depending upon what database you are using). On Mar 1, 12:52 pm, dalia dalia@gmail.com wrote: Hi, I have a table of phone numbers which has 3 columns named - 1. ph_no Integer not null 2. last_contacted Datetime not null 3. expiry_code Text() The behaviour of the table should be - When the last_contacted column has a date which is 3 months older, the expiry_code column should have the value 'number expired'. I'm not sure how this can be done using declarative method. I did the following - class PhNumber(Base): __tablename__ = 'ph_numbers' ph_no = Column(Integer, nullable=False) last_con = Column(DateTime, nullable=False) expiry_code = Column(Text(), default=mydefault, onupdate=mydefault) def mydefault(context): msg = 'Expired 3 months' now = datetime.datetime.now() if now (context.current_parameters['last_con'] - 90): return msg return 'Not Applicable' mydefault function calculates if the value in last_con column is greater than 3 months of today's date, it stores 'Expired 3 months' in expiry_code. But this happens only when a new insert or update occurs in this table. I want the value in expiry_code to be changed even without any update/ insert operations on the table. Whenever the table is selected, the updated value should be shown. Is this possible in SQLAlchemy? Please let me know. -- 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: The right way to clear database of content?
I the order is required for Foreign Key relationships. i.e. to make sure the children are deleted before the parents. So the for table in reversed(meta.sorted_tables) example is the more correct way to delete all data. On Feb 15, 1:29 pm, Arve Knudsen arve.knud...@gmail.com wrote: Thank you GHZ, it did work! Wondering about one thing though; the recipe in the documentation iterates over the tables in reverse sorted order, like so: for table in reversed(meta.sorted_tables) Do you know what this would be good for (since your code does not care about the table order)? Arve On Mon, Feb 14, 2011 at 2:51 PM, GHZ geraint.willi...@gmail.com wrote: maybe it needs to be in a transaction: con = engine.connect() trans = con.begin() for name, table in meta.tables.items(): print table.delete() con.execute(table.delete()) trans.commit() On Feb 14, 1:29 pm, Arve Knudsen arve.knud...@gmail.com wrote: Hi What's the right way to clear a database all of content, but keep the schema? I tried the method of deleting all tables athttp:// www.sqlalchemy.org/docs/05/metadata.html#reflecting-all-tables..., but content still remains. I couldn't find any documentation on Table.delete either, for that matter. Maybe I'm missing something... Thanks, Arve -- 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: The right way to clear database of content?
maybe it needs to be in a transaction: con = engine.connect() trans = con.begin() for name, table in meta.tables.items(): print table.delete() con.execute(table.delete()) trans.commit() On Feb 14, 1:29 pm, Arve Knudsen arve.knud...@gmail.com wrote: Hi What's the right way to clear a database all of content, but keep the schema? I tried the method of deleting all tables athttp://www.sqlalchemy.org/docs/05/metadata.html#reflecting-all-tables..., but content still remains. I couldn't find any documentation on Table.delete either, for that matter. Maybe I'm missing something... Thanks, Arve -- 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] prevent extra selects to get default values
Hi, If I create a table with a default. create table T (id number primary key, data number default NULL) Then reflect this table. then using the ORM I trigger an insert, but only into the id column and flush() then I try to access the 'data' attribute this causes a select, to get the actual value of 'data' Is there any simple way to turn this behavior off`.. i.e for sqlalchemy to pretend the server default does not exist..? Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: prevent extra selects to get default values
including small example
in the actual case, I can't find anything as obvious as the equivalent
of 'print t.data' in my code.. but something is triggering these
selects of columns with defaults
from sqlalchemy import MetaData, create_engine, Table, Column, Integer
from sqlalchemy.orm import mapper, sessionmaker, relationship
engine = create_engine('oracle://bob:b...@mig01', echo=True)
meta = MetaData(bind=engine)
ddls = [
drop table t,
create table t (id number primary key,
data number default null)
]
for ddl in ddls:
engine.execute(ddl)
t = Table('t', meta, autoload=True, useexisting=True)
meta.create_all()
class T(object):
pass
session = sessionmaker()()
mapper(T, t)
p = T()
p.id = 1
session.add(p)
session.flush()
print p.data
2010-06-21 11:41:46,168 INFO sqlalchemy.engine.base.Engine.0x...84ec
{'table_name': u'T', 'schema': u'BOB'}
2010-06-21 11:41:46,173 INFO sqlalchemy.engine.base.Engine.0x...84ec
SELECT table_name FROM all_tables WHERE table_name = :name AND owner
= :schema_name
2010-06-21 11:41:46,173 INFO sqlalchemy.engine.base.Engine.0x...84ec
{'name': u'T', 'schema_name': u'BOB'}
2010-06-21 11:41:46,184 INFO sqlalchemy.engine.base.Engine.0x...84ec
BEGIN
2010-06-21 11:41:46,185 INFO sqlalchemy.engine.base.Engine.0x...84ec
INSERT INTO t (id) VALUES (:id)
2010-06-21 11:41:46,186 INFO sqlalchemy.engine.base.Engine.0x...84ec
{'id': 1}
2010-06-21 11:41:46,215 INFO sqlalchemy.engine.base.Engine.0x...84ec
SELECT t.data AS t_data
FROM t
WHERE t.id = :param_1
2010-06-21 11:41:46,215 INFO sqlalchemy.engine.base.Engine.0x...84ec
{'param_1': 1}
On 21 Jun, 11:40, GHZ geraint.willi...@gmail.com wrote:
Hi,
If I create a table with a default.
create table T (id number primary key, data number default NULL)
Then reflect this table.
then using the ORM I trigger an insert, but only into the id column
and flush()
then I try to access the 'data' attribute
this causes a select, to get the actual value of 'data'
Is there any simple way to turn this behavior off`.. i.e for
sqlalchemy to pretend the server default does not exist..?
Thanks.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: help please
you should access column names via lower case
i.e.
columns = 'projectid', 'program', 'progmanger']
On 10 Jun, 03:39, Aref arefnamm...@gmail.com wrote:
Hello All,
I just began learning sqlalchemy and am not quite used to it yet so
please excuse my ignorance and which might be a trivial question to
some of you.
I am writing a database module and need to load a table and possibly
modify a record in the table. I can get the connection established and
everything works fine. The problem I am running into is that I do not
necessarily know the column name before hand to code it in the update
method. I want to be able to find out to send a generic column name
which will be updated (gets the column name dynamically).
I tried the following:
columns=['ProjectID', 'Program', 'progmanger']
test = str('table.c.'+columns[1])
update = table.update(test=='project-name', values = {test:'program'})
print update
update.execute()
I get a error when I try to run it. It does not recognize the column
for some reason even though if I print test everything seems to be OK.
I get 'project.c.Program'
Is there something I am missing here? How can I send the project and
column name to the update method dynamically?
Thank you so much in advance for any help or insight you could provide.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Starting with SQLAlchemy and Oracle: Reflection question
you can check for primary key constraints using:
select table_name, constraint_name from user_constraints where
constraint_type = 'P'
the orm will need to know the primary keys, but if they do not exist
in the schema, you can set them when you reflect the table
t = Table('t1',
meta,
Column('id', Integer, primary_key=True),
autoload=True)
On 25 Mai, 10:23, Urko M. urko.ma...@gmail.com wrote:
The primary_key collection seems to be empty. For example, I am
reflecting the table teachers, and the result of:
len(teachers.primary_key)
is 0.
The table is there, and is reflected apparently correctly. And
teachers.indexes shows me a correct list of indexes. I'm not an expert
in Oracle, so maybe this is consistent with the way the database is
created. I can't change that, as it is for a commercial product.
Are there any side effects if SQLAlchemy doesn't have a Primary Key
for a table?
On May 24, 8:59 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On May 24, 2010, at 5:34 AM, Urko M. wrote:
Hi everyone,
I am starting out with SQLAlchemy, and I want to use it to reflect
tables in an Oracle 10g database.
I have been successful in that, but it seems it is not reflecting the
primary keys. Any ideas?
Or any suggestion about how to check if the keys are getting reflected
or not?
you'd check the primary_key collection on the Table,
len(Table.primary_key). There's no issue I've seen before with PK columns
not reflecting on 10g.
--
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
athttp://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
athttp://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] Re: adding a Sequence link to a reflected table
table.c.id.default = Sequence('some_sequence')seems to work.
Thanks
On May 12, 4:01 pm, Michael Bayer mike...@zzzcomputing.com wrote:
you really shouldnt be using the useexisting flag - any configuration in
the Table you give is ignored, since the Table is already constructed.
This is less than ideal and perhaps useexisting should be made private,
since its mainly needed for the internals of the reflection process.
because we've only started having really good support for Oracle very
recently, the Sequence thing is an issue we haven't delved into deeply. I
would suggest seeing what happens if you take an existing table and just say
table.c.id.default = Sequence(some_sequence), which should do the job but
im not 100% sure.
On May 12, 2010, at 5:47 AM, GHZ wrote:
Hi,
Can I make the second form (metadata.reflect, then Table with
useexising=True), result in the same insert statement as the first
form (Table with autoload=True)?
Thanks.
from sqlalchemy import create_engine, Table, Column, Sequence,
MetaData, Integer
engine = create_engine('oracle://fred:f...@mig01')
ddls = [
drop table customer,
create table customer (
id number primary key,
name varchar2(10)
),
]
for ddl in ddls:
try:
print ddl,
engine.execute(ddl)
print 'ok'
except:
print 'fail'
pass
# First form is OK
# results in: INSERT INTO bob (id, name) VALUES
(bob_sq.nextval, :name) RETURNING bob.id INTO :ret_0
metadata = MetaData(bind=engine)
t = Table('bob', metadata, Column('id', Integer, Sequence('bob_sq'),
primary_key=True), autoload=True)
print t.insert().values(name='bob')
# Second form NOT OK
# results in: INSERT INTO bob (name) VALUES (:name)
metadata = MetaData()
metadata.reflect(bind=engine, only=['bob'])
t = Table('bob', metadata, Column('id', Integer, Sequence('bob_sq'),
primary_key=True), useexisting=True)
print t.insert().values(name='bob')
--
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
athttp://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
athttp://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] adding a Sequence link to a reflected table
Hi,
Can I make the second form (metadata.reflect, then Table with
useexising=True), result in the same insert statement as the first
form (Table with autoload=True)?
Thanks.
from sqlalchemy import create_engine, Table, Column, Sequence,
MetaData, Integer
engine = create_engine('oracle://fred:f...@mig01')
ddls = [
drop table customer,
create table customer (
id number primary key,
name varchar2(10)
),
]
for ddl in ddls:
try:
print ddl,
engine.execute(ddl)
print 'ok'
except:
print 'fail'
pass
# First form is OK
# results in: INSERT INTO bob (id, name) VALUES
(bob_sq.nextval, :name) RETURNING bob.id INTO :ret_0
metadata = MetaData(bind=engine)
t = Table('bob', metadata, Column('id', Integer, Sequence('bob_sq'),
primary_key=True), autoload=True)
print t.insert().values(name='bob')
# Second form NOT OK
# results in: INSERT INTO bob (name) VALUES (:name)
metadata = MetaData()
metadata.reflect(bind=engine, only=['bob'])
t = Table('bob', metadata, Column('id', Integer, Sequence('bob_sq'),
primary_key=True), useexisting=True)
print t.insert().values(name='bob')
--
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] mixing metaclass with redefining the primary key of an autoloaded table
Hi,
Using plain Declarative, I am able to redefine a primary key column
that has been autoloaded, so that I can link it to an oracle sequence
and give it a new name:
Id = Column('id', Integer, Sequence('table_sq'), primary_key=True)
However, if I then try to add some methods to the class using a
metaclass, the foreign key relationships pointing to this column, seem
to go missing.
Apologies for not being able to track down the exact cause of this,
but it seems to be something I am doing wrong with the combination of
autloading, redefining the primary key, and adding to the class
through a metaclass.
The following example should work against an empty oracle schema.
I get: sqlalchemy.exc.ArgumentError: Could not determine join
condition between parent/child tables on relationship
Customer.addresses. Specify a 'primaryjoin' expression. If this is a
many-to-many relationship, 'secondaryjoin' is needed as well.
http://python.pastebin.com/7V8MEfH3
from sqlalchemy import MetaData, Column, Integer, Sequence,
ForeignKey, create_engine
from sqlalchemy.orm import relationship, sessionmaker, scoped_session
from sqlalchemy.ext.declarative import DeclarativeMeta,
declarative_base
engine = create_engine('oracle://fred:f...@mig01')
ddls = [
drop table customer,
drop table address,
drop table country,
drop table customer_type,
create table customer_type (
id number primary key,
name varchar2(10)),
create table country (
id number primary key,
name varchar2(10)),
create table customer (
id number primary key,
name varchar2(10),
customer_type_id number references customer_type),
create table address (
id number primary key,
name varchar2(10),
country_id number references country,
customer_id number references customer),
]
for ddl in ddls:
try:
print ddl,
engine.execute(ddl)
print 'ok'
except:
print 'fail'
pass
metadata = MetaData(bind=engine)
Session = scoped_session(sessionmaker())
class RelationNameAttribute(object):
def __init__(self, relationname, childclass):
self.relationname = relationname
self.query = Session.query(childclass)
def __get__(self, obj, objtype):
return getattr(getattr(obj, self.relationname), 'name')
def __set__(self, obj, val):
child = self.query.filter_by(name=val).one()
setattr(obj, self.relationname, child)
class DataMeta(DeclarativeMeta):
def __init__(cls, classname, bases, dict_):
classvalues = {}
for name, obj in vars(cls).items():
#print name
if name in ('entity',
'entity_id',
'event_id',
'attributes',
'__tablename__',
'history_table',
'id_column',
'relations'):
classvalues[name] = obj
if 'attributes' in classvalues: # could have checked for any
variable names
#
# Id attribute
#
sequence_name = classvalues['__tablename__'] + '_sq'
cls.Id = Column('id', Integer, Sequence(sequence_name),
primary_key=True)
#
# Other attributes
#
for aname, nname, rname, childclass in
classvalues['attributes']:
#
# A relationship attribute
#
# The relationship
setattr(cls, rname, relationship(childclass,
uselist=False))
# The Name attribute
setattr(cls, nname, RelationNameAttribute(rname,
childclass))
#
# Table arguments
#
cls.__table_args__ = {'autoload': True,
'useexisting' : True}
return DeclarativeMeta.__init__(cls, classname, bases, dict_)
BaseForConfig = declarative_base(metadata=metadata)
BaseForData = declarative_base(metaclass=DataMeta, metadata=metadata)
class Country(BaseForConfig):
__tablename__ = 'country'
__table_args__ = {'autoload' : True}
class CustomerType(BaseForConfig):
__tablename__ = 'customer_type'
__table_args__ = {'autoload' : True}
class Address(BaseForData):
__tablename__ = 'address'
history_table = 'address_history'
id_column = 'addrnr'
entity = 'Address'
entity_id = 2
event_id = 103
attributes = [
('CountryKey', 'CountryName', 'country', Country),
]
class Customer(BaseForData):
__tablename__ = 'customer'
attributes = [
('TypeKey', 'TypeName', 'type', CustomerType)
]
addresses = relationship( Address, backref='customer')
c = Customer()
print dir(c)
print c.addresses
--
You received this message because you are subscribed to the Google Groups
[sqlalchemy] Re: TypeError: synonym() got an unexpected keyword argument
Hi,
http://www.sqlalchemy.org/changelog/CHANGES_0_6beta3
* 'proxy' argument on synonym() is removed. This flag
did nothing throughout 0.5, as the proxy generation
behavior is now automatic.
On 14 apr, 13:16, jose soares jose.soa...@sferacarta.com wrote:
Hi all,
seems synonym in version 0.6 don't have proxy parameter.
'user_name' : synonym('logname', proxy=True),
TypeError: synonym() got an unexpected keyword argument 'proxy'
j
--
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] Specifying Alternate Join Conditions to relation()
using 5.5
from sqlalchemy import create_engine, Table, Column,
ForeignKeyConstraint, MetaData, and_, String, Integer, ForeignKey
from sqlalchemy.orm import relation, sessionmaker, synonym, join
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite:///:memory:')
Base = declarative_base(bind=engine)
class User(Base):
__tablename__ = 'user'
user_id = Column('user_id', Integer, primary_key=True)
boston_addresses = relation('Address',
primaryjoin = and_
('User.user_id==Address.user_id',
'Address.city==Boston'),
foreign_keys = ['Address.user_id'])
class Address(Base):
__tablename__ = 'address'
user_id = Column('user_id', Integer, ForeignKey(User.user_id),
primary_key=True)
seq = Column('seq', Integer, primary_key=True)
city = Column('city', String)
session = sessionmaker()()
session.query(User)
gives:
sqlalchemy.exc.ArgumentError: Could not determine relation direction
for primaryjoin condition 'User.user_id==Address.user_id AND
Address.city==Boston', on relation User.boston_addresses. Do the
columns in 'foreign_keys' represent only the 'foreign' columns in this
join condition ?
Thanks
--~--~-~--~~~---~--~~
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] globaly convert existing types
I'm reflecting an existing schema with autoload=True All strings are stored as fixed length, which I need to strip() Is there a way of converting all attributes of an existing type? --~--~-~--~~~---~--~~ 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: insert and joined mappers
try :
m = mapper(MyJoin, a_table.join(b_table), properties={
'a_id' : [Table_a.__table__.c.id, Table_b.__table__.c.a_id]
})
from:
http://www.sqlalchemy.org/docs/05/mappers.html#mapping-a-class-against-multiple-tables
On May 5, 11:46 am, Alessandro Dentella san...@e-den.it wrote:
Hi,
how should I configure a mapper that represents a join between two tables
so that inserting a new object writes the foreign key between the two in
the proper way?
class Table_a(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
description = Column(String(100))
class Table_b(Base):
__tablename__ = 'b'
idb = Column(Integer, primary_key=True)
a_id = Column(ForeignKey(Table_a.id), nullable=False)
a_table = Table_a.__table__
b_table = Table_b.__table__
class MyJoin(object): pass
m = mapper(MyJoin, a_table.join(b_table))
j = MyJoin()
j.description = 'xxx'
sess.add(j)
Base.metadata.bind.echo = True
sess.commit()
2009-05-05 12:41:52,346 INFO sqlalchemy.engine.base.Engine.0x...7acL BEGIN
2009-05-05 12:41:52,347 INFO sqlalchemy.engine.base.Engine.0x...7acL INSERT
INTO a (description) VALUES (?)
2009-05-05 12:41:52,347 INFO sqlalchemy.engine.base.Engine.0x...7acL ['xxx']
2009-05-05 12:41:52,348 INFO sqlalchemy.engine.base.Engine.0x...7acL INSERT
INTO b (a_id) VALUES (?)
2009-05-05 12:41:52,348 INFO sqlalchemy.engine.base.Engine.0x...7acL [None]
2009-05-05 12:41:52,348 INFO sqlalchemy.engine.base.Engine.0x...7acL ROLLBACK
Is it possible to prepare the mapper so that a_id gets the value that
the first object got as id?
thanks
sandro
*:-)
--~--~-~--~~~---~--~~
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] association_proxy with association object - declarative
I tried to use the example from:
http://www.sqlalchemy.org/docs/05/reference/ext/associationproxy.html#simplifying-association-object-relations
But with declarative syntax. Any idea why this is going wrong?
from sqlalchemy import Table, Column, Integer, String, MetaData,
ForeignKey, Sequence, create_engine
from sqlalchemy.orm import relation, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(autoflush=False, bind=engine)
session = Session()
Base = declarative_base()
def _create_uk_by_keyword(keyword):
A creator function.
We expect keywords to already be in the DB.. therefore, just
search and return the existing keyword
return session.query(Keyword).filter_by(keyword=keyword).one()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
keywords = association_proxy('user_keywords', 'keyword',
creator=_create_uk_by_keyword)
class Keyword(Base):
__tablename__ = 'keywords'
id = Column(Integer, primary_key=True)
keyword = Column(String)
class UserKeyword(Base):
__tablename__ = 'userkeywords'
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey(users.id))
keyword_id = Column(Integer, ForeignKey(keywords.id))
user = relation(User, backref='user_keywords')
keyword = relation(Keyword)
Base.metadata.create_all(engine)
kw = Keyword(keyword='kw 1')
session.add(kw)
session.flush()
user = User(name='bob')
user.keywords=['kw 1']
which gives me:
user.keywords=['kw 1']
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/ext/associationproxy.py, line 207, in __set__
self._set(proxy, values)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/ext/associationproxy.py, line 259, in _set
proxy.extend(values)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/ext/associationproxy.py, line 394, in extend
self.append(v)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/ext/associationproxy.py, line 386, in append
self.col.append(item)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/orm/collections.py, line 909, in append
item = __set(self, item, _sa_initiator)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/orm/collections.py, line 884, in __set
item = getattr(executor, 'fire_append_event')(item, _sa_initiator)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/orm/collections.py, line 581, in
fire_append_event
return self.attr.fire_append_event(self.owner_state, item,
initiator)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/orm/attributes.py, line 629, in
fire_append_event
value = ext.append(state, value, initiator or self)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/orm/attributes.py, line 815, in append
child_state.get_impl(self.key).append(child_state, state.obj(),
initiator, passive=PASSIVE_NO_CALLABLES)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/orm/attributes.py, line 900, in get_impl
return self.manager.get_impl(key)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.3-
py2.6.egg/sqlalchemy/orm/attributes.py, line 1317, in get_impl
return self[key].impl
KeyError: 'user'
Thanks.
--~--~-~--~~~---~--~~
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] association_proxy question
I am trying to use the association_proxy for attributes that link to
tables containing mostly static data
e.g. my static data is:
COUNTRY
COUNTRY.CODE
COUNTRY.NAME
and the data I am changing is:
USER
USER.COUNTRY_CODE
I use the association_proxy as I want to be able to say:
user = User()
user.country_name = 'DENMARK'
#rather than user.country_cde = 'DK'
In class User() (declarative)
I have:
country_code = Column(String, ForeignKey('COUNTRY.CODE'))
country = relation('Country', uselist=False)
country_name = association_proxy('country', 'name',
creator=my_creator) #proxy to COUNTRY.NAME
where:
def my_creator(country_name):
country = session.query(Country).filter_by(name=country_name).one
()
i.e. I will only link to existing countries.. and not add a new one.
Is there a better way to do this?
In this instance I can create a new session in my_creator, but I would
really want to be using the same session, incase I have added, deleted
countries inside the same transaction. Making the session global
doesn't seem right.
thanks
--~--~-~--~~~---~--~~
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: Need SqlAlchemy Model Advice for a table with many foreign keys.
Don't know if this will work in your case.. but to handle joins to so
many static tables.
Note.. I didn't need to update these tables, nor query from them back
to the data tables. (i.e. no need to call Gender.member_profiles())
I did something like the following:
class MemberProfile(Base):
gender_by_desc = make_descriptor_for_static_field(tablename,
id_fieldname, desc_fieldname)('genderID')
with:
def make_descriptor_for_static_field(tablename, id_fieldname,
desc_fieldname)
class C(object):
_cache = None
def __init__(self, data_fieldname):
self.data_fieldname = data_fieldname
t = Table(tablename, metadata, autoload=True)
id_field = getattr(t.c, id_fieldname)
desc_field = getattr(t.c, desc_fieldname)
s = select([id_field, name_field])
result = engine.execute(s)
self._cache = dict(list(result))
def __get__(self, obj, objtype):
v = getattr(obj, self.data_fieldname)
if v is None:
return None
else:
return self._cache[v]
return C
On Feb 4, 6:02 pm, Gloria W strang...@comcast.net wrote:
Thanks for this response. I do need all of the data available at once.
Specifically, here is what I'm trying to do. I'm following this
example right from the docs:
d.
...
...
This same example works if I inherit from my Member class, but I don't
want to do this, since I have to also join Gender, and 40 something
other tables to this class.
What am I missing to make this work like the example shown?
Thank you immensely,
Gloria
--~--~-~--~~~---~--~~
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: joining to child, and using child in relation
Tested with trunk. Works, thanks.
On Jan 29, 6:42 am, Michael Bayer mike...@zzzcomputing.com wrote:
OK, well that was painful but we are stronger for the effort, thanks
for bringing up the issue. r5740 of trunk will allow your original
mapper(A.join(B))-mapper(B) to configure properly.
On Jan 28, 2009, at 11:28 PM, Michael Bayer wrote:
a join is of the form:
table1.join(table2, onclause)
such as
subscriber_table.join(address_table,
and_(address_table.c.subscriber_id==subscriber.c.id,
address_table.c.type=='MAIN'))
but unfortunately current relation() code does not support a join of
X/
Y to Y, unless the join of X/Y is assembled via joined table
inheritance. As a workaround, you can wrap your join() in an
aliased select(). A fix may be available in the next 10 minutes or
maybe not.
You also could forego the complexity of mapping to a join and just
modify your Subscriber class to break up the addresses collection
amongst a proxy of the MAIN element and a list of the remaining
elements. an attribute_mapped_collection could help to accomplish
this nicely.
On Jan 28, 2009, at 7:22 PM, GHZ wrote:
I have a subscriber and address table.
a subscriber will have one and only one 'MAIN' address.
I want the subscriber and MAIN address to be represented by one class
'Subscriber'. However, I want that class to have a collection
'addresses' which contains other addresses (e.g. old addresses) - (it
can include the 'MAIN' address too .. or not.. I don't care)
subscriber_table = Table('subscriber', metadata,
Column('id', primary_key=True),
autoload=True)
address_table = Table('address',
metadata,
Column('subscriber_id', ForeignKey
('subscriber.id'), primary_key=True),
Column('address_type', primary_key=True),
autoload=True)
subscriber_with_default_address = sql.join( subscriber_table.c.id
== address_table.c.subscriber_id).??? - something to say
address_table.type is 'MAIN'
mapper(Address, address_table)
mapper(Subscriber, subscriber_and_address, properties={
'id':[subscriber_table.c.id, address_table.c.subscriber_id],
'addresses' : relation(Address, collection_class=Addresses,
backref='customer')
})
a) I can't quite figure out how to say (address.type is default)
b) even without this I get:
sqlalchemy.exc.ArgumentError: Can't determine relation direction for
relationshi
p 'Subscriber.addresses' - foreign key columns are present in both
the
parent an
d the child's mapped tables. Specify 'foreign_keys' argument.
if I do specify foreign_keys parameter to the relation function, then
I still get the same.
Thanks
--~--~-~--~~~---~--~~
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] joining to child, and using child in relation
I have a subscriber and address table.
a subscriber will have one and only one 'MAIN' address.
I want the subscriber and MAIN address to be represented by one class
'Subscriber'. However, I want that class to have a collection
'addresses' which contains other addresses (e.g. old addresses) - (it
can include the 'MAIN' address too .. or not.. I don't care)
subscriber_table = Table('subscriber', metadata,
Column('id', primary_key=True),
autoload=True)
address_table = Table('address',
metadata,
Column('subscriber_id', ForeignKey
('subscriber.id'), primary_key=True),
Column('address_type', primary_key=True),
autoload=True)
subscriber_with_default_address = sql.join( subscriber_table.c.id
== address_table.c.subscriber_id).??? - something to say
address_table.type is 'MAIN'
mapper(Address, address_table)
mapper(Subscriber, subscriber_and_address, properties={
'id':[subscriber_table.c.id, address_table.c.subscriber_id],
'addresses' : relation(Address, collection_class=Addresses,
backref='customer')
})
a) I can't quite figure out how to say (address.type is default)
b) even without this I get:
sqlalchemy.exc.ArgumentError: Can't determine relation direction for
relationshi
p 'Subscriber.addresses' - foreign key columns are present in both the
parent an
d the child's mapped tables. Specify 'foreign_keys' argument.
if I do specify foreign_keys parameter to the relation function, then
I still get the same.
Thanks
--~--~-~--~~~---~--~~
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] find only loaded objects in relation collections
Hi,
I have a Subscriber and an Address table. Subscriber can have many
Addresses
mapper(Subscriber, subscriber_table, properties={
'addresses' : relation(Address, collection_class=Addresses,
backref='customer')})
From the a Subscriber object, I want to inspect all loaded objects in
any collections, but do it quietly - without causing any more to load.
class MyBase(object):
@reconstructor
def __my_init__(self):
self.rules = []
def get_all_rules_on_all_loaded_related_objects(self):
for collection in (p for p in object_mapper
(self).iterate_properties if type(p) is RelationProperty):
# How to access this collection without causing it to
load?
# I want to look at the 'rules' property on all loaded
objects
Thanks
--~--~-~--~~~---~--~~
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] expunge_all and statement caching - Oracle
Hi, I am using SQLA for reading from a database. I modify the objects to use in my code, but don't want to write anything back to the database. therefore I call: session.expunge_all() , before reading the next set of data. This works, but it results in the select statements being re-parsed by Oracle. (parse to execute ratio is 1:1) Is there a way of throwing away all objects from the last fetch without throwing away the parsed statements? --~--~-~--~~~---~--~~ 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: setting table and mapper arguments with Declarative
On Sep 18, 2:59 pm, Michael Bayer [EMAIL PROTECTED] wrote: Well, I would think __table_args__ is the only argument you'd really want to propigate in that way, and this is an inconvenience I've also had so perhaps we'll do something about it...I would propose a default_table_args keyword arg to declarative_base(). Thanks, I would appreciate the default_table_args parameter to declarative_base() --~--~-~--~~~---~--~~ 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: grandparent id relation
On Sep 18, 2:54 pm, Michael Bayer [EMAIL PROTECTED] wrote: 2. more involved: catch change events and populate a Child.grandparent relation(). 0.5 has made the AttributeExtension API public which would be a good place to catch this event. The advantage to this is that your Child has a grandparent already set before any SQL is issued to the database. Thanks Michael, I managed to get 2. to work.. However I was now thinking it may be simpler to create a new relation 'grandchildren' on the grandparent.. and add the new child to both the parent and grandparent parent.children = [child] grandparent.grandchildren = [child] Is there a hook for me to add to the second collection automatically (i.e. an event when object is added to a collection)? Thanks --~--~-~--~~~---~--~~ 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] RETURNING clause on Insert (Oracle sequences)
Hi, Is there a reason why this is not used to return the id column value? Would cut down on the number of roundtrips for insert statements e.g. insert into bob (id) values (bob_sq.nextval) returning id into :id rather than what seems to be happening at the moment: select bob_sq.nextval from dual; insert into bob (id) values (:id); Thanks. --~--~-~--~~~---~--~~ 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] setting table and mapper arguments with Declarative
I want to set table, mapper arguments automatically.
The following is the only way I have found to do this.
Is this supported?
Am I wasting my time with Declarative and should rather use the non
declarative if I want this control?
class MyMeta(DeclarativeMeta):
def __new__(meta, classname, bases, classdict):
# Copy interesting arguments from base classes
for base in bases:
for arg in (a for a in ('__table_args__',
'__tablename__',
'__mapper_args__') if a in
base.__dict__):
classdict[arg] = base.__dict__[arg]
return DeclarativeMeta.__new__(meta, classname, bases,
classdict)
Base = declarative_base(bind=engine)
class MyDeclarativeStuff(object):
# Set some generic stuff up.
__table_args__ = {'autoload':True}
class Customer(Base, MyDeclarativeStuff):
__metaclass__ = MyMeta
__tablename__ = 'customer'
Many Thanks.
--~--~-~--~~~---~--~~
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: RETURNING clause on Insert (Oracle sequences)
Thank you Michael, I found the changeset adding this to Postgresql. Will look into a similar change for Oracle. --~--~-~--~~~---~--~~ 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] simple Extract, Transform pattern needed
I am thinking about moving from home grown data access classes, to
sqlalchemy for a simple ETL tool for moving from legacy databases - at
least for the Extract and simplest of Transformations.
Example of what I'm trying to achieve.. if I have a customer table in
the legacy database, and a new_customer table (I show two examples of
target databases) in the target system.
(source and target are currently Oracle)
create table old_customer (cunr number, cutype char(1));
insert into customer values (1,'A');
--create table new_1_customer(id number, type number);
--create table new_2_customer(cunr number, cutype number);
I want to use SA to provide a customer object containing both old and
new columns, with any required column level transformations.
e.g. in the first example, I want an object with the following
properties
customer.o_cunr
customer.o_cutype
customer.id
customer.type (converted from cutype through mapping function)
and in the second:
customer.o_cunr
customer.o_cutype
customer.cunr
customer.cutype (converted from cutype through mapping function)
eventually I want to provide these mapping separate from the main
class definition.. but I start with them inline:
from sqlalchemy import *
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine=create_engine('oracle://gw_val:[EMAIL PROTECTED]')
Base = declarative_base(engine=engine)
class Customer(Base):
__tablename__ = 'old_customer'
__table_args__ = {'autoload':True}
__mapper_args__ = {'column_prefix' : 'o_'}
id = Column('cunr', Integer, primary_key=True)
def _get_cutype(self):
# Trival mapping
def mapper(old_type):
return ord(old_type)
return mapper(self.o_cutype)
type = property(_get_cutype)
Session = sessionmaker()
session = Session()
customer = session.query(Customer).filter_by(id=1).one()
print customer.id
print 'type mapping %s - %d' % (customer.o_cutype, customer.type)
Which works fine.
However, I can't use the same construct for the second example (where
the columns have the same names)
from sqlalchemy import *
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine=create_engine('oracle://gw_val:[EMAIL PROTECTED]')
Base = declarative_base(engine=engine)
class Customer(Base):
__tablename__ = 'old_customer'
__table_args__ = {'autoload':True}
__mapper_args__ = {'column_prefix' : 'o_'}
o_cunr = Column('cunr', Integer, primary_key=True)
def _get_cutype(self):
# Trival mapping
def mapper(old_type):
return ord(old_type)
return mapper(self.o_cutype)
cutype = property(_get_cutype)
Session = sessionmaker()
session = Session()
customer = session.query(Customer).filter_by(o_cunr=1).one()
print customer.o_cunr
print 'type mapping %s - %d' % (customer.o_cutype, customer.cutype)
Gives me:
Traceback (most recent call last):
File ./test2.py, line 33, in module
print 'type mapping %s - %d' % (customer.o_cutype,
customer.cutype)
AttributeError: 'Customer' object has no attribute 'o_cutype'
Any suggestions on the best way to code this so I don't overwrite the
prefixed columns if they happen to have the same name in legacy and
target schemas, also with a long term goal of taking the explicit
mapping code out of each class writing something more like this:
class Customer(MyBase):
__tablename__ = 'old_customer'
__table_args__ = {'autoload':True}
__column_mappings__ = {
('cunr', 'id', None),
('cutype', 'type', lambda x : ord(x))
}
Thanks
--~--~-~--~~~---~--~~
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: simple Extract, Transform pattern needed
Figured it out. slight issue with documentation confused me http://www.sqlalchemy.org/docs/05/plugins.html#plugins_declarative 'instruments' should read 'descriptor'? --~--~-~--~~~---~--~~ 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: simple Extract, Transform pattern needed
now I have a real question based on example 1 above.
i.e. wanting an object with the following attributes:
customer.o_cutype
customer.id
customer.type (converted from cutype through mapping function)
from
create table old_customer (cunr number, cutype char(1));
The following works:
PREFIX = 'o_'
class Customer(Base):
__tablename__ = 'old_customer'
__table_args__ = {'autoload':True}
__mapper_args__ = {'column_prefix' : PREFIX}
id = Column('cunr', Integer, primary_key=True)
def _get_type(self):
def mapping(x):
return ord(x)
return mapping(self.o_cutype)
type = synonym('o_cutype', descriptor=property(_get_type))
Session = sessionmaker()
session = Session()
customer = session.query(Customer).filter_by(id=1).one()
print customer.id
print 'type mapping %s - %d' % (customer.o_cutype, customer.type)
Then when I want to make the mapping / transformation more generic..
e.g. by just supplying a list like:
__transforms__ = [ ('cutype', 'type', lambda x:ord(x)) ]
I came up with the following which seems to work:
from sqlalchemy import *
from sqlalchemy.orm import sessionmaker, synonym
from sqlalchemy.ext.declarative import declarative_base,
DeclarativeMeta
engine=create_engine('oracle://gw_val:[EMAIL PROTECTED]')
Base = declarative_base(engine=engine)
PREFIX = 'o_'
def make_getter(oldname, fn):
def getter(self):
return fn(getattr(self, PREFIX + oldname))
return getter
class MyMeta(DeclarativeMeta):
def __new__(meta, classname, bases, classdict):
for oldname, newname, fn in classdict['__transforms__']:
getter = make_getter(oldname, fn)
classdict['_get_' + newname] = getter
classdict[newname] = synonym(PREFIX + oldname,
descriptor=property(getter))
return type.__new__(meta, classname, bases, classdict)
class Customer(Base):
__metaclass__ = MyMeta
__transforms__ = [ ('cutype', 'type', lambda x:ord(x)) ]
__tablename__ = 'old_customer'
__table_args__ = {'autoload':True}
__mapper_args__ = {'column_prefix' : PREFIX}
id = Column('cunr', Integer, primary_key=True)
#def _get_type(self):
#def mapping(x):
#return ord(x)
#return mapping(self.o_cutype)
#type = synonym('o_cutype', descriptor=property(_get_type))
Session = sessionmaker()
session = Session()
customer = session.query(Customer).filter_by(id=1).one()
print customer.id
print 'type mapping %s - %d' % (customer.o_cutype, customer.type)
Question is - Is this recommended, am I overcomplicating things with
the metaclass?
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
