Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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 ...