sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] Can't Install SQLObject

[Oleg B. <ph...@ph...>]

Hi!

   So you have resolved mail problems finally? Welcome!

On Fri, Apr 08, 2016 at 05:13:17PM +0000, "Emmert, Steve" <Ste...@gd...> wrote:
> I need to install SQLObject on a machine that is isolated from the Internet, so I downloaded the package on another machine and copied it over and unzipped it.  I do not see a setup.py file or .egg file so I can't see how to install it.   What should I do, other than get an Internet connection?

   You can download an egg or tar.gz from PyPI:
https://pypi.python.org/pypi/SQLObject/2.1.2 (2.1.2 is currently the
latest stable version).

   SQLObject.tar.gz contains setup.py so you can build and egg yourself
or install directly from sources.

> Steve Emmert

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] Can't Install SQLObject

[Emmert, S. <Ste...@gd...>]

I need to install SQLObject on a machine that is isolated from the Internet, so I downloaded the package on another machine and copied it over and unzipped it.  I do not see a setup.py file or .egg file so I can't see how to install it.   What should I do, other than get an Internet connection?

Steve Emmert

Re: [SQLObject] Intermediate tables with MySql and SQLObject

[Oleg B. <ph...@ph...>]

Hi!

On Fri, Mar 25, 2016 at 03:06:00PM +0000, Jordan Thompson <jo...@th...> wrote:
> Hi there,
> I have the following tables:
> 
> class VmThread(sqlobject.SQLObject):
>    name            = sqlobject.StringCol(length=999, varchar=True,
> unique=True, notNull=True)
>    state           = sqlobject.StringCol(length=999, varchar=True,
> unique=True, notNull=True)
>    vm_id           = sqlobject.IntCol(notNull=True)
>    sessions         = sqlobject.RelatedJoin('Session')
> 
> 
> class Session(sqlobject.SQLObject):
>    num_vms             = sqlobject.IntCol(notNull=True)
>    start_time       = sqlobject.DateTimeCol(notNull=True)
>    end_time         = sqlobject.DateTimeCol(default=None)
>    vm_threads           = sqlobject.RelatedJoin('VmThread')
> 
> 
> I am running this on a MySql database and I expected an intermediate
> table with foreign keys but they are not created.  When the
> createTable method is called on the two objects I get the following
> three tables (as I expected):
> 
> mysql> desc vm_thread;
> +-------+--------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +-------+--------------+------+-----+---------+----------------+
> | id | int(11) | NO | PRI | NULL | auto_increment |
> | name | varchar(999) | NO | UNI | NULL | |
> | state | varchar(999) | NO | UNI | NULL | |
> | vm_id | int(11) | NO | | NULL | |
> +-------+--------------+------+-----+---------+----------------+
> 
> mysql> desc session;
> +-------------------+-------------+------+-----+---------+----------------+
> | Field             | Type        | Null | Key | Default | Extra          |
> +-------------------+-------------+------+-----+---------+----------------+
> | id                | int(11)     | NO   | PRI | NULL    | auto_increment |
> | num_vms           | int(11)     | NO   |     | NULL    |                |
> | start_time        | datetime(6) | NO   |     | NULL    |                |
> | end_time          | datetime(6) | YES  |     | NULL    |                |
> +-------------------+-------------+------+-----+---------+----------------+
> 
> mysql> desc session_vm_thread;
> +--------------+---------+------+-----+---------+-------+
> | Field        | Type    | Null | Key | Default | Extra |
> +--------------+---------+------+-----+---------+-------+
> | session_id   | int(11) | NO   |     | NULL    |       |
> | vm_thread_id | int(11) | NO   |     | NULL    |       |
> +--------------+---------+------+-----+---------+-------+
> 
> 
> However, I was expecting a foreign key into the intermediate table but
> it was not created:
> 
> mysql> select table_name, column_name, constraint_name,
> referenced_table_name, referenced_column_name
>     -> from information_schema.key_column_usage
>     -> where referenced_table_name = "vm_thread";
> Empty set (0.08 sec)
> 
> mysql> select table_name, column_name, constraint_name,
> referenced_table_name, referenced_column_name
>     -> from information_schema.key_column_usage
>     -> where referenced_table_name = "session_vm_thread";
> Empty set (0.08 sec)
> 
> What am I missing?

   By default intermediate tables are created in dbconnection.py as

    def _SO_createJoinTableSQL(self, join):
        return ('CREATE TABLE %s (\n%s %s,\n%s %s\n)' %
                (join.intermediateTable,
                 join.joinColumn,
                 self.joinSQLType(join),
                 join.otherColumn,
                 self.joinSQLType(join)))

   So, no foreign keys. Perhaps because not all databases support
foreign keys. MySQL, AFAIK, requires InnoDB to support them.

   You can create the intermediate table yourself, see

http://sqlobject.org/FAQ.html#how-can-i-define-my-own-intermediate-table-in-my-many-to-many-relationship

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] Intermediate tables with MySql and SQLObject

[Jordan T. <jo...@th...>]

Hi there,
I have the following tables:

class VmThread(sqlobject.SQLObject):
   name            = sqlobject.StringCol(length=999, varchar=True,
unique=True, notNull=True)
   state           = sqlobject.StringCol(length=999, varchar=True,
unique=True, notNull=True)
   vm_id           = sqlobject.IntCol(notNull=True)
   sessions         = sqlobject.RelatedJoin('Session')


class Session(sqlobject.SQLObject):
   num_vms             = sqlobject.IntCol(notNull=True)
   start_time       = sqlobject.DateTimeCol(notNull=True)
   end_time         = sqlobject.DateTimeCol(default=None)
   vm_threads           = sqlobject.RelatedJoin('VmThread')


I am running this on a MySql database and I expected an intermediate
table with foreign keys but they are not created.  When the
createTable method is called on the two objects I get the following
three tables (as I expected):

mysql> desc vm_thread;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(999) | NO | UNI | NULL | |
| state | varchar(999) | NO | UNI | NULL | |
| vm_id | int(11) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+

mysql> desc session;
+-------------------+-------------+------+-----+---------+----------------+
| Field             | Type        | Null | Key | Default | Extra          |
+-------------------+-------------+------+-----+---------+----------------+
| id                | int(11)     | NO   | PRI | NULL    | auto_increment |
| num_vms           | int(11)     | NO   |     | NULL    |                |
| start_time        | datetime(6) | NO   |     | NULL    |                |
| end_time          | datetime(6) | YES  |     | NULL    |                |
+-------------------+-------------+------+-----+---------+----------------+

mysql> desc session_vm_thread;
+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| session_id   | int(11) | NO   |     | NULL    |       |
| vm_thread_id | int(11) | NO   |     | NULL    |       |
+--------------+---------+------+-----+---------+-------+


However, I was expecting a foreign key into the intermediate table but
it was not created:

mysql> select table_name, column_name, constraint_name,
referenced_table_name, referenced_column_name
    -> from information_schema.key_column_usage
    -> where referenced_table_name = "vm_thread";
Empty set (0.08 sec)

mysql> select table_name, column_name, constraint_name,
referenced_table_name, referenced_column_name
    -> from information_schema.key_column_usage
    -> where referenced_table_name = "session_vm_thread";
Empty set (0.08 sec)

What am I missing?

Re: [SQLObject] How to order a table by fields of an other table connected to the first one by ForeignKey()

[Oleg B. <ph...@ph...>]

On Thu, Jan 07, 2016 at 03:03:22PM +0000, Paul Otto Seidon <p.o...@da...> wrote:
> Hi all,
> 
> this seems somehow related to a recent post asking about "querying for 
> foreign key". Here as well, I guess, tables have to be joined, but I 
> don't see how to pack this into a single ORDERBY clause.
> 
> The main table is Entry, which owns _status = ForeignKey( "Status"). 
> Status owns a field 'value' besides some other fields. Now I would like 
> the Entry rows I select by some other criterion (e.g. their priority) be 
> ordered by the value field of the Status of the Entry.
> 
> When I ORDERBY Status.q._value then SQLOBJECT correctly tells me, that 
> Entry doesn't own a _value field. All other combinations don't work 
> neither. 
> 
> This has to be done more SQL-ish, right?
> 
> Anyone willing to help me out here?

class Test1(SQLObject):
    name = StringCol()
    test2 = ForeignKey('Test2')

class Test2(SQLObject):
    age = IntCol()

Test2.createTable()
Test1.createTable()

test2_1 = Test2(age=0)
test2_2 = Test2(age=42)

Test1(name='1', test2=test2_1)
Test1(name='2', test2=test2_2)

print list(Test1.select(Test1.q.test2 == Test2.q.id, orderBy=Test2.q.age))

   Output:

SELECT test1.id, test1.name, test1.test2_id FROM test1, test2 WHERE ((test1.test2_id) = (test2.id)) ORDER BY test2.age
[<Test1 1 name='1' test2ID=1>, <Test1 2 name='2' test2ID=2>]

> Cheers
> Paul

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] How to order a table by fields of an other table connected to the first one by ForeignKey()

[Paul O. S. <p.o...@da...>]

Hi all,

this seems somehow related to a recent post asking about "querying for 
foreign key". Here as well, I guess, tables have to be joined, but I 
don't see how to pack this into a single ORDERBY clause.

The main table is Entry, which owns _status = ForeignKey( "Status"). 
Status owns a field 'value' besides some other fields. Now I would like 
the Entry rows I select by some other criterion (e.g. their priority) be 
ordered by the value field of the Status of the Entry.

When I ORDERBY Status.q._value then SQLOBJECT correctly tells me, that 
Entry doesn't own a _value field. All other combinations don't work 
neither. 

This has to be done more SQL-ish, right?

Anyone willing to help me out here?

Cheers
Paul

[SQLObject] SQLObject 2.2.0b2 and 3.0.0a2

[Oleg B. <ph...@ph...>]

Hello!

I'm pleased to announce versions 2.2.0b2 and 3.0.0a2 of SQLObject.


What's new in SQLObject since 2.2.0b1 (and 3.0.0a1)
===================================================

* Columns for ForeignKey are created using idType of the referenced
  table.

* Added new test help setupCyclicClasses to setup classes with mutual
  references.

Contributor for this release is Nathan Edwards.

For a more complete list, please see the news:
http://sqlobject.org/News.html


What is SQLObject
=================

SQLObject is an object-relational mapper.  Your database tables are described
as classes, and rows are instances of those classes.  SQLObject is meant to be
easy to use and quick to get started with.

SQLObject supports a number of backends: MySQL, PostgreSQL, SQLite,
Firebird, Sybase, MSSQL and MaxDB (also known as SAPDB).

Python 2.6 or 2.7 is required.


Where is SQLObject
==================

Site:
http://sqlobject.org

Development:
http://sqlobject.org/devel/

Mailing list:
https://lists.sourceforge.net/mailman/listinfo/sqlobject-discuss

Archives:
http://news.gmane.org/gmane.comp.python.sqlobject

Download:
https://pypi.python.org/pypi/SQLObject/2.2.0b2dev-20151224

News and changes:
http://sqlobject.org/News.html

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Strange ForeignKey behavior...

[Oleg B. <ph...@ph...>]

On Wed, Dec 23, 2015 at 11:09:35PM +0100, Oleg Broytman <ph...@ph...> wrote:
> 1. Postgres. Until now SQLObject used to create ForeignKeys of type
> idType of the current table, i.e. usually INT. I fixed that (by adding a
> lookup of idType of the referenced table), but that a big
> backward-incompatible change and I don't want to apply it to bugfix
> releases. So I think the patches will go to 2.2.0b2 and 3.0.0a2.

   BTW, see the patches (attached).

> > > Cheers!
> > > 
> > > - n
> > > 
> > > -- 
> > > Dr. Nathan Edwards                      nj...@ge...
> > > Department of Biochemistry and Molecular & Cellular Biology
> > >             Georgetown University Medical Center
> > >                 Room 1217, Harris Building,
> > >         3300 Whitehaven St, NW, Washington DC 20007
> > >            Phone: 202-687-7042, Fax: 202-687-0057

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Strange ForeignKey behavior...

[Oleg B. <ph...@ph...>]

On Wed, Dec 23, 2015 at 08:35:10PM +0100, Oleg Broytman <ph...@ph...> wrote:
> Hi!
> On Wed, Dec 23, 2015 at 12:09:27PM -0500, Nathan Edwards <nj...@ge...> wrote:
> > 
> > >> I'll see if I can figure out a minimalist patch to
> > >> propose...
> > > 
> > >    Eagerly waiting! But if you don't succeed notify me -- I'll at least
> > > add a TODO item.
> > 
> > Not sure if this will be up to your coding standards, but here is an
> > attempt - it uses the validator infrastructure to attempt conversion
> > using the idType function of the foreign table.
> > 
> > Patch for col.py and a new test module attached.
> 
>    Thank you! I need to work on it: the test doesn't work with Postgres;
> I'd like to rename 'to_python' to 'from_python' and remove 'from_python'
> assignment (I think there is no need to validate foreign keys when
> they're fetched from the database); and I want to fix style.

   I fixed all that but in required a few major changes.

1. Postgres. Until now SQLObject used to create ForeignKeys of type
idType of the current table, i.e. usually INT. I fixed that (by adding a
lookup of idType of the referenced table), but that a big
backward-incompatible change and I don't want to apply it to bugfix
releases. So I think the patches will go to 2.2.0b2 and 3.0.0a2.

2. 'to_python' => 'from_python': works.

3. Test applied, but I have to create a helper function to setup
mutually referenced classes; before that test framework didn't work with
cyclic references (but see test_cyclic_reference.py).

4. Style mostly fixed. I'll do more fixes when merge the code into
branch 3.0.

> > Cheers!
> > 
> > - n
> > 
> > -- 
> > Dr. Nathan Edwards                      nj...@ge...
> > Department of Biochemistry and Molecular & Cellular Biology
> >             Georgetown University Medical Center
> >                 Room 1217, Harris Building,
> >         3300 Whitehaven St, NW, Washington DC 20007
> >            Phone: 202-687-7042, Fax: 202-687-0057

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Strange ForeignKey behavior...

[Oleg B. <ph...@ph...>]

Hi!

On Wed, Dec 23, 2015 at 12:09:27PM -0500, Nathan Edwards <nj...@ge...> wrote:
> 
> >> I'll see if I can figure out a minimalist patch to
> >> propose...
> > 
> >    Eagerly waiting! But if you don't succeed notify me -- I'll at least
> > add a TODO item.
> 
> Not sure if this will be up to your coding standards, but here is an
> attempt - it uses the validator infrastructure to attempt conversion
> using the idType function of the foreign table. This will suffice to
> ensure values can be converted to the correct type, next level of
> validation would be to require the converted value to be a valid id for
> the foreign table (probably too much overhead). I think this approach is
> relatively consistent with the current style and conventions, but I'm
> sure you'll tell me if not. :-)
> 
> Patch for col.py and a new test module attached.

   Thank you! I need to work on it: the test doesn't work with Postgres;
I'd like to rename 'to_python' to 'from_python' and remove 'from_python'
assignment (I think there is no need to validate foreign keys when
they're fetched from the database); and I want to fix style.

> Cheers!
> 
> - n
> 
> -- 
> Dr. Nathan Edwards                      nj...@ge...
> Department of Biochemistry and Molecular & Cellular Biology
>             Georgetown University Medical Center
>                 Room 1217, Harris Building,
>         3300 Whitehaven St, NW, Washington DC 20007
>            Phone: 202-687-7042, Fax: 202-687-0057

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Strange ForeignKey behavior...

[Nathan E. <nj...@ge...>]

>> I'll see if I can figure out a minimalist patch to
>> propose...
> 
>    Eagerly waiting! But if you don't succeed notify me -- I'll at least
> add a TODO item.

Not sure if this will be up to your coding standards, but here is an
attempt - it uses the validator infrastructure to attempt conversion
using the idType function of the foreign table. This will suffice to
ensure values can be converted to the correct type, next level of
validation would be to require the converted value to be a valid id for
the foreign table (probably too much overhead). I think this approach is
relatively consistent with the current style and conventions, but I'm
sure you'll tell me if not. :-)

Patch for col.py and a new test module attached.

Cheers!

- n

-- 
Dr. Nathan Edwards                      nj...@ge...
Department of Biochemistry and Molecular & Cellular Biology
            Georgetown University Medical Center
                Room 1217, Harris Building,
        3300 Whitehaven St, NW, Washington DC 20007
           Phone: 202-687-7042, Fax: 202-687-0057

Re: [SQLObject] Strange ForeignKey behavior...

[Oleg B. <ph...@ph...>]

On Tue, Dec 22, 2015 at 02:19:12PM -0500, Nathan Edwards <nj...@ge...> wrote:
> On 12/22/2015 1:28 PM, Oleg Broytman wrote:
> > On Tue, Dec 22, 2015 at 12:50:09PM -0500, Nathan Edwards <nj...@ge...> wrote:
> class B(SQLObject):
>     anInt = IntCol()
>     afk = ForeignKey("A")
...
> # This succeeds and is inserted to sqlite database!
> b = B(anInt=4,afk='Strange!')
> 
> >    He-he. Yes, that a wart but it's hard to fix. The PRIMARY KEY (id)
> > column in a referenced table can be of string type[1] so ForeignKey
> > allows both ints and strings (a ForeignKey doesn't know the type of the
> > id column it points to).
> 
> Hmmm. I thought this was probably the rationale.

   Not at all. ForeignKeys just don't do any validation or conversion.
Would be worth fixing.

> I'll see if I can figure out a minimalist patch to
> propose...

   Eagerly waiting! But if you don't succeed notify me -- I'll at least
add a TODO item.

> Thanks,
> 
> - n
> 
> -- 
> Dr. Nathan Edwards                      nj...@ge...
> Department of Biochemistry and Molecular & Cellular Biology
>             Georgetown University Medical Center
>                 Room 1217, Harris Building,
>         3300 Whitehaven St, NW, Washington DC 20007
>            Phone: 202-687-7042, Fax: 202-687-0057

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Strange ForeignKey behavior...

[Nathan E. <nj...@ge...>]

Thanks for the quick response Oleg...

On 12/22/2015 1:28 PM, Oleg Broytman wrote:
> Hi!
> 
> On Tue, Dec 22, 2015 at 12:50:09PM -0500, Nathan Edwards <nj...@ge...> wrote:
>>
>> A number of my students used some form of the ForeignKey abuse shown
>> below. The first one B(anInt=2,afk=a.id) is pretty benign (though
>> unnecessary), but the really strange one is B(anInt=4,afk='Strange!').
>> At least with sqlite3 as the backend, this is stored in the database as
>> a string (!!!!), even though the schema declares it as an INT and
>> formalizes the reference constraint to table A's id (also an INT).
> 
>    Well, SQLite allows that: https://www.sqlite.org/faq.html#q3 (and see
> below...)

Wow. Quite the "feature". Gulp.

>    He-he. Yes, that a wart but it's hard to fix. The PRIMARY KEY (id)
> column in a referenced table can be of string type[1] so ForeignKey
> allows both ints and strings (a ForeignKey doesn't know the type of the
> id column it points to).

Hmmm. I thought this was probably the rationale. As a counterpoint,
however, when it the foreign key is dereferenced, the get method of the
Foreign table applies

  cls.sqlmeta.idType

to the value as part of the dereferencing code. This is in fact where
the exception is thrown when the string is not not the integer that
SQLObject expects.

This would suggest that ForeignKey *does* have a way to check the type
of the value at insert time...except that the ForeignKey column object
only has the name of the Foreign table as a string, not a table object,
due to declaration order issues, though by the time we are inserting
rows, it had better exist. Sigh.

>    If you want stricter foreign keys use stricter backends: MySQL with
> InnoDB tables or Postgres.
> 
> 1. http://sqlobject.org/FAQ.html#non-integer-ids

Here, the documentation suggests that idType can be int() or str(), and
other more complex non-integer ids be inserted by conversion to strings.

Seems like idType from the foreign table could be used to provide this
check (either calling it will each setting, or just testing for its two
recommended values). Not sure how big an efficiency hit it would be to
add this check, but all other values seem to be checked at insertion by
the column type...I'll see if I can figure out a minimalist patch to
propose...

Thanks,

- n

-- 
Dr. Nathan Edwards                      nj...@ge...
Department of Biochemistry and Molecular & Cellular Biology
            Georgetown University Medical Center
                Room 1217, Harris Building,
        3300 Whitehaven St, NW, Washington DC 20007
           Phone: 202-687-7042, Fax: 202-687-0057

Re: [SQLObject] Strange ForeignKey behavior...

[Oleg B. <ph...@ph...>]

Hi!

On Tue, Dec 22, 2015 at 12:50:09PM -0500, Nathan Edwards <nj...@ge...> wrote:
> 
> A number of my students used some form of the ForeignKey abuse shown
> below. The first one B(anInt=2,afk=a.id) is pretty benign (though
> unnecessary), but the really strange one is B(anInt=4,afk='Strange!').
> At least with sqlite3 as the backend, this is stored in the database as
> a string (!!!!), even though the schema declares it as an INT and
> formalizes the reference constraint to table A's id (also an INT).

   Well, SQLite allows that: https://www.sqlite.org/faq.html#q3 (and see
below...)

> Python 2.7.3, SQLObject version 2.1.2.
> 
> from sqlobject import *
> 
> class A(SQLObject):
>     aFloat = FloatCol()
> 
> class B(SQLObject):
>     anInt = IntCol()
>     afk = ForeignKey("A")
> 
> sqlhub.processConnection = connectionForURI('sqlite:test.db3?debug=1')
> 
> A.dropTable(ifExists=True)
> B.dropTable(ifExists=True)
> A.createTable()
> B.createTable()
> 
> a = A(aFloat=1.0)
> 
> b = B(anInt=1,afk=a)
> b = B(anInt=2,afk=a.id)
> b = B(anInt=3,afk=str(a.id))
> # This succeeds and is inserted to sqlite database!
> b = B(anInt=4,afk='Strange!')

   He-he. Yes, that a wart but it's hard to fix. The PRIMARY KEY (id)
column in a referenced table can be of string type[1] so ForeignKey
allows both ints and strings (a ForeignKey doesn't know the type of the
id column it points to).

   If you want stricter foreign keys use stricter backends: MySQL with
InnoDB tables or Postgres.

1. http://sqlobject.org/FAQ.html#non-integer-ids

> # IntCols are checked!
> # This throws a formencode exception
> # b = B(anInt='a string',afk=a)
> 
> for a in A.select():
>     print a
> for b in B.select():
>     # ValueError exception thrown in attempt to dereference
>     print b,b.afk
> 
> - n
> 
> -- 
> Dr. Nathan Edwards                      nj...@ge...
> Department of Biochemistry and Molecular & Cellular Biology
>             Georgetown University Medical Center
>                 Room 1217, Harris Building,
>         3300 Whitehaven St, NW, Washington DC 20007
>            Phone: 202-687-7042, Fax: 202-687-0057

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] Strange ForeignKey behavior...

[Nathan E. <nj...@ge...>]

A number of my students used some form of the ForeignKey abuse shown
below. The first one B(anInt=2,afk=a.id) is pretty benign (though
unnecessary), but the really strange one is B(anInt=4,afk='Strange!').
At least with sqlite3 as the backend, this is stored in the database as
a string (!!!!), even though the schema declares it as an INT and
formalizes the reference constraint to table A's id (also an INT).

Python 2.7.3, SQLObject version 2.1.2.

from sqlobject import *

class A(SQLObject):
    aFloat = FloatCol()

class B(SQLObject):
    anInt = IntCol()
    afk = ForeignKey("A")

sqlhub.processConnection = connectionForURI('sqlite:test.db3?debug=1')

A.dropTable(ifExists=True)
B.dropTable(ifExists=True)
A.createTable()
B.createTable()

a = A(aFloat=1.0)

b = B(anInt=1,afk=a)
b = B(anInt=2,afk=a.id)
b = B(anInt=3,afk=str(a.id))
# This succeeds and is inserted to sqlite database!
b = B(anInt=4,afk='Strange!')
# IntCols are checked!
# This throws a formencode exception
# b = B(anInt='a string',afk=a)

for a in A.select():
    print a
for b in B.select():
    # ValueError exception thrown in attempt to dereference
    print b,b.afk

- n

-- 
Dr. Nathan Edwards                      nj...@ge...
Department of Biochemistry and Molecular & Cellular Biology
            Georgetown University Medical Center
                Room 1217, Harris Building,
        3300 Whitehaven St, NW, Washington DC 20007
           Phone: 202-687-7042, Fax: 202-687-0057

Re: [SQLObject] sqlobject how to limit return column list

[Oleg B. <ph...@ph...>]

hi!

On Sun, Dec 20, 2015 at 09:38:56PM +0800, QQ???? <171...@qq...> wrote:
> Hi. I love sqlojbect very much.
> Please forgive my poor English,

   You're welcome!

> Example code:
> 
> class MyUser(SQLObject):
>   name=StringCol()
>   mobile=StringCol()
>   ??.
>   depart=StringCol()
>   sex =StringCol()
> 
> i want limit return column,
> data=MyUser.selectBy(depart=??xxx??,items=[??name??,??sex'])
> 
> only name and sex column in data.
> 
> Any help would be much appreciated!

   When you use high-level SQLObject interface (like SQLObject.select,
selectBy and such) the result is always a list of SQLObject instances,
instances of MyUser in your example, and every instance must have data
for all column filled in.
   If you want to execute less SQLObject-aware queries use Select from
SQLBuilder: http://sqlobject.org/SQLBuilder.html#select

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] sqlobject how to limit return column list

[QQ邮箱 <171...@qq...>]

Hi. I love sqlojbect very much.
  
Please forgive my poor English,

Example code:

class MyUser(SQLObject):
  name=StringCol()
  mobile=StringCol()
  ….
  depart=StringCol()
  sex =StringCol()

i want limit return column,
data=MyUser.selectBy(depart=‘xxx’,items=[’name’,’sex'])

only name and sex column in data.

Any help would be much appreciated!

Re: [SQLObject] sqlobject one-to-many join question

[Oleg B. <ph...@ph...>]

On Wed, Dec 16, 2015 at 10:10:13AM +0100, Oleg Broytman <ph...@ph...> wrote:
> class Table_Groups(SQLObject):
>     name = StringCol(length=32)
>     sensors = MultipleJoin("Table_Sensors", joinColumn="id")

   This time it was my fault:

    sensors = MultipleJoin("Table_Sensors", joinColumn="group_id")

> group = Table_Groups.select(Table_Groups.q.name=='Feels_Like')[0]
> for sensor in group.sensors:
>     print sensor

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] sqlobject one-to-many join question

[Oleg B. <ph...@ph...>]

Hi!

On Tue, Dec 15, 2015 at 03:49:29PM -0800, jo...@ma... wrote:
> So I've been absolutely loving sqlobject, until I get to actually get to
> making relations between tables.  At this point, I feel that the
> 'documentation' covers some points, but really doesn't explain other
> bits very well at all.

   Patches for documentation will be gladly accepted, as well as patches
for code and for tests.

> So I have 2 problems:
> 
> The first is that when running my program the first time, it fails to
> create all the tables (using .createTable(ifNotExists = True))  and only
> several tables are generated, before it errors out with: 
> 
> > sqlobject.dberrors.OperationalError: Can't create table `rainmeter`.`#sql-65c_209` (errno: 150 "Foreign key constraint is incorrectly formed")
> 
> Then running it again causes the rest of the tables to be created. 
> 
> Here's the relevant parts of my class definitions: 
> 
> > class Table_Sensors(sqlobject.SQLObject):
> > name = sqlobject.StringCol(length=32)
> > group = sqlobject.ForeignKey("Table_Groups")
> 
> > class Table_Groups(sqlobject.SQLObject):
> > name = sqlobject.StringCol(length=32)
> > sensors = sqlobject.MultipleJoin("Table_Sensors") 
> > Table_Sensors.createTable(ifNotExists = True)
> > Table_Groups.createTable(ifNotExists = True)

   You create table in the wrong order. Table_Sensors has a ForeignKey
pointing to to Table_Groups so you must create Table_Groups first and
then Table_Sensors.

> Anyways, this is inconvenient, but it does work after running it again,
> so I can put up with it.  The real problem I've been having is that I'm
> having trouble figuring out how to select all entries in Table_Sensors
> that point to a specific Table_Groups. 
> 
> Here's one example I've tried: 
> 
> > Table_Groups.select(Table_Groups.q.name=="Feels_Like", Table_Groups.q.sensors==Table_Sensors.q.group, orderBy=Table_Sensors.q.sort_order)

   There is a bug in the query: it lists two conditions separated by a
comma. That doesn't work because the second condition becomes the second
parameter for .select() instead of being a part of the condition. You
must join conditions using either AND() from sqlbuilder or '&' operator:

Table_Groups.select(Table_Groups.q.name=="Feels_Like" & Table_Groups.q.sensors==Table_Sensors.q.group, orderBy=Table_Sensors.q.sort_order)

> But no matter what I try, even a much simpler query, It just spits back:
> 
> > AttributeError: Table_Groups instance has no attribute 'sensors'

   In SQL sense Table_Groups really doesn't have an attribute 'sensors'.
MultipleJoin doesn't add an attribute to SQL table. Instead it instructs
SQLObject: "when the user asks for Table_Groups.sensors select rows from
Table_Sensors that point to that Table_Groups' row".
   So this query works:

Table_Groups.select(Table_Groups.q.name=="Feels_Like" &
                    Table_Groups.q.id==Table_Sensors.q.group,
                    orderBy=Table_Sensors.q.id)

   BTW, there is no need to construct an SQL query in your case -- use
the power of SQLObject. If you want to select Table_Sensors for a group
just fetch the group and ask for its sensors.
   First, let's fix the join, it should explicitly declare that it
points to 'id' column in the other table:

class Table_Groups(SQLObject):
    name = StringCol(length=32)
    sensors = MultipleJoin("Table_Sensors", joinColumn="id")

group = Table_Groups.select(Table_Groups.q.name=='Feels_Like')[0]
for sensor in group.sensors:
    print sensor

> As a side-note, I think some of the things I'm trying to do, I could use
> j-magic for, but the section on that is incredibly short, and I haven't
> been able to get that to work either :/ 

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] sqlobject one-to-many join question

[<jo...@ma...>]

 
So I've been absolutely loving sqlobject, until I get to actually get to
making relations between tables.  At this point, I feel that the
'documentation' covers some points, but really doesn't explain other
bits very well at all.

So I have 2 problems:

The first is that when running my program the first time, it fails to
create all the tables (using .createTable(ifNotExists = True))  and only
several tables are generated, before it errors out with: 

> sqlobject.dberrors.OperationalError: Can't create table `rainmeter`.`#sql-65c_209` (errno: 150 "Foreign key constraint is incorrectly formed")

Then running it again causes the rest of the tables to be created. 

Here's the relevant parts of my class definitions: 

> class Table_Sensors(sqlobject.SQLObject):
> name = sqlobject.StringCol(length=32)
> group = sqlobject.ForeignKey("Table_Groups")

> class Table_Groups(sqlobject.SQLObject):
> name = sqlobject.StringCol(length=32)
> sensors = sqlobject.MultipleJoin("Table_Sensors") 
> Table_Sensors.createTable(ifNotExists = True)
> Table_Groups.createTable(ifNotExists = True)

Anyways, this is inconvenient, but it does work after running it again,
so I can put up with it.  The real problem I've been having is that I'm
having trouble figuring out how to select all entries in Table_Sensors
that point to a specific Table_Groups. 

Here's one example I've tried: 

> Table_Groups.select(Table_Groups.q.name=="Feels_Like", Table_Groups.q.sensors==Table_Sensors.q.group, orderBy=Table_Sensors.q.sort_order)

But no matter what I try, even a much simpler query, It just spits back:


> AttributeError: Table_Groups instance has no attribute 'sensors'

As a side-note, I think some of the things I'm trying to do, I could use
j-magic for, but the section on that is incredibly short, and I haven't
been able to get that to work either :/ 

Re: [SQLObject] sqlobject caching (was: wsgi)

[Oleg B. <ph...@ph...>]

Hi!

On Sun, Dec 13, 2015 at 11:57:05PM -0800, jo...@ma... wrote:
> It appears that I am retarded.
> 
> For some reason, the thought that a wsgi instance might not reload stuff 
> never entered my head, because I thought "Oh, it's in a function even, 
> there's no way those bits can be carrying over."  But... of course the 
> actual database bits have already been imported, and they don't get 
> reloaded every time....  and wouldn't you know it, since stuff in the DB 
> is being modified by something else OTHER THAN than the wsgi 
> application, I need to use:
> 
> now = Table_Now.get(1)
> now.sync()

   The "problem" is that SQLObject caches fetched rows. The cache is in
the DB connection object. You can clear the cache manually, or disable
caching completely: when you create a DBConnection instance pass
"cache=False" parameter to it.

> for my previous example.
> 
> Hopefully this will help someone else making the same stupid mistakes as 
> I did.
> 
> ---
> Sent from my new email address: jo...@ma...
> 
> On 2015-12-13 11:34 pm, jo...@ma... wrote:
> > I seem to be having troubles getting sqlobject to play nicely with 
> > wsgi.
> > 
> > In short, sqlobject seems to be returning the same data that was 
> > fetched
> > before each time, until Apache is restarted.
> > 
> > Example code:  (much simplified)
> > 
> > now = Table_Now.get(1)
> > data["some_field"] = now.some_field
> > //do something with data{}
> > 
> > but even though the row being returned (I simplified it to the code
> > above with a single row to try to pinpoint the problem) is actually
> > being changed in the SQL db (mySQL) sqlobject always returns the same,
> > old data...
> > 
> > I did see mentioned somewhere about "sqlobject.wsgi_middleware," but I
> > can't seem to figure out how that is supposed to be implemented, and
> > unfortunatly, there doesn't seem to be any documentation covering that
> > anywhere...
> > 
> > Any help would be much appreciated!

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] sqlobject with wsgi [I'm retarded]

[<jo...@ma...>]

It appears that I am retarded.

For some reason, the thought that a wsgi instance might not reload stuff 
never entered my head, because I thought "Oh, it's in a function even, 
there's no way those bits can be carrying over."  But... of course the 
actual database bits have already been imported, and they don't get 
reloaded every time....  and wouldn't you know it, since stuff in the DB 
is being modified by something else OTHER THAN than the wsgi 
application, I need to use:

now = Table_Now.get(1)
now.sync()

for my previous example.

Hopefully this will help someone else making the same stupid mistakes as 
I did.

---
Sent from my new email address: jo...@ma...

On 2015-12-13 11:34 pm, jo...@ma... wrote:
> I seem to be having troubles getting sqlobject to play nicely with 
> wsgi.
> 
> In short, sqlobject seems to be returning the same data that was 
> fetched
> before each time, until Apache is restarted.
> 
> Example code:  (much simplified)
> 
> now = Table_Now.get(1)
> data["some_field"] = now.some_field
> //do something with data{}
> 
> but even though the row being returned (I simplified it to the code
> above with a single row to try to pinpoint the problem) is actually
> being changed in the SQL db (mySQL) sqlobject always returns the same,
> old data...
> 
> I did see mentioned somewhere about "sqlobject.wsgi_middleware," but I
> can't seem to figure out how that is supposed to be implemented, and
> unfortunatly, there doesn't seem to be any documentation covering that
> anywhere...
> 
> Any help would be much appreciated!
> 
> ------------------------------------------------------------------------------
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

[SQLObject] sqlobject with wsgi

[<jo...@ma...>]

I seem to be having troubles getting sqlobject to play nicely with wsgi.

In short, sqlobject seems to be returning the same data that was fetched 
before each time, until Apache is restarted.

Example code:  (much simplified)

now = Table_Now.get(1)
data["some_field"] = now.some_field
//do something with data{}

but even though the row being returned (I simplified it to the code 
above with a single row to try to pinpoint the problem) is actually 
being changed in the SQL db (mySQL) sqlobject always returns the same, 
old data...

I did see mentioned somewhere about "sqlobject.wsgi_middleware," but I 
can't seem to figure out how that is supposed to be implemented, and 
unfortunatly, there doesn't seem to be any documentation covering that 
anywhere...

Any help would be much appreciated!

Re: [SQLObject] DateCol format on default

[Gustavo A. D. <gus...@gm...>]

Thanks for the info! :)

Now I think my signature is fixed :D

Cheers.

-- 
Gustavo A. Díaz
GDNet - www.gdnet.com.ar
"Servicios TI para tus necesidades"

2015-08-26 17:11 GMT-03:00 Oleg Broytman <ph...@ph...>:

> On Wed, Aug 26, 2015 at 04:46:35PM -0300, "Gustavo A. D??az" <
> gus...@gm...> wrote:
> > Oleg!
> >
> > Thanks for reply. Yeah, but is ok since finally I don't care how is
> stored
> > in the DB since I can parse it later (sometimes I ask questions in lists,
>
>    Ok.
>
> > forums and in a few minutes I reply myself with a solution... sorry :P)
>
>    No problem!
>
> > About signature: lol (If true I didn't know :P)
>
>    https://tools.ietf.org/html/rfc3676#section-4.3
>
>    https://en.wikipedia.org/wiki/Signature_block
>
>    "The Usenet standards specify that a signature block should be
> displayed as plain text in a fixed-width font (no HTML, images, or other
> rich text), and should be delimited from the body of the message by a
> single line consisting of exactly two hyphens, followed by a space,
> followed by the end of line (i.e., "-- \n"). This latter prescription,
> which goes by many names, including "sig dashes", "signature cut line",
> "sig-marker", "sig separator" and "signature delimiter", allows software
> to automatically mark or remove the sig block as the receiver desires."
>
> > Thanks!
>
>    You are welcome!
>
> > --
> >
> > Gustavo A. D??az
> > GDNet - www.gdnet.com.ar
> > "Servicios TI para tus necesidades"
> >
> > 2015-08-26 14:26 GMT-03:00 Oleg Broytman <ph...@ph...>:
> >
> > > On Wed, Aug 26, 2015 at 02:07:38PM -0300, "Gustavo A. D??az" <
> > > gus...@gm...> wrote:
> > > > Is there anyway to change for date format on DateCol default value?
> > > >
> > > > I use DateCol(default=datetime.strftime(datetime.now(), '%d-%m-%Y')
> but
> > > > does not work:
> > > >
> > > > unhandled formencode.api.Invalid "expected a date/time string of the
> > > > '%Y-%m-%d' format in the DateTimeCol 'modified', got <class 'str'>
> > > > '26-08-2015' instead"
> > >
> > >    The problem is that the code that's used to parse user input is also
> > > used to parse output from the backend, so if you want to change the
> > > format yo have to change it at the DB level too. And then you have to
> > > change DateTimeConverter to pass date/time strings to the backing in
> the
> > > proper format.
> > >    See the discussion and some examples at
> > > https://github.com/sqlobject/sqlobject/issues/114
> > >
> > >    Your best bet, I think, is to parse d/t strings yourself and pass
> > > date/datetime instances to your column(s).
> > >
> > > > Thanks!
> > > >
> > > > --
> > >     ^ You have forgotten a space here; signatures are separated by two
> > > dashes and a space. (-:
>
> Oleg.
> --
>      Oleg Broytman            http://phdru.name/            ph...@ph...
>            Programmers don't die, they just GOSUB without RETURN.
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>

Re: [SQLObject] DateCol format on default

[Oleg B. <ph...@ph...>]

On Wed, Aug 26, 2015 at 04:46:35PM -0300, "Gustavo A. D??az" <gus...@gm...> wrote:
> Oleg!
> 
> Thanks for reply. Yeah, but is ok since finally I don't care how is stored
> in the DB since I can parse it later (sometimes I ask questions in lists,

   Ok.

> forums and in a few minutes I reply myself with a solution... sorry :P)

   No problem!

> About signature: lol (If true I didn't know :P)

   https://tools.ietf.org/html/rfc3676#section-4.3

   https://en.wikipedia.org/wiki/Signature_block

   "The Usenet standards specify that a signature block should be
displayed as plain text in a fixed-width font (no HTML, images, or other
rich text), and should be delimited from the body of the message by a
single line consisting of exactly two hyphens, followed by a space,
followed by the end of line (i.e., "-- \n"). This latter prescription,
which goes by many names, including "sig dashes", "signature cut line",
"sig-marker", "sig separator" and "signature delimiter", allows software
to automatically mark or remove the sig block as the receiver desires."

> Thanks!

   You are welcome!

> --
> 
> Gustavo A. D??az
> GDNet - www.gdnet.com.ar
> "Servicios TI para tus necesidades"
> 
> 2015-08-26 14:26 GMT-03:00 Oleg Broytman <ph...@ph...>:
> 
> > On Wed, Aug 26, 2015 at 02:07:38PM -0300, "Gustavo A. D??az" <
> > gus...@gm...> wrote:
> > > Is there anyway to change for date format on DateCol default value?
> > >
> > > I use DateCol(default=datetime.strftime(datetime.now(), '%d-%m-%Y') but
> > > does not work:
> > >
> > > unhandled formencode.api.Invalid "expected a date/time string of the
> > > '%Y-%m-%d' format in the DateTimeCol 'modified', got <class 'str'>
> > > '26-08-2015' instead"
> >
> >    The problem is that the code that's used to parse user input is also
> > used to parse output from the backend, so if you want to change the
> > format yo have to change it at the DB level too. And then you have to
> > change DateTimeConverter to pass date/time strings to the backing in the
> > proper format.
> >    See the discussion and some examples at
> > https://github.com/sqlobject/sqlobject/issues/114
> >
> >    Your best bet, I think, is to parse d/t strings yourself and pass
> > date/datetime instances to your column(s).
> >
> > > Thanks!
> > >
> > > --
> >     ^ You have forgotten a space here; signatures are separated by two
> > dashes and a space. (-:

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.