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
> 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
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
> 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
> 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
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
> 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
> 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
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
> 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.
> 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
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
> 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
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
> 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
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
> 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.
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
> 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
> 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
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
> 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:
>
>
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
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
> 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
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
> 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
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
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
> 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
> 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
> 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
>
> > 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
> 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
> 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
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
> 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
> 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
> 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
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
> 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
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
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
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
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?
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
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
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
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:
>
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
> 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
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.
> -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
> 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
> -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
> -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
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
> 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
-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
> -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
> -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
-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
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
-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
-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?
-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
-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
-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
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
-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,
-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
-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
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
73 matches
Mail list logo