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: SELECT x.* FROM table_a AS x LEFT JOIN table_b AS y USING (id, col) WHERE y.id IS NULL Hi Clemens, Totally missed the boat on those two. Much appreciated, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 side, this works fine none the less, but now I need to return row ids for each record on the left and these certainly don't match the row ids from the right hand side. The above query would most certainly return every record from the lhs. What is the most efficient way to perform this without creating further queries that re-evaluate the same data? Thanks, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 transaction is available on the connection for read access. 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? Thanks, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/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. I am still reading and trialing approaches as my only previous experience with SQLite was through Python which exposes a different interface. That being said, I think I have a reasonable grasp now of how to apply previously learned Python approaches in C#. Thanks for the follow up, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 a project? Thanks! jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 profile ( id, name ); CREATE TABLE p_attribute ( id INTEGER PRIMARY KEY AUTOINCREMENT, pid INTEGER NOT NULL REFERENCES profile (id) ON DELETE CASCADE, aid INTEGER NOT NULL REFERENCES attribute (id) ON DELETE CASCADE, valueTEXT ); CREATE INDEX p_attribute_idx_0 ON p_attribute ( id, pid, aid ); CREATE TABLE attribute ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE NOT NULL, CHECK(UPPER(name) = name) ); CREATE INDEX attribute_idx_0 ON attribute ( id, name ); SELECT a.name, p.value FROM p_attribute p JOIN attribute a ON a.id=p.aid WHERE p.pid=? This returns all relevant rows I need, where table profile has ~6000 rows, p_attribute has ~ 170k and attribute has ~60 rows. Analyze has been run, explain query plan shows: recno selectedorder fromdetail 0 0 0 SCAN TABLE p_attribute AS p 0 1 1 SEARCH TABLE attribute AS a USING INTEGER PRIMARY KEY (rowid=?) Any pointers as to what may not be optimal? Thanks, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 remove the id, from all of your indices, I think your performance will probably improve dramatically. Richard, Simon, So much for testing at the console over a remote session, while I was used to waiting for a single row, the new query increased by so much it overwhelmed my display, heh. Nice and thanks for that tip! jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 once? SELECT a.value, COUNT(*) total, SUM(CASE WHEN r.status IS NULL THEN 1 ELSE 0 END) unprocessed, SUM(CASE WHEN r.status='processed' THEN 1 ELSE 0 END) processed, SUM(CASE WHEN r.status='error' THEN 1 ELSE 0 END) error FROM diffset d JOIN request r ON r.guid_id=d.id JOIN action a ON a.req_id=r.id AND a.key='operationType' AND a.value IN ( SELECT DISTINCT(a.value) FROM action a JOIN request r ON r.guid_id=d.id WHERE a.key='operationType' ) WHERE d.id=? GROUP BY value Thanks, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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. Thanks for the insight, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 by-the-sword PEP8 shop. Its a double edged sword at times, and plus I look at those tables so often the abbreviations are second nature to me. Funny, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 from B and C. What is the most elegant way to accomplish this? Thanks for any pointers, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 require rows from B or C. However, if an FK ref in B or C exists for a row in A, then *all* of those related rows in B or C must accompany the set. I need to implement a filter where I may be given one or more column values that may exist in table B or C. Since the schema requires the ref to A within B or C, that data set (n rows) from B or C might need to exclude the row from A. For example: 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 LEFT JOIN action t ON t.req_id=r.id WHERE r.guid_id=1 AND r.status IS NULL AND NOT r.id IN (SELECT DISTINCT(req_id) FROM action WHERE key='something' AND value='') ORDER BY foo So for every I need to exclude, I add another AND NOT filter. Is there a way to avoid the nested select? Thanks for the patience guys, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 LEFT JOIN action t ON t.req_id=r.id AND NOT(t.key='something' AND t.value='') WHERE r.guid_id=1 AND r.status IS NULL ORDER BY foo It think you can simply add it to the ON-clause... That excludes just the single row from the related set which than produces a join with the offending record in table A and an incomplete set from table B. What I had will work, I just think its rather ugly. Thanks for all the help, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 loading process, we wrap it all up in a transaction so nothing will be committed in the event of some problem during the load. This presents a problem as far as I know, aside from extending the schema with a table to indicate state that is updated upon completion, is there anything about transactions I am not seeing where I can accomplish leaving the bulk load uncommitted in the event of an issue in my case? Thanks, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 the database. http://sqlite.org/c3ref/get_autocommit.html can help you decide what mode you are in. By the way, autocommit is not the best thing to do if you have related insert/update/delete statements in an application transaction. Related updates should be in the same, atomic, transaction. Hi Kees and Igor, I appreciate the atomic violation of the current implementation. The issue lies in the fact the individual rows for each table consume a cursor and are wrapped in a context manager which starts its own transaction. The wrapper that abstracts the user from raw sql, connections and cursors hands out individual connections as the data's consuming access is multiprocess based and to work around the concurrency issues I have no choice. The bulk load doesn't have to be done this way, only one process loads data and even a single connection can be used but that would segment the wrapper. So either that or temp tables / other schema changes might be needed in this case. I'm looking at refactoring properly for the sake of correctness. Thanks, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 ( col_a, col_b, col_c, col_d ) SELECT col_a FROM (SELECT id FROM table_b WHERE name=?) ,? ,? ,SELECT col_d FROM (SELECT id FROM table_c WHERE name=?); Anyone have a hint on how to perform such as query? Thanks, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 thinking my nested selects... Thanks Kees, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 elegantly construct an index or constraint such that for any
row, column a may appear twice with column c having a value of 'foo' and
'bar', unless this value for column a appears with a null value in column c
where no other rows may now exist for that value of column a.
id a b c
-- --- --- ---
1 ab foo
2 ab bar
(no more rows with col a having a value of 'a'.
id a b c
-- --- --- ---
1 ab NULL
2 ab bar - not allowed.
Thanks,
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 choice. If you make val unique -- and I see no reason not to -- then you can select the id for every val you insert with where val = 'value' . Hi James, Thanks for the follow up. I am certainly open to critique and although this is working I would rather have it right. I realize I omitted the fact that val in table_a is unique. Given the unanimous opinion within the thread I bit the bullet and just refactored but I am still keen to leverage one large self-contained sql script. The reason is, accessing pure dbapi c code in python is fast but the module I am now using still mixes in plenty python in there and it's not nearly as fast as the proper programmatic approach to inserting and using code to deduce the rowid, followed up with the related inserts while using mostly python dbapi. Sending one large statement in this case would bypass the overhead, but using val as the reference would make the string very long. That text data might be several thousand chars long. As soon as I have a moment to revisit this, I will try Simon's suggestion. Thanks, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 string (requiring an insert into table A) accompanied by several more strings (requiring inserts into table B with a ref to a pk in table A's row). So instead of doing this the typical way, if I can prepare all the sql as one large statement for several sets of related inserts (The initial insert into table A with all the related inserts into table B) I will get the performance I am after. Does this seem reasonable? Sqlite doesn't support variable declaration but I am sure there is a more efficient means to this using something along the lines of INSERT INTO SELECT, just not sure how to craft this with n inserts based on one select from the PK generating initial insert. Thanks, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 specifying
the id
and letting it auto generate):
CREATE TABLE table_a (
valVARCHAR COLLATE nocase NOT NULL,
id INTEGER NOT NULL,
PRIMARY KEY ( id )
);
(forgive that odd looking format, its SQLAlchemy output...)
I have for example 20 rows in table B to insert referencing the above:
CREATE TABLE table_b (
val VARCHAR COLLATE nocase,
key VARCHAR COLLATE nocase NOT NULL,
id INTEGER,
seqno INTEGER NOT NULL,
PRIMARY KEY ( seqno ),
FOREIGN KEY ( id ) REFERENCES table_a ( id )
);
So selecting last_insert_rowid() always gives me the 'id' of the previous row
from table_a
after an insert. So I would insert into table_a, get that rowid, and build the
remaining 20
inserts. For the sake of keeping the entire sql statement manageable, I was
hoping not to
build the next 20 statements based on SELECT id FROM table_a WHERE val='xxx' as
that string
will be very long.
So this works for one insert:
INSERT INTO table_a (val) VALUES ('xx');
INSERT INTO table_b (id, key, val)
SELECT last_insert_rowid(), 'yyy', 'zzz';
Just not sure how to perform 20 or 30 of those inserts into table_b after the
one into table_a
yields the id value I need.
Thanks!
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 of inserts into table_a be bundled with their associated inserts into table_b in one statement where I won't have the luxury of an iterative approach. So all of these lines of sql will be sent as one statement. Normally I would just use variables, but we know this is not an option so I was hoping to find a way to accomplish this otherwise. Thanks, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 the change in the table def made a difference from not specifying the collation whereas the index did. I did not override the default of the table in the query so the index was not used. I've encountered another issue as I was running my tests in sqlitestudio when I realized the query against the tables with the collation specified returned all rows in less than a minute. Running the query against the db in the sqlite shell is still bad. I know sqlitestudio enables certain non-default pragmas, but I wonder which ones could result in this speed difference. Thanks, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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
AND da.key='unique_string'
AND da.val LIKE sa.val
)
JOIN s_table_a s ON
s.id=sa.id
JOIN d_table_a d ON
(
d.id=da.id
AND NOT d.lid LIKE s.lid
)
I am using LIKE as the columns are indexed NOCASE and I need the
comparison case insensitive. I suspect this is where is breaks down
but I don't know enough sql to really appreciate the ways I could
approach this better.
Both {s|d}_table_a have 2 columns, id, lid where id is PK.
Both {s|d}_table_b have 4 columns, seqno, id, key, val where seqno is PK,
id is a FK ref to {s|d}_table_a.id, and several key/val pairs are inserted to
correspond
to the associated PK id from {s|d}_table_a.
I'd be grateful for any suggestions or hints to improve this.
Thanks,
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query problems
Hi, Can you do DESCRIBE QUERY PLAN your_query 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 SCAN TABLE d_table_b AS da (~10 rows) 0 1 3 SEARCH TABLE d_table_a AS d USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0 2 0 SEARCH TABLE s_table_b AS sa USING AUTOMATIC COVERING INDEX (key=?) (~7 rows) 0 3 2 SEARCH TABLE s_table_a AS s USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) Thanks, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 comparisons whose case only differed, I only defined COLLATE NOCASE in the index so I guess it wasn't being used. I just changed the table defs to use this and am reloading the data. Thanks, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 table/index if the like expression is not a constant (there is no other choice since the wildcarded expression could evaluate to '%d%' which would return every row with a 'd' anywhere in the value. This means that the query planner must assume that this join will require a full table/index scan for each inner-loop and may return all rows because no other plan assumption would be valid. This will result in really crappy performance. Are the columns declared as COLLATE NOCASE, or just the index? If just the index, why? Was just the index as I didn't know better, but its corrected now. If there is some (really strange) reason why the table column is not declared with COLLATE NOCASE, then you can always override the collation of the column in the expression itself: CollateBinaryColumn COLLATE NOCASE = SomeOtherColumnCollationDoesNotMatter This insight is much appreciated, thanks! jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 the same problem (kind of) and got the answer here to create a different index... Thank you. Can you post you schema? Sure, it's not mine technically so I have to sanitize portions. Thanks, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 in 5 hours to producing results in under a minute, many thanks for everyone's guidance! jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 connection. Seems I've made a proper mess, one of the modules causes a 5 second delay at import (big indicator there) and one of the modules calls a method that yields data while calling other methods as it iterates. Each of these methods opens its own cursor. One of which during some processing calls another method which opens a cursor and creates a temp table and this corrupts the top level cursor and causes it to yield a shorter count. If I open a debugger just as the top level method begins to yield, I can pull all the expected records. It seems to be one of the nested methods that leverages the singleton connection to the sqlite db, once it opens its own cursor and creates a temp table, things go south. A bit vague I know, but does anyone see the obvious mistake? I assumed the module setting up a singleton connection was a perfectly viable way to accomplish this? Thanks! jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 table_a and not in table_b. Any guidance on how one might do this in sqlite? Thanks! jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 True:
row = cr1.fetchone()
if not row:
break
...
cr2.execute('INSERT ...')
for example. If you are inserting into one of the tables used in the outer
select, simply make sure that
select has an order by with a + in front of one of the column names to avoid
side effects (ie, changes
made to the database by the insert are visible to all statements/cursors on
that connection even before
those changes are committed).
Right,
I read this can be a problem, but I ran several tests validating results and it
worked perfectly.
Thank you very much for the confirmation.
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 selects from table two, then insert to table three. I would prefer to iterate over table one etc. How does one setup the connection and cursor for this style of task? Thanks for any guidance, jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 jcas...@activenetwerx.com wrote: 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? From the above outline, one SQL statement: Hi, Problem is I need to perform some Python processing of the data, then insert. Thanks! jlc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
