Questions tagged [sqlite]
A popular open-source embeddable (i.e. non client-server) RDBMS implemented as a C library.
465
questions
0
votes
1answer
28 views
What standard SQL isolation levels do the SQLite isolation levels correspond to?
There are four standard SQL isolation levels: Read Uncommitted, Read Committed, Repeated Read, and Serializable. (c.f. Database System Concepts 6ed.)
In SQLite, there are three isolation levels: ...
0
votes
1answer
29 views
FOREIGN KEY Constraint Failed: SQlite3
I have two tables, created as below,
sqlite> CREATE TABLE Artists(
...> ArtistId INTEGER PRIMARY KEY,
...> ArtistName TEXT NOT NULL
...> );
sqlite> CREATE TABLE Albums(
...&...
1
vote
1answer
41 views
Can/Should rarely used columns be placed in a different table for performance?
I have a table that contains data (blobs). Data is often read, rarely inserted, and never modified.
For all of this data, I compute hashes that I also store in the DB. These hashes are only checked ...
-4
votes
1answer
61 views
how to get all the table existing in a database [closed]
import pandas as pd
import sqlite3
conn = sqlite3.connect('Db-IMDB.db')
imdb = pd.read_sql_query('''SELECT table_name FROM information_schema.tables''',conn)
but it is giving error:
DatabaseError: ...
2
votes
0answers
64 views
+100
Column length error querying SQLite via SQL Server Linked Server
I am attempting to query SQLite to copy data into corresponding tables in SQL Server. This is the first stage of an ETL process I'm putting together.
Windows 10 Pro, SQL Server 2017 Developer Edition,...
0
votes
0answers
12 views
How do I check if a value is unicode in sqlite?
Here is a table -- Proxy. I want to query out entries which location is nonunicode.
select * from proxy where unicode(location)=0
It seems sqlite doesn't have a unicode function.
0
votes
0answers
13 views
sqlite db design for pdu routing
I have no SQL experience whatsoever, and I'd like to map power distribution cabling for monitoring and management purposes using sqlite.
A managed PDU (power distribution unit) can turn on or off any ...
1
vote
0answers
89 views
Storing JSON in Postgres as BYTEA or other types
I need to have a generic key-value data-store in Postgres and Sqlite3. I originally planned to store this as a JSONB type but the SQL statements I execute needs to be compatible with both Postgres and ...
0
votes
0answers
104 views
SQLite: Extract data from the `-wal` file
Yesterday I accidentally deleted an SMS banking notification thread on my Android device.
A classic case: I didn't backup the messages for very long time.
After flashing the recovery and rooting the ...
0
votes
0answers
17 views
seafile-server error: sqlite3_prepare_v2 failed - disk I/O error (or http error?)
Two days ago my seafile-server (v7.0.5) stopped working, with error logs that look like below. What could be going on - not clear to me if this is an error in the database, in http-server, or in ...
-1
votes
1answer
21 views
SQL beginner, syntax error with WHERE [closed]
I'm sorry, but I don't find the problem. Can somebody help me please?
I have two tables:
CREATE TABLE "USERS" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"deviceaddress" TEXT NOT NULL UNIQUE,
...
0
votes
2answers
31 views
Computing date from record fields
In an SQLite DB I have a table with a station integer field and an at date field.
How can I combine these two values to obtain, in a WHERE clause a date which is at plus station months?
I.e. for a ...
-1
votes
1answer
460 views
How to downgrade sqlite3 that comes with Python and Django
So I am stuck in this problem: https://stackoverflow.com/questions/53637182/django-no-such-table-main-auth-user-old
I can't upgrade Django because many other applications are connected to it using ...
2
votes
0answers
77 views
How to reduce size of a large SQLite database with series data
I'm considering to use SQLite database for a C# application which deals with large volumes of data series. The data is currently in several CSV files of up to 20GBs in size each and of the following ...
-2
votes
2answers
109 views
sqlite database changes not commited immediately
For global connections(means connections which are not closed), for implicit transactions(with auto commit on),whether insert query is not committed immediately? (sometimes last inserted row is not ...
0
votes
0answers
29 views
Sqlite Join with 1 million records
I have 3 tables:
Device: Id, Name.
Machine: Id, P1, P2, date_time, IdDevice.
Parameters: Id, T, V, I, date_time, IdDevice.
In device there isn't a lot of records. But in the other way, inside ...
1
vote
1answer
49 views
How do I reference a row which has two foreign keys as its primary key?
I am currently trying to setup a database managing players and teams for a competition. Players are competing in teams of two but may be shuffled.
As every team combination can only exist once, I ...
0
votes
0answers
21 views
Query not excluding right amount of records using in clause
I'm trying to find excluded records from one table from another.
select old_fy.*
from old_fy
where "old_count_or_not" = "Count Current" or "old_count_or_not" = "Count Partial"
Returns 557 rows
...
1
vote
2answers
73 views
join vs virtual table in query performance
Suppose I have two tables linked by a foreign-key
example DDL's
CREATE TABLE raw_collection (
info_datetime DATETIME NOT NULL,
asset_id INTEGER NOT NULL,
price FLOAT,
PRIMARY KEY (...
3
votes
0answers
39 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
115 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
29 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
133 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
18 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
59 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
238 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
35 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
45 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
67 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
60 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
31 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
91 views
SQLite: Leave only n rows for each field
I have a table
+-----------------------------+---------------+-----------+
| Painter | Painting | Rate |
+-----------------------------+---------------+-----------+
| ...
1
vote
2answers
92 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
38 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
434 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
38 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
140 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
39 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
278 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
15 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
171 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
140 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
44 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
109 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 ...
1
vote
2answers
225 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
21 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
147 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
26 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
86 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 ...
