I have a sqlite3 DB with a few rows in it. When I try to fetch data from it in Python, fetchall returns an empty list. The data is being inserted in a different part of the project fine. I verified this by opening the DB in “DB Browser for SQLite” and running the following command. This returned t…
Tag: sqlite
Making sense of date-time and datatypes in SQLite
I’m learning SQL and SQLite at the moment, and from what I understand, SQLite doesn’t support a datetime datatype. However, when I run the command PRAGMA table_info(Orders); it’s showing one column as being of type datetime. I’ve read that In SQLite, the datatype of a value is associat…
Update of many rows with join extremely slow
I have a table with five relevant fields – id, source, iid, track_hash, alias. I want to group all entries into groups with a common track_hash and then for each row save the id of the row with the lowest source (with ties broken in favor of the highest iid) entry from its group into the alias field. To…
Database Error: NameError: name ‘db’ is not defined. What am I doing wrong?
On the web I found this illustration to create a database with gui with Tkinter. Everything ok, except when I enter the data and click on the Add button. I’m getting: NameError: name ‘db’ is not defined I think what I am wrong is nonsense in this part of the code. What am I doing wrong? Here…
Find the average over the n last purchases, for every user in the table using SQL
I have a SQLite db with two tables: users userID field1 field2 field3 field4 1 2 3 purchases purchaseID userID price timestamp 1 2 70 5166323 2 1 30 6543654 3 1 100 5456434 4 2 30 5846541 5 2 40 9635322 6 1 50 2541541 I want to write an SQL query that returns a table userID field1 field2
SQL Browser Crashing when running a Nested Query
I have a database with three tables: grocery nutrient grocery_nutrient (for many to many) Actually, the database is very big and I am facing the problems by running this query, the SQL browser crashes when I run, if I add LIMIT it works. and this is the query I have written for it. Answer You can simplify you…
How to Query JSON Within A Database
I would like to query information from databases that were created in this format: index label key data 1 sneaker UPC {“size”: “value”, “color”: “value”, “location”: “shelf2”} 2 location shelf2 {“height”: “value”, “row”: “value”, “column”: “value”} Where a large portion of the data is in one cell stored in a …
How to solve deadlock when inserting rows in sql many-to-many relationship with minimum cardinality one restriction?
This year I’ve been learning about relational databases and how to design them. In order to strenghten my knowledge, I’m trying to design and implement a database using Python and sqlite3. The database is about a textile company, and, among other thigs, they want to keep information about the foll…
SQLite – Select posts based on tags
I have two tables in my database: posts, which contains post id and other informations, and tags. In tags, there is a column post_id and a column tag. My idea is to store each tag in a separate row. For instance, a post with id 1 and tags foo, bar and eggs would be stored in tags as Which is
SQL statement to list final course grade for students taking two specific courses
This is my statement for the final course grade for all those students taking MATH, how can I adjust this so it displays the final course grade for MATH, but only for students who are taking both MATH and ART ? Answer You can do it with conditional aggregation: First, you should use a proper join with an ON c…