Questions tagged [sqlite]
A popular open-source embeddable (i.e. non client-server) RDBMS implemented as a C library.
-1
votes
2answers
38 views
How to update value increment by 1 SQL [on hold]
I have a table name downloads where I need to add value colum order + 1
downloads
order Name dlid website
0 Jon 51 A
1 Jim 54 A
2 Jack 55 ...
-2
votes
0answers
21 views
How to re-zip sqlite file as a .jet file [on hold]
I have a .sqlite file and the developer zip it as a .jet file. Now I can open it with 7zip, I can edit the db but at the end I couldn't re-zip again as a .jet file. I tried every solution that I saw ...
2
votes
1answer
48 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
12 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
18 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
18 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
18 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
25 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
39 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
29 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
35 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
9 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
25 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
50 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
19 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
48 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
18 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
37 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
35 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
55 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
24 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
61 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
177 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
20 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
127 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.
...
1
vote
2answers
46 views
Defining a primary key: Customer numbers are unique only within a country but may be duplicated across different countries
I am facing the following problem:
I have a table with customer information like the customer number, i.e. customerID set up as the primary key, and, besides a number of other data, also a country ...
-2
votes
1answer
36 views
How to use substr a column and fetch value between limits
Let assume that there is a table as Sample and Name, Country, UnitID, HS column of type as text.
-- Table
CREATE TABLE #TempTable (
Name text,
UnitID text,
Country text,
HS text
)
...
0
votes
2answers
60 views
How to SELECT last row of every hour?
I have a table (in database) with one minute data in each row.
Like this:
Date_time Value
2018-11-01 10:58:00 189.0
2018-11-01 10:59:00 189.0
2018-11-01 11:00:00 189.19
2018-11-01 11:...
1
vote
1answer
32 views
How to change date format using SQL?
I have column with strings of dates with format: "dd.mm.yyyy".
I need to convert the date format contained in the strings to ISO8601 format: "YYYY-MM-DD" for the whole column.
1
vote
1answer
28 views
Constraint via Foreign Key
I have the following schema in SQLite:
CREATE TABLE calendars
(
id INTEGER PRIMARY KEY,
title TEXT NOT NULL
);
CREATE TABLE entry_types
(
id INTEGER PRIMARY ...
0
votes
1answer
26 views
How to import SQL file which has no N prefix
I have a large .sql file in Persian Language. The file is exported from sqlite db browser and has no N prefix for unicode fields. How can I reuse it in Sql Server?
This is sample of insert query in ...
0
votes
0answers
33 views
SQLITE PHP Like Query wont return results on all rows
I have created a SQLite query in PHP which queries user input and checks it against the database, however when I run the query it just displays everything in the database. and if I was trying to ...
0
votes
1answer
110 views
Relation between Faculty, Student and Course tables [closed]
What should be the relation between following tables. Confuse whether i create an enroll table with student and course or courses_assigned table using faculty and course and then create an enroll ...
0
votes
1answer
165 views
SQLite - is it faster to use SELECT NOT EXISTS / EXISTS or IN / NOT IN in a WHERE clause?
In SQLite's query planners is it generally faster to do this kind of thing?
SELECT foo.bar,foo.baz
FROM foo
WHERE NOT EXISTS (SELECT lol.haha FROM lol WHERE lol.haha = foo.haha)
... or ...
SELECT ...
0
votes
1answer
27 views
SQLite how to store translations
Im looking for advise because my experience is not really good in SQLite.
My table look like this:
CREATE TABLE IF NOT EXISTS Route (
id integer PRIMARY KEY UNIQUE AUTOINCREMENT NOT NULL,
name ...
0
votes
0answers
57 views
Slow insert on very large tables, (even in transactions)
I have a table with about 50 million rows, the table itself is fairly simple
CREATE TABLE Word (id integer PRIMARY KEY, word TEXT UNIQUE)
I then do an insert
INSERT OR IGNORE INTO Word (word) ...
3
votes
3answers
460 views
Do all Relational DBMSes store table tuples in a clustered index based on primary key by default?
So i was reading MySQL innoDB, which apparently stores table data by default on a clustered Index (b+tree) based on primary key, and the tuples are in the leafs of that b+tree
https://blog.jcole.us/...
1
vote
0answers
38 views
How get process that is locking Database on SQLite?
I'm developing a iOS app using SQLite. I've got a class to manage SQL's operations, basically read, write and update. In many cases I get a SQLITE_BUSY error, so I want know which process is using the ...
1
vote
2answers
46 views
Software Uses SQLite and Has Frequent DB Lock Errors. Zero Access to Code. Anything we can do?
I've written an API for a client that accepts orders from clients and converts them into print jobs that go to an HTTP bridge into the company's RIP software. On the backend, the bridge is reading and ...
1
vote
1answer
53 views
Retrieving Parent Hierarchy Entity
I have the following table structure:
CREATE TABLE "Material" (
"MaterialId" TEXT NOT NULL CONSTRAINT "PK_Material" PRIMARY KEY,
"Content" TEXT NULL,
"Title" TEXT NULL,
"Paid" INTEGER ...
0
votes
2answers
61 views
in sqlite how to check the table is empty or not?
I need the check the table is empty or not
employee table contains columns are id name
id name
1 john
2 hunter
3 smith
4 will
1
vote
1answer
55 views
Commit() takes 50 seconds after inserting about 100000 rows
I have a sqlite database that is, (I think), fairly small.
I has about 1 million rows in a couple of table and the total space on disk is about 150 Mb.
I then open a transaction and insert about ...
2
votes
1answer
75 views
Why does a SELECT with no WHERE cause a full table scan, (or does it)?
I have the following table with ~2000000 rows
CREATE TABLE TableA (idA integer PRIMARY KEY, idB TEXT UNIQUE)
From time to time, I need to process each rows, the order does not mater.
So I process ...
2
votes
4answers
191 views
What is a DBA's definition of “batch”?
I've heard it being used in relation to SQLite sometimes but never completely understood what it means.
1
vote
2answers
365 views
SQL: How to Select Multiple Row Data for the Same ID?
My data is like this:
ID col1
0000001 00020
0000001 00023
0000001 00019
0000001 00010
0000003 00030
0000003 00020
I want to find which IDs have a combination of 2 values in the column 'col1' (...
1
vote
3answers
438 views
How to increase column limit of a table in SQLite?
SQLite: How can we configure the maximum number of columns allowed in a table?
1
vote
2answers
60 views
Another newbie query on 2 tables
I'm having trouble working out this apparently simple query, and would appreciate any suggestions for a solution using Sqlite.
I have two tables t1 and t2 :
Eg.,
Table1 :
--------
Key Data
A ...
1
vote
0answers
44 views
SQLite database: many-to-many optimization (simple but large example) without IDs available?
I'm a beginner looking for people who can think through these things more easily to see where I've messed up. I have a many-to-many table that I am trying to optimize for speed of creation, speed of ...
1
vote
0answers
25 views
Getting text surround search queries?
I'm looking for a way to retrieve search results from an sqlite3 db, including some text around the query, to show context in the search results.
I'm not finding any examples of this. Is this ...
0
votes
1answer
85 views
Python SQLite3 horizontal partitioning by string column
I have a 30 GB SQLite 3 database with a table containing 25 million rows. I want to group the table by a string column and create a new table for every group of records, named after the string and ...
