Questions tagged [sqlite3]
The sqlite3 tag has no usage guidance.
37 questions
1
vote
1
answer
41
views
Issues with Self-Referencing Foreign Key in SQLite
I'm having trouble with a self-referencing foreign key in SQLite. I've created a table to store employee details, where each employee can have a manager who is also an employee. Here's the table ...
0
votes
2
answers
64
views
How can I select all rows from two SQLite tables, but prefer one if there are matching rows in both?
I have two tables, each with a round and an id column and additional data columns. One table is the planned version, the other one is the actual one. E.g.:
planned:
round | id | value
------+----+-----...
1
vote
2
answers
24
views
SQLite Join 2 tables with table1 having two columns that reference table2
I have two tables:
jobs
CREATE TABLE IF NOT EXISTS jobs (
job_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
job_name TEXT,
prime_desc INTEGER NOT NULL REFERENCES descriptions(desc_id),
...
-1
votes
1
answer
50
views
Return data in the same order that I have inserted it with Sqlite3
I'm working with, I think, the lastest version of Sqlite and C++. I have a table with data, and I need to return this data in the order I inserted it.
Do I have any guarantee that the columns will ...
0
votes
1
answer
32
views
Create composed key (YEAR+COUNT) field with trigger after insert in SQLITE
I have a table Product with two columns: name of product and price
CREATE TABLE IF NOT EXISTS YearCounters (
year INTEGER PRIMARY KEY,
counter INTEGER DEFAULT 0
);
-- Product Table
CREATE ...
-1
votes
1
answer
62
views
How do I partition my database by rowid and query the partitions properly?
Obligatory I'm a beginner at database management. To be specific about my situation, I'm working in SQLite3 in Python as a means to an end for now for a finance project. The table is called ...
0
votes
2
answers
61
views
Select random records until a cumulative total is exceeded
I have a table of videos with an id, skill_level and duration column and I am attempting to write a query that will return a playlist of distinct videos that has a total duration exceeding X.
select ...
-1
votes
1
answer
855
views
sqlite3.OperationalError: no such column: stock_name
#user_input.py
from flask import Flask, request, jsonify
import sqlite3
app = Flask(__name__)
def process_user_input(data):
# Retrieve user input from the data
stock_name = data.get('...
1
vote
1
answer
102
views
Is frequent opening and closing of the SQLite file expensive operation?
I need to build a multi-tenant solution with a requirement for strict data isolation (meaning isolated Databases at tenant level). Even the authentication is different for each tenant?
I find that ...
3
votes
2
answers
356
views
Sqlite comparison of the same operand types behaves differently
Based on Sqlite docs: https://www.sqlite.org/datatype3.html#type_conversions_prior_to_comparison, especially this statement:
If one operand has INTEGER, REAL or NUMERIC affinity and the other operand ...
1
vote
1
answer
165
views
insert trigger to auto-set key fields for composite primary key
Basically the next step for Error "UNIQUE constraint failed" with composite primary key:
I created a trigger after insert to set SEQ to the highest SEQ plus one if it's less than one.
...
0
votes
1
answer
1k
views
Error "UNIQUE constraint failed" with composite primary key
With littele SQL(ite) practice, I tried to create a table with a composite primary key:
CREATE TABLE NAMES (
TYPE INTEGER NOT NULL,
SEQ INTEGER NOT NULL,
NAME VARCHAR(20),
PRIMARY KEY (TYPE, ...
0
votes
1
answer
24
views
Name a column with a string from the row
I have the following query, which works fine:
select
Produkt.Name,
max(case when Substanz.ID == 1 then Inhalt.Menge end) as "Histidin",
max(case when Substanz.ID == 2 then Inhalt.Menge ...
0
votes
1
answer
18
views
coalesce works only once
I have a table with redundant values in the first two columns:
sqlite> select * from amino where "lebensmittel" = 'Walnüsse';
Lebensmittel Protein pro 100g Histidin Isoleucin Leucin ...
1
vote
0
answers
16
views
is it possible to repair a partially downloaded sqlite3 file
I was only able to partially download a SQLite3 database file, is it possible to repair the file so that it can be opened?
Unfortunately the device where the file came from is out of power and ...
0
votes
1
answer
50
views
How I can find a best match on string records that use * as a wildcard?
In my application that I am making, I have the following table
records
------
id PK
pattern TEXT
In this database I have records like this
id
pattern
1
https://*.google.com
2
https://google.com/*
...
0
votes
2
answers
253
views
How I can check whether a column contains a substring of a given searchterm?
In sqlite I have the following table
CREATE table IF NOT EXISTS redirect (
id INTEGER PRIMARY KEY AUTOINCREMENT,
url_from TEXT not null,
url_to TEXT not null,
...
0
votes
1
answer
47
views
How I can make a single trigger for both create and update at sqlite3?
I made the following table:
CREATE table IF NOT EXISTS redirect (
id INTEGER PRIMARY KEY AUTOINCREMENT,
url_from TEXT not null,
url_to TEXT not null,
...
0
votes
2
answers
64
views
What I make wrong and Cannot inset a value at sqlite3 using custom trigger?
I am making the follwing table at SQLITE3:
CREATE table IF NOT EXISTS redirect (
id INTEGER PRIMARY KEY AUTOINCREMENT,
url_from TEXT not null,
url_to TEXT not null,
...
3
votes
2
answers
3k
views
Retrieving the local timezone inside SQLite
I have an SQLite table containing a "last changed" column with a date in the format e.g. "2022-11-07T11:51:06+01:00". Coreutils' date outputs this by using the following command:
...
0
votes
1
answer
800
views
Summing/Grouping LAG() results in SQLite query
I have a table with incremental counters in an SQLite database. A stripped-down example would be:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "counters"(date TEXT ...
0
votes
1
answer
1k
views
Archiving SQLite DB with WAL mode
I have an SQLite database gapongasi.db configured using WAL mode and I want to have daily archive with tar(1). With WAL, there are two files being used (1) gapongasi.db-wal and (2)gapongasi.db-shm, ...
0
votes
1
answer
665
views
Is there a way to VACUUM an sqlite3 database in parts to avoid OperationalError: database or disk is full/reduce size of sqlite3 databse
I have a rather large sqlite3 database that I've built/populated through python over a few months. I have done many inserts/deletes etc while putting it together and it now uses the majority of my ...
3
votes
2
answers
2k
views
1st process writes, 2nd one reads -- " SQLITE_BUSY: database is locked "
I have 2 processes that use the same Sqlite3 database.
1st one -- in NodeJs, is a program which constractly writes data into a database, with the frequency of around 1 write / 1 second. It only ...
0
votes
1
answer
2k
views
Why UPDATEs are faster when WHERE key is sorted in SQLite? How to improve performance?
I have a large database and a file with ID-Name pairs in each line, where ID corresponds to the primary key of the table in the database.
I need to write all the names from the file into the database ...
1
vote
0
answers
77
views
How do I make sure a specific TRIGGER will be executed only when other TRIGGERS are finished executing and return?
foo table
| row# | foo_col1 | foo_col2 | foo_col3 |
| -----|----------|----------|----------|
| 0 | 1 | 5 | 1 |
| 1 | 2 | 3 | 3 |
| 2 | 3 | ...
0
votes
1
answer
281
views
Sqlite spec: 4.2. Type Conversions Prior To Comparison
Based on Sqlite docs: https://www.sqlite.org/datatype3.html#type_conversions_prior_to_comparison, especially this statement:
If one operand has INTEGER, REAL or NUMERIC affinity and the other operand ...
2
votes
1
answer
59
views
Understanding `WHERE x = NEW.x` Syntax
I'm writing an application that uses SQLite to store court information obtained from a government database in a client table. I've set up a DB Fiddle here, which is a simplification of my application.
...
0
votes
1
answer
422
views
SQLite: excluded int primary key null value replaced with next auto value
I'm trying to create generic upsert query builder with specific behavior. Table has two unique constraints: primary key itId and column slug:
create table it (
itId integer primary key -- an alias ...
1
vote
0
answers
255
views
Proceeds JOIN only if condition met on the Owner table
I have many hasOne relationships like this:
components
component_headings
component_images
Here one Component can only have one hasOne to any of [headings, images].
Logically when a Component.type =...
0
votes
1
answer
502
views
formating dot commands in sqlite3
My first SQL database im using sqlite3 in idle on linux. Everything is working fine but I want my table to display in an easier to read format in the terminal. been reading about dot commands eg .mode ...
1
vote
1
answer
493
views
Random value duplicated, cannot select N random pairings, subquery not refreshing - sqlite
The Problem
Using SQLite v3.35.4 and v3.36.0 I have a first_name table and a surname table that have a list of common names. I want to produce N number of pairings into a new table.
I wrote this ...
0
votes
1
answer
1k
views
SQLite Aggregation then Transpose
I am currently uploading my solar inverter data to PVOutput using a Linux machine and a scratch built Perl script. I want to add the meter data from my enrgy supplier. I have an 87,000 row CSV file ...
0
votes
0
answers
234
views
How to create two sqlite triggers for two tables without conflict?
Below are my example queries:
"""
CREATE TABLE IF NOT EXISTS projects (
Project_ID TEXT UNIQUE,
Initial_Project_count TEXT,
Additional_Project_count TEXT,
...
0
votes
1
answer
3k
views
Is it possible to create Dynamic SQL statements purely in SQLite? [closed]
A lot of modern RDBMS have a feature called Dynamic SQL (MS SQL Server, for example) which allows you to build your SQL statements in strings and then execute that string of SQL.
Does such a feature ...
8
votes
2
answers
413
views
Sqlite: Finding the next or previous element in a table consisting of integer tuples
I have a sqlite table called tuples defined like
create table tuples
(
a INTEGER not null,
b INTEGER not null,
c INTEGER not null,
d INTEGER not null,
primary key (a, b, c, d)
) ...
0
votes
1
answer
24
views
How can I display multiple column records in the same row?
I want to display all related information in to the same row column. Currently my output and query are the following:
Trader
Items
Prapor
Weapons
Prapor
Wearables
Prapor
Weapon Modifications
...