Re: [sqlite] How to install REGEXP support?

My question is `But it is not clear how to install it for sqlite3 installed by homebrew.` On 9/19/19, Warren Young wrote: > On Sep 18, 2019, at 8:33 AM, Peng Yu wrote: >> >> But I don't want to always specify a full path. I am asking where is >> the standard place

[sqlite] Network file system that support sqlite3 well

Hi, I'd like to use sqlite3 db files on many compute nodes. But they should access the same storage device for the sqlite3 db files. The directory storing the db files looks the same on any compute node logically---the storage is mounted at the same mount point on the compute nodes. To achieve th

Re: [sqlite] Network file system that support sqlite3 well

> Why not use an actual client-server database system like MySQL? It's > optimized for this use case, so it incurs a lot less disk (network) I/O. I will need to use the actual files to test for dependency (just as the dependency that can be used by GNU make). With just database tables in MySQL, th

Re: [sqlite] Network file system that support sqlite3 well

> I know for sure that IBM's GPFS guarantees locking. I think GPFS is "global > parallel file system". It is a distributed file system. But it will be > rather slow. If only few jobs run in parallel, all will be ok. Locking will > always guarantee database integrity. > > With lots of jobs, you wi

Re: [sqlite] Network file system that support sqlite3 well

On 10/16/19, Simon Slavin wrote: > On 15 Oct 2019, at 11:47pm, Peng Yu wrote: > >> Is there a solution that are known to fill in this niche? Thanks. > > Unfortunately, no. Multiuser SQLite depends on locking being implemented > correctly. The developers haven't foun

Re: [sqlite] Network file system that support sqlite3 well

> > I will need to use the actual files to test for dependency (just as > > the dependency that can be used by GNU make) > > I don’t understand what that means. You want to use a makefile that checks > the mod date of the database? Suppose A is a sqlite3 db file, B is some other file which is gen

[sqlite] Why sqlite3 running at different time can have very different speed?

Hi, I see that sqlite3 can be very different in terms of run time. $ time sqlite3 file.sqa -Atv > /dev/null real0m3.259s user0m0.193s sys0m0.704s $ time sqlite3 file.sqa -Atv > /dev/null real0m0.148s user0m0.086s sys0m0.057s # Then do some else briefly $ time sqlite3

Re: [sqlite] Why sqlite3 running at different time can have very different speed?

How to prove the large time difference of sqlite3 is indeed due to cache and where is the cache? Why the caching used by sqlite3 can not persist for a longer time? On Sat, Oct 19, 2019 at 7:29 PM Keith Medcalf wrote: > > > On Saturday, 19 October, 2019 18:05, Peng Yu wrote: >

Re: [sqlite] Why sqlite3 running at different time can have very different speed?

> You can try clearing Linux file system cache to convince > yourself that cache misses contributes to performance drop. > > Run this as root: > > # sync; echo 3 > /proc/sys/vm/drop_caches I am on Mac OS X. Is there anything equivalent? Thanks. -- Regards, Peng

[sqlite] Why not allow subsecond file modification time in sqlar format?

Hi, https://www.sqlite.org/sqlar.html Only integer is allowed for last modification time. Why not allow subsecond file modification time in sqlar format, given subsecond time is allowed in many OSes? Thanks. mtime INT, -- last modification time -- Regards, Peng _

Re: [sqlite] Why not allow subsecond file modification time in sqlar format?

But what about `sqlite3 -Atv`? I think it only supports INT time? On 10/23/19, Richard Hipp wrote: > On 10/23/19, Peng Yu wrote: >> Hi, >> >> https://www.sqlite.org/sqlar.html >> >> Only integer is allowed for last modification time. Why not allow >> su

[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

Re: [sqlite] What is the default value of 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 BE

[sqlite] Where is in_transaction of sqlite3.Connection?

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

[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

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

gt; > -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 s

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 transactio

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

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? > > &quo

[sqlite] Is sqlite3 smart enough to remove redundant command?

Suppose that I have the following command, which writes two entries with the same key. So the 1st entry will be overwritten by the 2nd entry. Therefore, there is no need to write the 1st entry. Is sqlite3 smart enough to not to write the 1st entry? Or it will write both the 1st entry and the 2nd en

[sqlite] Is mutliple-execute-one-commit slower than multiple single-execute-single-commit?

Hi, I have two python programs using sqlite3. They function the same, except the following. In the first, execute() is called in batches and then commit() is called following them. In the second, commit() is called after each execute(). It seems that the second case is faster (I can not separate

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

In python sqlite3 program, if I call .execute() multiple times then call .commit(). Does it ensure that all the sqlite3 commands specified by execute()'s either all take effect or none effect? In other words, if any error occurs while running the sqlite3 commands specified in execute(), what happe

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

I use the following python3 code to create sqlar file. Is it necessary to sys.argv[2].encode('utf-8') in the line of execute()? In other word, does the native sqlar tools inteprete the name column as an encoded value or a non-encode value? Thanks. import sqlite3 conn=sqlite3.connect(sys.argv[1]) c

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

- > 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: Wednesday, 29 January, 2020 22:57 > >To: SQ

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

to implement this correctly. On Thu, Jan 30, 2020 at 12:43 AM Keith Medcalf wrote: > > On Wednesday, 29 January, 2020 22:45, Peng Yu wrote: > > >In python sqlite3 program, if I call .execute() multiple times then > >call .commit(). Does it ensure that all the sqlite3 com

[sqlite] What is a "journal"?

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 director

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

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 h

[sqlite] Default isolation_level for sqlite3.connect?

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] Does .commit() ensure the .execute()'s and .executemany()'s called before are run atomically?

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 http:

[sqlite] conditional insert operations

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 ... https://ww

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

> 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 http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/s

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

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' % isolati

Re: [sqlite] conditional insert operations

w 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 30,

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

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. https://www.sqlite.org/lang_transaction.html

Re: [sqlite] conditional insert operations

.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 Message- &g

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

The following is still very confusing. https://docs.python.org/3.8/library/sqlite3.html#controlling-transactions """ autocommit mode means that statements that modify the database take effect immediately." ... The Python sqlite3 module by default issues a BEGIN statement implicitly before a Dat

[sqlite] Import data from stdin to sqlite3 in python

Hi, I see this post uses Pandas to import data to sqlite3. https://datatofish.com/create-database-python-using-sqlite3/ But I don't want to make my code depend on Pandas. I'd like to use something like ".import /dev/stdin" to directly import data from stdin in python. Is it possible? Thanks. --

[sqlite] Is there a way to yield an error when opening a nonexistent dbfile?

Hi, By default the command sqlite3 will just open a dbfile if it does not exist. Suppose that I just want to perform read-only operations in a sqlite3 session, I will not need to create a non-exsitent file. Rather, I want the sqlite3 to fail when the dbfile does not exist. Is there a way to achie

[sqlite] log() in sqlite3

Hi, I see that many math functions (like log()) are not in sqlite3. It seems that SQL standard doesn't have them. https://www.sqlite.org/lang_corefunc.html But since sqlite3 contains non-standard functions anyway. Would it be considered to add those functions? Given the current version of sqlit

Re: [sqlite] Is there a way to yield an error when opening a nonexistent dbfile?

What is the recommended way to do so in python? I see the following two ways. ``` db = sqlite3.connect('file:/path/to/database?mode=ro', uri=True) fd = os.open(filename, os.O_RDONLY) c = sqlite3.connect('/dev/fd/%d' % fd) os.close(fd) ``` https://stackoverflow.com/questions/10205744/opening-sql

Re: [sqlite] log() in sqlite3

don't need to do the more tedious: >> a) Get original data with statement 1 >> b) Do calculations in the outside program >> c) Use the result in statement 2 >> >> >> -Original Message- >> From: sqlite-users On >> Behalf Of Peng Yu >

[sqlite] Is there something like PATH to search for extension libraries?

Hi, I have to specify either a full path (either relative or absolution) to use .load. But it would be more convenient if there is something like PATH (may be named as something like SQLITE3_LIB_PATH) to search for library files. Is it available in sqlite3? Thanks. https://www.sqlite.org/loadext.

Re: [sqlite] Is there something like PATH to search for extension libraries?

OK. This was not clear to me by reading https://www.sqlite.org/loadext.html. Maybe it should be included by the maintainer of that page. On 2/1/20, Keith Medcalf wrote: > > On Friday, 31 January, 2020 21:15, Peng Yu wrote: > >>I have to specify either a full path (either relativ

[sqlite] Is the table created by "create temp table" in the database file or in memory?

Hi, https://www.sqlite.org/lang_createtable.html "The database in which the new table is created. Tables may be created in the main database, the temp database, or in any attached database." It is not clear to me in the doc. If a table is created by "create temp table", is it temporarily a datab

Re: [sqlite] Is the table created by "create temp table" in the database file or in memory?

But it doesn't explain what is "the temp database". Is it an actual database file? Or it is just a in-memory database? On 2/1/20, Simon Slavin wrote: > On 1 Feb 2020, at 1:30pm, Peng Yu wrote: > >> https://www.sqlite.org/lang_createtable.html >> >>

[sqlite] Make sqlite3 database searchable on Mac OS X

Hi, It seems that sqlite3 databases are not searchable by Spotlight on Mac OS X. Is there a way to make them searchable? Thanks. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/

[sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

Hi, I am trying to see what tables are available in sqlite_master from firefox cookies sqlite3 fire. ~/Library/Application Support/Firefox/Profiles/jaseom4q.default-1480119569722/cookies.sqlite But the error message says "Error: database is locked". I see a cookies.sqlite-wal file in the same d

Re: [sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

> Does it work when you close Firefox? If it works when Firefox is closed but > not when Firefox is open, then the answer is probably no. I can check the content when Firefox is closed (the -wal file disappears after Firefox is closed). Why the database can not be read by another sqlite3 session

[sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

When I open an sqlite3 db using the following python code, conn=apsw.Connection(filepath, flags = apsw.SQLITE_OPEN_READONLY) , I got the following error. Traceback (most recent call last): File "/xxx.py", line 21, in for x in c.execute('SELECT (data) FROM sqlar'): File "src/cursor.c", l

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

I see this. What does it mean? Does it mean even when the sqlite3 session is closed there is still -wal and -shm left on the disk? """ There is an additional quasi-persistent "-wal" file and "-shm" shared memory file associated with each database, which can make SQLite less appealing for use as an

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

On 3/8/20, Keith Medcalf wrote: > > On Sunday, 8 March, 2020 21:24, Peng Yu wrote: > >>When I open an sqlite3 db using the following python code, > >>conn=apsw.Connection(filepath, flags = apsw.SQLITE_OPEN_READONLY) > >>, I got the following error. > >>T

[sqlite] convert zip to sqlar file

Hi, I'd like to convert zip files to sqlar files. Is there a command line tool that I can help with the conversion? Thanks. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailm

<    1   2