Re: [sqlite] Single or double quotes when defining alias?

[Keith Medcalf]

On Friday, 25 October, 2019 20:45, Simon Slavin  wrote:

>On 25 Oct 2019, at 10:55pm, Thomas Kurz  wrote:

>> SELECT column2 AS "d"

>If you want to do it, do it like that.  Double quotes indicate an entity
>name.  Single quotes indicate a string of characters.

>However, almost nobody quotes entity names these days.  The language is
>written so that you don't need to.  Anything unquoted is understood to be
>an entity name until proven otherwise.

If the identifier is also a keyword and used in a location where it could be 
that keyword (or any location even where the interpretation as a keyword rather 
than an identifier would be absurd and you are using a particularly stupid 
parser); it starts with an ill-conceived character for an identifier; or, it 
contains an embedded ill-conceived character, then you need to quote the 
identifier.  Ill-conceived starting characters include most non-alphabetic 
characters except and underscore, and ill-conceived embedded characters include 
symbols that have other, usually terminal, meanings (space +-*/. etc).

Other than those cases you need not quote identifiers.  Identifiers are case 
preserving but case insensitive.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



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

Re: [sqlite] Single or double quotes when defining alias?

[Simon Slavin]

On 25 Oct 2019, at 10:55pm, Thomas Kurz  wrote:

> SELECT column2 AS "d"

If you want to do it, do it like that.  Double quotes indicate an entity name.  
Single quotes indicate a string of characters.

However, almost nobody quotes entity names these days.  The language is written 
so that you don't need to.  Anything unquoted is understood to be an entity 
name until proven otherwise.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Single or double quotes when defining alias?

[František Kučera]

Dne 25. 10. 19 v 23:55 Thomas Kurz napsal(a)
> this might be a stupid question, but do I have to use single or double quotes 
> when defining an alias?
>
> SELECT column1 AS 'c'
> --or--
> SELECT column2 AS "d"
>
> On the one hand, the name refers to a column or table identifier. On the 
> other hand, at the time of using this statement, the identifier does not 
> exist yet. At that moment, it is a string literal telling that an identifier 
> with that name should be created.
>
> So which one is correct?

I was bit surprised that sqlite accepts also AS 'c' (other DBMS do not).
But the standard way is AS "d" – it is an identifier. It is like if you
are defining a variable in a programming language – the variable with
this name does not exist yet, but same rules apply for the name syntax.

Franta


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

Re: [sqlite] Where is in_transaction of sqlite3.Connection?

[Peng Yu]

Forget about. I think in_transaction is only available in python3 but
not python2.

On 10/25/19, Peng Yu  wrote:
> https://docs.python.org/3.9/library/sqlite3.html
>
> The manual says in_transaction is an attribute of sqlite3.Connection.
> But I don't see it. Why?
>
> """
> in_transaction
> True if a transaction is active (there are uncommitted changes), False
> otherwise. Read-only attribute.
> """
>
> ==> main.py <==
> #!/usr/bin/env python2
> import sqlite3
> import sys
> conn=sqlite3.connect(sys.argv[1])
> print(conn.in_transaction)
>
> ==> main.sh <==
> #!/usr/bin/env bash
> dbfile=$(mktemp -u)
> ./main.py "$dbfile"
>
> $ ./main.sh
> Traceback (most recent call last):
>   File "./main.py", line 8, in 
> print(conn.in_transaction)
> AttributeError: 'sqlite3.Connection' object has no attribute
> 'in_transaction'
>
> --
> Regards,
> Peng
>


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

Re: [sqlite] What is the default value of isolation_level?

[Keith Medcalf]

On Friday, 25 October, 2019 13:49, Peng Yu  wrote:

>isolation_level is an empty string by default as shown below. But the
>python manual does not say that it can take an empty string. What does
>an empty string mean? Is it equivalent to None? Thanks.

No.  It means use the default.  And sqlite3 defaults to using deferred 
transactions, so '' is equivalent to 'deferred'.

>https://docs.python.org/3/library/sqlite3.html#connection-objects

>"""
>isolation_level

>Get or set the current default isolation level. None for
>autocommit mode or one of “DEFERRED”, “IMMEDIATE” or “EXCLUSIVE”. See
>section Controlling Transactions for a more detailed explanation.
>"""

Internally when the when the wrapper determines that a magical transaction is 
needed it basically executes the following:

if isolation_level is not None:
   .execute('BEGIN %s TRANSACTION;' % (isolation_level,))

if the isolation_level is None then no extra command is issued.
if the isolation_level is '' then the command BEGIN  TRANSACTION; is issued
if the isolation_level is 'DEFERRED' then the command BEGIN DEFERRED 
TRANSACTION; is issued
if the isolation_level is 'IMMEDIATE' then the command BEGIN IMMEDIATE 
TRANSACTION; is issued
if the isolation_level is 'EXCLUSIVE' then the command BEGIN EXCLUSIVE 
TRANSACTION; is issued

The setter for the isolation_level property of the connection ensures that a 
valid value for isolation_level is being set:

if not (not isolation_level or isolation_level.lower() in ('deferred', 
'immediate', 'exclusive')): 
raise ValueError('invalid value for isolation_level')

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] What does commit actually do?

[Peng Yu]

The manual says this.

"The Python sqlite3 module by default issues a BEGIN statement
implicitly before a Data Modification Language (DML) statement (i.e.
INSERT/UPDATE/DELETE/REPLACE)."
> If you did NOT specify "isolation_level = None" in the .connect() call then
> you probably ARE in a transaction if you've been running queries.

I just use the default .connect() without specifying isolation_level
explicitly. Then I am in a transaction?

import sqlite3
conn=sqlite3.connect(f)
c=conn.cursor()
import zlib
c.execute('SELECT name FROM sqlar')
result = c.fetchall()
for x in result:
print(x[0])

> But why do I need to commit my transaction if I'm just reading?

Is there any .commit() implied in the SELECT statement?

> If you're just reading then you can commit or rollback.

So I should close the `conn` immediately after `c.fetchall()` to
release resources used by sqlite3?

> If the database is not in WAL mode

In the above example, should I use WAL mode or not?

https://charlesleifer.com/blog/going-fast-with-sqlite-and-python/

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

Re: [sqlite] What does commit actually do?

[Keith Medcalf]

You can tell if you are in a transaction by the in_transaction property of the 
connection.  This tracks whether or not a BEGIN of some kind has been issued by 
the wrapper (either through magic or because you explicitly issued a command to 
BEGIN a transaction).  If in_transaction is True, then you need to issue a 
commit or rollback to end that transaction.  This property reflects the what 
the WRAPPER thinks is the transaction state, and not the underlying SQLite3 
database transaction state.

So, if you are issuing multiple SELECTs and you need repeatable read for these 
SELECTs, then you need to issue the BEGIN explicitly.

If you want something that allows Python to use SQLite3 as SQLite3 (rather than 
as a dbapi compliant generic database) then you should look at APSW instead.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Peng Yu
>Sent: Friday, 25 October, 2019 14:13
>To: SQLite mailing list 
>Subject: Re: [sqlite] What does commit actually do?
>
>The manual says this.
>
>"The Python sqlite3 module by default issues a BEGIN statement
>implicitly before a Data Modification Language (DML) statement (i.e.
>INSERT/UPDATE/DELETE/REPLACE)."
>> If you did NOT specify "isolation_level = None" in the .connect() call
>then
>> you probably ARE in a transaction if you've been running queries.
>
>I just use the default .connect() without specifying isolation_level
>explicitly. Then I am in a transaction?
>
>import sqlite3
>conn=sqlite3.connect(f)
>c=conn.cursor()
>import zlib
>c.execute('SELECT name FROM sqlar')
>result = c.fetchall()
>for x in result:
>   print(x[0])
>
>> But why do I need to commit my transaction if I'm just reading?
>
>Is there any .commit() implied in the SELECT statement?
>
>> If you're just reading then you can commit or rollback.
>
>So I should close the `conn` immediately after `c.fetchall()` to
>release resources used by sqlite3?
>
>> If the database is not in WAL mode
>
>In the above example, should I use WAL mode or not?
>
>https://charlesleifer.com/blog/going-fast-with-sqlite-and-python/
>
>--
>Regards,
>Peng
>___
>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] What is the default value of isolation_level?

[Peng Yu]

So basically an empty string in isolation_level is the same as
"DEFERRED"? This is confusing. I think it should have been implemented
so that the value of the isolation_level attribute is DEFERRED when it
is not specified in .connect().

On 10/25/19, David Raymond  wrote:
> https://docs.python.org/3.7/library/sqlite3.html#controlling-transactions
>
> "If you specify no isolation_level, a plain BEGIN is used, which is
> equivalent to specifying DEFERRED."
>
> I believe the empty string qualifies as "no isolation_level" for this.
>
>
>
> -Original Message-
> From: sqlite-users  On Behalf
> Of Peng Yu
> Sent: Friday, October 25, 2019 3:49 PM
> To: SQLite mailing list 
> Subject: [sqlite] What is the default value of isolation_level?
>
> Hi,
>
> isolation_level is an empty string by default as shown below. But the
> python manual does not say that it can take an empty string. What does
> an empty string mean? Is it equivalent to None? Thanks.
>
> https://docs.python.org/3/library/sqlite3.html#connection-objects
>
> """
> isolation_level
>
> Get or set the current default isolation level. None for
> autocommit mode or one of “DEFERRED”, “IMMEDIATE” or “EXCLUSIVE”. See
> section Controlling Transactions for a more detailed explanation.
> """
>
> $ cat main.py
> #!/usr/bin/env python2
> import sqlite3
> import sys
> conn=sqlite3.connect(sys.argv[1])
> print(repr(conn.isolation_level))
>
> $ cat main.sh
> #!/usr/bin/env bash
> dbfile=$(mktemp -u)
> ./main.py "$dbfile"
>
> $ ./main.sh
> ''
>
> --
> Regards,
> Peng
> ___
> 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
>


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

Re: [sqlite] Stream loading SQL script

[Keith Medcalf]

The sqlite3 command line shell already does this.  see function process_input

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of František Kucera
>Sent: Friday, 25 October, 2019 13:16
>To: SQLite mailing list 
>Subject: [sqlite] Stream loading SQL script
>
>Hello,
>
>I am developing a tool* in C++ and one of its features will be that it
>will load an SQL script (CREATE TABLE, INSERT), execute it, then execute
>some queries and print results.
>
>The SQL script might be long and I do not want to load it whole in the
>memory. Usually it will easily fit, but the tool should be capable to
>process longer scripts with constant memory usage. What is recommended
>way?
>
>I see that there is sqlite3_complete() function, which can detect
>complete query, but I would have to check the input using this function
>character by character, to stop exactly at the semicolon and then
>execute.
>
>Or I can try to execute what I have loaded and if it fails, load more –
>but this way I risk that I load incomplete statement, which could be
>valid and mistakenly executed (e.g. DELETE or SELECT without WHERE
>condition). So this will also not work.
>
>Franta
>
>*) if anyone interested, it is free software, described here:
> sources:
>
>
>
>___
>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] What is the default value of isolation_level?

[David Raymond]

https://docs.python.org/3.7/library/sqlite3.html#controlling-transactions

"If you specify no isolation_level, a plain BEGIN is used, which is equivalent 
to specifying DEFERRED."

I believe the empty string qualifies as "no isolation_level" for this.



-Original Message-
From: sqlite-users  On Behalf Of 
Peng Yu
Sent: Friday, October 25, 2019 3:49 PM
To: SQLite mailing list 
Subject: [sqlite] What is the default value of isolation_level?

Hi,

isolation_level is an empty string by default as shown below. But the
python manual does not say that it can take an empty string. What does
an empty string mean? Is it equivalent to None? Thanks.

https://docs.python.org/3/library/sqlite3.html#connection-objects

"""
isolation_level

Get or set the current default isolation level. None for
autocommit mode or one of “DEFERRED”, “IMMEDIATE” or “EXCLUSIVE”. See
section Controlling Transactions for a more detailed explanation.
"""

$ cat main.py
#!/usr/bin/env python2
import sqlite3
import sys
conn=sqlite3.connect(sys.argv[1])
print(repr(conn.isolation_level))

$ cat main.sh
#!/usr/bin/env bash
dbfile=$(mktemp -u)
./main.py "$dbfile"

$ ./main.sh
''

-- 
Regards,
Peng
___
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

[sqlite] What is the default value of isolation_level?

[Peng Yu]

Hi,

isolation_level is an empty string by default as shown below. But the
python manual does not say that it can take an empty string. What does
an empty string mean? Is it equivalent to None? Thanks.

https://docs.python.org/3/library/sqlite3.html#connection-objects

"""
isolation_level

Get or set the current default isolation level. None for
autocommit mode or one of “DEFERRED”, “IMMEDIATE” or “EXCLUSIVE”. See
section Controlling Transactions for a more detailed explanation.
"""

$ cat main.py
#!/usr/bin/env python2
import sqlite3
import sys
conn=sqlite3.connect(sys.argv[1])
print(repr(conn.isolation_level))

$ cat main.sh
#!/usr/bin/env bash
dbfile=$(mktemp -u)
./main.py "$dbfile"

$ ./main.sh
''

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

[sqlite] Single or double quotes when defining alias?

[Thomas Kurz]

Dear all,

this might be a stupid question, but do I have to use single or double quotes 
when defining an alias?

SELECT column1 AS 'c'
--or--
SELECT column2 AS "d"

On the one hand, the name refers to a column or table identifier. On the other 
hand, at the time of using this statement, the identifier does not exist yet. 
At that moment, it is a string literal telling that an identifier with that 
name should be created.

So which one is correct?

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

[sqlite] Where is in_transaction of sqlite3.Connection?

[Peng Yu]

https://docs.python.org/3.9/library/sqlite3.html

The manual says in_transaction is an attribute of sqlite3.Connection.
But I don't see it. Why?

"""
in_transaction
True if a transaction is active (there are uncommitted changes), False
otherwise. Read-only attribute.
"""

==> main.py <==
#!/usr/bin/env python2
import sqlite3
import sys
conn=sqlite3.connect(sys.argv[1])
print(conn.in_transaction)

==> main.sh <==
#!/usr/bin/env bash
dbfile=$(mktemp -u)
./main.py "$dbfile"

$ ./main.sh
Traceback (most recent call last):
  File "./main.py", line 8, in 
print(conn.in_transaction)
AttributeError: 'sqlite3.Connection' object has no attribute 'in_transaction'

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

Re: [sqlite] What does commit actually do?

[David Raymond]

"I just use the default .connect() without specifying isolation_level
explicitly. Then I am in a transaction?"
E, umm, well.
Even regular users may not know the answer to that do to "automatic stuff 
behind the scenes" which may have corner cases or maybe bad documentation. 
Which is why we prefer to use the "isolation_level = None" at connection time 
to be absolutely sure.
If you don't want to deal with that you can always just run .commit() anyway 
just to make sure. If you weren't in a transaction it won't complain, and 
you'll be sure to be no longer in a transaction immediately afterwards.


"Is there any .commit() implied in the SELECT statement?"
Nope.


"So I should close the `conn` immediately after `c.fetchall()` to
release resources used by sqlite3?"
If you're done reading from the database then it never hurts. If you're gonna 
go back and do more queries then you can keep the connection live which will 
keep things cached. Just don't be in a transaction or you'll be blocking anyone 
else trying to write.


If you're the only one ever using that file then all this is a moot point 
though as there's no one else to block.


"In the above example, should I use WAL mode or not?"
If you're just getting started with SQLite then probably don't worry about it 
for now, especially if everything is working fine with the default mode. You 
can look at it later for fun.
https://www.sqlite.org/wal.html

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

Re: [sqlite] What is the default value of isolation_level?

[Keith Medcalf]

On Friday, 25 October, 2019 14:02, Peng Yu  wrote:

>So basically an empty string in isolation_level is the same as
>"DEFERRED"? This is confusing. I think it should have been implemented
>so that the value of the isolation_level attribute is DEFERRED when it
>is not specified in .connect().

But that would not be dbapi compliant, and the whole point of the pysqlite2 
(sqlite3) wrapper is that it is dbapi compliant.  This means that the 
"interface" is the same for a whole raft of things that are all different and 
attempts to make them all work equally poorly.

The 'default' is an empty string ('') simply because that means "do the default 
thing that the underlying implementation does, and we have absolutely no clue 
nor do we care what that default is".  In the PARTICULAR case of SQLite3 the 
default is that "BEGIN  TRANSACTION;" is the same as "BEGIN DEFERRED 
TRANSACTION;".  However, for HobblinGobblin7 the "BEGIN  TRANSACTION;" is the 
same as "BEGIN OBFUSCATED TRANSACTION;".

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



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

Re: [sqlite] What is the default value of isolation_level?

[Peng Yu]

> if the isolation_level is None then no extra command is issued.
> if the isolation_level is '' then the command BEGIN  TRANSACTION; is issued
> if the isolation_level is 'DEFERRED' then the command BEGIN DEFERRED
> TRANSACTION; is issued
> if the isolation_level is 'IMMEDIATE' then the command BEGIN IMMEDIATE
> TRANSACTION; is issued
> if the isolation_level is 'EXCLUSIVE' then the command BEGIN EXCLUSIVE
> TRANSACTION; is issued

I got it. So it is basically from here

https://www.sqlite.org/lang_transaction.html

"Transactions can be DEFERRED, IMMEDIATE, or EXCLUSIVE. The default
transaction behavior is DEFERRED."

I still think that python sqlite3 module is better to be implemented
to hide this level of detail. Otherwise, users in python will need to
know "" is the same as DEFERRED at the raw SQLITE3 level. This
information is totally unnecessary burden to a python user.

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

Re: [sqlite] What is the default value of isolation_level?

[David Raymond]

https://www.sqlite.org/lang_transaction.html
Depending on how they're implementing it, one could argue that they're just 
copying the specs for SQLite and saying "if you're not gonna specify it, then 
we're not gonna specify it, and we're just gonna let the SQLite library do with 
it as it pleases without that keyword"


-Original Message-
From: sqlite-users  On Behalf Of 
Peng Yu
Sent: Friday, October 25, 2019 4:02 PM
To: SQLite mailing list 
Subject: Re: [sqlite] What is the default value of isolation_level?

So basically an empty string in isolation_level is the same as
"DEFERRED"? This is confusing. I think it should have been implemented
so that the value of the isolation_level attribute is DEFERRED when it
is not specified in .connect().

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

[sqlite] Stream loading SQL script

[František Kučera]

Hello,

I am developing a tool* in C++ and one of its features will be that it
will load an SQL script (CREATE TABLE, INSERT), execute it, then execute
some queries and print results.

The SQL script might be long and I do not want to load it whole in the
memory. Usually it will easily fit, but the tool should be capable to
process longer scripts with constant memory usage. What is recommended way?

I see that there is sqlite3_complete() function, which can detect
complete query, but I would have to check the input using this function
character by character, to stop exactly at the semicolon and then execute.

Or I can try to execute what I have loaded and if it fails, load more –
but this way I risk that I load incomplete statement, which could be
valid and mistakenly executed (e.g. DELETE or SELECT without WHERE
condition). So this will also not work.

Franta

*) if anyone interested, it is free software, described here:
 sources:



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

Re: [sqlite] What does commit actually do?

[David Raymond]

"https://docs.python.org/2/library/sqlite3.html;

Also, please consider going to Python 3 instead of 2.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What does commit actually do?

[David Raymond]

This question could span the entire range of experience level from beginner to 
expert.

When you're in a transaction, .commit() says "make everything done in this 
transaction on this connection permanent." If there are any crashes or improper 
disconnections in the middle of a transaction then nothing you did in the 
transaction will persist in the database, it will all be rolled back.
If you haven't made any changes then it's still fine to run, it'll just say 
"that was easy, nothing for me to save."

Are you in a transaction? (Python library specific question)
See Keith's message for a longer version. But in short...
If you did NOT specify "isolation_level = None" in the .connect() call then you 
probably ARE in a transaction if you've been running queries.
If you DID then you're usually NOT in a transaction, unless you explicitly 
started one (in which case you probably know about transactions).

But why do I need to commit my transaction if I'm just reading?
If you're just reading then you can commit or rollback. Either is fine. Just 
don't leave the transaction lingering around still open because someone else 
may want to write to the database, or to do a checkpoint.
If the database is not in WAL mode then their attempt to write will have to 
wait for your read transaction to finish before it can do any writing.
If the database IS in WAL mode, then writers can commit ok, but checkpointing 
of the WAL will be blocked from the point you started your transaction. You 
also will not see in your own queries any of those changes from writers until 
you start a new transaction. (Which may or may not be what you want)

Under the hood in the Python library I think all it does is issue a "commit;" 
command, and suppress any exception raised because you weren't in a transaction 
already.



-Original Message-
From: sqlite-users  On Behalf Of 
Peng Yu
Sent: Friday, October 25, 2019 12:44 PM
To: SQLite mailing list 
Subject: [sqlite] What does commit actually do?

The python manual just tell me what I should do but it is not very
clear what commit() actually does under the hood.

https://docs.python.org/2/library/sqlite3.html

"""
commit()

This method commits the current transaction. If you don’t call
this method, anything you did since the last call to commit() is not
visible from other database connections. If you wonder why you don’t
see the data you’ve written to the database, please check you didn’t
forget to call this method.
"""

So, only if I want to write something to the db, I need to call
commit()? If I just read something from the db, there is no need to
call commit()?

Thanks.

-- 
Regards,
Peng
___
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] proposal for write-lock on "commit" rather than "begin transaction"

[Brannon King]

>
> Two users – members of staff – enter data.  Each user enters a new
> invoice.  One of these entries gets rolled back.  What should their
> software do ?  Or should it just return an error message to the user ?
>

Multi-user data entry is not a part of my intended use case. I think other
database engines are better suited for this need. It could be done, though,
if you had an easy way to check for conflicts on commit. This change would
get us much closer to this than we are now.

My particular use case is for data verification. I have one part of the
system that does some computation. I then have another part of the system
that verifies that computation. I don't want the "verifier" to write to the
database; that data will be written by the true data owner. However, the
verifier to redo some of the inserts/deletes in order for the computation
to come out correctly.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What does commit actually do?

[Keith Medcalf]

On Friday, 25 October, 2019 10:44, Peng Yu  wrote:

>The python manual just tell me what I should do but it is not very
>clear what commit() actually does under the hood.

>https://docs.python.org/2/library/sqlite3.html

>"""
>commit()

>This method commits the current transaction. If you don’t call
>this method, anything you did since the last call to commit() is not
>visible from other database connections. If you wonder why you don’t
>see the data you’ve written to the database, please check you didn’t
>forget to call this method.
>"""

commit() returns the database to autocommit mode ending the transaction that 
was commenced with a begin command (it issues a COMMIT to the underlying 
sqlite3 database).  If the wrapper is operating in "magical mode" then 
statements which might update the database might be preceded with a magical 
begin statement (with a probability somewhere between 0% and 100% of the time). 
 See the isolation_level parameter when opening a connection for a description 
of the magical incantation modes available.  The text you quoted above assumes 
that magical mode is in effect.

By default the 'DEFERRED' magic mode is in effect.  You can turn off magical 
mode by using isolation_level=None when opening the connection, in which case 
you must BEGIN and COMMIT transactions yourself (the magical faery dust is 
turned off).  When the magical mode is turned off, each statement is executed 
in its own transaction in autocommit mode, or inside an explicit transaction 
which you BEGIN and COMMIT/ROLLBACK.

>So, only if I want to write something to the db, I need to call
>commit()? If I just read something from the db, there is no need to
>call commit()?

If you write to the database, and magical mode is enabled (isolation_level is 
any value other than None), and the wrapper faery managed to figure out that 
you were writing to the database and therefore issued a BEGIN, then you must 
commit the transaction in order to end the transaction that the magical 
transaction faery started for you.

Also, if you commenced a transaction with an explicit BEGIN command, then you 
must COMMIT that transaction in order for it to be visible to anyone else.

When a transaction is in process outstanding transactions are rolled back when 
the connection is closed.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume. 



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

[sqlite] What does commit actually do?

[Peng Yu]

The python manual just tell me what I should do but it is not very
clear what commit() actually does under the hood.

https://docs.python.org/2/library/sqlite3.html

"""
commit()

This method commits the current transaction. If you don’t call
this method, anything you did since the last call to commit() is not
visible from other database connections. If you wonder why you don’t
see the data you’ve written to the database, please check you didn’t
forget to call this method.
"""

So, only if I want to write something to the db, I need to call
commit()? If I just read something from the db, there is no need to
call commit()?

Thanks.

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

Re: [sqlite] proposal for write-lock on "commit" rather than "begin transaction"

[Simon Slavin]

On 25 Oct 2019, at 5:07pm, Brannon King  wrote:

> Once one connection commits, the other connection will no longer be allowed 
> to commit. It will be forced to rollback (or perhaps rebase if there are no 
> conflicts).

While lots of software supports rollback, in that it issues an error message 
rather than crashing, imagine what this would do in real life.

Two users – members of staff – enter data.  Each user enters a new invoice.  
One of these entries gets rolled back.  What should their software do ?  Or 
should it just return an error message to the user ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] proposal for write-lock on "commit" rather than "begin transaction"

[Brannon King]

This is a request for a small change to the handling of multiple
connections. I think it would significantly enhance the usefulness there
via allowing multiple "views" of the data.

Consider that I have two simultaneous connections to one file, named Con1
and Con2. They could be in one process or one thread -- that's irrelevant.
Either one may write to the DB; we don't know yet. For starters, assume
that their journal mode is MEMORY.

Both connections begin with "begin transaction". Already I'm dead in the
water; one of those will fail presently with "database is locked". But it
doesn't need to be that way! Each connection can have its own journal file,
especially if it's in memory. Once one connection commits, the other
connection will no longer be allowed to commit. It will be forced to
rollback (or perhaps rebase if there are no conflicts).

Multiple WAL files could be supported in a similar fashion; they just need
some kind of unique naming scheme. For recovery, the user would be prompted
to select one or none. It doesn't seem that far from Sqlite's current
behavior. Thoughts?

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

[sqlite] Making SQLite LSM extension another flagship software

[Amirouche Boubekki]

Hello!


I want to ask whether it is feasible to make SQLite LSM extension more
visible as another flagship software from the sqlite family.

# Proposal

- Change the name to something more memorable, for instance: okvslite
(prefered), lsmlite or add-your-proposal-here

- Move the code (and possibly history) to its own repository

- Improve the documentation

# Rationale

I have been working (on my free time) on OKVS databases mainly with
wiredtiger. I proposed to add OKVS to a Scheme standard as SRFI-167
[0][1]. And intend to continue to work with sqlite lsm whenever
possible and whenever it makes sense.

[0] https://srfi.schemers.org/srfi-167/
[1] 
https://github.com/scheme-requests-for-implementation/srfi-167/blob/master/libraries.md

I believe the OKVS approach to be very fruitful.

OKVS are used in modern distributed database system.

It will be good for the Internet to have a go-to learning material for OKVS.

# Request for feedback

What do you think?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can SQLite do this in a single query?

[Winfried]

Keith Medcalf wrote
> Well, "paint" is to draw your output.

Thank you.




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users