I have a table with incomplete time strings, such as ‘0:09.735’ (minutes:seconds.milliseconds). As the function strftime expects certain formats, such as ‘%H:%M:%f’, I have “to complete” the incomplete timestring first. The first strftime function with the complete timestri…
Tag: sqlite
How to pull the count of occurences from 2 SQL tables
I am using python on a SQlite3 DB i created. I have the DB created and currently just using command line to try and get the sql statement correct. I have 2 tables. When I setup table two, I added this statement in the creation of my messages table, but I have no clue what, if anything, it does: What
SQL IN in WHERE. How smart is the optimizer?
I have the following query to execute: UPDATE scenario_group SET project_id = @projectId WHERE scenario_group_id = @scenarioGroupId AND @projectId IN (SELECT …
How to use conditions with a RANK statement
The following piece of code does its job : it gives me the top 10 results for each category. SELECT * FROM ( SELECT *, RANK() OVER (PARTITION BY “pera_id” ORDER BY “surface” DESC) …
python list in tuple compare
I have a list and a tuple ( from sqlite query) and what to check if the items in list are not in the db tuple. If not add to newJobs list links = [“example.com”, “mysite.com”] …
SQLite: Order by number of NULL values
In SQLite, is there any way of ordering by the number of NULL values in each row, without having stored this number explicitly in a separate column? Something like SELECT rowid FROM (SELECT rowid, …
Conditional removing duplicate records
I’m storing some realtime data in SQLite. Now, I want to remove duplicate records to reduce data and enlarge its timeframe to 20 seconds by SQL commands. Sample data: id t col1 col2 –…
How to add date rows for messages query?
I got a Messages table. id | sender_id | message | date 1 | 1 | Cya | 10/10/2020 2 | 2 | Bye | 10/10/2020 3 | 1 | Heya | 10/11/2020 I want to insert date rows …
How to work around Django’s lack of composite keys in this complicated case?
I can’t seem to figure out how to work around the lack of composite keys in Django for the following case. I’m going to write the schema I’d like using SQLite3 dialect. (The only thing below that might be unfamiliar to someone who knows SQL but not sqlite is sqlite’s “WITHOUT ROW…
SQLite query SELECT login, password FROM user WHERE login = ‘login’ returns wrong result
I have a “user” table with two entries. For some reason, the query “SELECT login, password FROM user WHERE login = ‘login’” returns two records. Any help, please. Answer I assume that your query is actually using double-quotes for your string ‘login’ instead of single…