Questions tagged [sqlite]
A popular open-source embeddable (i.e. non client-server) RDBMS implemented as a C library.
505
questions
0
votes
1answer
9 views
How to add an additional table over two one to many relations tables?
I have two tables TableA and TableB which have a relation of one to many
Later I am told that there must be an additional table that links the initial tables with additional information. To carry ...
-1
votes
1answer
21 views
How should cardinality be understood in a table with a compound key?
I am learning about database structuring and when watching the following video I cannot identify if there is a cardinality between the ENVIO, PEDIDO and PRODUCTO tables?
If it is clear to me that ...
0
votes
1answer
22 views
Is there a way of securing ProxySQL's own SQLite3 database?
As I understand ProxySQL has support for SSL encryption for inbound and outbound traffic (front/backend) and supports password hashing. However back in 2017, one had direct database access:
How to ...
0
votes
1answer
16 views
SQLite, update one row with another row data
I have a following table
CREATE TABLE LoadRecords(RoomNum INTEGER DEFAULT 0, UserNum INTEGER DEFAULT 0, PresetNum INTEGER DEFAULT 0, PresetName TEXT DEFAUTL 'Preset', Load1 INTEGER DEFAULT 0, Load2 ...
0
votes
0answers
18 views
Can a table be un-normalizable or am I misunderstanding the theory
I was considering normalizing a flat table (SQLite if its relevant) I'd been working with in a small personal project and it just didn't seem to make sense.
Consider an example table like below. Each ...
0
votes
2answers
28 views
ERROR: 42803: column “wage_histories.wage” must appear in the GROUP BY clause or be used in an aggregate function
I'm trying to migrate an application from SQLite to PostgreSQL, and now I have reached the point of re-expressing the queries that are valid in one and invalid in the other.
I have a table:
CREATE ...
0
votes
1answer
7 views
Sqlite Affinity for hashvalues with certain size
I must store many SHA256-values in a sqlite-database and now i wonder which sqlite-affinity would be the correct one. SHA256-values have an output-length of 256bit=32byte. I am looking for an sqlite-...
0
votes
0answers
17 views
Tags and Products intersection
I'm new to SQLite and searching for a way to create a query to do the following:
I have 3 tables:
goods: List of goods
tags: list of tags
tag_goods: table used to associate a tag to a product
This ...
0
votes
1answer
43 views
Update SQLITE table [closed]
I want to update my sqlite table and I use a request with conditionals my purpose is to set the name to the new value when the value is not null and keep the old value if the new value is null .
I ...
1
vote
1answer
11 views
How to query/join tables where foreign key constraint is set in schema
I'm relatively new to sqlite and databases in general.
I've successfully setup a schema of 4 tables, each having a foreign key pointing to its parent table. The schema also has the foreign key ...
0
votes
0answers
18 views
Left Join on large dataset
I have a large table with over 140 million of rows (basically from all brazilian investment funds shares organized by the enterprise official number (CNPJ) that I would like to left join with a table ...
0
votes
1answer
22 views
Getting proper output of my SELECT clause. SQLite
I am coding a bus connection search engine. My goal is to see the time when the bus is departing, while the stop_from and stop_to names are provided.
I have created the STOP table
CREATE TABLE STOP (...
1
vote
0answers
17 views
SQLite fails to delete the foreign keys with the cascade delete [duplicate]
I have a simple SQLite database with a foreign key on a master table. This table is a relational table to help mapping Table A to Table B.
CREATE TABLE IF NOT EXISTS "PERSON"
(
"ID" INTEGER NOT ...
0
votes
1answer
13 views
copy a sqlite3 table preserving custom _id
I'm trying to copy a table whose _id field is given by integers with some gaps, e.g. 1,2,5,7, presumably due to deleted messages.
(This is a toy-model for a Whatsapp database I'm manipulating.)
I'd ...
6
votes
3answers
1k views
Is a single-table relational database similar to a simple NoSQL database?
One advantage touted for NoSQL databases is the lack of a schema. In the case of a single-table relational database, there are no relationships between multiple tables, and new columns can be easily ...
1
vote
1answer
24 views
SQLite where condition - compare two last rows
I have a SQLite DB table like this:
[Stock_price_Table]:
+-----+-------------+--------+------------+--------+--------------+----------------------+
|code | date | first | open | close ...
0
votes
2answers
48 views
How to pivot data in SQLite
I've a table which contains text data containing a bunch of translations for different language. Each translation is for a specific label.
I need to generate a pivot table so to get quickly what's ...
0
votes
1answer
50 views
Full text search with json field in SQLite
Is it possible to have full text search in SQLite along using json?
In SQLite json is stored as TEXT (JSON1).
Full text search requires creation of virtual table (full text) but I don't know how to ...
0
votes
0answers
6 views
Sqlite3 indexes after deleting an element
I am using sqlite3 in my project and a have a problwm with elements indexes. There are 2 columns in my table "number"(index) and "user_input". For example, if we have 5 notes in this table their ...
1
vote
1answer
15 views
Is it possible to set SQLite DB on server and connect to it in an Android app?
I'm creating an app as a study project. One of requirements is not to hardcode. For this reason, I want to get data from DB with every start of the app and change data stored in DB outside of the app ...
1
vote
1answer
64 views
How to automatically rename columns from same table join?
When I join two tables to avoid conflicts I use column aliases. But that is prone to errors if there a lot of columns. Also, some ORMS require to use hardcoded or dynamic prefixes to column names. Is ...
0
votes
1answer
13 views
SQLite - Counting referenced rows in one table using a different, related self-referencing table
media_subscriptions
+----+--------------------------+
| id | target_metadata_item_id |
+----+--------------------------+
| 30 | 19039 |
| 31 | NULL |
| 32 | ...
0
votes
0answers
16 views
Matching columns within the same table in SQLite
I have a table of Employees which contain, among other things, the employeeId, the lastName and the ReportsTo column, that refers to the manager ID to which the employee has to report. That manager is ...
0
votes
1answer
14 views
How to enforce strict handling of GROUP BY with non-aggregate / bare columns or UPDATE with multiple-row SELECT in SQLite
Unlike some other RDBMS, SQLite does allow for bare columns in SELECT querys with GROUP BY, i.e. not all selected columns need to be part of the group by or aggregate functions otherwise. In this case,...
1
vote
2answers
38 views
Getting a query to work on SQLite as well as on MySQL
I have a subquery with a condition in the main query where clause.
Here is the sample schema
CREATE TABLE `payments` (
`id` int(11) NOT NULL,
`discount_id` int(11),
`amount` int(11)
);
...
0
votes
1answer
27 views
Git corrupts SQLite database
I use SQLite for websites I create and manage for my clients (I am a web developer). They are very small and straightforward databases.
This is now happened twice, so it is pretty clear that it ...
0
votes
1answer
59 views
How can I fill in a foreign key value after creating the first entry of the table the foreign key is referencing?
I am seeking to create an online store so I got to the database definition. I want to have as few tables as possible, so I've got them narrowed down to:
Users
Products
Orders
Cart_Items
I got to the ...
0
votes
0answers
83 views
Cannot run JSON_VALUE in SQLite database (.db file) in SQL Server Management Studio
I want to update some JSON data in SQLite database (.db file) and I met a problem.
I work in Microsoft SQL Server Management Studio where I added SQLite/SQL Server Compact Toolbox through Visual ...
0
votes
1answer
284 views
Import csv file into sqlite3 table - ERROR “Insert failed: datatype mismatch”
I am using sqlite3 and I am writing my scripts in two files. The first one is called "Books.sql"
DROP TABLE IF EXISTS Books;
CREATE TABLE Books (
books_id INTEGER PRIMARY KEY AUTOINCREMENT,
...
0
votes
1answer
25 views
How bad is adding new column each time to compare the fixed values?
As for now we have a program that gets some data weekly which need to be compared with previous values, maimly for uniqueness.
All data keys are dynamic and are expected to be changed.
So creating ...
0
votes
1answer
19 views
Selecting records based of other table records
In an SQLite DB, I have teh following tables
*reports*
id: integer
name: string
*sections*
id: integer
warning_table: string
warning_kind: string
report_id: integer (foreign key)
*old_warnings*
id:...
1
vote
1answer
56 views
SQLite Internals - Records
Hey I'm trying to wrap my head around SQLite data storage, specifically how it is storing Records. I've found a book The Definitive guide to SQLlite, where the author explains internal record format (...
0
votes
1answer
14 views
Transfering records with foreign key referring to new value
In an SQLite DB, I have to transfer some records from two tables (old_warnings and old_acknowledges) to other two tables(new_warnings and new_acknowledges).
Transfering warnings is not a problem.
...
1
vote
1answer
61 views
Storing many pandas DataFrames in SQLite with metadata
I need some help with designing a database. My aim is to persistently store a number of pandas DataFrames in a searchable way, and from what I've read SQLite is perfect for this task.
Each DataFrame ...
0
votes
1answer
65 views
COUNT after CROSS JOIN
I have a script that cross joins about 4 tables:
SELECT *
FROM Districts
CROSS JOIN Seasons
CROSS JOIN Sectors
CROSS JOIN Sites
The above query will combine all the tables into one, see a sample ...
0
votes
2answers
26 views
inserting multiple values into SQL with %
This works:
cursor = conn.execute("""Select FAMILY,F_NAME from birthday where B_MONTH = '%s' """ % (currentMonth))
I cannot seem to expand it to work using an AND clause to also include day, I get a ...
0
votes
1answer
41 views
How to have PostgreSQL running forever with constant insert and delete per day
Question:
Lots of rows are getting inserted every second. Batch deletion is performed when the disk is close to full. This causes two problems:
It is impossible to examine actually how much space in ...
0
votes
1answer
45 views
Why aren't read-only queries executing concurrently? [closed]
I have a Django app using either sqlite or Postgres. (I've tried both and encountered the same problem.) Requesting a particular endpoint results in a read-only query like this that takes 1.5-3 ...
0
votes
1answer
45 views
Sqlite - Limit Characters for different parts of one String with Check
I have to make sure that any inserted Email has the format: "X@Y.Z"
X and Y may only contain letters and numbers and Z can only have Letters.
I tried something like this, but i cant figure out how ...
0
votes
0answers
21 views
Optimal query using indexes
I have an sqlite table Task(type INT ,due_date LONG) where due_date is stored in epoch
TYPE DUE_DATE
---- ----------
1 1582038293
1 1485034292
2 1082048291
3 ...
0
votes
1answer
51 views
SQLite, ASCII Characters 0x20 to 0x7E Check Constraint
I have the following table which I'm trying to limit the column name to ASCII characters ranging from 0x20 to 0x7E. Why is it not working?
CREATE TABLE test
(
id INTEGER NOT NULL,
name TEXT ...
0
votes
1answer
104 views
SQLite - Remove duplicates across multiple columns
I have the following table:
C1 | C2 | C3
-------------
A | X | 1
A | Y | 2
B | X | 3
B | Y | 4
I want to deduplicate across columns 1 and 2, and select the max from column 3. In case, since rows 1 ...
0
votes
2answers
665 views
How to get row number without windows functions in SQLite?
Assume there is the following table schema and data
create table tbl(
id integer primary key,
name text,
pid integer
);
insert into tbl
values
(1,'d',0),
(2,'c',1),
(3,'b',1),...
0
votes
1answer
27 views
accelerating a SQL quadruple self-join with a complex alternation in the WHERE clause
The following Sqlite query has a triple self-join on table t:
SELECT "update_variable",
lhs_acc.name_suffix || ":" || rhs_var.name_suffix,
op.span,
op.path
FROM t op
JOIN t lhs_acc ON (...
0
votes
1answer
35 views
How to store file paths, which collation to use?
I have a table that stores Windows system paths. I am scratching my head about how to store them and in particular which collation to use.
The paths are unicode strings. They are case insensitive, ...
0
votes
1answer
155 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
430 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
45 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
88 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: ...
3
votes
1answer
282 views
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,...
