[sqlite] Insert with multiple nested selects.

Hey guys, Trying to create a statement for use with parameters in a Python execute method when performing inserts with multiple nested selects. I can adjust it for use with Python, but I am having issues when there is more than one nested select. Something such as: INSERT OR IGNORE INTO table_a

Re: [sqlite] Insert with multiple nested selects.

> Perhaps: > > INSERT OR IGNORE INTO table_a > ( > col_a, > col_b, > col_c, > col_d > ) VALUES ( >(SELECT id FROM table_b WHERE name=?) > ,? > ,? > ,(SELECT id FROM table_c WHERE name=?) > ); Hah, I need some time away from this one, not sure what I was

[sqlite] Transaction behaviour

I have a scenario where I am writing a series of entries across several tables with relationships using Python and context managers. The sql is abstracted away from the user by a class providing all the needed methods. These each open implicit transactions, now its been asked that during the bulk

Re: [sqlite] Transaction behaviour

> Start the bulk load with "BEGIN IMMEDIATE;" or "BEGIN > EXCLUSIVE;" and count errors. > If there were no errors at the end of the bulk load, issue > "COMMIT;", else issue "ROLLBACK". > If the program crashes, sqlite will take care of the rollback > automatically the next time any process opens

Re: [sqlite] Transaction behaviour

> SQLite transaction is a property of a database connection - there ain't > no such thing as a transaction spanning multiple connections. In hindsight, I completely overlooked that very point. Between then and now it's all been refactored to do this correctly. Thanks everybody! jlc

[sqlite] Filtering a join

I have three tables where table A is a left joined one to many relationship against two other tables. I now need to modify this to accept filtering what is returned from table A based on one of the many rows in table B and/or C. The row from table A is only valid with all the corresponding rows

Re: [sqlite] Filtering a join

> use a 'inner join', in stead of a 'left join' ? Hi Luuk, Sorry for the ambiguity, let me clarify. Table A yields individual rows that I am interested in. For each of these rows, a one to many exists with table B and or C. In this case, an inner join wont work as valid rows from table A don't

Re: [sqlite] Filtering a join

> SELECT > r.id AS foo > , r.col_a > , r.col_b > , a.name AS a_name > , a.value AS a_value > , t.res_id AS t_res_id > , t.key AS t_key > , t.value AS t_value > FROM request r > LEFT JOIN attribute a >ON a.req_id=r.id

[sqlite] Troubleshooting nested query performance issue

I have a query where if I hard code the results of the nested SELECT DICTINCT to a few static values, it completes very fast. Leaving the select causes this query to slow down badly. Running an explain query plan wasn't obvious with my weak sql experience. Is the nested query not evaluated only

Re: [sqlite] Troubleshooting nested query performance issue

> No. It appears to be a correlated subquery. It depends on the current row > of the "d" table (diffset) because of the "ON r.guid_id=did" term and thus > has to be reevalatued for every row of the "d" table. Richard, After a closer look, the subquery was useless and needed to be removed.

Re: [sqlite] Troubleshooting nested query performance issue

> Not directly related to your question, but… why oh why do people molest their > queries by > gratuitously and pointlessly aliasing perfectly good table name to > meaningless random > one letter codes?!? Masochism? lol, you're not wrong. This code is used in Python, and we are strict

[sqlite] Troubleshooting query performance

Hey guys, I have a query that's giving me abysmal performance and it's not immediately obvious to me as to what's wrong with the table structure to cause this. CREATE TABLE profile ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE NOT NULL ); CREATE INDEX profile_idx_0 ON

Re: [sqlite] Troubleshooting query performance

> Don't put the PRIMARY KEY as the first column of your index. Ever. This > applies to all SQL database engines, not just SQLite. > > For that matter, don't put the PRIMARY KEY anywhere in your index. The > PRIMARY KEY will be added automatically at the end, where it belongs. > > If you

[sqlite] Building with sqlite in C#

I am new to C# and am building an x64 console app, visual studio copies the System.Data.SQLite.dll to the build directory as its added to as a reference so how do people routinely manage the interop dll in projects? Is there an automated way to get it copied to the build directory when setting up

Re: [sqlite] Building with sqlite in C#

> The recommended way to handle this is by using the NuGet package, e.g.: > > https://www.nuget.org/packages/System.Data.SQLite/ Much appreciated Joe, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] Multiple SQLiteDataReader objects against a single connection

Hey guys, How does one accomplish this in the case where I iterate over a long result set with the first reader open, then open a new reader against a prepared statement and pass in a value derived from the first reader. Thanks, jlc ___ sqlite-users

Re: [sqlite] Multiple SQLiteDataReader objects against a single connection

> By doing exactly what you have described. > > What is the problem? Hey Clemens, Sorry I should have updated the thread, I was receiving an "There is already an open DataReader associated with this Command which must be closed first." exception which was simply from a lack of paying attention.

[sqlite] Blocking on commit

I have a .net app where one thread runs a queue waiting for other threads to push data to persist. The underlying sqlite access is provided by a singleton class exposing only one connection. When I call commit on a transaction that has just written a bunch of data, this returns before the

Re: [sqlite] Blocking on commit

> Rather than sleep, is their a blocking method I can accomplish a commit > through > or what other means can I assert all writes are complete? Missed the obvious, was a threading issue. jlc ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Preferred cast in C#

> I would really love to have an easy way of putting a long? into the database, > and then getting a long? back out. What do you want to happen when the column is null as in your string example? jlc ___ sqlite-users mailing list

[sqlite] Query help

I have a query I am trying to rewrite as efficient as possible and not clear. SELECT x.id, x.col FROM table_a x EXCEPT SELECT y.id, y.col FROM table_b y JOIN table_a . The right hand part of the except performs several joins and already duplicates the entire query on the left hand

Re: [sqlite] Query help

> There are two ways to rewrite this query, with a correlated subquery: > > SELECT * > FROM table_a AS x > WHERE NOT EXISTS (SELECT 1 >FROM table_b AS y >WHERE x.id = y.id > AND x.col = y.col) > > or with an outer join: > >

[sqlite] Guidance with Python and nested cursors

I am using Python to query a table for all its rows, for each row, I query related rows from a second table, then perform some processing and insert in to a third table. What is the technically correct approach for this? I would rather not accumulate all of the first tables data to make one off

Re: [sqlite] Guidance with Python and nested cursors

From: sqlite-users-boun...@sqlite.org on behalf of Petite Abeille Sent: Wednesday, July 17, 2013 1:25 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Guidance with Python and nested cursors On Jul 17, 2013, at 9:07 PM, Joseph L. Casale

Re: [sqlite] Guidance with Python and nested cursors

> It is perfectly allowed to open multiple cursors against a single connection. > You can only execute one > statement per cursor at a time, but you can have multiple cursors running > from the same connection: > > cr1 = cn.cursor() > cr2 = cn.cursor() > > cr1.execute('select ...') > while

[sqlite] Query help

Hey guys, I am trying to left join the results of two selects that both look exactly like this: SELECT DISTINCT SUBSTR(col, INSTR(col, 'string')) AS name FROM table_a Both tables have the exact data type and format, I need to reformat each tables results, then join and return only what is in

Re: [sqlite] Query help

> Will the SQL 1969 "EXCEPT" compound operator not work for some reason? Worked perfect, my sql is weak as I didn't even know of this one... Thanks! jlc ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] Best practice for connections and cursors

I have created a python module that I import within several files that simply opens a connection to an sqlite file and defines several methods which each open a cursor before they either select or insert data. As the module opens a connection, wherever I import it I can call a commit against the

[sqlite] Query problems

I have a query that is unbearable at scale, for example when s_table_a and s_table_b have 70k and 1.25M rows. SELECT s.id AS s_id ,s.lid AS s_lid ,sa.val AS s_sid ,d.id AS d_id ,d.lid AS d_lid FROM s_table_b sa JOIN d_table_b da ON ( da.key=sa.key

Re: [sqlite] Query problems

> Hi, > Can you do "DESCRIBE QUERY PLAN " and post results here? > > Also, what do you mean by "unbearable at scale"? Did you measure it? What > is the result? > > Thank you. It doesn't finish with maybe 4 or 5 hours run time. Sorry, do you mean "explain query plan ..."? 0 0 1

Re: [sqlite] Query problems

> Have you tried using '=' ? > > Also if you declare the columns as COLLATE NOCASE in your table definition, > then using '=' will definitely work the way you want it to. An example would > be > > CREATE TABLE myTable (myName TEXT COLLATE NOCASE) > > Simon. I did and it excluded the

Re: [sqlite] Query problems

> LIKE is used when comparing strings with wildcards. For example, val LIKE > 'abra%' (which will match 'abraCaDAbra' and 'abrakadee'. > > If there are no wildcards you should be using =, not LIKE. LIKE will/should > always indicate that a table or index scan is required, perhaps of the whole >

Re: [sqlite] Query problems

> > 0 0 1 SCAN TABLE d_table_b AS da (~10 rows) > > > > Is this the index you referenced in you reply to Simon? > Maybe you are using wrong index/column? I'll recheck, I am also reading up on indexes as they relate to optimizing queries. Could be I made a mistake. > I had

Re: [sqlite] Query problems

> Have you tried using '=' ? > > Also if you declare the columns as COLLATE NOCASE in your table definition, > then using '=' will definitely work the way you want it to. An example would > be > > CREATE TABLE myTable (myName TEXT COLLATE NOCASE) Simon, That took this query from not finishing

Re: [sqlite] Query problems

> Plus, of course, index will only ever be used for operations where you have > overridden the default collating sequence for the operation, for example by > specifying collate nocase in the join expression, or adding the collate > nocase to > the order by or group by. I assume this explains why

[sqlite] Insert statement

Hi, What is the most efficient way to insert several records into a table which has a fk ref to the auto incrementing pk of another insert I need to do in the same statement. I am migrating some code away from using the SQLAlchemy orm to using the Core. The way the data is returned to me is a

Re: [sqlite] Insert statement

> If I understand the question, and there is no key other than the > auto-incrementing > integer, there might not be a good way. It sounds like the database's design > may > have painted you into a corner. Hi James, Well, after inserting one row into table A which looks like (without

Re: [sqlite] Insert statement

> Look up the last_insert_rowid() you want and store it in your programming > language. That's what programming languages are for. But if you want to do > it less efficiently ... Hey Simon, That is the procedure I utilize normally, the requirement for this specific case is that the entire set

Re: [sqlite] Insert statement

> Yes, that's what I suspected. Because your table_a has no natural key, you > have > no good way to select the auto-generated id value. You can find out what the > last > auto-generated value was, which lets you work a row at a time, but you're > really > suffering from a poor design

[sqlite] Table constraints

Hi, I have a table as follows: CREATE TABLE t ( id INTEGER NOT NULL, a VARCHAR NOT NULL COLLATE 'nocase', b VARCHAR COLLATE 'nocase', c VARCHAR CHECK (c IN ('foo', 'bar', NULL)) COLLATE 'nocase', PRIMARY KEY (id) ); How does one

Re: [sqlite] Table constraints

> If I have decoded correctly what you were trying to say, use a trigger > like this, and duplicate it for UPDATE: Thanks Clemens, this got me sorted out. jlc ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Selecting from view with System.Data.SQLite throws

From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Joe Mistachkin Sent: Saturday, April 8, 2017 1:18 PM To: SQLite mailing list Subject: Re: [sqlite] Selecting from view with System.Data.SQLite throws > What is the declared

[sqlite] Create view from a single table as one to many

I have a table t1 that I am trying to create a view from where the view will produce many rows for each row in t1. The resulting data is a set of rows which assign a text string for each bit flag present in a column in t1 from an enum I reference. How does one construct such a view? Thanks, jlc

Re: [sqlite] Create view from a single table as one to many

From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Clemens Ladisch Sent: Saturday, April 8, 2017 7:15 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Create view from a single table as one to many > Join that table with the table that contains

[sqlite] Selecting from view with System.Data.SQLite throws

I am using System.Data.SQLite.Core 1.0.104 in vs2017 with .NET 4.6.2 and trying to select from a view. The query is valid and does return data manually, however the reader throws an index out of range exception when accessing the first row result. Anything atypical needed when querying a view?

[sqlite] Equivalent syntax in sqlite

Whats the trick with SQLites working set to format a single statement with parameters where if a row exists for a given criteria, returns its Id, otherwise insert and return the last_insert_rowid()? For example: CREATE TABLE Foo ( Id INTEGER PRIMARY KEY NOT NULL, ColA TEXTNOT

Re: [sqlite] Equivalent syntax in sqlite

From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of David Raymond Sent: Wednesday, April 26, 2017 3:00 PM To: SQLite mailing list Subject: Re: [sqlite] Equivalent syntax in sqlite > With the comment that the insert or

[sqlite] SQL join syntax

Hey guys, Need a pointer some join syntax. I have a couple where TableA is one to many on TableB. TableB structure is three columns Id,Key,Value. For each row I select in Table, I join TableB several times for each Key whose value I want in the single row result. How do you construct the join and

Re: [sqlite] SQL join syntax

From: sqlite-users on behalf of Simon Slavin Sent: Thursday, April 27, 2017 5:26 PM To: SQLite mailing list Subject: Re: [sqlite] SQL join syntax   > On 28 Apr 2017, at 12:21am, Keith Medcalf wrote: >

Re: [sqlite] Semantics regarding command instances and queries in the C# client

From: sqlite-users on behalf of Clemens Ladisch Sent: Friday, April 28, 2017 2:51 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Semantics regarding command instances and queries in the C# client   > Show

Re: [sqlite] SQL join syntax

> Works fine when BAR and BAZ values exist in TableB.Key. Move the predicate on to the join it seems? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Semantics regarding command instances and queries in the C# client

Typically I open a connection and a command instance and associate instantiated parameters with the command instances, then process in a loop simply changing parameter values. I have a situation when I am needing to insert and select based on several criteria and that practice looks a bit ugly.

Re: [sqlite] Bulk load strategy

> -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Gerry Snyder > Sent: Wednesday, May 17, 2017 9:14 AM > To: SQLite mailing list > Subject: Re: [sqlite] Bulk load strategy > > If the

Re: [sqlite] Bulk load strategy

> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Richard Hipp > Sent: Wednesday, May 17, 2017 8:54 AM > To: SQLite mailing list > Subject: Re: [sqlite] Bulk load strategy > > Can you send ore details about your data

Re: [sqlite] Bulk load strategy

> -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Simon Slavin > Sent: Wednesday, May 17, 2017 10:05 AM > To: SQLite mailing list > Subject: Re: [sqlite] Bulk load strategy > I’m

Re: [sqlite] Bulk load strategy

> -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Simon Slavin > Sent: Wednesday, May 17, 2017 2:02 PM > To: SQLite mailing list > Subject: Re: [sqlite] Bulk load strategy > I forgot: once

[sqlite] Bulk load strategy

I am trying to bulk load about a million records each with ~20 related records into two tables. I am using WAL journal mode, synchronous is off and temp_store is memory. The source data is static and the database will only be used as a means to generate reporting and is not vital. I am deferring

Re: [sqlite] Bulk load strategy

> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Clemens Ladisch > Sent: Wednesday, May 17, 2017 6:04 AM > To: sqlite-users@mailinglists.sqlite.org > Subject: Re: [sqlite] Bulk load strategy > > Without an index, searching for a previous entry is likely to

Re: [sqlite] problem with special letters

-Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of hfiandor Sent: Friday, May 26, 2017 3:18 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] problem with special letters > I think: Lazarus work well when the data is

Re: [sqlite] auntondex with unique and integer primary key

> -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Paul Sanderson > Sent: Friday, May 19, 2017 11:22 AM > To: General Discussion of SQLite Database us...@mailinglists.sqlite.org> > Subject: [sqlite] auntondex with unique and

Re: [sqlite] auntondex with unique and integer primary key

> -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Paul Sanderson > Sent: Friday, May 19, 2017 12:08 PM > To: SQLite mailing list > Subject: Re: [sqlite] auntondex with unique and integer

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

-Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Thomas Flemming Sent: Tuesday, May 30, 2017 8:08 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] SELECT WHERE with RTREE and second condition slow > > Do you know

[sqlite] Reducing the output of a query

I have a query produced from several left joins which follows the format: XXX ItemA NULL NULL XXX ItemA ItemB NULL XXX ItemA NULL ItemC I need to group the data by all columns, column 0 is trivial, however columns 1:3 can collapse when any non null field matches. In the above case this could

Re: [sqlite] Seg fault with core dump. How to explore?

-Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Kevin O'Gorman Sent: Saturday, September 30, 2017 3:55 PM To: sqlite-users Subject: [sqlite] Seg fault with core dump. How to explore? > Here's

Re: [sqlite] Seg fault with core dump. How to explore?

-Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Kevin O'Gorman Sent: Saturday, September 30, 2017 6:40 PM To: SQLite mailing list Subject: Re: [sqlite] Seg fault with core dump. How to explore?

Re: [sqlite] SQLITE bug

-Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Sunday, September 3, 2017 7:51 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] SQLITE bug > Lastly, a comment I've made possibly more than once on this

Re: [sqlite] ASP.NET MVC 5 Connection

-Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Rahmat Ali Sent: Tuesday, October 31, 2017 10:43 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] ASP.NET MVC 5 Connection > I am trying to attach SQLite with my MVC 5

Re: [sqlite] ASP.NET MVC 5 Connection

-Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Rahmat Ali Sent: Tuesday, October 31, 2017 2:17 PM To: SQLite mailing list Subject: Re: [sqlite] ASP.NET MVC 5 Connection > Yes I am using MVC 5

[sqlite] Selecting with distinct on across two columns

I have a table where I need to select all distinct records from two columns and include a third that varies. It is possible that for multiple records where the first and second columns are equivalent, the third varies so it cannot be used in the distinct clause. In this case, I want to select the

Re: [sqlite] Selecting with distinct on across two columns

-Original Message- From: sqlite-users On Behalf Of R Smith Sent: Saturday, June 9, 2018 6:04 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Selecting with distinct on across two columns > DISTINCT is nothing more than a GROUP BY for the entire SELECT list. Brilliant,

Re: [sqlite] Grouping guidance

-Original Message- From: sqlite-users On Behalf Of Roman Fleysher Sent: Thursday, December 13, 2018 2:29 PM To: SQLite mailing list Subject: Re: [sqlite] Grouping guidance > Hypothesis can never be proven. It can only be rejected with data > contradicting it at hand. > > "..the

Re: [sqlite] Grouping guidance

-Original Message- From: sqlite-users On Behalf Of Igor Tandetnik Sent: Thursday, December 13, 2018 12:16 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Grouping guidance > select Request from MyTable group by Request having count(distinct > Description) = 2 Nice, I

[sqlite] Grouping guidance

Hi, I have data that resembles the following: Request NumberDescription REQ0090887 TASK0236753 Foo REQ0090887 TASK0234920 Bar REQ0090887 TASK0234921 Bar REQ0090887 TASK0237261 Foo REQ0086880 TASK0224045 Foo REQ0086903 TASK0224555 Bar REQ0086990 TASK0223977 Bar