[sqlalchemy] ActiveMapper : adding a DB column after a model change
I have defined a class like class Image(AciveMapper): class mapping: __table__ = image id = column(Integer, primary_key=True) url = column(Unicode(128)) and created the DB according to the model. I have modified the Image class to add a new column class Image(AciveMapper): class mapping: __table__ = image id = column(Integer, primary_key=True) url = column(Unicode(128)) mime_type = column(Unicode(32)) I can't find how to update the DB schema without dropping the table or going through SQL. Any help ?? --~--~-~--~~~---~--~~ 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: un-deferring columns
there are defer() and undefer() MapperOptions:
session.query(Class).options(undefer('somefield')).select()
and they even have unit tests so they might actually work...
On Jan 7, 2007, at 11:06 AM, Jonathan Ellis wrote:
Is there a way to defer or un-defer columns at the query level?
--~--~-~--~~~---~--~~
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: ActiveMapper : adding a DB column after a model change
if youre looking for automated ALTER-style operations, you can take a look at Migrate: http://erosson.com/migrate/trac/ personally for a one-time operation id just go into the console and type an ALTER statement...(or write a script...) On Jan 7, 2007, at 6:32 AM, remi jolin wrote: I have defined a class like class Image(AciveMapper): class mapping: __table__ = image id = column(Integer, primary_key=True) url = column(Unicode(128)) and created the DB according to the model. I have modified the Image class to add a new column class Image(AciveMapper): class mapping: __table__ = image id = column(Integer, primary_key=True) url = column(Unicode(128)) mime_type = column(Unicode(32)) I can't find how to update the DB schema without dropping the table or going through SQL. Any help ?? --~--~-~--~~~---~--~~ 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] Update on MS-SQL work
Hi,
Here's an update on the problems I've been working on.
1) commit issue
Ticket #411 raised and the fix has been applied. I am working on a unit
test; see below.
2) coinitialize
My current feeling is that this is an adodbapi bug, nothing to do with
sa. I'll work with them in the first instance.
3) unicode fields
Ticket #298 had been created for this, but is not a complete solution. I
have re-opened the ticker, with this comment:
Unicode columns are still created as VARCHAR. The problem is that
Unicode is a TypeDecorator
http://www.sqlalchemy.org/trac/wiki/TypeDecorator around a String
type, and that TypeDecorator
http://www.sqlalchemy.org/trac/wiki/TypeDecorator.dialect_impl gets
the database type for the type that it wraps (i.e. String, not Unicode).
Fixing this could be interesting! One option is to make Unicode a
completely separate type from String. This could raise issues for
cross-database portability. MS-SQL has different database types for
string and unicode fields. Postgres has a single string type, and it is
a database option whether your strings are unicode.
It has been noted that NVARCHAR fields are UCS-2 only. Still, this is
preferable to using VARCHAR with utf-8 encoding, as other applications
(not using SQLAlchemy) may access the data. In any case, most Python
builds are UCS-2 only.
4) session.flush() not getting primary keys
No update on this, but if it helps it appears when using ActiveMapper
classes. No reflection is involved.
I have been working on a test for the commit problem. Basically we need
a test that writes some data in one connection, commits, and then
creates a new connection to check it can see the data. I had a go at
this, but I will need some help getting this incorporated in the
existing unit tests. As an aside, on my PC a number of unit tests fail
for sqlite and loads fail for MSSQL. Over the coming months I will be
using SA/MSSQL quite a lot so I will try to gradually get fixes in for
the failing tests.
import sqlalchemy
from sqlalchemy import *
import os
import adodbapi
def getit():
return adodbapi.connect('Provider=SQLOLEDB;Data Source=.;User
Id=pythonweb;Password=mdi*2mdO1;Initial Catalog=test')
db = create_engine(mssql:///paj, pool=sqlalchemy.pool.QueuePool(getit))
metadata = BoundMetaData(db)
users = Table('users', metadata,
Column('user_id', Integer, primary_key=True),
Column('user_name', String))
#users.create()
c1 = db.connect()
t1 = c1.begin()
c1.execute(users.insert(), user_id=1, user_name='user1')
t1.commit()
c1.close()
#print len(c2.execute(select * from users).fetchall())
c2 = db.connect()
#t2 = c2.begin()
print len(c2.execute(select * from users).fetchall())
Michael and Rick - thanks a lot for all your help on this.
Best wishes,
Paul
--~--~-~--~~~---~--~~
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: Update on MS-SQL work
Hi,
return adodbapi.connect('Provider=SQLOLEDB;Data Source=.;User
Id=pythonweb;Password=mdi*2mdO1;Initial Catalog=test')
Ooops! I thought I had just managed to cancel that mail in time :-)
Paul
--~--~-~--~~~---~--~~
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] bitwise operators?
Hi, I was wondering if there was any way to write the following query without having to use a text block. select * from table where table.flags 1 = 1 and table.flags 4 = 0 I couldn't find any indication that it would be supported in the docs. The closest I found was the the ability to use the op () function. It allows me to use use the '' operator, but it doesn't allow me to check what the result is. so I have been able to write the following query select * from table where table.flags 1 but I have not been able to write the query select * from table where table.flags 1 = 1 Thanks in advance, David --~--~-~--~~~---~--~~ 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] Mapping special child rows
Say I have tables corresponding to users and orders from test/tables.py.
I have dozens or hundreds of orders per user. I want to show a list of
users with their most recent order, but because I have so many orders
doing an eager load to orders is a bad solution. Having a manually
defined python property to pull in the most recent order is also bad
because I only want to do a single query.
So, I tried this
mapper(User,
users,
properties={
'orders':relation(mapper(Order, orders), backref='user'),
'max_order':relation(mapper(Order, max_orders, non_primary=True)),
'addresses':relation(mapper(Address, addresses), backref='user'),
})
But that's about as far as I got -- I couldn't come up with a max_orders
select that would work. First I tried
max_order_id = select([func.max(orders.c.order_id)],
group_by=[orders.c.user_id], scalar=True)
max_orders = orders.select(orders.c.order_id==max_order_id).alias('max_orders')
but max_orders.select() generated
SELECT orders.order_id, orders.user_id, orders.description, orders.isopen
FROM orders
WHERE orders.order_id = (SELECT max(orders.order_id) GROUP BY orders.user_id)
which selected every order rather than just orders 4 and 5. (The
subselect, with scalar=True removed, correctly selected those IDs.)
So I thought maybe adding an alias would un-confuse the subselect-in-where:
o2 = orders.select().alias('o2')
max_orders = orders.select(orders.c.order_id==max_order_id).alias('max_orders')
but this made negative progress:
sqlalchemy.exceptions.SQLError: (OperationalError) no such column: orders.order_
id 'SELECT orders.order_id, orders.user_id, orders.description, orders.isopen
FROM orders
WHERE orders.order_id = (SELECT max(o2.order_id)
FROM (SELECT orders.order_id AS order_id, orders.user_id AS user_id,
orders.description AS description, orders.isopen AS isopen) AS o2
GROUP BY o2.user_id)' []
So:
- I take it SA doesn't really support subselects in WHERE clauses?
- Is there an alternative way to map max_order that I'm missing?
(I could work around the subselect problem with a postgresql function,
but I seem to run into a lot of places to use this kind of
optimization so I'd prefer to avoid that.)
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
