I’m creating a SQLite database where I have 3 tables: transactions, categories, sub-categories in the transactions table, there is a column “category” with a foreign key to the categories table in the sub-categories table, there is column “parent-category” with a foreign key to t…
Tag: sqlite
Generalized way to remove empty row given by aggregate functions
Example: The below query would give an empty NULL row when no data is present. The NULL row result looks like this: MaxNumber 1 NULL To detect if the query returns data or not I had to do this: Returns 1 if a max number exists and 0 if empty table. Is this how we generally tackle the empty row
python function parameter control
I have a function called “getBooks”, and this function is actually a combination of 2 functions. one function must work without taking the ‘name’ parameter, the other must work by taking the ‘name’ parameter because i have to change the sql template according to the ‘…
Why does this suggested solution prefer a different way of calculating percentages based on aggregations?
I’m working through a problem set from CMU’s public db systems course. I have the following two tables: Order Id CustomerId EmployeeId OrderDate RequiredDate ShippedDate ShipVia Freight ShipName ShipAddress ShipCity ShipRegion ShipPostalCode ShipCountry 10248 VINET 5 2012-07-04 2012-08-01 2012-07-…
Using INSERT and UPSERT in SQLite trigger to update one table from another table?
I’m attempting to create a SQL database for an Auction company I work for. The issue I’m running into is creating triggers that update other inventory tables from the main inventory. I’ve tried quite a few different things, but can’t wrap my head around how to get this to work. The lat…
How to optimize querying multiple unrelated tables in SQLite?
I have scenario when I have to iterate through multiple tables in quite big sqlite database. In tables I store informations about planet position on sky through years. So e.g. for Mars I have tables Mars_2000, Mars_2001 and so on. Table structure is always the same: Thing is that for certain task I need to it…
PYTHON SQLITE selecting multiple where conditions that may or may not exist
Working on some code that uses pysimplegui as the UI and SQlite for the data sorting. I’m using SQLite’s execute function to select data based on input from the user in the UI through variables. For example user wants to search for part name they input all or part of the name into the box, hit the…
Insert into table where id is not null and auto increment sqlite3
I’m trying to add some values into a table, but I get an error for not manually inputting the id. When I remove ‘not null’ the id just stays null Answer The answer Shawn gave worked, which is to remove AUTO_INCREMENT https://sqlite.org/autoinc.html
Why does sqlite3 not escape metacharacters with parameterized values?
I’m making a database interface in python for a project where i need to encrypt everything that goes into my database and decrypt everything that goes out of it. Unfortunately some of my characters get encrypted to meta characters in sql and this gives trouble when selecting something from the database.…
How to correctly compute cumulative distribution on sample dataset
Consider the following table and data set. I want to create a cumulative distribution that shows cumulative % of total Callers based on the values in the Callees column, for the group. For example, for the value Callees = 1, I would like the first output row: Where 0.6662 is 505 divided by total number of Cal…