[sqlalchemy] Re: Feature suggestion: Description attribute in Tables/Columns
Am Mittwoch, 19. September 2007 23:44 schrieb Paul Johnston:
Hi,
For my documentation, it is very handy to gather the table definitions
directly from my SQLAlchemy-based python code - that's relatively easy, I
This would be useful for me too, and it would be good to have hooks to
store arbitrary information. I'd be using this as hints for a form
builder, just like the Django auto-admin does.
I currently do it like this:
class DocTable(Table):
Extends Table by adding a description attribute
def __init__(self, name, metadata, **kwargs):
Extract some parameters and call parent
description = kwargs.pop('description', None)
self.description = description
super(DocTable, self).__init__(name, metadata, **kwargs)
class DocColumn(Column):
Extends Column by adding a description attribute
def __init__(self, name, type_, *args, **kwargs):
Extract some parameters and call parent
description = kwargs.pop('description', None)
self.description = description
super(DocColumn, self).__init__(name, type_, *args, **kwargs)
And then I use DocTable / DocColumn instead of Table/Column.
Regards,
Hermann
--
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7
--~--~-~--~~~---~--~~
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: Auto load problem with SQLAlchemy 0.3.10 and mySQL
Noufal wrote:
snip
I create two tables like so
run_table = sa.Table('runs',md,
sa.Column('rid', sa.Integer,
primary_key=True),
sa.Column('cmdline', sa.String(250)),
sa.Column('hostname', sa.String(20)),
sa.Column('workdir', sa.String(250)),
sa.Column('incremental', sa.Boolean),
sa.Column('user', sa.String(20)),
sa.Column('starttime', sa.TIMESTAMP),
sa.Column('endtime', sa.TIMESTAMP),
sa.Column('status',sa.String(20)),
sa.Column('machinetype',sa.String(20))
)
run_table.create()
stats_table = sa.Table('stats',md,
sa.Column('sid',sa.Integer,primary_key=True),
sa.Column('rid',sa.Integer,sa.ForeignKey('runs.rid')),
sa.Column('stagename',sa.String(50)),
sa.Column('description',sa.String(250)),
sa.Column('starttime',sa.TIMESTAMP),
sa.Column('endtime',sa.TIMESTAMP))
stats_table.create()
Then I can actually use these tables.
However, if I autoload them like so.
run_table = sa.Table('runs', md, autoload=True)
stats_table = sa.Table('stats', md, autoload=True)
(md is the metadata)
I get an error. The final assertion raised is like so
sqlalchemy.exceptions.ArgumentError: Error determining primary and/or
secondary join for relationship 'Run.stages (Stats)'. If the
underlying error cannot be corrected, you should specify the
'primaryjoin' (and 'secondaryjoin', if there is an association table
present) keyword arguments to the relation() function (or for
backrefs, by specifying the backref using the backref() function with
keyword arguments) to explicitly specify the join conditions. Nested
error is Can't find any foreign key relationships between 'runs' and
'stats'
snip
The table reflection doesn't seem to be picking up the foreign key
definition from stats.rid to runs.rid. In 0.3.10, SA uses a regular
expression on the output of 'SHOW CREATE TABLE' to find foreign keys
(round about line 1169 of databases/mysql.py). You could run 'SHOW
CREATE TABLE stats' yourself and try and figure out why the regular
expression isn't matching.
http://www.sqlalchemy.org/trac/browser/sqlalchemy/tags/rel_0_3_10/lib/s
qlalchemy/databases/mysql.py#L1169
However, you can provide your own foreign key definition while still
autoloading the table. Something like this should work:
stats_table = sa.Table('stats', md,
sa.ForeignKeyConstraint(['stats.rid'],
['runs.rid']),
autoload=True)
Also, I believe mysql table reflection is being completely reworked in
0.4, so the issue might go away.
Hope that helps,
Simon
--~--~-~--~~~---~--~~
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] LIMIT syntax in old versions of MySQL
Hi, The ancient version of MySQL that I am connecting to (3.23.58) doesn't support the syntax 'LIMIT limit OFFSET offset' syntax. Instead, it uses LIMIT offset, limit. This is described in the docs, but it doesn't say what version introduced the more standard syntax: http://dev.mysql.com/doc/refman/5.0/en/select.html#id3376456 I'm currently monkeypatching MySQLCompiler to use LIMIT offset, limit. Would it make sense for MySQLCompiler to be changed to always use that syntax, since it is supported in more MySQL versions than the standard? Thanks, Simon --~--~-~--~~~---~--~~ 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] SQLAlchemy: like and security (sql injection attacks)
Hi,
I have a question related to sql injection when using a clause like
this: User.c.username.like('%' + userinput + '%')
What restrictions do I have to put on the variable userinput? Of course,
I will ensure that is no percent character ('%') in userinput. Is that
enough (assuming that SQLAlchemy will do the rest by applying
database-specific quoting rules) or do I need to filter more characters?
Is this specific for database used?
Thank you very much
fs
--~--~-~--~~~---~--~~
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: Insert select results
It's on the to-do. This would be a great place to start hacking on SA if you're interested, it's a feature that's been requested a few times now. --~--~-~--~~~---~--~~ 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: Feature suggestion: Description attribute in Tables/Columns
This is Python, after all, and it would be trivial to simply put whatever attribute you want on a Table, Column or any SA object. SA would just need to stay out of the way and agree not to use a certain attribute like description or userdata, or whatever. --~--~-~--~~~---~--~~ 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: SQLAlchemy: like and security (sql injection attacks)
Don't build SQL strings up from fragments that contain user input -- it's what makes the application subject to SQL injection in the first place. Safest would be to use a bound parameter for the literal. See here for details: http://www.sqlalchemy.org/docs/04/sqlexpression.html#sql_everythingelse_bindparams --~--~-~--~~~---~--~~ 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] Nested selects
Hi all, is it possible to do nested selects in sqlalchemy? For example, something like: select p.id from mytable as p where p.number = (select max(number) from mytable as p2 where p2.attribute = p.attribute) I'm using sqlalchemy 0.3.10 regards, Bruno --~--~-~--~~~---~--~~ 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: Auto load problem with SQLAlchemy 0.3.10 and mySQL
Noufal wrote:
Hello everyone,
I've recently picked up SQLAlchemy for a project that I'm working
on. I couldn't get a version newer than 0.3.10 (admin bureaucracy) and
have to use this.
I create two tables like so
run_table = sa.Table('runs',md,
sa.Column('rid', sa.Integer,
primary_key=True),
sa.Column('cmdline', sa.String(250)),
sa.Column('hostname', sa.String(20)),
sa.Column('workdir', sa.String(250)),
sa.Column('incremental', sa.Boolean),
sa.Column('user', sa.String(20)),
sa.Column('starttime', sa.TIMESTAMP),
sa.Column('endtime', sa.TIMESTAMP),
sa.Column('status',sa.String(20)),
sa.Column('machinetype',sa.String(20))
)
run_table.create()
stats_table = sa.Table('stats',md,
sa.Column('sid',sa.Integer,primary_key=True),
sa.Column('rid',sa.Integer,sa.ForeignKey('runs.rid')),
sa.Column('stagename',sa.String(50)),
sa.Column('description',sa.String(250)),
sa.Column('starttime',sa.TIMESTAMP),
sa.Column('endtime',sa.TIMESTAMP))
stats_table.create()
Then I can actually use these tables.
However, if I autoload them like so.
run_table = sa.Table('runs', md, autoload=True)
stats_table = sa.Table('stats', md, autoload=True)
(md is the metadata)
I get an error. The final assertion raised is like so
sqlalchemy.exceptions.ArgumentError: Error determining primary and/or
secondary join for relationship 'Run.stages (Stats)'. If the
underlying error cannot be corrected, you should specify the
'primaryjoin' (and 'secondaryjoin', if there is an association table
present) keyword arguments to the relation() function (or for
backrefs, by specifying the backref using the backref() function with
keyword arguments) to explicitly specify the join conditions. Nested
error is Can't find any foreign key relationships between 'runs' and
'stats'
Am I doing something wrong or is there something else wrong here? If
it's a bug that's been fixed in later versions, is there some kind of
a workaround I could use?
Did you originally create the tables through SQLAlchemy or are you
reflecting an existing schema? In either case I'd need to see the
output of SHOW CREATE TABLES for the problem table to make a diagnosis.
You can workaround this by manually adding a FK after autoloading:
stats_table.append_constraint(
sa.ForeignKeyConstraint(['rid'], ['runs.rid']))
--~--~-~--~~~---~--~~
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: Nested selects
Yes, those are called subqueries; they're fully supported by SA. Your query above has a couple of items of note: a) it's a correlated subquery: the inner query references items in the outer query. (supported by SA) b) it's a self-join: the inner query and outer query reference the same table. (also supported by SA) your query roughly translates into: p2 = p.alias() select([p.c.id], p.c.number == select(func.max(p2.c.number), p2.c.attribute == p.attribute)) --~--~-~--~~~---~--~~ 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: Nested selects
Thanks! I'll try it! On 9/20/07, Rick Morrison [EMAIL PROTECTED] wrote: Yes, those are called subqueries; they're fully supported by SA. Your query above has a couple of items of note: a) it's a correlated subquery: the inner query references items in the outer query. (supported by SA) b) it's a self-join: the inner query and outer query reference the same table. (also supported by SA) your query roughly translates into: p2 = p.alias() select([p.c.id ], p.c.number == select(func.max(p2.c.number), p2.c.attribute == p.attribute)) -- um abraço, Bruno --~--~-~--~~~---~--~~ 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: Feature suggestion: Description attribute in Tables/Columns
Rick Morrison wrote: This is Python, after all, and it would be trivial to simply put whatever attribute you want on a Table, Column or any SA object. SA would just need to stay out of the way and agree not to use a certain attribute like description or userdata, or whatever. There is a ticket (#573) for adding a public bucket for arbitrary data on Tables and beyond. Getting this going is mostly just a matter of choosing a name for the attribute and adding a section about the feature to the main docs and the docstrings for affected classes. Any volunteers? ;) -j --~--~-~--~~~---~--~~ 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: SQLAlchemy: like and security (sql injection attacks)
Felix Schwarz wrote:
Hi,
I have a question related to sql injection when using a clause like
this: User.c.username.like('%' + userinput + '%')
What restrictions do I have to put on the variable userinput? Of course,
I will ensure that is no percent character ('%') in userinput. Is that
enough (assuming that SQLAlchemy will do the rest by applying
database-specific quoting rules) or do I need to filter more characters?
Is this specific for database used?
In terms of sql injection, SQLAlchemy builds literal value comparisons
as bind params, so the db-api is receiving that as a query of 'username
LIKE ?' with '%expr%' provided separately as a bind value. The db-api
then executes that safely in a database-dependent manner.
Pattern injection is your responsibility though. So removing or
escaping '%' and '_' from user input is in order. And probably the
escape character as well...
I don't believe that SA currently has direct support for specifying the
escape character with LIKE 'expr' ESCAPE '\', though it probably should.
Other than specifying it at the expression-level, there's no universal
way I know of to deduce what the database connection's configured escape
character is (if any).
--~--~-~--~~~---~--~~
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: Auto load problem with SQLAlchemy 0.3.10 and mySQL
Did you originally create the tables through SQLAlchemy or are you reflecting an existing schema? In either case I'd need to see the output of SHOW CREATE TABLES for the problem table to make a diagnosis. I create the tables using SQLAlchemy. I'll send you the output in a day, I'm away from the machine where this code is right now. You can workaround this by manually adding a FK after autoloading: stats_table.append_constraint( sa.ForeignKeyConstraint(['rid'], ['runs.rid'])) I'll try that. Thanks for the 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: LIMIT syntax in old versions of MySQL
King Simon-NFHD78 wrote: Hi, The ancient version of MySQL that I am connecting to (3.23.58) doesn't support the syntax 'LIMIT limit OFFSET offset' syntax. Instead, it uses LIMIT offset, limit. This is described in the docs, but it doesn't say what version introduced the more standard syntax: http://dev.mysql.com/doc/refman/5.0/en/select.html#id3376456 I'm currently monkeypatching MySQLCompiler to use LIMIT offset, limit. Would it make sense for MySQLCompiler to be changed to always use that syntax, since it is supported in more MySQL versions than the standard? OFFSET was added after v3. If the offset,limit syntax doesn't generate any warnings on 5.1 or 6.0, sure that can go in. Could you post a patch (or your monkeypatch) to the trac? Cheers, Jason --~--~-~--~~~---~--~~ 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] Import problem
This might trivial to most of u but Iam having problems importing sqlalchemy on windows. The installation went without a problem but when i went to site packages directory there was a SQLAlchemy-0.3.10-py2.5.egg folder and no folder called sqlalchemy or a .py file. Moreover i cant import sqlalchemy. It gives module not found error. Can some one help me out? Goutham --~--~-~--~~~---~--~~ 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: Import problem
what error do you get is you enter import sqlalchemy Jose Goutham Lakshminarayan wrote: This might trivial to most of u but Iam having problems importing sqlalchemy on windows. The installation went without a problem but when i went to site packages directory there was a SQLAlchemy-0.3.10-py2.5.egg folder and no folder called sqlalchemy or a .py file. Moreover i cant import sqlalchemy. It gives module not found error. Can some one help me out? Goutham --~--~-~--~~~---~--~~ 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: Import problem
Its an import error. The module doesnt exist. There is only a folder called SQLAlchemy-0.3.10-py2.5.egg(This is a folder,not an EGG file) in my site packages. Nothing else related to sqlalchemy is there in the sitepackages.The installation went fine. The problem is there is no sqlalchemy folder or a .py script in the site packages to import from. Goutham --~--~-~--~~~---~--~~ 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: Auto load problem with SQLAlchemy 0.3.10 and mySQL
On Sep 20, 10:34 pm, Noufal [EMAIL PROTECTED] wrote: [..] I create the tables using SQLAlchemy. I'll send you the output in a day, I'm away from the machine where this code is right now. The output of the create table column looks like this CREATE TABLE `stats` ( `sid` int(11) NOT NULL auto_increment, `rid` int(11) default NULL, `stagename` varchar(50) default NULL, `description` varchar(250) default NULL, `starttime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `endtime` timestamp NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`sid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
