Thread: [SQLObject] Strange column type conversion
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Hong Y. <hon...@ho...> - 2005-01-02 17:23:40
|
I find that under some cercumstances a column declared as a certain type can return a different type as shown in the following sample code, tested with SQLObject 0.6.0 The test table, with PostgreSQL 7.4.6: CREATE TABLE test ( id int4 NOT NULL DEFAULT nextval('test_id_seq'::text), number int2 NOT NULL, CONSTRAINT pkey PRIMARY KEY (id) ) WITHOUT OIDS; ALTER TABLE test OWNER TO test; The test script: from sqlobject import * __connection__ = 'postgres://test:test@localhost/test?debug=0' class test(SQLObject): number = IntCol() for i in [3, 5, 8]: test(number = i) i = test.select(test.q.number == 8) i[0].number = '10' results = test.select() for r in results: print r.number, type(r.number) While I would expect the result to be all <type 'int'>, the actuall output is: 3 <type 'int'> 5 <type 'int'> 10 <type 'str'> It seems that sqlobject has remembered that the type of column 'number' was set to '10', a string type, though I thought IntCol should take care of converting the string to int before sending it to the SQL database. I think this kind of inconsistency of column return type will cause great confusion to the programms that use sqlobject and should be fixed. Best Regards Hong Yuan |
From: Oleg B. <ph...@ma...> - 2005-01-02 20:03:50
|
On Mon, Jan 03, 2005 at 01:22:18AM +0800, Hong Yaun wrote: > i[0].number = '10' [skip] > 10 <type 'str'> What version are you using? The bug has been fixed in the Sunbversion repository and the fixed version soon will be released as SQLObject 0.6.1. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ma...> - 2005-01-02 20:17:41
|
On Sun, Jan 02, 2005 at 11:03:40PM +0300, Oleg Broytmann wrote: > On Mon, Jan 03, 2005 at 01:22:18AM +0800, Hong Yaun wrote: > > i[0].number = '10' > [skip] > > 10 <type 'str'> > > What version are you using? The bug has been fixed in the Sunbversion > repository and the fixed version soon will be released as SQLObject 0.6.1. Sorry, I've spoken too fast. The bug was fixed only for columns that have a validator - the value is passed through fromPython() and toPython() calls. IntCol does not have from/toPython and hence doesn't convret a string value to an int. I am not sure in waht way that should be fixed. Should IntCol silently accepts strings and converts them to integers, or should it raises TypeError? Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2005-01-03 09:10:51
|
Hello! (CC'ing to the list.) On Mon, Jan 03, 2005 at 10:43:10AM +0800, Hong Yaun wrote: > IMHO IntCol should convert the string to integers, and only raises > errors when the conversion can not be made, Why?! You've declared IntCol, an *integer* column. Of course, it expects to be passed in only integers, not strings. If strings, why not other types, for example objects thta implements __int__()? > on the following grounds: > > I have encountered this problem when I am passing the user input value > collected in the GUI components (wxPython in particular) to the > database. The GUI components returns their values as string, so I would > either convert the strings to integer in my own program, or have The logic is flawed. You are connecting GUI and SQLObject in your program. The job of connecting and converting is neither in the GUI nor in SQLObject - it is the job of your program. It is easy to implement, btw. See below. > SQLObject do this for me. Since I have already declared the column to be > integer with SQLObject, it seems more natural that SQLObject would do > this conversion for me, otherwise I have to declare the column type > again elsewhere in my program. No, you don't have to. You have to declare your own descendant of IntCol, and implement a validator that will convert values according to your rules. See UnicodeCol for a good example. > Second, I think this behavior is more compatible with the way that > database is coping with type conversion. You can for example simply send > a string in the SQL command to an int column in the database, and the > database would perform the conversion whenever possible. I have never understand that behaviour of databases. And of programming langauges. Why on Earth one would want a typesystem where (s)he can add an integer to a string and got a result instead of TypeError? Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Hong Y. <hon...@ho...> - 2005-01-03 14:39:19
|
Hello! >>on the following grounds: >> >>I have encountered this problem when I am passing the user input value >>collected in the GUI components (wxPython in particular) to the >>database. The GUI components returns their values as string, so I would >>either convert the strings to integer in my own program, or have >> >> > > The logic is flawed. You are connecting GUI and SQLObject in your >program. The job of connecting and converting is neither in the GUI nor >in SQLObject - it is the job of your program. > It is easy to implement, btw. See below. > > ...... >>Second, I think this behavior is more compatible with the way that >>database is coping with type conversion. You can for example simply send >>a string in the SQL command to an int column in the database, and the >>database would perform the conversion whenever possible. >> >> > > I have never understand that behaviour of databases. And of >programming langauges. Why on Earth one would want a typesystem where >(s)he can add an integer to a string and got a result instead of >TypeError? > >Oleg. > > I get your point, that the validating and type conversion belongs to the application itself, not the ORM or GUI layer. But at least I would be very happy if I could be spared the tedious job of doing simple and frequent conversions, specially the very common conversion from strings to numbers. As long as the program integrity is not engendered, the convenience of automatical conversion would always be welcome. For the end user at least, when she/he input a number in a field, she doesn't care whether it is actually a string or a number. For her it IS an nubmer. I think that is why the SQL standard is not so strict on the data types and most databases do a lot of automatic conversions. I think it is rather a matter of taste whether a type system should be more strict or user friendly, and either way should work. It is just necessary to point out which approach SQLObject has taken so we know what is to be expected. But anyway, for a column decalred as IntCol, it should always return values as <type int>, and I wish this could be fixed soon, maybe by prohibiting to assign a string value to such a column. Hong Yuan |
From: Oleg B. <ph...@ma...> - 2005-01-03 14:51:59
|
On Mon, Jan 03, 2005 at 10:39:29PM +0800, Hong Yaun wrote: > But anyway, for a column decalred as IntCol, it > should always return values as <type int>, and I wish this could be > fixed soon, maybe by prohibiting to assign a string value to such a column. What do other people here think? Should the IntCol accept strings? silently call int() on any input? or raise TypeError in case the input value is not of type int? Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Jorge L. G. F. <go...@ie...> - 2005-01-03 15:05:40
|
Oleg Broytmann, Segunda 03 Janeiro 2005 12:51, wrote: > What do other people here think? Should the IntCol accept strings? > silently call int() on any input? or raise TypeError in case the input > value is not of type int? I go with Python's rule: explicit is better than implicit. -- Godoy. <go...@ie...> |
From: Oleg B. <ph...@ma...> - 2005-01-03 15:15:24
|
On Mon, Jan 03, 2005 at 01:04:24PM -0200, Jorge Luiz Godoy Filho wrote: > Oleg Broytmann, Segunda 03 Janeiro 2005 12:51, wrote: > > What do other people here think? Should the IntCol accept strings? > > silently call int() on any input? or raise TypeError in case the input > > value is not of type int? > > I go with Python's rule: explicit is better than implicit. That is, do you prefer to convert by yourself? row.col = int(value)? Not even using a validator? Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Jorge L. G. F. <go...@ie...> - 2005-01-03 15:35:40
|
Oleg Broytmann, Segunda 03 Janeiro 2005 13:15, wrote: > That is, do you prefer to convert by yourself? row.col = int(value)? > Not even using a validator? The use of a validator implies in code that I've written myself or opted in using. Subclassing and making the subclass able to do an automatic conversion is also interesting. -- Godoy. <go...@ie...> |
From: Jorge L. G. F. <go...@ie...> - 2005-01-03 15:50:45
|
Jorge Luiz Godoy Filho, Segunda 03 Janeiro 2005 13:34, wrote: > The use of a validator implies in code that I've written myself or opted > in using. I was thinking about a wxPython validator -- it was mentioned the use of wxPython on the thread -- not on SQLObject's validators as you mentioned while talking about UnicodeCol... -- Godoy. <go...@ie...> |
From: Carlos R. <car...@gm...> - 2005-01-03 16:10:03
|
On Mon, 03 Jan 2005 13:04:24 -0200, Jorge Luiz Godoy Filho <go...@ie...> wrote: > Oleg Broytmann, Segunda 03 Janeiro 2005 12:51, wrote: > > > What do other people here think? Should the IntCol accept strings? > > silently call int() on any input? or raise TypeError in case the input > > value is not of type int? > > I go with Python's rule: explicit is better than implicit. There's still Postel's law approach to consider: "be conservative in what you do, be liberal in what you accept from others". In this case it means raise an exception for putting string data on an IntCol, but silently convert the data back to int if, for some reason, a string is read. Before anyone cries foul, it does make *at least some sense*. The user determined that he wanted int's on his application, but he may not have full control over the database. But to support this on SQLObject or not, that's another issue entirely. -- Carlos Ribeiro Consultoria em Projetos blog: http://rascunhosrotos.blogspot.com blog: http://pythonnotes.blogspot.com mail: car...@gm... mail: car...@ya... |
From: Oleg B. <ph...@ma...> - 2005-01-03 16:15:46
|
On Mon, Jan 03, 2005 at 02:09:53PM -0200, Carlos Ribeiro wrote: > raise an exception for putting string data on an IntCol, but Yes, I think so, too. > silently convert the data back to int if, for some reason, a string is > read. Do you know a database or driver that return a string from an int column?! Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Jorge L. G. F. <go...@ie...> - 2005-01-03 16:30:37
|
Oleg Broytmann, Segunda 03 Janeiro 2005 14:15, wrote: > Do you know a database or driver that return a string from an int > column?! I think, I'm not sure here, that pyPgSQL does return strings only. -- Godoy. <go...@ie...> |
From: Carlos R. <car...@gm...> - 2005-01-03 16:32:36
|
On Mon, 3 Jan 2005 19:15:33 +0300, Oleg Broytmann <ph...@ma...> wrote: > On Mon, Jan 03, 2005 at 02:09:53PM -0200, Carlos Ribeiro wrote: > > raise an exception for putting string data on an IntCol, but > > Yes, I think so, too. > > > silently convert the data back to int if, for some reason, a string is > > read. > > Do you know a database or driver that return a string from an int > column?! sqlite is very flexible in this regard; although it usually differentiates between text & numeric columns, there's no coercion AFAIK. But let's generalize it; and before you tell me, I admit that it's more a philosophical question than a practical one. Assume that I am using SQLObject with an external database, in such a way that I can't do anything about its schema. I want to treat the contents of a column as an int. The original database may have been declared as anything - an int, float, string, byte, shortint, whatever. But I know that the value that's stored there can be read as a valid integer number. So I declare it inside my app as an IntCol. If for any reason the value can't be converted, it will raise an exception on read, just that. In other words, I'm talking about being explicit... to the extreme. The programmer asked for an IntCol. So the code always try to make sure that it gets an int. If the programmer passes invalid data (non-int), it raises an exception. But it also makes sure that the DB is passing the correct data, so the match between the SQLObject and the DB is also explicitly enforced on *read*. Just my $0.02... and not that I'd bother very much about it, it was just something that crossed my mind while reading the original thread. -- Carlos Ribeiro Consultoria em Projetos blog: http://rascunhosrotos.blogspot.com blog: http://pythonnotes.blogspot.com mail: car...@gm... mail: car...@ya... |
From: Ian B. <ia...@co...> - 2005-01-03 16:57:47
|
Oleg Broytmann wrote: >>silently convert the data back to int if, for some reason, a string is >>read. > > Do you know a database or driver that return a string from an int > column?! The next version of PySQLite *might* return a string; the author mentioned that he wanted to get rid of the automatic conversion that the driver does. Personally I think that's a bad choice, because it makes SQLite the odd one out of databases. I agree that we should raise an exception when we get a non-integer. Well, int or long; should a whole-number float also be accepted? I'm leaning towards not. -- Ian Bicking / ia...@co... / http://blog.ianbicking.org |
From: Oleg B. <ph...@ma...> - 2005-01-03 17:19:28
|
On Mon, Jan 03, 2005 at 10:57:10AM -0600, Ian Bicking wrote: > I agree that we should raise an exception when we get a non-integer. > Well, int or long; Yes. I'll work on it. Also I am going to change SOUnicodeCol and apply the law of Demeter - SOUnicodeCol should not instantiate UnicodeStringValidator by name - that must be a parameter. This will allow to subclass SOUnicodeCol and change that parameter. After that I'll implement IntValidator in the same model. > should a whole-number float also be accepted? No, in my humble opinion. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Carlos R. <car...@gm...> - 2005-01-03 17:23:21
|
On Mon, 3 Jan 2005 20:19:14 +0300, Oleg Broytmann <ph...@ma...> wrote: > On Mon, Jan 03, 2005 at 10:57:10AM -0600, Ian Bicking wrote: > > I agree that we should raise an exception when we get a non-integer. > > Well, int or long; > > Yes. I'll work on it. > Also I am going to change SOUnicodeCol and apply the law of Demeter - > SOUnicodeCol should not instantiate UnicodeStringValidator by name - > that must be a parameter. This will allow to subclass SOUnicodeCol and > change that parameter. > After that I'll implement IntValidator in the same model. > > > should a whole-number float also be accepted? > > No, in my humble opinion. On set: only int-like objects (longs included) should be accepted. On get: I still think that putting an int() there would not do any harm. But I don't have strong opinions about it either. -- Carlos Ribeiro Consultoria em Projetos blog: http://rascunhosrotos.blogspot.com blog: http://pythonnotes.blogspot.com mail: car...@gm... mail: car...@ya... |
From: Oleg B. <ph...@ma...> - 2005-01-03 17:37:26
|
On Mon, Jan 03, 2005 at 03:23:14PM -0200, Carlos Ribeiro wrote: > On set: only int-like objects (longs included) should be accepted. Only int and long, and nothing else. Well, and subclasses - if not isinstance(value, (int, long)): raise TypeError. > On get: I still think that putting an int() there would not do any > harm. Practicality beats purity. Let's wait for the real need of it. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2005-01-05 12:43:39
|
On Mon, Jan 03, 2005 at 08:19:14PM +0300, Oleg Broytmann wrote: > On Mon, Jan 03, 2005 at 10:57:10AM -0600, Ian Bicking wrote: > > I agree that we should raise an exception when we get a non-integer. > > Well, int or long; > > Yes. I'll work on it. > Also I am going to change SOUnicodeCol and apply the law of Demeter - > SOUnicodeCol should not instantiate UnicodeStringValidator by name - > that must be a parameter. This will allow to subclass SOUnicodeCol and > change that parameter. Committed at revision 511. > After that I'll implement IntValidator in the same model. Committed at revision 512. There are problems with test. I am working on them. One of the problems is that attemptConvert() in different validators have different number of parameters; that leads to the exception: ERROR: testValidate (__main__.ValidationTest) ---------------------------------------------------------------------- Traceback (most recent call last): File "test_sqlobject.py", line 894, in testValidate t = SOValidation(name='hey') File "/home/phd/work/SQLObject/SQLObject-inheritance_opt/sqlobject/main.py", line 1013, in __init__ self._create(id, **kw) File "/home/phd/work/SQLObject/SQLObject-inheritance_opt/sqlobject/main.py", line 1065, in _create self.set(**kw) File "/home/phd/work/SQLObject/SQLObject-inheritance_opt/sqlobject/main.py", line 897, in set kw[name] = dbValue = fromPython(value, self._SO_validatorState) File "/home/phd/work/SQLObject/SQLObject-inheritance_opt/sqlobject/include/validators.py", line 202, in fromPython self.validateOther) TypeError: attemptConvert() takes exactly 4 arguments (6 given) Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2005-01-05 14:57:16
|
On Wed, Jan 05, 2005 at 03:43:30PM +0300, Oleg Broytmann wrote: > > Also I am going to change SOUnicodeCol and apply the law of Demeter - > > SOUnicodeCol should not instantiate UnicodeStringValidator by name - > > that must be a parameter. This will allow to subclass SOUnicodeCol and > > change that parameter. > > There are problems with test. I am working on them. One of the > problems is that attemptConvert() in different validators have different > number of parameters All fixes were commited at revision 516, and merged into the inheritance branch at 517. All tests passed. Well, I am not sure I took a right path to fix validators.py by renaming _(to/from)Python to (to/from)Python. I hope I haven't screwed things up, neither techically nor ideologically. IWBN if people take a look at the code and run tests. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Stuart B. <st...@st...> - 2005-01-04 13:11:59
|
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Oleg Broytmann wrote: | On Mon, Jan 03, 2005 at 10:39:29PM +0800, Hong Yaun wrote: | |>But anyway, for a column decalred as IntCol, it |>should always return values as <type int>, and I wish this could be |>fixed soon, maybe by prohibiting to assign a string value to such a column. | | | What do other people here think? Should the IntCol accept strings? | silently call int() on any input? or raise TypeError in case the input | value is not of type int? It should accept anything that has an __sqlrepr__ shouldn't it? class Default(object): def __sqlrepr__(self): return 'DEFAULT' foo.someintcol = Default() A similar use case would be set a value from a sequence, or for calculations involving stored procedures. Sticking an int() coersion in there would be nice, since I'd rather a Python error early than waiting until the DB spits an error message at me (important when working in lazy update mode). We have objects that define an __int__() method that I think we pass to IntCols and expect it to do the right thing. - -- Stuart Bishop <st...@st...> http://www.stuartbishop.net/ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.5 (GNU/Linux) iD8DBQFB2pYMAfqZj7rGN0oRAvIRAJwJolGBLPKGQtVj6WwjHZhF0jnc/wCcDUgr qQrX5gveQmjqyaLb9SmLvQA= =1u6Z -----END PGP SIGNATURE----- |
From: Ian B. <ia...@co...> - 2005-01-04 16:59:48
|
Stuart Bishop wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Oleg Broytmann wrote: > | On Mon, Jan 03, 2005 at 10:39:29PM +0800, Hong Yaun wrote: > | > |>But anyway, for a column decalred as IntCol, it > |>should always return values as <type int>, and I wish this could be > |>fixed soon, maybe by prohibiting to assign a string value to such a > column. > | > | > | What do other people here think? Should the IntCol accept strings? > | silently call int() on any input? or raise TypeError in case the input > | value is not of type int? > > It should accept anything that has an __sqlrepr__ shouldn't it? Yeah, but that's an open bug too, e.g., the sqlbuilder.func.NOW() issue (where NOW() ends up being the column value, even though it's not a concrete Python value, just an abstract SQL expression). Until that's fixed, it doesn't really help anyone to allow __sqlrepr__-having objects in. Probably the fix would be to detect these objects, and if found call .expire() on the instance, forcing the data to be refetched on the next access. Hmm... maybe that's not too hard. -- Ian Bicking / ia...@co... / http://blog.ianbicking.org |