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
_______________________________________________
Kexi mailing list
Kexi kde.org
https://ma
il.kde.org/mailman/listinfo/kexi
|
See here also