On 5/24/10 10:22 AM, Carey Gagnon wrote:
> Thanks to much help from Adrian I was able to pull data from the database
> under Linux no problem, with a call from my ui/FrmReportTimeSummary.py file
> to my db/getTimeSummaryDataSet.py file (for a report). Under Windows XP home
> (work computer) it's a whole other issue.
>
> Here's the content of my getTimeSummaryDataSet.py:
>
> <code>
>
> #-*- coding: utf-8 -*-
>
> import datetime
> import decimal
> import dabo
>
> def getTimeSummaryDataSet():
>
> import sqlite3
> conn=sqlite3.connect('C:\path\to\database\file')
Try this:
import sqlite3.dbapi2 as sqlite
conn = sqlite.connect('c:\\\\path\\to\\database\\file')
IOW, escape the backslashes so they don't get interpreted as control
characters. That
\t gets converted to a tab by Python otherwise.
> crs = conn.cursor()
> crs.execute("select customers.name as custName , projects.name as
> projName, d.duration from customers, projects, (select customerID,
> projectID, sum(strftime('%s',toTime)-strftime('%s',fromTime)) as duration
> from records where taskID!=6 and customerID!=3 GROUP BY
> customerID,projectID) as d where d.customerID=customers.id and d.projectID=
> projects.id")
Write it like this instead for easier reading:
sql = """
select customers.name as custName,
projects.name as projName,
...
"""
crs.execute(sql)
> ds = []
> for cust in crs.fetchall():
> dsentry = {'custName':cust[0], 'projName': cust[1],
> 'durationtotal': cust[2],}
> ds.append(dsentry)
> return ds
>
> </code>
>
> With the path set as shown I kept getting: sqlite3.OperationalError: unable
> to open database file.
> Changing it to C:/path/to/database/file (to fore slash instead of back
> slash) allowed the connection but now I get this error:
>
> Traceback (most recent call last):
> File "C:\src\dabo\ui\uiwx\dControlMixin.py", line 27, in _onWxHit
> self.raiseEvent(dEvents.Hit, evt, *args, **kwargs)
> File "C:\src\dabo\ui\uiwx\dPemMixin.py", line 959, in raiseEvent
> super(dPemMixin, self).raiseEvent(eventClass, nativeEvent, *args,
> **kwargs)
> File "C:\src\dabo\lib\eventMixin.py", line 93, in raiseEvent
> bindingFunction(event)
> File "C:\normacapps\timeReporting\ui\FrmReportBase.py", line 41, in
> onHit_cmdRunReport
> self.runReport()
> File "C:\normacapps\timeReporting\ui\FrmReportBase.py", line 53, in
> runReport
> self.requery()
> File "C:\normacapps\timeReporting\ui\FrmReportTimeSummary.py", line 39, in
> requery
> self.DataSet = self.DataSetFunction()
> File "C:\normacapps\timeReporting\db\getTimeSummaryDataSet.py", line 12,
> in getTimeSummaryDataSet
> crs.execute("select customers.name as custName , projects.name as
> projName, d.duration from customers, projects, (select customerID,
> projectID, sum(strftime('%s',toTime)-strftime('%s',fromTime)) as duration
> from records where taskID!=6 and customerID!=3 GROUP BY
> customerID,projectID) as d where d.customerID=customers.id and d.projectID=
> projects.id")
> sqlite3.OperationalError: no such table: customers
Seems like it should have worked. Do a Windows search for all files created in
the
last day and see if you can find where sqlite may have saved the file, instead
of the
intended location.
> The customers table definitely exists. As I said this works great under
> Linux so I know it's not a programming error.
No, Windows is the programming error. :)
> All my google searches show that there are issues under Windows regarding
> sqlite connections and sub queries but I've found no specific python or Dabo
> solution.
I think this is a pathing problem, and that a new empty database is being
created
instead of your existing database opened.
You could ascertain this by executing this sql on the connection:
sql = """
select name
from sqlite_master
where type = 'table'
"""
cur.execute(sql)
print [r[0] for r in cur.fetchall()]
If you see no output, it is a database with no tables defined.
Paul
_______________________________________________
Post Messages to: [email protected]
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/[email protected]
