[sqlite] Problem with accumulating decimal values

[Frank Millman]

Hi all

I am having a problem accumulating decimal values.

I am actually using Python, but I can reproduce it in the sqlite3 interactive 
terminal.

SQLite version 3.8.6 2014-08-15 11:46:33
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> CREATE TABLE fmtemp (acno INT, balance DECIMAL);
sqlite> INSERT INTO fmtemp VALUES (1, 0);

sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> SELECT bal FROM fmtemp;
123.45

sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> SELECT balance FROM fmtemp;
246.9

I repeat this a number of times, and it runs fine, until this happens -

sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> SELECT bal FROM fmtemp;
5802.15

sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> SELECT bal FROM fmtemp;
5925.599

sqlite> UPDATE fmtemp SET balance = balance + 123.45;
sqlite> SELECT bal FROM fmtemp;
6049.049

Can anyone explain what is going on, and is there a way to avoid it?

Thanks

Frank Millman

[sqlite] Problem with accumulating decimal values

[Frank Millman]

From: Frank Millman 
Sent: Friday, December 11, 2015 4:21 PM
To: sqlite-users at mailinglists.sqlite.org 
Subject: [sqlite] Problem with accumulating decimal values

> Hi all
> 
> I am having a problem accumulating decimal values.
> 
> I am actually using Python, but I can reproduce it in the sqlite3 interactive 
> terminal.
> 


Many thanks for all the replies. I understand what is happening now.

I have found an effective workaround. The Python sqlite3 module allows you to 
create a user-defined function that you can use from within SQL statements. I 
wrote a function that uses the Python Decimal module to perform the arithmetic 
and return the result, and it seems to work just fine.

Thanks again.

Frank

[sqlite] Problem with recursive CTE

[Frank Millman]

Hi all

According to the documentation for the WITH clause, "the recursive table 
must appear exactly once in the FROM clause of the recursive-select and must 
not appear anywhere else in either the initial-select or the 
recursive-select, including subqueries".


I am trying to do the following -

WITH RECURSIVE temp AS (
 [initial-select UNION ALL recursive-select]
 )
SELECT * FROM temp UNION * FROM temp

Obviously this is a simplification, but you get the idea.

I get the error 'no such table: temp'.

This query works with SQL Server and with PostgreSQL.

Is this a limitation of sqlite3, or is there any workaround?

Thanks

Frank Millman

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with recursive CTE

[Frank Millman]

From: "Frank Millman" <fr...@chagford.com>


WITH RECURSIVE temp AS (
 [initial-select UNION ALL recursive-select]
 )
SELECT * FROM temp UNION * FROM temp



Sorry, I meant

SELECT * FROM temp UNION SELECT * FROM temp

Frank

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with recursive CTE

[Frank Millman]

From: "Frank Millman" <fr...@chagford.com>Thanks for the reply, Richard.

WITH RECURSIVE temp(x) AS (
  SELECT 1 UNION ALL SELECT x+1 FROM temp WHERE x<5
)
SELECT x FROM temp UNION SELECT x+5 FROM temp;
   And did indeed get integers 1 through 10 as an answer.

I get the same error as before - 'no such table: temp'.

Could it be a version problem?  I am using the version bundled with 
Python3.4.1 for Windows.


sqlite3.sqlite_version shows '3.8.3.1'

I have just tried it on a Fedora 18 machine, which has version 3.7.13. There 
it gives 'near "WITH": syntax error'


It seems that the WITH statement is a fairly recent addition. I will upgrade 
and try again.


Frank

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with recursive CTE

[Frank Millman]

- Original Message - 
From: "Keith Medcalf" 

To: "General Discussion of SQLite Database" 
Sent: Monday, August 25, 2014 2:28 PM
Subject: Re: [sqlite] Problem with recursive CTE




Looking backwards on the list one finds that:

There was a bug regarding compound SELECT statements
that use CTEs discovered shortly after 3.8.3 was released:

  http://www.sqlite.org/src/info/67bfd59d9087a987
  http://www.sqlite.org/src/info/31a19d11b97088296a

The fix appeared in 3.8.4. If you upgrade, the statement will work.

Since you do not mention what version you are using, one assumes it is 
prior to this fix?




Thanks, Keith. I think you are spot on. See my reply to Richard.

I will upgrade and try again.

Frank

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with recursive CTE

[Frank Millman]

- Original Message - 
From: "Frank Millman" <fr...@chagford.com>

To: <sqlite-users@sqlite.org>
Sent: Monday, August 25, 2014 2:32 PM
Subject: Re: [sqlite] Problem with recursive CTE




Could it be a version problem?  I am using the version bundled with 
Python3.4.1 for Windows.


sqlite3.sqlite_version shows '3.8.3.1'

I have just tried it on a Fedora 18 machine, which has version 3.7.13. 
There it gives 'near "WITH": syntax error'


It seems that the WITH statement is a fairly recent addition. I will 
upgrade and try again.




I have upgraded to version 3.8.6, and I can confirm that it now works. 
Thanks very much, Richard and Keith


Now I have to figure out how to get Python to use the upgraded version, but 
that is one for the python mailing list.


Frank

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with recursive CTE

[Frank Millman]

- Original Message - 
From: "Alek Paunov" <a...@declera.com>

To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Monday, August 25, 2014 4:04 PM
Subject: Re: [sqlite] Problem with recursive CTE



On 25.08.2014 15:42, Frank Millman wrote:


I have upgraded to version 3.8.6, and I can confirm that it now works.
Thanks very much, Richard and Keith

Now I have to figure out how to get Python to use the upgraded version,
but that is one for the python mailing list.



You may consider upgrade to the current Fedora release - F20, which 
comes with sqlite-3.8.6 [1]. F18 is already out of support anyway.




Will do. Thanks for the advice, Alek.

Frank

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Foreign key on different database - possible?

[Frank Millman]

Hi all

I am using SQLite 3.7.2 on Fedora 10.

I have multiple databases, which I can access concurrently by using the
'attach' command, and then referring to each table using
'database.tablename'. It works well.

Now I want to create a foreign key constraint where the parent table is in
one database and the child table is in another database. However, when I use
the syntax 'REFERENCES database.tablename' I get a syntax error.

Is it possible to do what I want?

Thanks

Frank Millman

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key on different database - possible?

[Frank Millman]

Dan Kennedy wrote:
> 
> On Oct 20, 2010, at 3:59 PM, Frank Millman wrote:
> 
> > Hi all
> >
> > I am using SQLite 3.7.2 on Fedora 10.
> >
> > I have multiple databases, which I can access concurrently 
> by using  
> > the
> > 'attach' command, and then referring to each table using
> > 'database.tablename'. It works well.
> >
> > Now I want to create a foreign key constraint where the 
> parent table  
> > is in
> > one database and the child table is in another database. However,  
> > when I use
> > the syntax 'REFERENCES database.tablename' I get a syntax error.
> >
> > Is it possible to do what I want?
> 
> No. It is not.
> 

Ok, thanks.

Is there any chance of it being considered for a future release?

Frank

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key on different database - possible?

[Frank Millman]

Drake Wilson wrote:
> 
> Quoth Frank Millman <fr...@chagford.com>, on 2010-10-20 
> 11:47:06 +0200:
> > Ok, thanks.
> > 
> > Is there any chance of it being considered for a future release?
> 
> Search http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq for "foreign
> key".
> 

Thanks, Drake. I found the following quote, which I assume is what you were
referring to -

"The same goes for foreign key constraints. There are severe implementation
difficulties trying to get this to work across separate database. If you
have a foreign key in a separate database, that really argues that the two
databases ought to be one."

I can understand that.

For the record, I will explain what I am doing, in case someone can suggest
an alternative approach. If not, I can live with it.

I am writing a generalised business/accounting application, which is
multi-company. I have got it working using MS SQL Server and PostgreSQL as
databases, and now I want to add sqlite3 as a third option, for small
single-user systems and for demo purposes. In the first two DBMS's, I use
the concept of a 'schema' to represent the different companies. They are all
in the same database, but I can refer to individual tables using
'schema.tablename' notation.

sqlite3 does not seem to have the equivalent of a schema in the same sense
as the other two, but I have got close by using the concept of separate
databases. So far this is the first time I have bumped my head. If this is
the only restriction, I can live with it.

The link above refers to a similar restriction with 'triggers', but at this
stage I am only making limited use of triggers, and not cross-company, so
hopefully that will not become a problem.

If I find more problems, I may have to reconsider my options. I hope not, as
I am really enjoying working with sqlite3.

Frank

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with CASE in WHERE clause

[Frank Millman]

From: Simon Slavin 
Sent: Sunday, December 04, 2016 8:42 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Problem with CASE in WHERE clause


On 4 Dec 2016, at 8:42am, Simon Slavin slav...@bigfraud.org wrote:

> What are we allowed to change ?  Are you asking us to find a "SELECT" command 
> which works both in SQLite and PostgreSQL ?  Can we change the table 
> definition too ?


Er, sorry, I was not asking you to change anything.

If a column contains a ‘1’, I would expect sqlite3 to return true when testing 
for ‘1’, but in my example it returns false.

If this is a bug, then I am happy to wait for a fix.

If it is just the way sqlite3 works, a bit of an explanation would help, as 
that could assist me in finding a workaround.

Frank
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem with CASE in WHERE clause

[Frank Millman]

Hi all

I have a problem using a CASE statement in a WHERE clause.

On testing a column which contains a ‘1’, WHERE returns true if I test for 1 
(without the quotes), but false if I test for ‘1’ (with quotes).

CREATE TABLE test_1 (tran_type TEXT, amount INTEGER, posted BOOL)

INSERT INTO test_1 (tran_type, amount, posted) values ('inv', 100.00, '1')

SELECT * FROM test_1 WHERE CASE WHEN tran_type = 'inv' THEN posted END = 1
[(‘inv’, 100, 1)]

SELECT * FROM test_1 WHERE CASE WHEN tran_type = 'inv' THEN posted END = ‘1’
[]

I really need this to work, as my app is cross-platform and cross-database, and 
PostgreSQL does not accept an integer for a BOOL column.

I am using version 3.14.2 on Windows 10.

Any suggestions?

Thanks

Frank Millman
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with CASE in WHERE clause

[Frank Millman]

From: Simon Slavin 
Sent: Sunday, December 04, 2016 10:26 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Problem with CASE in WHERE clause


> On 4 Dec 2016, at 6:55am, Frank Millman <fr...@chagford.com> wrote:
> 
> > If a column contains a ‘1’, I would expect sqlite3 to return true when 
> > testing for ‘1’, but in my example it returns false.
> 
> I think I’ve found the problem ...
> 

Thank you very much for your explanation, Simon.

My live situation is a bit more complex than my example, so I will have to 
experiment to find the ideal solution.

But you have given me the information I need to move forward – much appreciated.

Frank
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with CASE in WHERE clause

[Frank Millman]

From: Jean-Christophe Deschamps 
Sent: Monday, December 05, 2016 9:48 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Problem with CASE in WHERE clause

> 
> At 06:29 05/12/2016, you wrote:
> >My app supports sqlite3, SQL Server, and PostgreSQL.
> >
> >SQL Server has a ‘bit’ data type, which accepts 1/0 and 
> >‘1’/’0’ as valid values.
> >
> >PostgreSQL has a ‘bool’ data type, which supports a variety of 
> >values ­ TRUE, ‘t’, ‘true’, ‘yy’, ‘yes’, ‘on’, 
> >‘1’ for true, and the opposites for false, but does not allow 1/0.
> 
> All [three] engines should support (1=1) and (1=0) for true and false, 
> respectively, as well as bare columnname as a boolean assertion, like 
> Simon said: select ... where columnC and not columnF ...
> 
> The choice of literals representing true and false is merely cosmetic.
> 

So if I understand correctly, it makes sense to use ‘1’/’0’ to *set* the 
boolean value in a cross-database manner, but there are a variety of ways to 
test for it.
Frank


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with CASE in WHERE clause

[Frank Millman]

From: Don V Nielsen 
Sent: Sunday, December 04, 2016 5:15 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Problem with CASE in WHERE clause

> Sorry, but the whole scenario is messy, at best. The column is declared
bool, and then a string '1' is assigned to it. The case lacks an else, so
it resulting in one of two types: a string when true and an integer when
false. Correct? And then on top of that, as Simon pointed out, the column
affinity is bool, so a string is being interpreted as a bool (technically
integer) and so the first one is resulting in true when it appears that the
second one should do so. Please agree that there is way more happening that
what should be.


Sorry about that. Maybe I over-simplified my example. In practice the case 
statement will never follow the ‘else’ clause, but it will select from a number 
of similar ‘then’ clauses.

Simon has given me enough info to come up with a solution. However, I am still 
a bit surprised at the result.

1) SELECT * FROM TEST WHERE posted = 1;
[(‘inv’, 100, 1)]

2) SELECT * FROM TEST WHERE posted = ‘1’;
[(‘inv’, 100, 1)]

3) SELECT * FROM TEST WHERE CASE WHEN tran_type = ‘inv’ THEN posted END = 1;
[(‘inv’, 100, 1)]

4) SELECT * FROM TEST WHERE CASE WHEN tran_type = ‘inv’ THEN posted END = ‘1’;
[]

In 3) and 4), the WHEN clause evaluates to true, so I expected it to treat 1 
and ‘1’ identically, the same way as it does in 1) and 2).

Not important, just curious.

Frank

P.S. Here is the reason for assigning ‘1’ instead of 1.

My app supports sqlite3, SQL Server, and PostgreSQL.

SQL Server has a ‘bit’ data type, which accepts 1/0 and ‘1’/’0’ as valid values.

PostgreSQL has a ‘bool’ data type, which supports a variety of values – TRUE, 
‘t’, ‘true’, ‘y’, ‘yes’, ‘on’, ‘1’ for true, and the opposites for false, but 
does not allow 1/0.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Running query in command window is slow

[Frank Millman]

Richard Hipp wrote:
On 9/22/17, Frank Millman <fr...@chagford.com> wrote:
> > I see that Python is using Sqlite3 3.14.2.
>
> What version of the command-line shell are you using?  It would be a problem 
> if the command-line shell is a later version and yet is running slower.
I was running 3.8.6, so it was actually quite old.

I have now upgraded both of them to 3.20.1

Sorry for the confusion.

Frank
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Running query in command window is slow

[Frank Millman]

Clemens Ladisch wrote:
> 
> > Frank Millman wrote:
> > I have a fairly complex query. If I execute it using Python, it takes
> > 1 second. If I copy and paste it into the Sqlite3 command window, it
> > takes 10 minutes.
> 
> Probably different SQLite versions.
> 
> What is the EXPLAIN QUERY PLAN output in both cases?
> 


Thanks, Clemens. You just beat me to it.

I was about to reply that I had forgotten to check that, but now that I have, I 
see that Python is using Sqlite3 3.14.2.

I won’t show the query plan unless you really want to see it. The query 
involves a JOIN to a VIEW, which then has to retrieve data from the underlying 
physical tables. Obviously some work has been done to optimise this in more 
recent versions.

Frank
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Running query in command window is slow

[Frank Millman]

Hi all

I am running Python 3.6.0 and Sqlite3 3.8.6 on Windows 10.

I have a fairly complex query. If I execute it using Python, it takes 1 second. 
If I copy and paste it into the Sqlite3 command window, it takes 10 minutes.

I am not too bothered – it is the Python one that is important. Still, it seems 
odd. Can anyone suggest a reason for this?

Thanks

Frank Millman
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Selecting data from WITH clause is very slow

[Frank Millman]

On Mon Sep 11, 2017 at 11:15:22AM +0200, no...@null.net wrote:
> SELECT
> acc_no,
> acc_name,
> SUM(i_90.invoice_bal) AS 90_days,
> SUM(i_current.invoice_bal) AS current
> FROM
>debtors_table
> LEFT JOIN
> invoices i_90
> ON
> i_90.debtor_id = debtors_table.id AND
> i_90.invoice_date > date_5 AND
> i_90.invoice_date <= date_4
> LEFT JOIN
> invoices i_current
> ON
> i_current.debtor_id = debtors_table.id AND
> i_current.invoice_date > date_2 AND
> i_current.invoice_date <= date_1
> 
> I should also mention for completeness the need for:
> 
> GROUP BY
> acc_no,
> acc_name
> 
> ORDER BY
> ...


Very interesting ideas. I have a lot of experimenting to do!

Thanks very much, Mark.

Frank
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Selecting data from WITH clause is very slow

[Frank Millman]

Clemens Ladisch wrote:

> Frank Millman wrote:

> > I changed it to use a WITH clause to make one scan of the invoice table and 
> > store the results.

> The WITH clause itself does not store anything; it's syntactic sugar [...] To 
> force the ageing results to be stored, you'd have to create a temporary table.

Thanks – I did not know that. It explains everything!
Frank

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Selecting data from WITH clause is very slow

[Frank Millman]

Hi all

I am writing an accounting system, and want to select ‘aged balances’ from the 
debtors table.

To do this, I need to assign each outstanding invoice to an ageing ‘bucket’, 
where each bucket contains the sum of invoices where date > start_date and date 
<= end_date. There will be five buckets altogether. In my test database I have 
12 debtors and a few thousand invoices.

I could structure it like this (pseudo code) -

SELECT acc_no, acc_name,
(SELECT SUM(invoice_bal) FROM invoices WHERE invoice_date <= date_5) AS 
120_days, 
(SELECT SUM(invoice_bal) FROM invoices WHERE invoice_date > date_5 AND 
invoice_date <= date_4) AS 90_days, 
(SELECT SUM(invoice_bal) FROM invoices WHERE invoice_date > date_4 AND 
invoice_date <= date_3) AS 60_days, 
(SELECT SUM(invoice_bal) FROM invoices WHERE invoice_date > date_3 AND 
invoice_date <= date_2) AS 30_days, 
(SELECT SUM(invoice_bal) FROM invoices WHERE invoice_date > date_2 AND 
invoice_date <= date_1) AS current
FROM debtors_table

This works, but it requires 5 separate scans of the invoice table, which is 
inefficient.

I changed it to use a WITH clause to make one scan of the invoice table and 
store the results.

I won’t show the WITH clause here, as it is not the reason for the question, 
and would just add clutter. If I run the WITH clause separately, it executes in 
about 0.15 seconds, and generates 60 rows with 3 columns – account number, 
bucket number, and accumulated balance.

In the main clause I now have this -

WITH ageing AS (...)
SELECT acc_no, acc_name,
(SELECT balance FROM ageing WHERE account_number = acc_no AND 
bucket_number = 5) AS 120_days,
(SELECT balance FROM ageing WHERE account_number = acc_no AND 
bucket_number = 4) AS 90_days,
(SELECT balance FROM ageing WHERE account_number = acc_no AND 
bucket_number = 3) AS 60_days,
(SELECT balance FROM ageing WHERE account_number = acc_no AND 
bucket_number = 2) AS 30_days,
(SELECT balance FROM ageing WHERE account_number = acc_no AND 
bucket_number = 1) AS current
FROM debtors_table

It works, but it takes about 3.5 seconds to execute.

Exactly the same exercise on Sql Server takes 0.17 seconds, and on PostgreSQL 
0.22 seconds.

I appreciate that the temporary table created in the WITH clause is not 
indexed, but 3.5 seconds seems a long time to extract the data from 60 rows.

Is there any way to speed this up?

Frank Millman
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Frank Millman]

On 2/28/18 3:18 PM, Frank Millman wrote:

> 
> On 2/28/18 2:53 PM, Richard Damon wrote:
> 
> > 
> > On 2/28/18 6:59 AM, Frank Millman wrote:
> > > Hi all
> > >
> > > I am using Python 3.6.0 and sqlite3 3.20.1. I am getting the message 
> > > ‘database is locked’ which, from reading the docs, I think is coming from 
> > > an SQL_BUSY error.
> > >
> > > It does not behave in the way I expect.
> > 
[...]
> > 
> > Your description, seeming to imply a total order, has an issue. If this 
> > is happening in a single thread, then if it uses a second connection to 
> > try and insert, that will block and the thread will never get to step 3, 
> > so the commit will not happen. You have a dead lock.
> > 
> 
> Of course! I should have thought of that.
> I am using python’s asyncio module, so everything is happening in a single 
> thread.
> I understand what is happening now. Thanks very much.

>

For the record, in case anyone runs into a similar situation, I found an 
effective solution.

I try to keep my transactions as short as possible, but with multiple 
connections running in the same thread it is possible that one of them starts a 
transaction before another one has committed.

To control that, I created an asyncio.Lock(), which is acquired at the start of 
the transaction and released after the commit. Problem solved.

I tested with 10 concurrent connections, and they all updated perfectly, with 
hardly any slowdown.

Frank
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Frank Millman]

On 2/28/18 2:53 PM, Richard Damon wrote:

> 
> On 2/28/18 6:59 AM, Frank Millman wrote:
> > Hi all
> >
> > I am using Python 3.6.0 and sqlite3 3.20.1. I am getting the message 
> > ‘database is locked’ which, from reading the docs, I think is coming from 
> > an SQL_BUSY error.
> >
> > It does not behave in the way I expect.
> 
[...]
> 
> Your description, seeming to imply a total order, has an issue. If this 
> is happening in a single thread, then if it uses a second connection to 
> try and insert, that will block and the thread will never get to step 3, 
> so the commit will not happen. You have a dead lock.
> 

Of course! I should have thought of that.
I am using python’s asyncio module, so everything is happening in a single 
thread.
I understand what is happening now. Thanks very much.
Frank
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database is locked

[Frank Millman]

Hi all

I am using Python 3.6.0 and sqlite3 3.20.1. I am getting the message ‘database 
is locked’ which, from reading the docs, I think is coming from an SQL_BUSY 
error.

It does not behave in the way I expect. I tested using two concurrent 
connections – call them conn_1 and conn_2.

1. conn_1 performs an INSERT.

2. conn_2 performs an INSERT.

3. conn_1 sleeps for 1 second, then performs a COMMIT.

4. conn_2 sleeps for 1 second, then performs a COMMIT.

After step 2, the database is locked, which I understand. I don’t know which 
connection is holding the lock, but I don’t think that matters.

What I expected to happen was that, after one second, whichever connection was 
holding the lock would COMMIT, which would free up the other connection, which 
would then complete normally.

AFAICT, what happens in practice is that the lock is held for the default 
timeout of 5 seconds, then the connection which did *not* hold the lock fails 
with ‘database is locked’, and the connection which *did* hold the lock 
completes normally.

Is there any way to get the behaviour that I am looking for?

Thanks

Frank Millman
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Frank Millman]

On Dec 15, 2018, at 08.58, Jay Kreibich wrote:

> 
> > On Dec 15, 2018, at 12:49 AM, Frank Millman  wrote:
> > 
> > I know that floating point is not precise and not suitable for financial 
> > uses. Even so, I am curious about the following -
> > 
[...]
> > 
> > With the same version of sqlite3 and the same select statement, why does 
> > python return a different result from sqlite3.exe?
> 
> Because the shell is altering the output to make it easier to read.  Consider:
> 
> $ sqlite3
> 
> SQLite version 3.16.0 2016-11-04 19:09:39
> 
> Enter ".help" for usage hints.
> 
> Connected to a transient in-memory database.
> 
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> select 211496.252;
> 211496.26
> 

 That makes sense.

Thanks, Jay
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Frank Millman]

On 2018-12-14 11:24 AM, Darren Duncan wrote:

> 
> If yours is a financial application then you should be using exact numeric 
> types 
only, such as integers that represent multiples of whatever quantum you are 
using, such as cents; fractional numbers are a display or user input format 
only, and in those cases they are character strings.
> 

Thanks, Darren. In principle I agree with you, but I am experimenting with a 
different approach.
My application supports SQL Server and PostgreSQL as well as sqlite3, and those 
databases do have exact numeric types for monetary use, and I am trying to 
stick to one code base as much as possible.
The python sqlite3 module allows you to convert sqlite3 data to a python 
object, so my approach is to store decimal data as text in sqlite3, and convert 
it to a python Decimal object when reading it in. I find that this works ok. I 
do have a bit of trouble when using sqlite3 to ‘sum’ a column, as it then 
switches to floating point and can lose precision, but provided I convert the 
result back to a Decimal object with appropriate rounding it also works.

Having said that, I am still testing, and I may decide that I have to bite the 
bullet and store everything as integers, in which case I will use the same 
approach for the other databases as well.

Simon Slavin says ‘Currency amounts should be stored as integers’. Does this 
apply to sqlite3 specifically, or is that your recommendation for all databases?

Thanks

Frank
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question about floating point

[Frank Millman]

Hi all

I know that floating point is not precise and not suitable for financial uses. 
Even so, I am curious about the following -

SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open /sqlite_db/ccc
sqlite> select sum(amount_cust) from ar_trans where cust_row_id = 4 and 
tran_date between '2015-05-01' and '2015-05-31';
211496.26

Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 27 2018, 04:59:51) [MSC v.1914 64 bit 
(AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.sqlite_version
'3.26.0'
>>> conn = sqlite3.connect('/sqlite_db/ccc')
>>> cur = conn.cursor()
>>> cur.execute("select sum(amount_cust) from ar_trans where cust_row_id = 4 
>>> and tran_date between '2015-05-01' and '2015-05-31'")

>>> cur.fetchone()
(211496.252,)

With the same version of sqlite3 and the same select statement, why does python 
return a different result from sqlite3.exe?

Thanks

Frank Millman
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users