gmane.comp.python.sqlobject

Storing password hashes using built-in functions like PASSWORD() (where available)

Florian Haas — 2008-09-04T10:38:02

[First post to list, hello everyone!] Hi, I'm failing to find a solution to an issue that seems fairly commonplace to me, so I'm assuming it's been answered before and I failed to dig up the answer (I tried). So feel free to alert me to any resources where this type of question has previously been answered. I'm currently trying to figure out a way to store a password hash in a table, allowing the password to be passed in in plain. I'd prefer to use a built-in password hashing functions on connections that support them, like so: from sqlobject import * from Crypto.Hash import SHA256 class Credential(SQLObject): username = StringCol(unique=True, alternateID=True) password = StringCol() def _set_password(self, newpass): try: # FIXME: update field with hash generated # from PASSWORD(newpass) except dberrors.OperationalError: # assume internal password function is # not available, do my own hashing hash = SHA256.new(newpass).hexdigest() self._SO_set_password(hash) So the "do my own hashing" part is easily achieved. It's the use of the internal password function that's baffling me. I'd like to use PASSWORD() on MySQL, and I believe this should work for MS SQL Server too as it also appears to have a built-in password hashing function. Is there a way to achieve this, combining _SO_set_* and sqlbuilder.func.PASSWORD perhaps? If so, how would I do that? Grateful for any insights. Thanks much in advance. Cheers, Florian ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/

How do I use a postgreSQL function like date_part?

Matthew Wilson — 2008-09-03T13:43:54

I want to do a select like this with SQLObject: select * from people where date_part('day', createddate) = date '2008-09-01'; That query should choose people created any time on September 1st. In SQLObject, this is what I'm doing: People.select("date_part('day', createddate) = date '2008-09-01'") Is there some way to avoid using a string of raw SQL? Matt ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/

problem clearing the cache

sophana — 2008-08-19T21:07:40

Hi I need to iterate over all the rows to check something. When doing this, I saw memory consumption raising infinitely. Then I remembered that sqlobjects caches everything. Then I tried: for a in SOClass.select(): connection.cache.clear() memory is still growing. Is it a python problem or a SQLObject problem? What do you suggest for this? Thanks ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/

Support for point data types in postgresql

Sam's Lists — 2008-08-19T00:57:17

------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/_______________________________________________ sqlobject-discuss mailing list sqlobject-discuss< at >lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Having a modified column filled in automagically...

Sam's Lists — 2008-08-17T10:46:19

Inheritance and MultipleJoin

Luis Javier Peris — 2008-08-12T16:18:39

How can I add extra columns to my select results?

Matthew Wilson — 2008-08-06T17:23:34

This is the SQL I want to do: select employee.*, 1 + 1 as x from employee where employee.id = 44; I know how to do this: User.select(User.q.id == 44) But how can I get the extra column? In my real-world example, the extra column is more complex. Matt ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/

timestamp - date select

Petr Jakeš — 2008-08-06T11:05:02

patch to add TIMESTAMP to DateTimeConverter

Matthew Wilson — 2008-07-31T20:58:04

I want to use the OVERLAPS keyword in postgres, but it complains because the current DateTimeConverter doesn't explicity set the type. This patch makes the change. This is my patch: It also corrects a tiny spelling error. Index: sqlobject/converters.py =================================================================== --- sqlobject/converters.py(revision 3536) +++ sqlobject/converters.py(working copy) < at >< at > -163,7 +163,7 < at >< at > registerConverter(time.struct_time, StructTimeConverter) def DateTimeConverter(value, db): - return "'%04d-%02d-%02d %02d:%02d:%02d'" % ( + return "TIMESTAMP '%04d-%02d-%02d %02d:%02d:%02d'" % ( value.year, value.month, value.day, value.hour, value.minute, value.second) Index: sqlobject/manager/command.py =================================================================== --- sqlobject/manager/command.py(revision 3536) +++ sqlobject/manager/command.py(working copy) < at >< at > -874,7 +874,7 < at >< at > help = ('Record state of table definitions. The state of each ' 'table is written out to a separate file in a directory, ' 'and that directory forms a "version". A table is also ' - 'added to your datebase (%s) that reflects the version the ' + 'added to your database (%s) that reflects the version the ' 'database is currently at. Use the upgrade command to ' 'sync databases with code.' % SQLObjectVersionTable.sqlmeta.table) ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/

Many To Many with a existing middle table

Jerome Kerdreux — 2008-07-31T12:10:04

Hi, all. I'm trying to write a python library around the f-spot database. I used SQLO for a couple of projects so, I decided to use it here too. But f-spot database schema is not really standard so I need to use its intermediate table in a many to many. Here is the doc I used: http://www.sqlobject.org/FAQ.html#how-can-i-define-my-own-intermediate-table-in-my-many-to-many-relationship And here is the result code. class Photo(SQLObject): class sqlmeta: table = 'photos' time = IntCol() uri = UnicodeCol() description = UnicodeCol() roll_id = IntCol() default_version_id = IntCol() rating = IntCol() tags = RelatedJoin('Tag') tags = SQLRelatedJoin('Tag',intermediateTable='photo_tags') class Tag(SQLObject): class sqlmeta: table = 'tags' name = UnicodeCol() category_id = IntCol() is_category = BoolCol() sort_priority = IntCol() icon = StringCol() photos = SQLRelatedJoin('Photo',intermediateTable='photo_tags') class PhotosTags(SQLObject): class sqlmeta: table = 'photo_tags' photo = ForeignKey('Photo') tag = ForeignKey('Tag') As you can see the photo_tags is the intermediate table (which links photos to tags). That's look fine except that when I try to find tags for a given photo, I get something like this: (Pdb) print Photo.select()[2500].tags SELECT tags.id, tags.name, tags.category_id, tags.is_category, tags.sort_priority, tags.icon FROM photos, photo_tags, tags WHERE ((tags.id = photo_tags.tags_id) AND ((photo_tags.photos_id = photos.id) AND (photos.id = 5427))) Ok, but the right column in the intermediate table is this : TABLE photo_tags ( photo_id INTEGER, tag_id INTEGER ); As you can see the columns are photo_id and tag_id .. but SQLO is looking for photos_id and tags_id (look the s at the end).. So my question is : The FAQ explain how to name and populate the intermediate table, but how can I set the column name for this table. Thanks for any help.

How to use OVERLAPS keyword with SQLObject?

Matthew Wilson — 2008-07-24T18:25:20

I just learned about the OVERLAPS keyword, used like this: matt=# select (TIMESTAMP '2008-07-24 03:00:00', TIMESTAMP '2008-07-24 09:00:00') overlaps (TIMESTAMP '2008-07-24 5:0:0', TIMESTAMP '2008-07-24 11:0:0') ; overlaps ---------- t (1 row) I'm testing if the time interval from 3 AM today through 9 AM today overlaps the time interval from 5 AM through 11 AM. How can I use this with SQLObject? I poked around a little with SQLOp. Before I write an ugly patch, is there already a way to do it? It is important to use the TIMESTAMP label to disambiguate types. Matt ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/

sqlobject 0.10 and picklecol protocol problem

Sophana — 2008-07-24T15:17:06

Hi there seem to be a problem with picklecol and v0.10 It seem that v0.10 uses highest pickle protocol by default (2). When reading back there are problems related to protocol2 and unicode. pickle does not understand what it just pickled before... Can someone confirm this? My workaround is to force protocol 0. Thanks for all ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/

Nested Table/Foreign Key to same table

dimi duj — 2008-07-24T07:56:01

How to provide default non-null values for foreign keys?

Matthew Wilson — 2008-07-23T19:50:47

I have an employees table and a departments table, and every employee has a foreign key to the departments table. 99% of the time, new employees belong to one particular department, "SALES". How can I make the SALES department the default department for new employees? I tried this: class Department(SQLObject): name = UnicodeCol(alternateID=True) class Employee(SQLObject): user_name = UnicodeCol(alternateID=True) department = ForeignKey(default=Department.byName("SALES") And it failed. Any ideas? Thanks in advance. Matt ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/

childName

Luis Javier Peris — 2008-07-22T18:48:46

SQLObject with a custom backend?

inhahe — 2008-07-22T11:25:38

What if I want to modify the backend to SQLObject in some way? I want its requests to call my own custom function(s) that ultimately use the DBS. Basically I'm using stackless and I want my custom function to put the request in a queue that goes to a thread pool of database connections to MySQL and when a result is ready sends it back to the function on a channel so that the function can return the result. Can this be done? ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/

assert id is not None

Ray Van Dolson — 2008-07-22T00:31:24

Hi all, I'm running into an exception trying to create a new row in a table with fromDatabase = True defined (the table is pre-existing). The class definition looks like this: class SystemsInfo(SQLObject): class sqlmeta: fromDatabase = True table = 'SystemsInfo' idType = str idName = 'SystemName' lazyUpdate = True So, my "id" key has been changed to SystemName. In this particular table, SystemName is the primary key and is not allowed to be NULL. All other columns may be NULL. I just want to create a bare minimum row: item = SystemsInfo(SystemName='uniqenname') However, I get the following error: Traceback (most recent call last): File "./update_matrix.py", line 113, in ? main(sys.argv) File "./update_matrix.py", line 104, in main s = SystemsInfo(SystemName=row[0]) File "/usr/lib/python2.4/site-packages/sqlobject/declarative.py", line 98, in _wrapper return fn(self, *args, **kwargs) File "/usr/lib/python2.4/site-packages/sqlobject/main.py", line 1210, in __init__ self._create(id, **kw) File "/usr/lib/python2.4/site-packages/sqlobject/main.py", line 1241, in _create self._SO_finishCreate(id) File "/usr/lib/python2.4/site-packages/sqlobject/main.py", line 1268, in _SO_finishCreate self._init(id) File "/usr/lib/python2.4/site-packages/sqlobject/main.py", line 929, in _init assert id is not None AssertionError This actually results in the row I want being added to the database, but the error above causes my script to halt (unless I add a try/except block around it). It's an assertion, so perhaps it would go away if I called python with -O, but... I'd rather make this warning go away completely. Thinking that somehow the constructor was angry because I wasn't passing an 'id' variable, I also tried: item = SystemsInfo(id='uniqename'). This results in the following: Traceback (most recent call last): File "./update_matrix.py", line 112, in ? main(sys.argv) File "./update_matrix.py", line 104, in main item = SystemsInfo(id=row[0]) File "/usr/lib/python2.4/site-packages/sqlobject/declarative.py", line 98, in _wrapper return fn(self, *args, **kwargs) File "/usr/lib/python2.4/site-packages/sqlobject/main.py", line 1210, in __init__ self._create(id, **kw) File "/usr/lib/python2.4/site-packages/sqlobject/main.py", line 1241, in _create self._SO_finishCreate(id) File "/usr/lib/python2.4/site-packages/sqlobject/main.py", line 1265, in _SO_finishCreate id, names, values) File "/usr/lib/python2.4/site-packages/sqlobject/dbconnection.py", line 396, in queryInsertID return self._runWithConnection(self._queryInsertID, soInstance, id, names, values) File "/usr/lib/python2.4/site-packages/sqlobject/dbconnection.py", line 255, in _runWithConnection val = meth(conn, *args) File "/usr/lib/python2.4/site-packages/sqlobject/mssql/mssqlconnection.py", line 136, in _queryInsertID c.execute(q) File "/usr/lib/python2.4/site-packages/pymssql.py", line 126, in execute self.executemany(operation, (params,)) File "/usr/lib/python2.4/site-packages/pymssql.py", line 152, in executemany raise DatabaseError, "internal error: %s" % self.__source.errmsg() pymssql.DatabaseError: internal error: SQL Server message 264, severity 16, state 1, line 1: Column name 'SystemName' appears more than once in the result column list. DB-Lib error message 20018, severity 5: General SQL Server error: Check messages from the SQL Server. And sure, enough; when I turn on SQL debugging I see that the INSERT query includes SystemName twice. In the first version, there is only one occurence of SystemName, but it is not at the beginning of the query making me think somehow sqlobject doesn't know I am specifying the primary key via the function argument. Maybe I can remove SystemName from the column list somehow and stick to using it via the id parameter which maybe should map over transparently? Any thoughts? Thanks, Ray ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/

Getting one item at a time in a select...

Sam's Lists — 2008-07-19T09:52:04

AttributeError on foreign key access

nnp — 2008-07-18T15:56:55

Hey, I'm not sure why but sometimes when I attempt to run the following line of code I get an error saying 'AttributeError: FileAction instance has no attribute 'file'' Is there any obvious reason why this would happen? I can't see any obvious reason from the docs as to why that would happen sometimes but not others. deleteFileAction = list(FileAction.select(AND(FileAction.q.file == codeBug.file, FileAction.q.action == deleteAction))) class FileAction(SQLObject): file = ForeignKey('File', notNull=True) commitLog = ForeignKey('CommitLog') action = ForeignKey('Action') Cheers, nnp

string, unicode and column id

Luc Milland — 2008-07-17T09:31:22

Hello, I am discovering sqlobject with turbogears and I face a problem which may be sum up as follow : My model : (...) class Page(SQLObject): pagename = UnicodeCol(alternateID=True,unique=True,notNone=True,length=15) tabname = UnicodeCol(length=30) content = RelatedJoin("Content") sidebar = ForeignKey("Sidebar") (...) And in a console: As I populate the table from a web form, all values come as a unicode type. I catch all of them as a key/pair value and modify my "p" record in a loop with something like setattr(p,key,value). My problem is that setattr(p,'sidebar',u'1') does not raise any error but silently gives the None value to sidebarID, which force me to an ugly and unsecure workaround : if hasattr(p,key+'ID'): setattr(p,key+'ID',int(value)) This unicode problem does not affect RelatedJoin columns : So my question are : did I miss something ? Is this a bug ? Does on of you has a clue on how to fix this ? Btw, I use sqlobject 0.7.1 cheers, Luc ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/

Working with pre-existing tables

Luis Javier Peris — 2008-07-16T17:38:57