[sqlalchemy] Off Topic: Declarative-style for XML?
Hello, I've become quite used to SQLAlchemy's Declarative style notation for defining data to be stored in SQL. Does anyone know of library that will do something similar for simple XML? Basically I need objects (and attributed collections of objects) that are effectively records for groups of simple data types like strings, dates, numbers, etc. Googling the subject returns some libraries that will parse arbitrary XML and return python objects, but I'd like to go the other way... define a python object and strictly generate both a schema and an XML representation of instances. Any ideas? Thank you, :) -- 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] UNION with SQLAlchemy: Some questions
Hello, [Note: This is a revised edition of my previous post multi table select] I have events logged to several diffeerent tables, and I need to get the union of these different tables. (The event types use different tables for various reasons.) Here is what I am doing now, and it seems to work: # Note the static 'event_type' columns stmt = 'SELECT * FROM (SELECT A AS event_type, user_id as user, notes, time_created ' stmt = stmt + 'FROM events_a WHERE origin_mobile_number = :uid ' stmt = stmt + 'UNION ALL ' stmt = stmt + 'SELECT SELECT B AS event_type, user_id as user, notes, time_created ' stmt = stmt + 'FROM events_b WHERE user_id = :uid) as events_all ' stmt = stmt + 'ORDER BY events_all.time_created DESC' res = DBSession.execute(stmt, dict(uid = uid)) records = res.fetchall() So I have several questions: 1) Is the the raw SQL I am using sane? 2) How can I use SQLAlchemy to simply things? 3) How would I add tables events_c? events_d? 4) Since the events_x tables are already defined with declarative_base, is there a reasonable way to make an SQLAlchemy Events object that knows where to retrieve and insert event records based on the event_type field? Thank you, AF --~--~-~--~~~---~--~~ 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] [TYPO FIX] UNION with SQLAlchemy: Some questions
Hello, [Note: This is a revised edition of my previous post multi table select] I have events logged to several diffeerent tables, and I need to get the union of these different tables. (The event types use different tables for various reasons.) Here is what I am doing now, and it seems to work: # Note the static 'event_type' columns stmt = 'SELECT * FROM (SELECT A AS event_type, user_id as user, notes, time_created ' stmt = stmt + 'FROM events_a WHERE user_id = :uid ' stmt = stmt + 'UNION ALL ' stmt = stmt + 'SELECT SELECT B AS event_type, user_id as user, notes, time_created ' stmt = stmt + 'FROM events_b WHERE user_id = :uid) as events_all ' stmt = stmt + 'ORDER BY events_all.time_created DESC' res = DBSession.execute(stmt, dict(uid = uid)) records = res.fetchall() So I have several questions: 1) Is the the raw SQL I am using sane? 2) How can I use SQLAlchemy to simply things? 3) How would I add tables events_c? events_d? 4) Since the events_x tables are already defined with declarative_base, is there a reasonable way to make an SQLAlchemy Events object that knows where to retrieve and insert event records based on the event_type field? Thank you, AF --~--~-~--~~~---~--~~ 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: Multi table select?
On Oct 27, 8:03 am, Mike Conley mconl...@gmail.com wrote:
On Mon, Oct 26, 2009 at 10:09 PM, Bobby Impollonia bob...@gmail.com wrote:
Let's say you have 2 mapped classes
class Stuff1(Base):
--- etc.
class Stuff2(Base)
--- etc.
You can do something like this
q1 = session.query(Stuff1.columnA,
Stuff1.columnB, literal_column('S1').label('source'))
q2 = session.query(Stuff2.columnA,
Stuff2.columnB, literal_column('S2').label('source'))
subq = session.query().from_statement(union_all(q1, q2)).subquery()
qry = session.query(subq)
Note: just in case your font makes the quotes hard to read,
'S1' is double-quote,single-quote,S,1,single-quote,double-quote
Thank you for the help I've been playing with the code, but have
been unable to make it work.
Firstly, I'm not really clear on ideas behind, subquery vs. a regular
query, but I did figure out to change:
union_all(q1, q2) to union_all(q1.statement, q2.statement)
It seems to return results, but now I need to to do more things:
1) Sort the results by time_stamp (newest first)
2) Optionally, filter by specific user_id
For #1 tried doing q = DBSession.query(subq).order_by(desc
('time_stamp')), and while it seems to work, I don't understand the
SQL it generates:
SELECT anon_1.type AS anon_1_type, anon_1.time AS anon_1_time
[..]
FROM ([]) AS anon_1 ORDER BY time_stamp DESC
Why AS anon_1 ORDER BY time_stamp DESC, and not AS anon_1 ORDER BY
anon_1_time_stamp DESC?
For #2, I am not sure where to best put the filter clause. In q1/q2?
In subq?
Thank you,
:)
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Report generation?
Hello, For instance if I have data tables for users, orders, and order_line_items how would I generate a report that lists: 1) users, 2) and for each user, his/her orders, 3) and for each order, all the order_line_items I guess I could do it in a loop in python, but I wonder of there is a better way using SQLAlchemy. Thank you, :) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Multi table select?
Hello, I don't know if this is even possible is SQL, so please bear with me :) There are a couple a tables (say, a b) that are used as logs for two different processes. They both have the same simple structure. (id, time_stamp, user_id, message) I would like to create a query that merges the data and returns following results: time_stamp, user_id, a_or_b, message (where a_or_b is a value that indicates which table the data row came from) Can this be done in SQL/SQLAlchemy. Thank you, :) p.s. Alternatively, the message columns do not need to be merged though I guess time_stamp / user would still need to be. That is: time_stamp, user_id, message_a, message_b I don't know if that makes any easier... --~--~-~--~~~---~--~~ 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: Multi table select?
On Oct 26, 8:48 pm, AF allen.fow...@yahoo.com wrote: Hello, I don't know if this is even possible is SQL, so please bear with me :) There are a couple a tables (say, a b) that are used as logs for two different processes. They both have the same simple structure. (id, time_stamp, user_id, message) I would like to create a query that merges the data and returns following results: time_stamp, user_id, a_or_b, message (where a_or_b is a value that indicates which table the data row came from) Can this be done in SQL/SQLAlchemy. Thank you, :) p.s. Alternatively, the message columns do not need to be merged though I guess time_stamp / user would still need to be. That is: time_stamp, user_id, message_a, message_b I don't know if that makes any easier... OK: http://www.w3schools.com/Sql/sql_union.asp Doh. OK, so now I have an SQL statement I wrote by hand that works fine, but I still have two questions: 1) Can this be done via the SQA ORM? 2) If not, how should I I build this using non-ORM SQA? Thank you, :) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Testing SQLAlchemy based app? (Tutorial?)
Hello, I'm writing a basic SQLAlchemy application, and have started to explore the concept of unit testing in general and specifically with nose. Since I am new to both SQLAlchey and nose, I do not know where to start. Right now the app is at the point where: 1) The tables are defined with declarative base. 2) I've made a few functions to populate the database from a series of source data files. (The data files are text, but are an odd almost csv format.) 3) I've made a few functions to dump the database back to these data files. So now, I would like to construct a series of tests to ensure the database is really being populated correctly based on sample data files. (And that the database can be correctly saved back out to these data files.) Do you have any suggestions / tutorials you can point me towards? Thank you, :) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] VIEW alternative in SQLAlchemy
Hello, I have a table of records in the database that I want to run read queries against, but I do want to include all of them in the search. (There are a couple of filtering parameters to exclude records from the searched pool, including an aptly named is_active flag.) Traditionally, I would think to put this in to a VIEW, and let the DB optimise out all the non-active records. Since SQLAlchemy does appear to support views, what is the correct way to handle this? (Or did i miss it?) I am using declarative base. 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] Declarative Base: remote_side single_parent
Hello, What is the correct way to use remote_side single_parent relation parameters under Declarative Base? I'm trying to create an Adjacency List Relationship as suggested in the docs, but I am not sure how to do this with Declarative Base. I tried: children = relation(Node, backref=backref(parent, remote_side=nodes.id)) But got: InvalidRequestError: When compiling mapper Mapper|Node|nodes, expression 'nodes.id' failed to locate a name (name 'nodes' is not defined). If this is a class name, consider adding this relation() to the class 'db.Node' class after both dependent classes have been defined. Thank you, :) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Materialized Path for SQLAlchemy Declarative Base
Hello all, Has anyone here used the sqlamp: Materialized Path for SQLAlchemy library? I am wondering: 1) Does it seem to work well? 2) Did you use it with Declarative Base, and if so, how did you configure it? Thank you, :) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Suggestions for db connect string configuration?
Hello, Where do you folks recommend storing the database connection string in my application. Clearly not in the same file with my declaratively defined model objects. And more generally, how do you recommend laying out an SQLAlchemy based application? (In what files to define the engine, session, other objects, etc..) Sort of looking for best practices, I guess Thank you, :) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Hierarchical data: Get all (sub-) children? Parents?
Hello, Given hierarchical data similar to: http://www.sqlalchemy.org/docs/05/mappers.html?#adjacency-list-relationships With out resorting to brute force recursive queries in my objects: 1) Is there any way to retrieve all a node's children / sub-children? 2) Is there a way to retrieve the list of a nodes parents? Thank you, :) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] CircularDependencyError? (Wrong approach?)
Hello,
I have tables: Users Departments. (SQlite)
Each User is assigned to one Department. (User.dept_id =
Department.id )
Now, I want to make once User in each Dept the default user.
As in:
Department.default_user_id = User.id
i.,e. Column('default_user_id', ForeignKey('users.id'))
However I get a CircularDependencyError from SQLAlchemy.
Any ideas?
Thank you,
:)
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Showing only unused User Choices?
Hello Assuming a table of Users, Choices and UserChoices... (many-to-many) How can I generate a list for a user showing only the choices they have _not_ opted in for? Thank you --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Random value for field by default?
Hello, Perhaps this is more of a Python question that SQLalchemy.. but... How can I assign a random number to a DB field by default? I tried: default = random.randrange(1000,1) on the table definition, but I get the same number each time? Ideas? --~--~-~--~~~---~--~~ 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] Validators: Define at table / mapper level ?
Hello, Can validators be defined at table / mapper level? (Is it even a good idea?) I ask, since it's at the table definition layer that I define what datatypes my columns have, so it seems natural to place the policing function there as well. :) --~--~-~--~~~---~--~~ 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] filtered or computed version of an existing relation?
OK, next question.
Well... two related questions. :)
1) In general, inside an object's method def, where I am doing
arbitrary calculations, how can I get access to the session the object
is bound to in order to run other queries?
2) More specifically, I want to make a filtered and computed
version of an existing relation, how should I do this?
See the code below.
Thank you,
AF
Code:
=
users_table = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
Column('room', ForeignKey('rooms.id')),
Column('height', Numeric)
Column('gender', String(1))
)
rooms_table = Table('rooms', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
Column('size', Integer)
)
metadata.drop_all(engine)
metadata.create_all(engine)
class user(object):
pass
class room(object):
def percent_men(self):
# How to code these?
# Must return the % of men vs. women
# in this room.
def room.percent_of_all_users(self):
# % of users here vs. count of all users
def men(self):
# just the male users in this room
mapper(user, users_table, properties={'room' : relation('rooms.id',
backref = 'users')})
mapper(room, rooms_table)
# and here are the methods I wish to have:
percent_men = room.percent_men()
percent_of_population = room.percent_of_all_users()
--~--~-~--~~~---~--~~
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] relation error?
I'm probably just missing something... here is my code:
engine = create_engine('sqlite:///:memory:', echo=True,
convert_unicode=True)
Session = sessionmaker(bind=engine)
session = Session()
metadata = MetaData()
users_table = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
Column('room', ForeignKey('rooms.id'))
)
rooms_table = Table('rooms', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50))
)
metadata.drop_all(engine)
metadata.create_all(engine)
class user(object):
pass
class room(object):
pass
mapper(user, users_table)
mapper(room, rooms_table)
u = user()
r = room()
session.add_all([u,r])
session.commit()
[OK]
u.room = r
session.commit()
[ERROR: InterfaceError: (InterfaceError) Error binding parameter 0 -
probably unsupported type. u'UPDATE users SET room=? WHERE users.id
= ?' [__main__.room object at 0xa9a9b4c, 1] ]
Any ideas? Am I doing this wrong?
Thank you,
:)
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Can I coerce strings into Unicode?
Hello,
I'm using sqlite and convert_unicode = True on the engine.
How can I force coerce string based object attributes in to unicode?
(I had thought convert_unicode = True would do this)
Here is what I am seeing...
Setup code:
engine = create_engine('sqlite:///:memory:', echo=True,
convert_unicode=True)
Session = sessionmaker(bind=engine)
session = Session()
metadata = MetaData()
m1 = message(u'message body 1')
Now, in ipython:
In [1]: session.add(m1)
In [2]: m1.body
Out[2]: u'message body 1'
In [3]: m1.body = u'new - unicode'
In [4]: m1.body
Out[4]: u'new - unicode'
In [5]: m1.body = 'new - NOT unicode'
In [6]: m1.body
Out[6]: 'new - NOT unicode'
In [7]: unicode(m1.body)
Out[7]: u'new - NOT unicode'
Output line 6 is the problem.
Ideally, I'd like to see output lines 6 7 be the same.
Am I doing something wrong?
Thank you,
Allen
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
