[sqlalchemy] Tool to check whether DB matches model
Hi, Is there a tool to check whether the DB matches the model? Something like SQLObject's status command. I'm sure I noticed something along these lines on the site, that even did rudimentary automatic alter table commands, but I can't find it now! 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: polymorphic mapping with more than 2 level of inheritance
So, ive been working on this crapola pretty much all day... WAW! hey, don't overdose... (YOURE WELCOME) ... Hhmm. i don't believe in virtual beers, so maybe, treat you with this? http://www.giovannisample.com/media/mondo/Mondovision640.zip and the latest is in a branch http://svn.sqlalchemy.org/sqlalchemy/ branches/polymorphic_relations . i'll test how far it goes... quick test: This one fails some of the AB_all cases (the trunk passes them all - total 139). All failures are of same kind - the inheriting mapper (B) does not load some of it's references. i think there was similar error before and u did fix it then. see attachments (run sa_ref_A_B_A_all.py eager generate_many failed_only to get them all as sep.files) i'll check more on how A,B,C behaves. so, the one thing i really cannot crack at all is how to make polymorphic_union figure out the dupe id column in: table_Employee.join(table_Engineer).select(table_Employee.c.atype == 'Engineer'), since the embedded list of columns comes out only at compilation time for the query. so i think i want to look into modifying Select() to detect this internally and just raise an error. you cant say use_labels on this particular query either because polymorphic_union needs the real column names in order to determine the names for the union. all you have to say is: select([table_Employee, table_Engineer.c.machine], table_Employee.c.atype == 'Engineer', from_obj=[table_Employee.join(table_Engineer)]), and it works, since you manually construct a column list that doesnt contain a dupe. u mean to put the explicit-column select() instead of polumuion's entry for Engineer? okay i'll try this approach... --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- poly-branch-fails.tbz Description: application/tbz sa_gentestbase.py Description: application/python
[sqlalchemy] Re: polymorphic mapping with more than 2 level of inheritance
all tests pass with rev 2267 of that branch. try that rev specifically, since i want to take whats there and do another pass. im trying to get it so that the entire science of parent table, child table, polymorphic selectables, primary join - polymorphic joins - determine direction/lazy clause/eager clause/synchronize FKs is super-well-nailed down. On Jan 28, 2007, at 11:16 AM, [EMAIL PROTECTED] wrote: So, ive been working on this crapola pretty much all day... WAW! hey, don't overdose... (YOURE WELCOME) ... Hhmm. i don't believe in virtual beers, so maybe, treat you with this? http://www.giovannisample.com/media/mondo/Mondovision640.zip and the latest is in a branch http://svn.sqlalchemy.org/sqlalchemy/ branches/polymorphic_relations . i'll test how far it goes... quick test: This one fails some of the AB_all cases (the trunk passes them all - total 139). All failures are of same kind - the inheriting mapper (B) does not load some of it's references. i think there was similar error before and u did fix it then. see attachments (run sa_ref_A_B_A_all.py eager generate_many failed_only to get them all as sep.files) i'll check more on how A,B,C behaves. so, the one thing i really cannot crack at all is how to make polymorphic_union figure out the dupe id column in: table_Employee.join(table_Engineer).select(table_Employee.c.atype == 'Engineer'), since the embedded list of columns comes out only at compilation time for the query. so i think i want to look into modifying Select() to detect this internally and just raise an error. you cant say use_labels on this particular query either because polymorphic_union needs the real column names in order to determine the names for the union. all you have to say is: select([table_Employee, table_Engineer.c.machine], table_Employee.c.atype == 'Engineer', from_obj=[table_Employee.join(table_Engineer)]), and it works, since you manually construct a column list that doesnt contain a dupe. u mean to put the explicit-column select() instead of polumuion's entry for Engineer? okay i'll try this approach... poly-branch-fails.tbz sa_gentestbase.py --~--~-~--~~~---~--~~ 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 mapping with more than 2 level of inheritance
all tests pass with rev 2267 of that branch. try that rev specifically, since i want to take whats there and do another pass. im trying to get it so that the entire science of parent table, child table, polymorphic selectables, primary join - polymorphic joins - determine direction/lazy clause/eager clause/synchronize FKs is super-well-nailed down. okay, give me a day. i've done the bruteforce A-B-C combinations test and now trying to classify the results... one thing that hickups is that i hit some memory leak or something - 4000 times setuping and tearing-down SA with sqlite/memory fills the available 2Gig RAM. That is 500K per session is leaking. i guess it might be sqlite problem, as gc didn't reveal anything... i'll try on postgres tomorrow. Is there any particular way of stopping SQLalchemy, so it force-releases all connections, removes all caches etc etc ? bye svil --~--~-~--~~~---~--~~ 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: Tagging example
I'm a SA newbie, so bare with me :) This is the SQL query for a tag cloud, returning each tag_name and it's weight (the count of page tagged with this tag). SELECT tag_name, COUNT(page_id) AS quantity FROM pages_tags JOIN tags USING (tag_id) GROUP BY tags.tag_id ORDER BY quantity DESC; What's the SA equivalent? --~--~-~--~~~---~--~~ 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 mapping with more than 2 level of inheritance
clear_mappers() engine.dispose() let sessions and mapped objects fall out of scope ...and thats pretty much it. however, you really shouldnt be opening up a brand new sqlite:// connection for every test. you should be using the same engine for the whole program, and just do a metadata.drop_all() each time. which leads to the next thing, it would be extremely helpful if you converted this program to work as a regular SA unit test so i can check it in somewhere (i.e. using testbase.AssertMixin or testbase.ORMTest as a base class). definitely stick to the nested loops style of testing all the cases, it would be madness to make a 300 meg source file (like that generation script does). that way the tests could embed into all the other services the testbase offers (testing across all databases, turning on code coverage, forcing different connection pool options, etc). On Jan 28, 3:55 pm, [EMAIL PROTECTED] wrote: all tests pass with rev 2267 of that branch. try that rev specifically, since i want to take whats there and do another pass. im trying to get it so that the entire science of parent table, child table, polymorphic selectables, primary join - polymorphic joins - determine direction/lazy clause/eager clause/synchronize FKs is super-well-nailed down.okay, give me a day. i've done the bruteforce A-B-C combinations test and now trying to classify the results... one thing that hickups is that i hit some memory leak or something - 4000 times setuping and tearing-down SA with sqlite/memory fills the available 2Gig RAM. That is 500K per session is leaking. i guess it might be sqlite problem, as gc didn't reveal anything... i'll try on postgres tomorrow. Is there any particular way of stopping SQLalchemy, so it force-releases all connections, removes all caches etc etc ? bye svil --~--~-~--~~~---~--~~ 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: Tool to check whether DB matches model
you might be thinking of migrate (http://trac.erosson.com/migrate ) but thats not exactly what you describe here. (i dont actually know what SO's status command does). On Jan 28, 4:58 am, Paul Johnston [EMAIL PROTECTED] wrote: Hi, Is there a tool to check whether the DB matches the model? Something like SQLObject's status command. I'm sure I noticed something along these lines on the site, that even did rudimentary automatic alter table commands, but I can't find it now! 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] An assign_mapper question
Hello folks, I'm playing around with using assign_mapper inside of a Turbogears projects, and I ran into a small problem. My assign_mappers work fine for taking things out of the database, but if I pull an object out, and add a pre-existing element(say a user to a session) to a relation and then attempt to put them back, I get errors about key conflicts because sqlalchemy attempts to insert the pre-existing elements, causing key conflicts. I can get around this by just manipulating the secondary join tables manually, but the assign_mappers are so cool, I would love to be able to change things around to get them working. Here's my model building code. What I'm doing here is using a dictionary describing the relations to set up my assignmappers in order to use secondary joins. There's some stuff that I cut out here to make things more readable, so this code won't run, but if anybody is interested I can provide the whole thing. http://euler.cs.umb.edu/pastes/model.html Here's the code where I'm attempting to acces the database and getting into trouble: http://euler.cs.umb.edu/pastes/access.html Here's the stack trace: http://euler.cs.umb.edu/pastes/ Thanks, Alex --~--~-~--~~~---~--~~ 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] [Solved] Avoiding SQL injection with raw-ish queries (MySQL FULLTEXT search)
I finally discovered the Using Bind Parameters in Text Blocks section of the SQLAlchemy manual -- very useful and very easy to use. Perhaps this will help others who are trying to search against MySQL's FULLTEXT index safely. FWIW, I'm doing this in Pylons. Here's what I ended up doing: t = metadata.engine.text( SELECT ROUND(MATCH(message) AGAINST(:message), 2) AS score, facility,severity,message,explanation,solution,significance,os FROM kb WHERE MATCH(message) AGAINST(:message) AND facility=:facility AND severity=:severity LIMIT :limit ) c.results = t.execute(message=text, facility=fac, severity=sev, limit=100).fetchall() If you echo the SQL it's using, you can see how it quotes any query parameters that have quotes in them. Slick. Thanks for such a nice tool! --~--~-~--~~~---~--~~ 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] Help me catching a SQLError IntegrityError bij using try and except
Hello,
I have a problem catching a sqlalchemy error in a try and expect.
You see in the model that my user_name must be unique. So if the is a
user_name like Ken and i fill in my form the name Ken for user_name
i will get an error like this:
SQLError: (IntegrityError) column user_name is not unique
So i like to catch the SQLError in the expect.
Model code:
users_table = Table ('users', metadata,
Column('user_id', Integer, primary_key=True),
Column('user_name', Unicode(16), unique=True),
Column('user_lastname', Unicode(255)),
Column('user_zipcode', Unicode(6)),
Column('display_name', Unicode(255),),
Column('password', Unicode(40)),
Column('created', Date, default=date.today)
)
class Users(object):
pass
assign_mapper(session.context, Users, users_table)
Controller code:
@expose()
def drop_user(self, user_id=None, name=, last_name=, zipcode=,
password=, msg=):
try:
user = Users()
user.user_name = 'Ken'
session.save(user)
session.flush()
except Exception, e:
turbogears.flash(e)
return dict()
It's not working so can anyone tel me what to do.
Greets,
Ken van Riel
--~--~-~--~~~---~--~~
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 and business object verification??
I am planning on using sqlalchemy to build the api for a database I am developing, and I was wondering if there is any type of column verification that occurs before database commit. I.E.: a string column with length 40 would throw a verification exception if a value longer that 40 characters was placed into it and saved. Additionally has anyone thought of implementing some sort of verification support for objects that are mapped which would allow the object to perform pre database action logic? The intent being that the instance would check to see that the object meets additional business logic requirements before it is inserted or updated. I.E.: a User business object would verify that the userid was part of the [a-z] [A-Z]and [0-9] character classes, and if not an exception would be raised to prevent the database action. My sincerest apologies If this functionality already exists for sqlalchemy. If the functionality exists please point me in the right direction. If not I would be interested in some help developing the functionality for my uses, and then returning the code to the sqlalchemy repository if the community thinks that the features are needed/useful. --~--~-~--~~~---~--~~ 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] Avoiding SQL injection with raw-ish queries (MySQL FULLTEXT search)
I'm doing a query against a MySQL table that has a column which has
a fulltext index, so I need to do some raw-ish queries. Problem is
that these open me up to SQL injection attacks. How do I avoid them --
bound variables? filtering of quotes and funny chars?
I create the index on a table already created with SQLAlchemy by:
metadata.engine.execute(ALTER TABLE kb ADD FULLTEXT(message))
I later query against it with two exact matches and a MATCH...AGAINST
the fulltext index:
query =
SELECT MATCH(message) AGAINST('%s') AS score,
facility,severity,message
FROM kb
WHERE MATCH(message) AGAINST('%s')
AND facility='%s'
AND severity='%s'
LIMIT %s
query = query % (text,text,fac,sev,100)
results = metadata.engine.execute(query).fetchall()
This works nicely most of the time.
If one of the parameters includes a single-quote, however, it breaks
the query. This seems a avenue for an SQL injection attack.
Problem is I can't figure out how to use SQLAlchemy's bound variables
to re-implement the query.
I can remove the SELECT part of the query string and do something like:
query =
MATCH(message) AGAINST('%s') AS score,
facility,severity,message
FROM kb
WHERE MATCH(message) AGAINST('%s')
AND severity='%s'
query = query % (text,text,sev)
results = select([query], engine=metadata.engine).execute().fetchall()
But that doesn't help with the SQL injection.
If I remove the AND portion from the query string and move it into the
execute():
results = select([query],
engine=metadata.engine).execute(severity=sev).fetchall()
it doesn't help at all -- the SQL that's echoed indicates no
AND severity... at all and I get too many results.
Is there a way I can do the MATCH...AGAINST but using bound variables,
or some other way that SQLAlchemy can protect me from injection
attacks?
If not, how do you recommend I sanitize the user-supplied query
parameters so the query can't be exploited?
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: Column and business object verification??
On Jan 24, 1:50 am, chris e [EMAIL PROTECTED] wrote: I am planning on using sqlalchemy to build the api for a database I am developing, and I was wondering if there is any type of column verification that occurs before database commit. I.E.: a string column with length 40 would throw a verification exception if a value longer that 40 characters was placed into it and saved. your database will throw an error. why reinvent the wheel ? Additionally has anyone thought of implementing some sort of verification support for objects that are mapped which would allow the object to perform pre database action logic? The intent being that the instance would check to see that the object meets additional business logic requirements before it is inserted or updated. I.E.: a User business object would verify that the userid was part of the [a-z] [A-Z]and [0-9] character classes, and if not an exception would be raised to prevent the database action. thats exactly the kind of thing you should write into your application. has nothing to do with an ORM. for generic validation widgets to help, check out formencode ( http://formencode.org/ ). --~--~-~--~~~---~--~~ 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: Help me catching a SQLError IntegrityError bij using try and except
my apologies as this email was caught in the spam filter for a few
days (along with seven others...). i dont know turbogears but your
code looks fine to me, a try/except around the flush() should catch
any issues within.
On Jan 26, 5:07 pm, ken.riel [EMAIL PROTECTED] wrote:
Hello,
I have a problem catching a sqlalchemy error in a try and expect.
You see in the model that my user_name must be unique. So if the is a
user_name like Ken and i fill in my form the name Ken for user_name
i will get an error like this:
SQLError: (IntegrityError) column user_name is not unique
So i like to catch the SQLError in the expect.
Model code:
users_table = Table ('users', metadata,
Column('user_id', Integer, primary_key=True),
Column('user_name', Unicode(16), unique=True),
Column('user_lastname', Unicode(255)),
Column('user_zipcode', Unicode(6)),
Column('display_name', Unicode(255),),
Column('password', Unicode(40)),
Column('created', Date, default=date.today)
)
class Users(object):
pass
assign_mapper(session.context, Users, users_table)
Controller code:
@expose()
def drop_user(self, user_id=None, name=, last_name=, zipcode=,
password=, msg=):
try:
user = Users()
user.user_name = 'Ken'
session.save(user)
session.flush()
except Exception, e:
turbogears.flash(e)
return dict()
It's not working so can anyone tel me what to do.
Greets,
Ken van Riel
--~--~-~--~~~---~--~~
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: An assign_mapper question
On Jan 28, 12:37 am, avegas [EMAIL PROTECTED] wrote: I'm playing around with using assign_mapper inside of a Turbogears projects, and I ran into a small problem. My assign_mappers work fine for taking things out of the database, but if I pull an object out, and add a pre-existing element(say a user to a session) to a relation and then attempt to put them back, I get errors about key conflicts because sqlalchemy attempts to insert the pre-existing elements, causing key conflicts. im dont know TG much and didnt really understand your examples, but if youre getting key conflicts because you are re-saving instancesactually im not sure how youre pulling that off unless you are specifically blowing away the _instance_key element on the object...once an instance is INSERTed thats the only way it would conceivably INSERT it again. other than that try to let SA handle creating your primary key values, in the case that you are setting the PK attributes on the instances yourself and saving over and over again. --~--~-~--~~~---~--~~ 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] working with multiple databases
Hello,
I'm struggling to setup SA/Pylons for a multidatabase env without much
luck.
As far as I understand, I need a session per database. In Pylons, I get
it for free via session_context binding. In other words, I have setup
a session_context object for each of the database I need to work with.
db.get_db_session('blog')
sqlalchemy.orm.session.Session object at 0xb7337a8c
db.get_db_session('cdb')
sqlalchemy.orm.session.Session object at 0xb720f7ec
Nevertheless, it is not usable:
model.blog_wp_posts_tbl.count().execute()
Traceback (most recent call last):
File console, line 1, in ?
File build/bdist.linux-i686/egg/sqlalchemy/sql.py, line 474, in
execute
File build/bdist.linux-i686/egg/sqlalchemy/sql.py, line 411, in
execute
except Exception ,e:
InvalidRequestError: This Compiled object is not bound to any engine.
I suppose my metadata setup is wrong. Currently it looks like this:
blog_meta = DynamicMetaData()
blog_wp_posts_tbl = Table('wp_posts', blog_meta,
Column('ID', Integer, primary_key=True),
Column('guid', String(255)),
Column('post_author', Integer, ForeignKey('wp_users.ID'),
nullable=False),
Column('post_content', Unicode),
)
Do I need to use separate DynamicMetaData() for each db conn? Do I need
to connect single metadata to the correct session? SA docs shows how to
connect metadata to a engine but all I have is a session.
Max.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
