[Kexi] Examples/questions re. scripts for greater immediate Kexi applicability

[Roderick Drew]

First of all, many thanks to Jaraslow Staniek and all the Kexi developers for 
all the great work you are all doing on Kexi.  It already has great features, 
runs fast and is stable enough that I have never lost data, and only rarely 
lost changes I have made to forms!

My reason for writing is to discuss maximisation of Kexi's applicability
as it presently stands. Having written a database around it, several 
limitations have inevitably come to light.  I know that these are being 
worked on, but coding and testing are complex and time-consuming, 
and Rome wasn't built in a day!   Importantly, Kexi cannot process real 
numbers running SQL queries within it (see my KDE bug nos 147809), and many 
SQL queries don't work.  Kexi doesn't yet allow manual assignation of real 
numbers (see my KDE bug No.  146818) when reading .csv files, and when .csv 
files contain null cells, data often end up in the wrong table columns!  
So one needs to populate any such .csv files with dummy values that can be 
subsequently removed from within Kexi.  Dummy values also ensure that Kexi 
can correctly and automatically interpret column formats.  A further problem 
is not to do with Kexi but with SQL: one can only seem to issue one query at 
a time, and I need to issue over 50 queries at one click!

Being enthusiastic, but with limited knowledge of programming and SQL, I've 
tried to find a way around all this after reading Kexi support and other 
useful information that has been posted.  The solution that works for me is 
to run an *external* python script that operates on my Kexi database and runs 
all the queries at one click, updating the whole table.  Here's an example of 
what works:

===================
Python Script (filenename.py)
===================
#!/usr/bin/env python
import sys, sqlite3

# Create a connection to the database file "test.kexi":
conn = sqlite3.connect('test.kexi')
conn.isolation_level = None
# Get a Cursor object that operates in the context of Connection conn:
c = conn.cursor()

# Run SQL commands (which conveniently can be "wrapped" within Python)
c.execute("update tablename set column_a = 'some text here' where column_a is 
Null")
c.execute("update tablename set column_p = column_e*column_g")
.
.
c.close()

For this, one needs to have sqlite3.  Note that in doing this, several columns 
of my table are "results columns".  I therefore combine data and results in 
one table.  I'd probably be shot by a died-in-the-wool SQL expert, but hell, 
it works perfectly for me for the mo and for the relatively small databases I 
have.  Actually I would prefer to put the results in a separate results table, 
but so far haven't worked out an SQL command that also works in the above 
Python script to do this!  For example, c.execute("update table2 set 
table2.column_a = 3*table1.column_c") won't work.

I have two more python scripts, one which populates a Kexi table with dummy 
variables where there are nulls.  This table can then be exported from Kexi 
as a .csv file.  I can then edit, add columns etc., and then read the .csv 
file back into Kexi.  Semicolons are the best separator, and I edit out all 
the commas in kwrite or kate prior to reading back into Kexi.  Having read 
the .csv file back into Kexi, the second external python script removes all 
the dummy variables from what is now the Kexi table.  It all works fine, 
though it would be much handier to run such scripts *within* Kexi. The 
example in Simple_Database.kexi shows commands like:

import krosskexidb, krosskexiapp, os, codecs, sqlite3, sys
keximainwindow = krosskexiapp.get("KexiAppMainWindow")
connection = keximainwindow.getConnection()
table = connection.tableSchema("orders")
query = table.query()
cursor = connection.executeQuerySchema(query)

but I do not have (and don't yet know where to acquire) a clear understanding 
of Kross, what some of these commands do, nor how to be able to run those 
c.execute("...") commands.  A welcome alternative would be a simple Kexi 
script to run my external python scripts.  One would then simply 
compress the database to immediately see all the updates.

I find here is a great advantage to writing out/reading in tables as .csv 
files. Not only can they easily be read into spreadsheets any time, but 
tables can be easily modified and read back into Kexi with all data 
preserved.  Changing a table column heading within any SQL client results in 
*all* table data being lost.

Even the absence of reports (which I know Adam Pigg is currently working on - 
good luck there!) can be circumvented for now.  Kexi is superb for creating 
forms, which can be used to present data (e.g. as a delivery note or invoice).  
By doing a screen copy of the form area (using PrtScn key in KDE) one can 
select just the form area and save it, for example, as a .jpg file.  One 
problem is that if one then uses another PC with a smaller screen there's no 
way to somehow zoom out to see the whole form (as one can in "edit mode")!  
So that's another facility that could be very useful, and help to make Kexi 
more applicable right now.

Cheers,
Rod