Re: [sqlite] Using IGNORE for complete record match

Not possible in my case, but something to keep in mind for my next project. Thanks all for helping out a newbie. > On Oct 21, 2016, at 7:30 PM, Keith Medcalf wrote: > > > And what about NULL values? > >> All fields would match in an existing record compared to that of

Re: [sqlite] Using IGNORE for complete record match

Of course, allows "duplicate" rows to be inserted if one (or more) of the fields are NULL: sqlite> create table x(x int, y int, unique (x,y)); sqlite> insert into x values (1,1); sqlite> insert into x values (NULL,1); sqlite> insert into x values (1,NULL); sqlite> insert into x values (1,1);

Re: [sqlite] Using IGNORE for complete record match

And what about NULL values? > All fields would match in an existing record compared to that of a > proposed new record. That help? > > On 10/21/2016 4:49 PM, Simon Slavin wrote: > > On 21 Oct 2016, at 10:46pm, Rick Kohrs wrote: > > > >> I want to make sure that I do

Re: [sqlite] Using IGNORE for complete record match

On 21 Oct 2016, at 10:53pm, Rick Kohrs wrote: > All fields would match in an existing record compared to that of a proposed > new record. That help? So if two rows had all fields identical except for segment, they are not identical ? Okay, in that case CREATE

Re: [sqlite] Using IGNORE for complete record match

All fields would match in an existing record compared to that of a proposed new record. That help? On 10/21/2016 4:49 PM, Simon Slavin wrote: On 21 Oct 2016, at 10:46pm, Rick Kohrs wrote: I want to make sure that I do not insert a new record if ALL of the variables

Re: [sqlite] Using IGNORE for complete record match

> On Oct 21, 2016, at 2:46 PM, Rick Kohrs wrote: > > INSERT or IGNORE does not seem to be working as expected. The “or IGNORE” part describes what happens if there’s a conflict that would otherwise cause the INSERT to fail. Your schema doesn’t declare any column or

Re: [sqlite] Using IGNORE for complete record match

On 21 Oct 2016, at 10:46pm, Rick Kohrs wrote: > I want to make sure that I do not insert a new record if ALL of the variables > match. I can potentially have 3 systems writing to the same database and I > don't want duplicate records > > sqlCommand = """

[sqlite] Using IGNORE for complete record match

I want to make sure that I do not insert a new record if ALL of the variables match. I can potentially have 3 systems writing to the same database and I don't want duplicate records sqlCommand = """ CREATE TABLE himawari_db ( dateTime TEXT,

Re: [sqlite] Troubles matching variable as type TIMESTAMP

thanks On 10/21/2016 3:04 PM, Keith Medcalf wrote: SQLite does not have a type of "TIMESTAMP". Only TEXT, INTEGER, REAL, and BLOB. They type of the data in the field depends on what you are storing in it. What are you storing in it (you cannot store Python objects, such as a datetime

Re: [sqlite] Troubles matching variable as type TIMESTAMP

And do not forget to always convert your datetime to UTC before storing, especially if you live somewhere where the timezone offset from UTC has ever changed (which includes the entire planet earth). Wallclock timestamps in any localtime format cannot be compared unless your platform time

Re: [sqlite] Troubles matching variable as type TIMESTAMP

SQLite does not have a type of "TIMESTAMP". Only TEXT, INTEGER, REAL, and BLOB. They type of the data in the field depends on what you are storing in it. What are you storing in it (you cannot store Python objects, such as a datetime object, in an SQLite database, only TEXT, INTEGER, REAL or

Re: [sqlite] Troubles matching variable as type TIMESTAMP

"Python 3.19"? SQLite doesn't have a set datetime record format. It's up to you to standardize the input. There're some built-in functions to help out, but you have to format it yourself. If you're doing them as standardized strings, ('2016-10-21 15:40:14') then when you're retrieving them

Re: [sqlite] 2 consecutive rises in value

> so that one can find more than one row for the same values of (Id,Date)? Yes and that messes up your idea. Solution is to tidy that table up first before running any select SQL. > Average, last one, largest one? For now I have taken the largest one as in R Smith's SQL. Probably it is better to

[sqlite] Troubles matching variable as type TIMESTAMP

Using Python 3.19. I am reading in a lines from an ever growing log file. Values from each line of the log file are parsed and placed into a database. Each record has a variable of type TIMESTAMP. Multiple records have the same the same value for time stamp. After a line is processed, I need

Re: [sqlite] 2 consecutive rises in value

> I had a look at this and tried it without using the extra auto-increment field, using the table ROWID > instead to check for consecutiveness. It would work great and a lot faster indeed if it wasn't for > the multiple values on the same date. Problem with that is that if you group by date it

Re: [sqlite] Drop Existing Table Results in 'no such table'

Using the writable_schema pragma "only" makes sqlite_master treated like any other table for queries, it doesn't introduce extra logic to check for needed re-parsing or other controlled stuff. So running an UPDATE query on sqlite_master becomes just like any UPDATE on any normal table. It's

Re: [sqlite] 2 consecutive rises in value

> select distinct T1.Id-- only one per Id > from TABLE1 T1, TABLE1 T2, TABLE1 T3 -- very fast join on indexed rowid > where T2.Seq = T1.Seq+1 and T3.Seq = T1.Seq+2 -- consecutive triples > andT1.Id = T2.Id and T3.Id + T1.Id

Re: [sqlite] Drop Existing Table Results in 'no such table'

I believe "analyze sqlite_master;" will force a re-parse without requiring a new connection. Try it and let us know. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of sanhua.zh Sent: Friday, October 21, 2016 2:49 AM To: SQLite

Re: [sqlite] Drop Existing Table Results in 'no such table'

> On Oct 20, 2016, at 11:40 PM, Clemens Ladisch wrote: > > sanhua.zh wrote: >> I am trying to rename a table by editing the sqlite_master directly. > > Don't do this. Patient: “Doctor, my arm hurts when I do this!” Doctor: “Then don’t do that.” —Jens

Re: [sqlite] sqlite3 db is encrypted how to decrypt

> On Oct 21, 2016, at 12:16 AM, ravi.shan...@cellworksgroup.com wrote: > >I have a sqlite3 db which is encrypted using lib.so file how to > decrypt the db. If i access the db it print db is encrypted or not a > database. I don't have any idea about lib.so file and encryption used

Re: [sqlite] Risk of damaging a non-sqlite file with `sqlite3 `?

> From: Clemens Ladisch [mailto:clem...@ladisch.de] > Sent: Friday, October 21, 2016 1:31 AM > It would be possible to open a 'wrong' file only if someone had created a > fake database file deliberately. I think that is generally correct, but "possible ... only if" is perhaps a bit strong.

Re: [sqlite] Typical suffixes of sqlite database files

On 20/10/2016 23:17, Rolf Ade wrote: > What suffixes to sqlite database files do you use or see in the wild? Too many for suffix identification to be a viable option. One program I use, has 20 different suffixes, for the SQLite databases that it utilizes. Most of its competitors use between 3

Re: [sqlite] Drop Existing Table Results in 'no such table'

Hello Simon, I try it again and I find that I can update the memory of connection itself by changing any other connections' schema_version. This kind of behavior make me confused. Since it can tell other connections’ that the schema is changed, why can’t it tell to itself ? 原始邮件

[sqlite] sqlite3 db is encrypted how to decrypt

Hi guys, I have a sqlite3 db which is encrypted using lib.so file how to decrypt the db. If i access the db it print db is encrypted or not a database. I don't have any idea about lib.so file and encryption used in it. Does lib.so file uses any algorithm for encryption. Any

Re: [sqlite] Drop Existing Table Results in 'no such table'

On 21 October 2016 at 14:49, sanhua.zh wrote: > Don't do this. > I don’t think so. It is written on the sqlite.com. So it should be a > tricky way but not a wrong way. > The docs say, regarding the procedure you followed: > The following simpler procedure is appropriate

Re: [sqlite] Typical suffixes of sqlite database files

On 21 Oct 2016, at 12:17am, Rolf Ade wrote: > What suffixes to sqlite database files do you use or see in the wild? If you're writing a general utility for SQLite databases you can not depend on anything. The two suffixes '.db' and '.dat' can mean anything at all. The

Re: [sqlite] Drop Existing Table Results in 'no such table'

Don't do this. I don’t think so. It is written on the sqlite.com. So it should be a tricky way but not a wrong way. All we need to do is testing enough. 原始邮件 发件人:Clemens ladischclem...@ladisch.de 收件人:sqlite-userssqlite-us...@mailinglists.sqlite.org 发送时间:2016年10月21日(周五) 14:40 主题:Re: [sqlite]

Re: [sqlite] Drop Existing Table Results in 'no such table'

You are changing the details saved on disk but not the copy in memory. Since reopen a new connection is a ugly way, is there any other way to update the memory ? I know that sqlite3InitCallback may update it, but I can’t find a way to invoke it. 原始邮件 发件人:Simon slavinslav...@bigfraud.org

Re: [sqlite] Drop Existing Table Results in 'no such table'

sanhua.zh wrote: > I am trying to rename a table by editing the sqlite_master directly. Don't do this. > But I find that the ‘no such table’ error occurs while I try to drop > the renamed table. Only I close the connection or start a new > connection, the renamed table can be dropped.

Re: [sqlite] Drop Existing Table Results in 'no such table'

On 21 Oct 2016, at 4:42am, sanhua.zh wrote: > I am trying to rename a table by editing the sqlite_master directly. I do > know there are a ‘ALTER TABLE … RENAME TO …’ syntax to do that. I just want > to try it in this way. > But I find that the ‘no such table’ error

Re: [sqlite] Risk of damaging a non-sqlite file with `sqlite3 `?

Rolf Ade wrote: > If the cmd line tool sqlite3 is used, is it possible to damage a file, > given as sqlite database file argument to sqlite3? No. A database file is uniquely identified by the magic header string: It would be possible

Re: [sqlite] Typical suffixes of sqlite database files

Rolf Ade wrote: > What suffixes to sqlite database files do you use or see in the wild? > > There are for sure > > .db (e.g. firefox) > .sqlite[23]? These are the most likely ones. But you cannot use the file name to exclude files from the selection; you have to allow all files. Regards,

Re: [sqlite] Typical suffixes of sqlite database files

> What suffixes to sqlite database files do you use or see in the wild? .sql3 .dat On Fri, Oct 21, 2016 at 7:46 AM, Jean-Christophe Deschamps wrote: > At 01:17 21/10/2016, you wrote: > > What suffixes to sqlite database files do you use or see in the wild? >> > > I