Re: [sqlite] free excel-like COLORFUL gui for sqlite

[Mag. Wilhelm Braun]

there are a number of front ends to sqlite - I use sometime tksqlite. 
http://reddog.s35.xrea.com/wiki/TkSQLite.html


maybe not exactly what you want - but it might be a starting point.

regards W.Braun


[EMAIL PROTECTED] wrote:

Still not sure why you want to use SQLite here, but I think to get what
you want you will have to code
it yourself.

RBS

  

Let me be more clear.  There's very little data, and I want to help my
client
be able to expand his business, so the first step is automating what he's
been doing by hand.  I can get the excel data into sqlite no problem --
and
wish to do so in order to START doing automated stuff with the data -- but
would like to PRESENT (and only present) the data in the familiar
spreadsheet manner.

This is a general problem: many people use excel as a database only
because
of how nice it looks once they add coloring, and because it's easier to,
say, have the three address fields as three columns, even though logically
it should be a separate table Addresses.  So, I'd like to have the same
familiar input view without having the client worry about the details
(which
column is really what table, etc) and also to be able to color it as he
has
done to date.  So, is there is a free gui frontend to sqlite that will do
it, or do I have to code one myself?

Thanks!



bartsmissaert wrote:


If it is so good then why would you want to use
SQLite? Holiday data can't be that much, so I would
think Excel can cope with that fine.
If you really want to move the data from Excel to
SQLite then you will need a VB wrapper.

RBS

  

I have a client who's using a colorful excel sheet as a database.  It's
colorful, well-structured, and a joy to use.  He has no code working on
the
data though -- it's just used for holding data, like a ledger book!

My question is how I can put his information into a sqlite database but
give
him a very similar interface -- the same, well-structured, colorful,
spreadsheet view?  I don't want him to even have to worry about which
column
is actually in which table-- just have it look like an excel sheet.

This is very basic and easy, and I'd hate to have to reinvent the wheel
coding it -- is there a free sqlite gui that can present such a
colorful
spreadsheet view?

Thank you!
--
View this message in context:
http://www.nabble.com/free-excel-like-COLORFUL-gui-for-sqlite-tp14686423p14686423.html
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-







-
To unsubscribe, send email to [EMAIL PROTECTED]
-



  

--
View this message in context:
http://www.nabble.com/free-excel-like-COLORFUL-gui-for-sqlite-tp14686423p14686909.html
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-









-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  

Re: [sqlite] Best way of merging tables

[Mag. Wilhelm Braun]

Thanks a lot Kees Nuyt,

greate help

W.Braun

Kees Nuyt wrote:

On Mon, 31 Dec 2007 09:56:23 +0100, "Mag. Wilhelm Braun"
<[EMAIL PROTECTED]> wrote:

  

hi,

I have following situation:

database2006: table 'myname': Columns: "ID integer primary key, 
timestamp integer, x text, y text
database2007: table 'myname': Columns: "ID integer primary key, 
timestamp integer, x text, y text


empty
comvineddatabase: : table 'myname': Columns: "ID integer primary key, 
timestamp integer, x text, y text


I would like do have a combined database table 'myname': Columns: "ID 
integer primary key, timestamp integer, x text, y text
where I insert first the columns of database2006, and afterwards 
database2007.


at the moment I do something like this:

*attach: database2006:*

select timestamp, x , y from database2006

and for eachrow in selectionresult

insert into comvineddatabase (timestamp, x , y) values(?,?,?)

*after that the same for database2006.*

select timestamp, x , y from database2006

and for eachrow in selectionresult

insert into comvineddatabase (timestamp, x , y) values(?,?,?)


I was wondering if there is not a more effective way of doing that: 
maybe even within a single SQL statement?



Sure there is, using the "INSERT INTO / SELECT" syntax on
http://www.sqlite.org/lang_insert.html

There are several possibilities, here is an (unteste3d) example:

(open comvineddatabase)
(create tables as needed)
ATTACH DATABASE 'database2006' AS d2006;
INSERT INTO myname (timestamp, x, y) 
	SELECT timestamp, x, y FROM d2006.myname;

DETACH DATABASE d2006;
ATTACH DATABASE 'database2007' AS d2007;
INSERT INTO myname (timestamp, x, y) 
	SELECT timestamp, x, y FROM d2007.myname;

DETACH DATABASE d2007;

If the table structures are exactly the same, the INSERT
statement can even be shortened:
INSERT INTO myname SELECT * FROM d2006.myname;
etc.


  

Thanks in advance W.Braun



HTH
  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Best way of merging tables

[Mag. Wilhelm Braun]

hi,

I have following situation:

database2006: table 'myname': Columns: "ID integer primary key, 
timestamp integer, x text, y text
database2007: table 'myname': Columns: "ID integer primary key, 
timestamp integer, x text, y text


empty
comvineddatabase: : table 'myname': Columns: "ID integer primary key, 
timestamp integer, x text, y text


I would like do have a combined database table 'myname': Columns: "ID 
integer primary key, timestamp integer, x text, y text
where I insert first the columns of database2006, and afterwards 
database2007.


at the moment I do something like this:

*attach: database2006:*

select timestamp, x , y from database2006

and for eachrow in selectionresult

insert into comvineddatabase (timestamp, x , y) values(?,?,?)

*after that the same for database2006.*

select timestamp, x , y from database2006

and for eachrow in selectionresult

insert into comvineddatabase (timestamp, x , y) values(?,?,?)




I was wondering if there is not a more effective way of doing that: 
maybe even within a single SQL statement?



Thanks in advance W.Braun

Re: [sqlite] Re: Fastest way to check if new row or update existing one?

[Mag. Wilhelm Braun]

Thanks Pagaltzis. Great help.

W.Braun

A. Pagaltzis wrote:

* Mag. Wilhelm Braun <[EMAIL PROTECTED]> [2007-12-25 19:30]:
  

If row 50 does not exists it does nothing and I seem not to get
any return to know?



http://sqlite.org/c3ref/changes.html

  

using pysqlite.



I don’t know anything about pysqlite, but apparently you are
looking for the `rowcount` attribute on the Cursor class.

Regards,
  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Fastest way to check if new row or update existing one?

[Mag. Wilhelm Braun]

Thanks as in my case just number 2 is possible a quite 'silly' question:
How do you normally check if Update was successful if the specified row 
did not exists.


e.g: UPDATE MyTable SET Account='MyAccountName' WHERE ID=50

If row 50 does not exists it does nothing and I seem not to get any return to 
know?

using pysqlite.

Thanks W.Braun



A. Pagaltzis wrote:

* Mag. Wilhelm Braun <[EMAIL PROTECTED]> [2007-12-25 15:10]:
  

QUESTION: is there a better way to make this important
decision? using Sqlite



1. If you are changing the entire row on every update, you can
   simply use `INSERT OR REPLACE` (assuming there is a UNIQUE
   column) to always do this in a single query.

   See <http://sqlite.org/lang_conflict.html>.

2. If you only want to update some of the columns, particularly
   if you are likely to update rows several times, you can use
   `UPDATE` to try and update, and if this did not affect any
   rows you do an `INSERT`.

In #1, you always get the job done with a single query. In #2,
you are usually done after the first but sometimes need a second.
Both are more efficient than your current approach, which always
runs two queries.

Regards,
  

[sqlite] Fastest way to check if new row or update existing one?

[Mag. Wilhelm Braun]

hi,

just a short question to speed up:

as with any database one has quite often to decide if we *INSERT a NEW 
row -- or -- UPDATE an existing row*



at the moment I do a check select on an unique ID intege which is 
resonable fast:


   code:
   

SELECT ID FROM MyTable WHERE Account='MyAccountName' 


   

I just fetch one row.

if that get's a return I update otherwise I insert a new row.

QUESTION: is there a better way to make this important decision? using 
Sqlite


regards W.Braun

Re: [sqlite] Re: Re: Any Ideas to speed up CAST

[Mag. Wilhelm Braun]

Thanks excellent thought - so simple and I did not think about it. What 
a shame.


THANKS once again and


'MERRY X-MASS'

W.Braun






Igor Tandetnik wrote:

Mag. Wilhelm Braun
<[EMAIL PROTECTED]> wrote:

I would be interesting about your point of keeping the precision in
floating point values without storing it as strings.


Usually, when you want to do this, it's because you are working with 
monetary values. In this case, it is better to store them as integers, 
scaled, say, by a factor of 1000 or whatever accuracy you need.


Igor Tandetnik

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 







-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Any Ideas to speed up CAST

[Mag. Wilhelm Braun]

Thanks Igor

that makes sense about integers.

I would be interesting about your point of keeping the precision in 
floating point values without storing it as strings.


would be a great help as I have a few cases like that.

Thanks so much for your kind help

W.Braun


Igor Tandetnik wrote:

Mag. Wilhelm Braun
<[EMAIL PROTECTED]> wrote:

because of accuracy I use everywhere text entries even for numerical
entries.


You seem to be storing integer values as text. What kind of accuracy 
improvement do you expect from this? Integers are stored losslessly 
already.


I could remotely understand storing floating point values as strings 
(though there are better ways to preserve precision) - but integers?


Igor Tandetnik

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 







-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Any Ideas to speed up CAST

[Mag. Wilhelm Braun]

hello,

because of accuracy I use everywhere text entries even for numerical 
entries.


So if I have to select max or min items I use cast: e.g

I have exactly: 2310556   rows in the table

1. all CAST
select * from "SOME_table" where cast(UTCTimestamp AS INTEGER) = (select 
max(cast(UTCTimestamp AS INTEGER)) from "SOME_table")


takes about 8436  msec


2. Less CAST
select * from "SOME_table" where UTCTimestamp= (select 
max(cast(UTCTimestamp AS INTEGER)) from "SOME_table")


takes about 2823 msec

3. NO CAST
select * from "SOME_table" where UTCTimestamp= (select max(UTCTimestamp) 
from "SOME_table")


takes about 1 msec


in this particular case it gives me back all the same results but in 
other cases I need the Cast version.


NOW is there a way to speed this up except inserting the data as numeric.


Thanks W.Braun



for my first discussion on way I need text entry see topic: Is there a 
way to do comparison of text in a numerical way.




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] select date using variables in Python

[Mag. Wilhelm Braun]

hi C M,

I'm by no means an expert but what I do in similar cases is: I prepare 
the variables beforehand - actually I prepare the whole SQL statement 
beforehand and do normally not use ?.  except by executemany.


date=date("now","+1 day")
sqlcu.execute ("SELECT string FROM test WHERE d >=?",(date,) )



or something like that should also work:

sqlcu.execute ("SELECT string FROM test WHERE d >=?", (date("now", "+1 
day"),) )


I actually use never the date but convert everything to utc timestamps 
in an extra column.


regards W.Braun

C M wrote:

I'm new to SQLite and can't figure out the right way to write this. I want
to select a range of dates, let's say anything beyond tomorrow So in my
table called test I want to select the column called string based on the
date being tomorrow or later...

This statement (from the sql wiki about dates) in my Python code works:

cur.execute('SELECT string FROM test WHERE d >= date("now","+1 day")')

However, I'd like to make it flexible, so that a user can put in an amount
of days forward or backward and the query will use that--basically I want
the user to be able to select the date range over the data in the table.  I
tried something like:

amount = "1"  #just to try it, later this will refer to a user-chosen
variable
cur.execute ('SELECT string FROM test WHERE d >= date("now", "+",?,"
day")',amount)

But of course that's not right and it doesn't work.  What is the right
syntax in this case to use the ? to stand for the 1 in the original "+1 day"
portion?

Or am I barking up the wrong tree with this approach?  Ultimately I want to
make it totally generalizable, so that users can select whatever range of
dates they want, and so I thought I needed a way to sub in the variable of
#of days--just not sure how.

Any help is appreciated.

  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Is there a way to do comparison of text in a numerical way.

[Mag. Wilhelm Braun]

Thanks for the hint.
W.Braun

Dennis Cote wrote:

Mag. Wilhelm Braun wrote:


I thought that this might properly a bigger thing. Well, I found a 
solution which fits my purpose at the moment. ( SELECT txt FROM test 
WHERE txt=(SELECT max(CAST(txt AS REAL)) from test) )


I do not use selection of max() or min() very often - it seems it is 
the best suiting solution (effort - result) at the moment.



I think you should probably use a query like the following:

select txt from test where cast(txt as real) = (select max(cast(txt as 
real)) from test)


Which applies the same cast to each row for the comparison that it 
applied to each row for the max value determination. This cast may be 
done implicitly by SQLite, but it is probably safer to make it explicit.


HTH
Dennis Cote

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 







-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Is there a way to do comparison of text in a numerical way.

[Mag. Wilhelm Braun]

Thanks,

I thought that this might properly a bigger thing. Well, I found a 
solution which fits my purpose at the moment. ( SELECT txt FROM test 
WHERE txt=(SELECT max(CAST(txt AS REAL)) from test) )


I do not use selection of max() or min() very often - it seems it is the 
best suiting solution (effort - result) at the moment.


regards W.Braun


John Stanton wrote:
We built a fixed point arithmetic library using text strings.  The 
format stored is right justified, leading space filled decimal numbers 
with embedded decimal points and leading sign.  The purpose of that is 
not for arithmetic efficiency but so that they can be directly output 
into a printed page or HTML document.  The algorithms we use are 
essentially from Knuth's Semi Numerical Algorithms volume.


Functions exist for the common arithmetic operations plus moves and 
comparisons.  Rounding is implemented using the algorithm which 
minimizes skew.  These functions are also added into Sqlite as custom 
functions so that the decimal numbers can be used from SQL.


We define the decimal numbers using standard SQL with precision and 
scale assigned in the type declaration.  Sqlite's ability to store 
declared types makes the integration possible.


This is not a simple fix, but it does let us produce accurate 
financial reports.


Mag. Wilhelm Braun wrote:

Thanks Stanton,

could you elaborate a bit on that - I'm not sure if I get exactly 
what you mean.


if you have a smallish example would be great. the help from 'Igor 
Tandetnik' with cast is a good starting point - but on selections 
with max or min I still get the incorrect rounded numbers back.


EXAMPLE:column txt
"0.2009"
"10.200899"
"4.0"
"300.2009"

and I do a selection: SELECT max(CAST(txt AS REAL)) FROM test

it returns returns 300.2008


so the only solution till now seems to make a sub-query like: SELECT 
txt FROM test WHERE txt=(SELECT max(CAST(txt AS REAL)) from test)


not sure how messy that might get in complex queries.

anyway for any suggestion I'm more than grateful

regards W.Braun



John Stanton wrote:
Our approach to that problem was to write a library of ASCII decimal 
arithmetic functions, store the data as underlying type TEXT but 
give them a declared type of DECIMAL(n,m) and have added functions 
which understand that declared type.  With that addition Sqlite 
becomes useful for accounting and other such activities requiring 
arithmetic accuracy.


For a simple display interface we use display format, fixed point 
decimal numbers, right justified.


Mag. Wilhelm Braun wrote:

Dear all,


I use sqlite to store numerical text strings.

Why do I use text type: because of the float problem of 
incorrection.example in numeric Columns: 3.2009returns as 
3.2008 which is not what I want.



Column Type=TEXT


is there a way to do comparison of text in a numerical way.
EXAMPLE rows:Column txt

"0.200899"
"1.2009"
"113.2008999"
"4.0"
"3.1"
"3.2009"

SELECT max(txt) FROM test

should return "113.2008999" and not "4.0"


ALSO:
SELECT * FROM test WHERE txt>10.0

should just return "113.2008999" and not

"113.2008999"
"4.0"
"3.1"


so my question is there a way to do that correctly?

Thanks for any helpful hints

regards W.Braun


by the way: I use pysqlite.


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 







- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 







-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Huge performance drop when using prepared statement

[Mag. Wilhelm Braun]

hi Markus,

I just tried for fun:

start = time.time()
SQLString=('''SELECT * FROM entry, word, word_entry WHERE
entry.id = word_entry.entry_id AND
word.id = word_entry.word_id AND
word.word GLOB '%s'
''' % "hui*")

c.execute(SQLString)

and it is as fast as your first one - seems a pysqlite problem to me

I know they say this is not a secure way to do it -- well.


Kind regards,

W.Braun



Markus Gritsch wrote:

Hi,

when using bind variables I get a huge performace drop compared to
using a plain string.  The query is demonstrated in the attached file
"problematic_query.py".

The database used can be downloaded from
  http://xile.org/le/prepared_statement.zip (1.75 MB)

or generated by using the attached file "create_test_db.py".

Kind regards,
Markus
  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Is there a way to do comparison of text in a numerical way.

[Mag. Wilhelm Braun]

Thanks Stanton,

could you elaborate a bit on that - I'm not sure if I get exactly what 
you mean.


if you have a smallish example would be great. the help from 'Igor 
Tandetnik' with cast is a good starting point - but on selections with 
max or min I still get the incorrect rounded numbers back.


EXAMPLE:column txt
"0.2009"
"10.200899"
"4.0"
"300.2009"

and I do a selection: SELECT max(CAST(txt AS REAL)) FROM test

it returns returns 300.2008


so the only solution till now seems to make a sub-query like: SELECT txt 
FROM test WHERE txt=(SELECT max(CAST(txt AS REAL)) from test)


not sure how messy that might get in complex queries.

anyway for any suggestion I'm more than grateful

regards W.Braun



John Stanton wrote:
Our approach to that problem was to write a library of ASCII decimal 
arithmetic functions, store the data as underlying type TEXT but give 
them a declared type of DECIMAL(n,m) and have added functions which 
understand that declared type.  With that addition Sqlite becomes 
useful for accounting and other such activities requiring arithmetic 
accuracy.


For a simple display interface we use display format, fixed point 
decimal numbers, right justified.


Mag. Wilhelm Braun wrote:

Dear all,


I use sqlite to store numerical text strings.

Why do I use text type: because of the float problem of 
incorrection.example in numeric Columns: 3.2009returns as 
3.2008 which is not what I want.



Column Type=TEXT


is there a way to do comparison of text in a numerical way.
EXAMPLE rows:Column txt

"0.200899"
"1.2009"
"113.2008999"
"4.0"
"3.1"
"3.2009"

SELECT max(txt) FROM test

should return "113.2008999" and not "4.0"


ALSO:
SELECT * FROM test WHERE txt>10.0

should just return "113.2008999" and not

"113.2008999"
"4.0"
"3.1"


so my question is there a way to do that correctly?

Thanks for any helpful hints

regards W.Braun


by the way: I use pysqlite.


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 







-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Is there a way to do comparison of text in a numerical way.

[Mag. Wilhelm Braun]

Thanks Igor Tandetnik great help.

W.Braun


Igor Tandetnik wrote:

Mag. Wilhelm Braun
<[EMAIL PROTECTED]> wrote:

is there a way to do comparison of text in a numerical way.
EXAMPLE rows:Column txt

"0.200899"
"1.2009"
"113.2008999"
"4.0"
"3.1"


SELECT max(txt) FROM test

should return "113.2008999" and not "4.0"


select max(cast(txt as real)) from test;

Igor Tandetnik

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 







-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Is there a way to do comparison of text in a numerical way.

[Mag. Wilhelm Braun]

Dear all,


I use sqlite to store numerical text strings.

Why do I use text type: because of the float problem of incorrection.
example in numeric Columns: 3.2009returns as 3.2008 
which is not what I want.



Column Type=TEXT


is there a way to do comparison of text in a numerical way.
EXAMPLE rows:Column txt

"0.200899"
"1.2009"
"113.2008999"
"4.0"
"3.1"


SELECT max(txt) FROM test

should return "113.2008999" and not "4.0"


ALSO:
SELECT * FROM test WHERE txt>10.0

should just return "113.2008999" and not

"113.2008999"
"4.0"
"3.1"


so my question is there a way to do that correctly?

Thanks for any helpful hints

regards W.Braun


by the way: I use pysqlite.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-