Questions tagged [sqlite]
A popular open-source embeddable (i.e. non client-server) RDBMS implemented as a C library.
0
votes
1answer
10 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
0answers
15 views
Select data evry 15 minutes from sqlite [migrated]
Hello so i'm new in sqlite and php
and i have a function that i want it to be executed every 15 minutes but i don't know how would i do it.
select command
from common_commandlog
where datetime ...
0
votes
0answers
15 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
10 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
17 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
24 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
48 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
22 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
20 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
33 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
16 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
38 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
2answers
34 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
43 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
30 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
24 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
24 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
32 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
67 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
70 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
53 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
455 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
29 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 ...
0
votes
2answers
28 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
49 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
33 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
50 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
62 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
179 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
236 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
193 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
59 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
36 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
24 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
61 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 ...
1
vote
1answer
62 views
SQL squash rows returned when joining several tables
I have a database which has a table for the main items, lets call it A with primary key 'id'.
Then there are a number of tables (B, C...), each corresponding to some attribute that each item can have ...
1
vote
1answer
25 views
Replacing one row with multiple similar rows? [closed]
I'm trying to update an inventory in my database. I have an item number, location, and quantity that I would like to expand to an item number, location, and serial number. Example: Item Number, Item ...
-1
votes
1answer
25 views
How to group rows
I don't understand how to retrieve the results from the following tables depending on some prerequisites.
I have 2 tables
Table "project"
| id | category_id | count_view
| -- | ------------| ------...
2
votes
1answer
81 views
Directly go to Hadoop or use SQLite/Postgres as a stepping stone towards Hadoop/Spark? [closed]
In our organization, some people are working on setting up Hadoop with a lot of security restrictions etc. By the rate of progress, this seems complex, especially considering security restrictions, ...
0
votes
1answer
560 views
How can I disable database lock in sqlite with python?
I made a application in python and it should read and write simultaneously in multiple instances(multi CMD widows)(multi-threading). each thread is configured independently so it will not change the ...
2
votes
2answers
45 views
Any benefit in running ANALYZE after bulk insert in table with only PRIMARY KEY index in SQLite?
If I create a table in SQLite that has only the PRIMARY KEY index, is there any role for running ANALYZE after a bulk insert, or will that key automatically have been kept up-to-date by necessity? In ...
3
votes
1answer
108 views
How large is SQLites minimum data storage overhead and what generates them?
Motivation:
In my company there exists the (probably strange) idea to store pure time series data in a normal SQL table (instead of using BLOBs or just binary files which would probably be a better ...
1
vote
1answer
30 views
SQLite DB from file vs “service”
I apologize if this question has been asked before, I am not exactly sure how to phrase it.
I am currently creating a SQLite database for use in a NodeJS application. The DB will need to be read from ...
0
votes
1answer
85 views
SQLite database becomes corrupt
I created a single table SQLite db from a csv file using python. I was able to open, browse and query the database using DB Browser for SQLite (browser) immediately after creating it. However, when I ...
1
vote
1answer
69 views
Updating table schema with correct datatype in sqlite
I know that importing from CSV into SQLite treats all fields as text. According to How to import csv file to sqlite with correct data types, one can use
UPDATE MyTable SET MyColumn = CAST(MyColumn ...
0
votes
1answer
220 views
Is there a way in sqlite3 to set .width of all columns to be equal to the width of the column header?
I am using:
SQLite version 3.21.0
I look for away to prettify my SQLite SELECT shell output (to make it more human readable, in essence). This answer helped me a lot.
Though, now I am wondering if ...
0
votes
1answer
22 views
Find rows that have the same values in another column
I have the following table.
User Permission
John 1
Mary 1
Anne 1
Joe 2
Marie 2
Nick 3
I want to select and group users with the same permission number. Is this actually possible? I have ...
1
vote
3answers
42 views
What's the correct syntax for this Postgres query in MySQL?
I have a query which works well in our PostgreSQL db, but we now need to run our app on a MySQL DB as well.
The query takes an array of IDs and positions, and updates each record with given ID with ...
