[dabo-users] Grid from SQLite Views

Back to top

Author: Dewald Jacobs

Posted: 2012-03-02 05:56:16 Link

Hi,

I have a SQLite database with a View (created with the command CREATE VIEW, just to be clear) that was created by combining 2 tables plus and an added column to compute JULIANDAY. How would I go about adding this View in a grid in Class Designer? I want to treat this view exactly the same as a normal table and have tried to implement it as such, but have failed to do so.

Any tips or advice would be much appreciated.

Thanks

Dewald

_______________________________________________

Post Messages to: Dabo-users@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users

Searchable Archives: http://leafe.com/archives/search/dabo-users

This message: http://leafe.com/archives/byMID/6DE7900F-5D7A-4D4C-BC56-5F0A592EBC2E@gmail.com

New Search

Back to top

Re: [dabo-users] Grid from SQLite Views

Author: John Fabiani

Posted: 2012-03-02 09:27:41 Link

On Friday, March 02, 2012 01:56:16 PM Dewald Jacobs wrote:

> Hi,

>

> I have a SQLite database with a View (created with the command CREATE VIEW,

> just to be clear) that was created by combining 2 tables plus and an added

> column to compute JULIANDAY. How would I go about adding this View in a

> grid in Class Designer? I want to treat this view exactly the same as a

> normal table and have tried to implement it as such, but have failed to do

> so.

>

> Any tips or advice would be much appreciated.

>

> Thanks

> Dewald

I don't use SQLite.

But, looking at the code - dbSQLite.py - the views are not retrieved.

Something like

select name from sqlite_master where type = 'view'

is needed.

Question is how best to add in the views and will anything need changing to

support ClassDesigner.

Johnf

_______________________________________________

Post Messages to: Dabo-users@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users

Searchable Archives: http://leafe.com/archives/search/dabo-users

This message: http://leafe.com/archives/byMID/1847780.x6huU0sifq@linux-12

New Search

Back to top

Re: [dabo-users] Grid from SQLite Views

Author: John Fabiani

Posted: 2012-03-02 09:31:25 Link

On Friday, March 02, 2012 07:27:41 AM John Fabiani wrote:

> On Friday, March 02, 2012 01:56:16 PM Dewald Jacobs wrote:

> > Hi,

> >

> > I have a SQLite database with a View (created with the command CREATE

> > VIEW, just to be clear) that was created by combining 2 tables plus and

> > an added column to compute JULIANDAY. How would I go about adding this

> > View in a grid in Class Designer? I want to treat this view exactly the

> > same as a normal table and have tried to implement it as such, but have

> > failed to do so.

> >

> > Any tips or advice would be much appreciated.

> >

> > Thanks

> > Dewald

>

> I don't use SQLite.

> But, looking at the code - dbSQLite.py - the views are not retrieved.

> Something like

> select name from sqlite_master where type = 'view'

> is needed.

>

> Question is how best to add in the views and will anything need changing to

> support ClassDesigner.

>

> Johnf

Opps - sent to fast!

Try this and see if it works.

In dbSQLite.py

def getTables(self, cursor, includeSystemTables=False):

cursor.execute("select * from sqlite_master")

rs = cursor.getDataSet()

if includeSystemTables:

tables = [rec["name"] for rec in rs

if rec["type"] == "table"]

else:

tables = [rec["name"] for rec in rs

if rec["type"] == "table" or rec['type'] == 'view'

and not rec["name"].startswith("sqlite_")]

return tuple(tables)

_______________________________________________

Post Messages to: Dabo-users@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users

Searchable Archives: http://leafe.com/archives/search/dabo-users

This message: http://leafe.com/archives/byMID/1397013.NQbhjzTa81@linux-12

New Search

Back to top

Re: [dabo-users] Grid from SQLite Views

Author: Dewald Jacobs

Posted: 2012-03-02 11:33:00 Link

Hi,

I tried that and I can now successfully choose a View from "Add controls from Data Environment" in class designer. The column names also show up in the grid. The app runs, but I get the following traceback:

Traceback (most recent call last):

File "C:\Python27\lib\site-packages\wx-2.8-msw-unicode\wx\_core.py", line 1466

9, in <lambda>

lambda event: event.callable(*event.args, **event.kw) )

File "C:\projects\dabo\ui\uiwx\dGrid.py", line 2250, in _restoreSort

self.processSort(sortCol, toggleSort=False)

File "C:\projects\dabo\ui\uiwx\dGrid.py", line 2894, in processSort

biz.sort(columnToSort, sortOrder, self.caseSensitiveSorting)

File "C:\projects\dabo\biz\dBizobj.py", line 1209, in sort

cc.sort(col, ordr, caseSensitive)

File "C:\projects\dabo\db\dCursorMixin.py", line 560, in sort

_("Invalid column specified for sort: ") + col)

dabo.dException.dException: Invalid column specified for sort: Gebeurtenis

Gebeurtenis is a column in the grid/view. When I then proceed to click on the tab in which the grid in question is situated I get an endless loop with the following traceback:

Traceback (most recent call last):

File "C:\projects\dabo\ui\uiwx\dGrid.py", line 331, in GetValue

ret = self.getStringValue(bizobj.getFieldVal(field, row))

File "C:\projects\dabo\biz\dBizobj.py", line 1872, in getFieldVal

ret = self._CurrentCursor.getFieldVal(fld, row, _rowChangeCallback=changeRow

NumCallback)

File "C:\projects\dabo\db\dCursorMixin.py", line 940, in getFieldVal

_("Field"), fld, _("does not exist in the data set")))

dabo.dException.FieldNotFoundException: Field 'Julian' does not exist in the dat

a set

Julian is a column in the grid/view. This traceback repeats and cycles all the column names from the View. How should I proceed? Is there any other info you need from me?

Thanks again.

Dewald

On Mar 2, 2012, at 5:31 PM, John Fabiani wrote:

> On Friday, March 02, 2012 07:27:41 AM John Fabiani wrote:

>> On Friday, March 02, 2012 01:56:16 PM Dewald Jacobs wrote:

>>> Hi,

>>>

>>> I have a SQLite database with a View (created with the command CREATE

>>> VIEW, just to be clear) that was created by combining 2 tables plus and

>>> an added column to compute JULIANDAY. How would I go about adding this

>>> View in a grid in Class Designer? I want to treat this view exactly the

>>> same as a normal table and have tried to implement it as such, but have

>>> failed to do so.

>>>

>>> Any tips or advice would be much appreciated.

>>>

>>> Thanks

>>> Dewald

>>

>> I don't use SQLite.

>> But, looking at the code - dbSQLite.py - the views are not retrieved.

>> Something like

>> select name from sqlite_master where type = 'view'

>> is needed.

>>

>> Question is how best to add in the views and will anything need changing to

>> support ClassDesigner.

>>

>> Johnf

>

> Opps - sent to fast!

>

> Try this and see if it works.

> In dbSQLite.py

> def getTables(self, cursor, includeSystemTables=False):

> cursor.execute("select * from sqlite_master")

> rs = cursor.getDataSet()

> if includeSystemTables:

> tables = [rec["name"] for rec in rs

> if rec["type"] == "table"]

> else:

> tables = [rec["name"] for rec in rs

> if rec["type"] == "table" or rec['type'] == 'view'

> and not rec["name"].startswith("sqlite_")]

> return tuple(tables)

> _______________________________________________

> Post Messages to: Dabo-users@leafe.com

> Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users

> Searchable Archives: http://leafe.com/archives/search/dabo-users

> This message: http://leafe.com/archives/byMID/1397013.NQbhjzTa81@linux-12

--- StripMime Report -- processed MIME parts ---

multipart/alternative

text/plain (text body -- kept)

text/html

---

_______________________________________________

Post Messages to: Dabo-users@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users

Searchable Archives: http://leafe.com/archives/search/dabo-users

This message: http://leafe.com/archives/byMID/2C9701DF-8356-4AC1-94B7-40F173B56ACC@gmail.com

New Search

Back to top

Re: [dabo-users] Grid from SQLite Views

Author: John Fabiani

Posted: 2012-03-02 12:00:46 Link

On Friday, March 02, 2012 07:33:00 PM Dewald Jacobs wrote:

> Hi,

>

> I tried that and I can now successfully choose a View from "Add controls

> from Data Environment" in class designer. The column names also show up in

> the grid. The app runs, but I get the following traceback:

>

> Traceback (most recent call last):

> File "C:\Python27\lib\site-packages\wx-2.8-msw-unicode\wx\_core.py", line

> 1466 9, in <lambda>

> lambda event: event.callable(*event.args, **event.kw) )

> File "C:\projects\dabo\ui\uiwx\dGrid.py", line 2250, in _restoreSort

> self.processSort(sortCol, toggleSort=False)

> File "C:\projects\dabo\ui\uiwx\dGrid.py", line 2894, in processSort

> biz.sort(columnToSort, sortOrder, self.caseSensitiveSorting)

> File "C:\projects\dabo\biz\dBizobj.py", line 1209, in sort

> cc.sort(col, ordr, caseSensitive)

> File "C:\projects\dabo\db\dCursorMixin.py", line 560, in sort

> _("Invalid column specified for sort: ") + col)

> dabo.dException.dException: Invalid column specified for sort: Gebeurtenis

>

> Gebeurtenis is a column in the grid/view. When I then proceed to click on

> the tab in which the grid in question is situated I get an endless loop

> with the following traceback:

>

> Traceback (most recent call last):

> File "C:\projects\dabo\ui\uiwx\dGrid.py", line 331, in GetValue

> ret = self.getStringValue(bizobj.getFieldVal(field, row))

> File "C:\projects\dabo\biz\dBizobj.py", line 1872, in getFieldVal

> ret = self._CurrentCursor.getFieldVal(fld, row,

> _rowChangeCallback=changeRow NumCallback)

> File "C:\projects\dabo\db\dCursorMixin.py", line 940, in getFieldVal

> _("Field"), fld, _("does not exist in the data set")))

> dabo.dException.FieldNotFoundException: Field 'Julian' does not exist in the

> dat a set

>

> Julian is a column in the grid/view. This traceback repeats and cycles all

> the column names from the View. How should I proceed? Is there any other

> info you need from me?

>

> Thanks again.

> Dewald

>

Hmmmmmmm! I don't know enough about SQLite to really be a help. I don't know

if the issue lies with the view or the column data type (never really

understood SQLites lack of data types).

Here's how I'd move forward:

I would try and determine if the issue lies with view or the column by

creating a real table with the same fields and try using it with

ClassDesigner. If works then it has something to do with the view. If not

then it has someting to do with the data type and how the code is trying to

sort.

There have been recent changes to the ClassDesigner and maybe those changes

are causing new issues. Maybe someone with a little experience with SQLite

will jump in and provide a little insight.

Johnf

_______________________________________________

Post Messages to: Dabo-users@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users

Searchable Archives: http://leafe.com/archives/search/dabo-users

This message: http://leafe.com/archives/byMID/8019684.MRniqbd0Py@linux-12

New Search

Back to top

Re: [dabo-users] Grid from SQLite Views

Author: Ed Leafe

Posted: 2012-03-02 12:05:09 Link

On Mar 2, 2012, at 11:33 AM, Dewald Jacobs wrote:

> Julian is a column in the grid/view. This traceback repeats and cycles all the column names from the View. How should I proceed? Is there any other info you need from me?

Just curious: is the actual column name capitalized, or could there be a problem with upper/lower case names here?

-- Ed Leafe

_______________________________________________

Post Messages to: Dabo-users@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users

Searchable Archives: http://leafe.com/archives/search/dabo-users

This message: http://leafe.com/archives/byMID/DADBC4D5-0F61-4540-986E-04C584B16550@leafe.com

New Search

Back to top

Re: [dabo-users] Grid from SQLite Views

Author: Dewald Jacobs

Posted: 2012-03-02 12:10:06 Link

It is capitalized. Is that a problem? I have been working with this database for a while and my tables all work in dabo with capitalized columns. The two tables that I use for the View is already used in the app. The only column that is created in the View is Julian, but I get errors on all the columns. Should I try all column names in the lower case?

On Mar 2, 2012, at 8:05 PM, Ed Leafe wrote:

> On Mar 2, 2012, at 11:33 AM, Dewald Jacobs wrote:

>

>> Julian is a column in the grid/view. This traceback repeats and cycles all the column names from the View. How should I proceed? Is there any other info you need from me?

>

> Just curious: is the actual column name capitalized, or could there be a problem with upper/lower case names here?

>

> -- Ed Leafe

>

> _______________________________________________

> Post Messages to: Dabo-users@leafe.com

> Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users

> Searchable Archives: http://leafe.com/archives/search/dabo-users

> This message: http://leafe.com/archives/byMID/DADBC4D5-0F61-4540-986E-04C584B16550@leafe.com

_______________________________________________

Post Messages to: Dabo-users@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users

Searchable Archives: http://leafe.com/archives/search/dabo-users

This message: http://leafe.com/archives/byMID/51D92639-25E9-48A4-B4CA-86BA4D5E8C03@gmail.com

New Search

Back to top

Re: [dabo-users] Grid from SQLite Views

Author: Ed Leafe

Posted: 2012-03-02 12:35:51 Link

On Mar 2, 2012, at 12:10 PM, Dewald Jacobs wrote:

> It is capitalized. Is that a problem? I have been working with this database for a while and my tables all work in dabo with capitalized columns. The two tables that I use for the View is already used in the app. The only column that is created in the View is Julian, but I get errors on all the columns. Should I try all column names in the lower case?

It might be worth it to try. As a rule, I always name my tables, columns, indices, etc., in lower case to avoid case-sensitivity issues, so I'm sure that if there are some places in the code where case comes into play, it probably isn't very well tested.

-- Ed Leafe

_______________________________________________

Post Messages to: Dabo-users@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users

Searchable Archives: http://leafe.com/archives/search/dabo-users

This message: http://leafe.com/archives/byMID/81F05995-0F49-401E-BF4C-F925047D589F@leafe.com

New Search

Back to top

Re: [dabo-users] Grid from SQLite Views

Author: John Fabiani

Posted: 2012-03-02 12:40:54 Link

On Friday, March 02, 2012 12:35:51 PM Ed Leafe wrote: