Questions tagged [sqlite]
A popular open-source embeddable (i.e. non client-server) RDBMS implemented as a C library.
448
questions
2
votes
0answers
18 views
History of updated rows
Imagine we have a CRUD application in Python and are using SQLite.
Let's say we have in SQLite a table called CUSTOMERS:
CREATE TABLE CUSTOMERS
(ID INTEGER PRIMARY KEY AUTOINCREMENT,
NAME TEXT
...
0
votes
1answer
37 views
sqlite - very slow performance of WHERE EXISTS without index on EXISTS
I've got a strange case of (very) slow performance of an update query when I don't have an index on the table in the EXISTS clause.
For example:
UPDATE tbl1
SET col1 = "Y"
WHERE EXISTS (SELECT ...
0
votes
1answer
24 views
How can I maintain the dateonly field
Hello I have a table in Postgres and one of the fields is type Date with the format '2019-09-10' and I'm transferring this table into a SQLite one BUT in this SQLite I'm getting this field as Date ...
0
votes
0answers
13 views
Determine whether SQLite database is locked
I've read other answers on how to detect whether the SQLite database is locked, and it suggests to use sqlite3_busy_handler/sqlite3_busy_timeout. I'm using Qt which has support of SQLite databases but ...
0
votes
1answer
17 views
How to rank an aggregation in each newly formed group?
The problem below that I would like to solve sounds quite simple but it is quite difficult for me to arrive at the solution with SQL.
Let's say I have a simplified ERD that looks as follows:
This ...
0
votes
0answers
28 views
How do I migrate mysql .frm and .ibd files to sqlite?
Mysql seems to not work anymore on my machine. I have spend way too much time trying to fix it but without success.
But I found out that sqlite does all the stuff I need so why not just change to it. ...
0
votes
1answer
29 views
SQLite - Get all other rows with any duplicate column value
I have a table of users, with information about them and the combination of the fields and UserName is unique (A row as a whole, not only on one field)
Users
+----------+--------+--------+--------+
|...
0
votes
2answers
30 views
Database design question: Tags with values(duration) linked to them
I am creating a database that takes in daily inputs such as:
Date
Events in that date (e.g. project foo: 5 hours, bar: 2 hours, etc...)
Rating of the date
There's more but these are probably the ...
0
votes
1answer
34 views
Syntax error in simple trigger
I have a SQLite table with these fields among others
Fld_QuoteNumberRoot string
Fld_Revision integer
Fld_QuoteNumber string
I need to fill ...
1
vote
2answers
66 views
how can I ensure database connectivity on a spotty network? (and allow for entirely offline connectivity)
My place of work has a pretty spotty network... we have quite regular (though usually brief) outtages.
Not too long ago, my team was logging application data to a SQL Server owned and managed by IT (...
2
votes
2answers
34 views
How to find exact string from a sentence
I'm using SQLITE and I'm trying to find the exact word on a sentence, but I'm not getting just the word I'm getting for instance if I'm looking for Water I'm getting also aWater, wateri24 and that's ...
0
votes
1answer
28 views
Listing IDs that match values in two columns of a table
I'm using SQLite 3.27.2-3 on Debian Buster/stable. I could use some other DB, but for my use case (LuaTeX), something with low overhead seems to make sense.
My use case is that I have a letter (in ...
0
votes
3answers
41 views
SQLite: Leave only n rows for each field
I have a table
+-----------------------------+---------------+-----------+
| Painter | Painting | Rate |
+-----------------------------+---------------+-----------+
| ...
1
vote
2answers
36 views
How to set “PRAGMA foreign_keys = ON” each time I'm connecting to sqlite db via Datagrip
In my pet project I'm working with small sqlite db and I've just came to realization that, in order to ON DELETE CASCADE actually do the thing, I need to set PRAGMA foreign_keys = ON each time I'm ...
1
vote
1answer
36 views
Is there any advantage to putting some columns in another table, or should I just put them in the same table?
I have a SQLite database with about 50 tables in it. The largest table contains location data and it has 6,000,000 rows.
An example of some of the columns in this table are:
location_contrivedkey --(...
1
vote
3answers
75 views
Split Multiple Columns Into Multiple Columns by Delimiter
I would like to split multiple columns into multiple columns by a delimiter. I am using SQLite, but I am open to importing into another DBMS, if necessary.
An example table, showing how the table ...
0
votes
0answers
36 views
Common way to handle a changing reference in dependency of status
I am new to SQL an SQLite, so this is probably a silly question.
I am designing the data model for a commercial application. While documents (like quotes) are being created, I need to reference to ...
1
vote
2answers
63 views
Deduplication/normalization of string column on insert
I have a database of text tuples. Imagine e.g. full file paths + standard comments. Then you dump large file tree and generate comments for files. A file can have lots of comments, but their text ...
0
votes
0answers
37 views
Laying out the database schema for an interactive novel
I'm trying to create an interactive story telegram bot on Node-red. To store the story I'm using SQLite.
How it works: Bot shows you a piece of the story, then shows some answers (buttons) regarding ...
0
votes
1answer
94 views
Updating a column in one table based on matches with column in another table
I have 2 tables say table1 containing says 3 columns col1, col2 and col3 and table2 which contains col1 and col2. I want to set col3 = 'some value' where table1.col1 = table2.col1 and table1.col2 = ...
0
votes
1answer
14 views
How to AUTOINCREMENT against another column rather than the whole table in sqlite?
I'm using a table to track messages sent in several different channels.
I'd like to be able to track the index of each message on a per-channel basis. 'index' is a forbidden word in SQLite so here I'...
1
vote
1answer
49 views
How to insert values to a table from 2 foreign keys
Hey guys was wanting some help. I want to add the same value for Student_ID that is in the Studentinfo table in the GradeInfo and also wanted to add the same value for CourseNo that is in the ...
0
votes
1answer
58 views
What is the correct way to select rows from either side of a bridge table?
I want to do an SQL query for three columns. The database has five tables, two of which are bridge tables for the other three. When I attempt to select from the tables, I get a very large number of ...
0
votes
0answers
41 views
How to deal with refrencing a foreign key to two tables?
I have an interesting situation where I am making a data base off someone else's design that has to be as rule complaint as possible.
The issue I have is with the section shown above as it is a sub-...
0
votes
3answers
51 views
Usage of aggregate function with when self-joining a table
I want to find people from a database who has a weight bigger then the average weight:
SELECT *
FROM Player p1, Player p2
WHERE p1.weight > avg(p2.weight)
But I have an error:
Result: misuse of ...
0
votes
1answer
36 views
Looping over column names in SQLite and running a command for each column name
I'm trying to run the following command
UPDATE attachments SET colname=TRIM(colname);
on every column name in a SQLite table.
I found the following approach to getting a list of column names in
an ...
1
vote
1answer
20 views
Which part of a DB bulk insert takes the most time?
One of the most common tip with regards to DB writing is to group your inserts and execute them as a single transaction. My question is, which part of the [begin transaction] - insert - [commit ...
2
votes
1answer
92 views
How to design a parent-child relationship that includes child priority/order
I am trying to design a table with a string column in SQLite3, I believe this is necessary to connect the tables parent to child, which represent entity types that are involved in a connection with a ...
0
votes
1answer
20 views
SQLite return sum of duplicate rows based on array
If I have a simple table fruits that looks like this:
+---+---------+------+
| | name | lbs |
+---+---------+------+
| 1 | apple | 0.25 |
| 2 | melon | 3.00 |
+---+---------+------+
In a ...
0
votes
1answer
23 views
SELECT column for a row only if its value is not empty
I want to display a certain column only if its value is not empty.
Pseudo code:
SELECT column1, IF_NOT_EMPTY(column2), ... FROM table;
Will display all columns except column2 if column2 is empty.
-1
votes
1answer
33 views
MySql data export to import in sqlite [closed]
This question has been asked a few times. However the links all lead to how to export the db such as schema and import that into sqlite. My scenario I already have an sqllite db with the tables. I ...
0
votes
1answer
19 views
What kind of a lock does IN keyword takes in SQL
If I have a query in which I have used IN keyword than what kind of lock will be taken by IN keyword.
Query is :
UPDATE table1 SET b_id where id in (SELECT id from table1 where id IS NULL )
I ...
0
votes
0answers
26 views
Getting Database is locked error [duplicate]
I have a query in which I am updating a table and have used IN keyword in the query. The query is something like this:
UPDATE q
SET b_id = 123
WHERE id IN (SELECT id FROM q WHERE b_id IS NULL ...
1
vote
2answers
170 views
sqlite Json grouping json_each
I want to generate json grouping users by year.
I have got a "user" table in sqlite with the following columns:
date
name
surname
value
I would like to create a JSON with the following structure:
...
1
vote
1answer
52 views
Multiple concurrent connections to same large SQLite file on a network share
I have a 7GB SQLite file and I am running multiple queries on it from different virtual machines in the cloud. Some of the queries are running in Python using the built in sqlite3 package, and others ...
1
vote
1answer
55 views
Top 10,20,30 in Group
Morning all! I have a program and database I created that pulls down data from a website (player cost for MLB teams for a game) and I aggregate that data and use it for various things.
One of the ...
0
votes
0answers
13 views
Want to be able to use sqlite to organize photos so that they can be selected by who is in the photograh
I want to create an sqlite db that has photograhs of a band's various gigs they have played at.
I want to be able to search the database by event,but also (and more importantly) by who is in the ...
0
votes
1answer
29 views
How to multi-match two tables efficiently?
I'm relatively new to database-design and want to create a SQLite database for an application. The application needs to handle lots of objects and assign them to a smaller list of categories. Both ...
0
votes
1answer
112 views
Overhead of triggers and foreign key constraints?
I have a sqlite database which is accessed by two programs. There are a number of invariants/constraints which should always hold. In addition to the usual foreign key constrains between tables, ...
0
votes
1answer
34 views
sqlite3 command does not save database after creation?
I'm migrating from MySQL. I'm having trouble scripting a SQLite database creation. Similar code worked well for MySQL.
It appears the database and tables are created, but then sqlite3 command does ...
0
votes
1answer
271 views
Comparing multiple tables in Sqlite 3 using python:
I am quite new to SQLITE3 as well as python. I am working on a project where I have one database with about 20 tables inside of it. One table is for user input and the other tables are pre-loaded with ...
0
votes
0answers
28 views
NoSQL big DB retrieve performance
A question about retrieving performance for simple but bug key-value DBs on python3.
I am working on a pipeline to compare several entries based on key-values (genetic variants found in patients with ...
0
votes
1answer
83 views
Group json data in db using a query
I have a db whose values are as follows:img Can I call the json value of listing_id(23,27....so on) in properties column(with having row value as "listing") using a single query, I would Really ...
1
vote
1answer
41 views
How to correctly use group by for getting the record with the closest time for each rec_id
Background
I have a table records which stores rec_id, rec_time and rec_value. A record is stored for each rec_id only when its rec_value changes. I would like to query the value for a bunch of ...
5
votes
1answer
60 views
Why an incorrect JOIN using correlated sub-query is so much slower
I'm doing some fairly lightweight data massaging/cleaning and ran into a problem where one version of JOIN using a correlated sub-query (probably an erroneous one) ran much much slower than what I ...
1
vote
1answer
51 views
SQLITE: Select row ID based on only when the column all the values are same
I have a table where more than one rows with same ttid. I would like to get ttid rows based on status column is done.
tb tbid ttid status
-----------------------------
216 6 5 done
216 6 ...
0
votes
0answers
133 views
How to ignore timezone in sqlite?
I have a table:
CREATE TABLE "date" (
"datetime" TEXT,
PRIMARY KEY("datetime")
);
and rows:
datetime
--------
2019-02-01 01:30+03:00
2019-02-01 02:30:10-03:00
...
For example, my timezone ...
1
vote
1answer
537 views
SQLITE: Multiple foreign key referenced to multiple table cascade delete
In SQLite I would like to delete parent table row and it has to delete all the related child tables. I have gone through all the questions in StackExchange and other websites but my query no where ...
0
votes
1answer
32 views
How to denormalize many-to-many relation without nested select?
I have the following schema:
house has many students, student has one house
gender has many students, student has one gender
game has many students. student has many games
For instance, consider
...
1
vote
1answer
385 views
how to filter the resulted table in dbeaver?
I have big sqlite table that I filtered one column with IN statement from the GUI. It worked will but I want to filter another second column also based on the first selected. eg full table looks like.
...
