Re: [sqlite] [EXTERNAL] Re: Default isolation_level for sqlite3.connect?

2020-01-30 Thread Hick Gunter
"Autocommit" means that each SQL Statement executes in it's own transaction. Just as if you were to execute "begin; ; commit;" each time. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Peng Yu Gesendet: Freitag, 31.

Re: [sqlite] Default isolation_level for sqlite3.connect?

2020-01-30 Thread Keith Medcalf
At any given instant in time a connection can either (a) have a transaction in progress or (b) have no transaction in progress. An SQL statement cannot be executed EXCEPT inside of a transaction. "autocommit" means that the SQLite3 database engine (not the sqlite3 wrapper) will start the

Re: [sqlite] conditional insert operations

2020-01-30 Thread Peng Yu
h1, h2 pair are unique. I don't quite understand your example. Could you explain what it does in plain English so that I can be sure it does what I want? (What is v?) On 1/30/20, David Raymond wrote: > Is the combo of h1, h2 unique? If so you could do an upsert >

Re: [sqlite] Default isolation_level for sqlite3.connect?

2020-01-30 Thread Peng Yu
I still have a hard time to understand what the difference is according to the python manual. It keeps saying see somewhere else in the python manual. But I don't see where it explains the differences between the differences comprehensively and clearly.

Re: [sqlite] Generated columns and COLLATE in the AS parens

2020-01-30 Thread Keith Medcalf
On: Wednesday, 29 January, 2020 06:45, Markus Winand wrote: >I think there might be a glitch in the way SQLite 3.31.x derives the >collation information from the expression of a generated column. >In particular, COLLATE inside the AS parens seems to be ignored, but it >is honoured after the

Re: [sqlite] "Standard SQL" ?

2020-01-30 Thread Markus Winand
> On 30 Jan 2020, at 21:12, Keith Medcalf wrote: > > On Thursday, 30 January, 2020 12:20, Simon Slavin > wrote: > >> I would appreciate your help. Reading a technical article today, I came >> across a casual reference to "Standard SQL" as if it was a well-known >> thing. This worried me

Re: [sqlite] Generated columns and COLLATE in the AS parens

2020-01-30 Thread Markus Winand
> On 30 Jan 2020, at 18:20, Richard Hipp wrote: > > On 1/30/20, Markus Winand wrote: >> >> Unfortunately, the “what would PostgreSQL do” approach doesn’t provide >> guidance here. > > Maybe it does. PostgreSQL doesn't allow typeless columns, but it does > allow columns with unspecified

Re: [sqlite] "Standard SQL" ?

2020-01-30 Thread Keith Medcalf
On Thursday, 30 January, 2020 12:20, Simon Slavin wrote: >I would appreciate your help. Reading a technical article today, I came >across a casual reference to "Standard SQL" as if it was a well-known >thing. This worried me since I've never heard the term and I'm meant to >know about such

Re: [sqlite] "Standard SQL" ?

2020-01-30 Thread Chris Brody
A few resources I found from https://www.google.com/search?q=sql+standard : - https://en.wikipedia.org/wiki/SQL - https://blog.ansi.org/2018/10/sql-standard-iso-iec-9075-2016-ansi-x3-135/#gref - https://dev.to/0xcrypto/who-owns-the-sql-standard-76m -

[sqlite] "Standard SQL" ?

2020-01-30 Thread Simon Slavin
I would appreciate your help. Reading a technical article today, I came across a casual reference to "Standard SQL" as if it was a well-known thing. This worried me since I've never heard the term and I'm meant to know about such things. It doesn't seem to refer to the official standard for

Re: [sqlite] conditional insert operations

2020-01-30 Thread David Raymond
Is the combo of h1, h2 unique? If so you could do an upsert (https://www.sqlite.org/lang_UPSERT.html) create unique index tbl_uidx_h1_h2 on tbl (h1, h2); insert into tbl values ('a', '', 'X') on conflict (h1, h2) do update set v = excluded.v where v is not excluded.v; -Original

Re: [sqlite] Generated columns and COLLATE in the AS parens

2020-01-30 Thread David Raymond
From a Windows installation of 12.0 testing=> select version(); version PostgreSQL 12.0, compiled by Visual C++ build 1914, 64-bit (1 row) Time: 0.283 ms testing=> create table foo ( testing(> a text,

Re: [sqlite] Default isolation_level for sqlite3.connect?

2020-01-30 Thread Keith Medcalf
The default is an empty string (ie, ''). It can take the value None, '', 'DEFERRED', 'IMMEDIATE', 'EXCLUSIVE' and the value (if not None) is appended to the BEGIN when a magical transaction is started, and if None, then you are indicating that you will be using manual transaction control

Re: [sqlite] Generated columns and COLLATE in the AS parens

2020-01-30 Thread Richard Hipp
On 1/30/20, Markus Winand wrote: > > Unfortunately, the “what would PostgreSQL do” approach doesn’t provide > guidance here. Maybe it does. PostgreSQL doesn't allow typeless columns, but it does allow columns with unspecified collating sequences, does it not? What if you have a normal column X

Re: [sqlite] conditional insert operations

2020-01-30 Thread Peng Yu
Suppose the table is this (the first line is just header) h1,h2,v a,,Y a,C,3 Since v of h1=a and h2="" is Y which is not X, the table should be updated to h1,h2,v a,,X a,A,1 a,B,2 ... Suppose the table is this, as v of h1=a and h2="" is X, the table is not changed. h1,h2,v a,,X a,C,3

Re: [sqlite] Default isolation_level for sqlite3.connect?

2020-01-30 Thread Peng Yu
On 1/30/20, Keith Medcalf wrote: > > The isolation_level specifies the default suffix to put after 'BEGIN' when > beginning a transaction. Inside the library the following is used when the > magic wants to start a transaction: > > if isolation_level is not None: >.execute('BEGIN %s' %

Re: [sqlite] conditional insert operations

2020-01-30 Thread David Raymond
I'm not quite following what you're trying to do here. Could you provide a few examples of "here's what used to be in there", "here's what I want to insert", "here's what it should like in the end" -Original Message- From: sqlite-users On Behalf Of Peng Yu Sent: Thursday, January

Re: [sqlite] Default isolation_level for sqlite3.connect?

2020-01-30 Thread Keith Medcalf
The isolation_level specifies the default suffix to put after 'BEGIN' when beginning a transaction. Inside the library the following is used when the magic wants to start a transaction: if isolation_level is not None: .execute('BEGIN %s' % isolation_level) This is so that you can set

Re: [sqlite] Default isolation_level for sqlite3.connect?

2020-01-30 Thread David Raymond
Incorrect. "Not specifying one" is sqlite3.connect(fi) And the connection will still start implicit transactions for you. (with "begin;") "Setting it to None" is sqlite3.connect(fi, isolation_level = None) Which will turn off all implicit transactions, put it in autocommit mode, and you have

Re: [sqlite] Default isolation_level for sqlite3.connect?

2020-01-30 Thread Peng Yu
> and if you don't specify one it issues a plain "begin;" So that is basically isolation_level = None? Thanks. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] conditional insert operations

2020-01-30 Thread Peng Yu
Hi, Suppose that I have a table with three columns h1, h2, v. I want to delete all rows with h1=a, and insert rows like the following (data shown in TSV format), only if there is not an entry with h1=a and h2="" (empty), it exists but its v is not equal to a value X. a,A,v1 a,B,v2 ...

Re: [sqlite] Does .commit() ensure the .execute()'s and .executemany()'s called before are run atomically?

2020-01-30 Thread David Raymond
That's just my own personal paranoia wanting to make sure the cursor always gets closed, even on an exception. As I don't think the normal context manager on a cursor closes it when it exits. In the real world it's probably overkill as a) The destructors probably take care of that b) It's the

Re: [sqlite] Is it necessary to encode() for file names in sqlar format?

2020-01-30 Thread Keith Medcalf
Yes. If it is bytes type then the data stored by the database will be a BLOB, not TEXT ... -- 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:

Re: [sqlite] Default isolation_level for sqlite3.connect?

2020-01-30 Thread David Raymond
https://docs.python.org/3.8/library/sqlite3.html#sqlite3-controlling-transactions "You can control which kind of BEGIN statements sqlite3 implicitly executes via the isolation_level parameter to the connect() call, or via the isolation_level property of connections. If you specify no

Re: [sqlite] Does .commit() ensure the .execute()'s and .executemany()'s called before are run atomically?

2020-01-30 Thread Peng Yu
Thanks. What is the purpose of contextlib. If I just use `cur = conn.cursor()`, what problems it will cause? > with contextlib.closing(conn.cursor()) as cur: -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] Default isolation_level for sqlite3.connect?

2020-01-30 Thread Peng Yu
Hi, I don't see what is the default isolation_level here. Is it None? Thanks. https://docs.python.org/3/library/sqlite3.html#module-functions-and-constants sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])¶ -- Regards, Peng

Re: [sqlite] [EXTERNAL] Either a bug or I don't understand SQL update

2020-01-30 Thread Hick Gunter
You have fallen into the double quote trap. SQLite uses double quotes to denote COLUMN NAMES, and single quotes to delimit STRING CONSTANTS. When asking for "M" or "G", you get the contents of the column named m and g respectively (column names are case insensitive). When asking for "P" or

Re: [sqlite] Either a bug or I don't understand SQL update

2020-01-30 Thread David Raymond
The mailing list strips all attachments, so you'll have to either provide a link to it on some external source, or give more create table/insert statements like at the bottom. Without having all the data I'll say: Remember to use single quotes for text literals. You have ...and

Re: [sqlite] Either a bug or I don't understand SQL update

2020-01-30 Thread Igor Tandetnik
"M" refers to a column named M, specifically, citytax.m . Similarly, "G" is a reference to citytax.g. String literals in SQL are enclosed in single quotes, as in 'M' and 'G' ; double quotes are used to enclose names (helpful for names that contain spaces or other characters not allowed in

[sqlite] Either a bug or I don't understand SQL update

2020-01-30 Thread Paul Ausbeck
I've been using sqlite for some time but haven't used SQL update until recently. On my first real use, I've encountered a problem that I can't understand at all. To pare this down, I have two tables, citytax and taxitems2, and I've attached a database with just these two tables, total size

Re: [sqlite] Please unsubscribe me

2020-01-30 Thread Tim Streater
On 30 Jan 2020, at 15:34, kuppappa wrote: > Regards, > Kuppappa > Mobile: +91 8050095558 > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users You do that

[sqlite] Please unsubscribe me

2020-01-30 Thread kuppappa
Regards, Kuppappa Mobile: +91 8050095558 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New word to replace "serverless"

2020-01-30 Thread test user
Someone should put all the proposals into a vote. The voting system could be driven by a serverless database I presume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] New word to replace "serverless"

2020-01-30 Thread E.Pasma
I thougth about self-service, self-serve or self-served. Thanks, E. Pasma ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Behavior change around triggers between 3.19 and 3.30

2020-01-30 Thread Dominique Devienne
On Thu, Jan 30, 2020 at 3:38 PM Graham Holden wrote: > Thursday, January 30, 2020, 12:24:40 PM, Dominique Devienne > wrote: > > The strange thing though, is that I can't repro on a small example. > > Despite using not_there in the trigger, and doing DML and ALTER TABLE, > > still doesn't fail

Re: [sqlite] Behavior change around triggers between 3.19 and 3.30

2020-01-30 Thread Graham Holden
Thursday, January 30, 2020, 12:24:40 PM, Dominique Devienne wrote: > The strange thing though, is that I can't repro on a small example. > Despite using not_there in the trigger, and doing DML and ALTER TABLE, > still doesn't fail the same way as in production. What could be the cause? > --DD

Re: [sqlite] Does .commit() ensure the .execute()'s and .executemany()'s called before are run atomically?

2020-01-30 Thread David Raymond
-Original Message- From: sqlite-users On Behalf Of Peng Yu Sent: Thursday, January 30, 2020 5:16 AM To: SQLite mailing list Subject: Re: [sqlite] Does .commit() ensure the .execute()'s and .executemany()'s called before are run atomically? Could you show a python example on how to

Re: [sqlite] Where is the journal file for locking_mode=EXCLUSIVE?

2020-01-30 Thread Olivier Mascia
> Le 30 janv. 2020 à 15:05, Peng Yu a écrit : > > https://www.sqlite.org/tempfiles.html > > The above page says that there should be a journal file. > > "The PERSIST journal mode foregoes the deletion of the journal file > and instead overwrites the rollback journal header with zeros, which >

[sqlite] Where is the journal file for locking_mode=EXCLUSIVE?

2020-01-30 Thread Peng Yu
https://www.sqlite.org/tempfiles.html The above page says that there should be a journal file. "The PERSIST journal mode foregoes the deletion of the journal file and instead overwrites the rollback journal header with zeros, which prevents other processes from rolling back the journal and thus

Re: [sqlite] Behavior change around triggers between 3.19 and 3.30

2020-01-30 Thread Dominique Devienne
On Thu, Jan 30, 2020 at 1:09 PM Richard Hipp wrote: > On 1/30/20, Dominique Devienne wrote: > > My first question would be to ask whether there's a pragma or > > compile-time option to get back to the old behavior? > > Did you try "PRAGMA legacy_alter_table=ON;"? BINGO!!! Thanks a bunch

Re: [sqlite] What is a "journal"?

2020-01-30 Thread Peter da Silva
In programming a journal is a file or other data structure containing a series of change records but can be replayed to reconstruct an operation. On Thu, 30 Jan 2020, 05:29 Peng Yu, wrote: > Hi, > > https://www.sqlite.org/lockingv3.html#rollback > > "When a process wants to change a database

Re: [sqlite] Behavior change around triggers between 3.19 and 3.30

2020-01-30 Thread Richard Hipp
On 1/30/20, Dominique Devienne wrote: > > My first question would be to ask whether there's a pragma or > compile-time option to get back to the old behavior? Did you try "PRAGMA legacy_alter_table=ON;"? > > Second, any idea when this was introduced? > People have been requesting enhanced

Re: [sqlite] New word to replace "serverless"

2020-01-30 Thread Niall O'Reilly
On 29 Jan 2020, at 22:54, Brian Curley wrote: > The marketing buzzword usage will disappear... long before we’ll have the bike shed painted! /N ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] What is a "journal"?

2020-01-30 Thread Peng Yu
Hi, https://www.sqlite.org/lockingv3.html#rollback "When a process wants to change a database file (and it is not in WAL mode), it first records the original unchanged database content in a rollback journal. The rollback journal is an ordinary disk file that is always located in the same

Re: [sqlite] New word to replace "serverless"

2020-01-30 Thread Olaf Schmidt
Am 28.01.2020 um 12:18 schrieb Richard Hipp: On 1/28/20, Howard Chu wrote: Wait, really? AFAICS embedded means in-process, no IPC required to operate. Things like MySQL-embedded and H2 run a "server" as a thread instead of as a separate process. Clients then use Inter-Thread Communication

Re: [sqlite] Does .commit() ensure the .execute()'s and .executemany()'s called before are run atomically?

2020-01-30 Thread Peng Yu
Could you show a python example on how to make multiple entries either being all inserted (each entry done by an insert statement) or none on any error (e.g. ctrl-c)? I also want want to make sure no two python processes simultaneously editing these entries. I am not sure I fully understand how

[sqlite] Behavior change around triggers between 3.19 and 3.30

2020-01-30 Thread Dominique Devienne
BEFORE 3.19.3 2017-06-08 14:26:16 0ee482a1e0eae22e08edc8978c9733a96603d4509645f348ebf55b579e89636b AFTER 3.30.1 2019-10-10 20:19:45 18db032d058f1436ce3dea84081f4ee5a0f2259ad97301d43c426bc7f3dfalt2 Every 18 to 24 months we upgrade SQLite in a large commercial software suite. Such a recent upgrade

Re: [sqlite] Is it necessary to encode() for file names in sqlar format?

2020-01-30 Thread Peng Yu
So to confirm. In python 3, the str type should be used for name? Thanks. On Thu, Jan 30, 2020 at 12:58 AM Keith Medcalf wrote: > > sys.argv is a list of unicode text strings. There is no need to > specifically encode or decode it so long as sys.getdefaultencoding() > returns 'utf-8'. If your

Re: [sqlite] New word to replace "serverless"

2020-01-30 Thread Clemens Ladisch
Jim Dodgen wrote: > I vote for ignoring the marketing types and stick with "serverless" The word is intended to communicate a specific meaning to readers. Ignoring that the marketing types have changed the common meaning of "serverless" will just lead to confusion. Originally, "serverless" was a

Re: [sqlite] Generated columns and COLLATE in the AS parens

2020-01-30 Thread Markus Winand
> On 29 Jan 2020, at 14:59, Richard Hipp wrote: > > On 1/29/20, Markus Winand wrote: >> Hi! >> >> I think there might be a glitch in the way SQLite 3.31.x derives the >> collation information from the expression of a generated column. > > I think the current behavior is correct. > > If you