Hot answers tagged

5

If you run man sqlite3, you can find command line parameters -csv and -separator. So you can do something like this: cat mycsvfile.csv | sqlite3 -csv -separator ';' mydb.db '.import /dev/stdin mycsvtable' SQLite will automatically create the table if it does not exist, using the first line as the column names. I tested this and it worked like a charm. ...


4

I always use .mode line which prints query results vertically, similar to MySQL's \G modifier.


4

Concurrency is not a problem, as long as you only read. SQLite doesn't allow concurrency on writes, because it never was designed for that. I don't know the internals of the python driver and DBVisualized, but expect both smb, your os and any middleware to cache automatically at least parts of the file, because unless requested so, it is done automatically ...


3

There is no information_schema in SQLite as pointed out by @mustaccio. However, you can get the information you require by using this SQL: SELECT name FROM sqlite_master WHERE type ='table' AND name NOT LIKE 'sqlite_%'; See the link here. Tables which begin with sqlite_ are SQLite's system tables and are explained here. Remember ...


3

DEFERRED, IMMEDIATE, and EXCLUSIVE are not isolation levels. All three modes ensure the serializable isolation level. (SQLite's transcations are always serializable because it actually serializes them; there is a single, database-wide lock.) IMMEDIATE takes the equivalent of a write lock at the beginning of the transaction; this prevents a deadlock when two ...


3

The % operator takes a string on its left side, and a list of values on its right side: execute("SELECT ... WHERE B_MONTH = '%s' AND B_DAY = '%s'" % (currentMonth, currentDay)) Please note that using % introduces the risk of SQL injections when used with strings, so you should always use SQL parameters instead: execute("SELECT ... WHERE B_MONTH = ? AND ...


2

Here is a rough sketch. I could only test with SQLite 3.8 and it appears as if UPSERT is introduced in 2018-06-04 - Release 3.24.0. I.e. the following is untested, but hopefully you can make something out of it anyhow create table Texts ( tid integer not null primary key AUTOINCREMENT , textval varchar(20) not null unique); create index x1 on texts (...


2

In SQLite, you have to do it the hard way with the instr() and substr() functions: SELECT before_semicolon, substr(rest, 1, pos - 1) AS after_semicolon1, substr(rest, pos + 1) AS after_semicolon2 FROM (SELECT before_semicolon, rest, instr(rest || ';', ';') AS pos FROM (SELECT substr(data1, 1, pos - 1) AS ...


2

Please, go the SQLite driver options, Advanced tab and set true to foreign_keys parameter.


2

You have come across the first fallacy of distributed computing: the network is reliable. I think no matter how much time and money your company invests in improving the network, there will always be outages. I recommend designing the application/system such that it does not rely on the network to be up to function correctly. What I think you are asking ...


2

You can use the following: WHERE before <= date(at, (station || ' months') ) Please note that according to the documentation: "±NNN months" works by rendering the original date into the YYYY-MM-DD format, adding the ±NNN to the MM month value, then normalizing the result. Thus, for example, the data 2001-03-31 modified by '+1 month' initially ...


2

What I would do here is to have a table match_winner! It will be a JOINing table (also more formally called an ASSOCIATIVE ENTITY as well as many other names from the page). I'm not quite sure why you have two tables called matches and games - are they not the same thing? If not, let me know and I'll modify the schema. Something like this is what you ...


2

You have two problems compounding each other. First, you are using reserved words to and from (and also time) as your table columns, which is generally a bad idea. You have resolved that problem by quoting the questionable identifiers: "from" INTEGER NOT NULL, "to" INTEGER NOT NULL, Now you forget that from there on you must always quote these ...


2

You've created 3 separate tables (well 2, pending the apparent typo?). If you want to unify the data, you probably shouldn't be forcibly overwriting target tables with if_exists="replace" From the .to_sql() documentation replace: Drop the table before inserting new values. append: Insert new values to the existing table. Assuming your similarly ...


2

Putting data (here: "old"/"new") into the table name is a bad idea becaus it usually complicates queries. It should be stored in a column instead. The easiest way to handle this is to create such a table will all warnings: WITH all_warnings(warning_table, id, warning_kind, creation_date) AS ( SELECT 'old_warnings', id, kind, creation_date FROM ...


1

It depends on a number of things. You list postgres in one of your tags, so it should be noted that the CTE will be materialised in full as CTEs are an optimisation fence that block predicate push-down. I'm not sure about sqlite, the other DB you list in the tags, but this is not the case for all databases, SQL Server for one can optimise across CTEs. So ...


1

The unique index on tbl2.col2 isn't adding any new information that a full table scan wouldn't give it That is true; the question is, how many table scans do you think are there? SQLite documentation will tell you that its optimizer is capable of only nested-loop joins, so regardless of whether it is smart enough to rewrite the EXISTS subquery into a join1 ...


1

Use grouping to get the count per country, and a window function to get the rank: WITH t1 AS ( SELECT CASE WHEN Country IN ('France', 'Germany') THEN 'Europe' WHEN Country IN ('China', 'Japan') THEN 'Asia' WHEN Country IN ('Egypt', 'South Africa') THEN 'Africa' END AS Region, GenreName, ...


1

You could do something like this: When considering that a word like water can only be surrounded by spaces, comma's and square brackets. SELECT * FROM b WHERE list LIKE '%[water,%' OR list LIKE '%, water,%' OR list LIKE '%, water]%'; And you could technically remove some of the wildcards: SELECT * FROM b WHERE list LIKE '[water,%' OR list LIKE '%, water,%...


1

SELECT DISTINCT abbreviations.* FROM attachments JOIN abbreviations ON abbreviations.Abbreviation IN (attachments.`To`, attachments.`From`) WHERE attachments.RowID IN ('2019.06.05', '2019.06.11', '2019.06.12', '2019.06.15', '2019.06.22'); ?


1

Another version using row_number() window function (And thus needing sqlite 3.25 or newer): DELETE FROM ratings WHERE rowid IN (SELECT rowid FROM (SELECT rowid , row_number() OVER (PARTITION BY painter ORDER BY rate DESC) AS rn FROM ratings) WHERE rn > 2);


1

WITH cte AS ( SELECT author, name, rating, ROW_NUMBER() OVER ( PARTITION BY author ORDER BY rating DESC ) rn FROM ratings ) DELETE FROM ratings WHERE EXISTS ( SELECT 1 FROM cte WHERE ratings.author = cte.author AND ...


1

If I were you, I would include the new fields within the location table - while at the same time making considerable use of Declarative Referential Integrity. I would do something like this (I haven't included all of your new fields - you can check out the fiddle here): Create and populate two tables, crime and weather: CREATE TABLE crime (cr_rating ...


1

You said you are open to other DBMS solutions as well. This is very easy in Postgres: select id, split_part(data1, ';', 1) as Data1_Before_Semicolon, split_part(data1, ';', 2) as Data1_After_Semicolon1, split_part(data2, ';', 1) as Data2_Before_Semicolon, split_part(data2, ';', 2) as Data2_After_Semicolon1, ...


1

For the sake of the completeness, it works pretty fine. After kickstarted by Lennart's answer. Here is my final solution. I have separate unique text repositories and an N-M relation between them: CREATE TABLE "text_group1" ( "id" INTEGER NOT NULL DEFAULT null PRIMARY KEY AUTOINCREMENT UNIQUE, "text" TEXT NOT NULL UNIQUE ) CREATE TABLE "...


1

You can use .mode tabs for convenience. sqlite> select * from user; name age Bob 18 Ali 19


1

You can try something like this, this is setting table1's col3 value to table2's col3 value where they match on col1 and col2. You can replace the col3 with your 'some value'. create table table1 ( col1 int, col2 int, col3 int ); create table table2 ( col1 int, col2 int, col3 int ); insert into table1 values (1,2,3); insert into table1 values (4,5,6); ...


1

I'd just create a view on top of the table. The view uses the analytic function row_number() to provide what you needed. You'll need a column to sort on. That could be your AUTOINCREMENT column. https://www.sqlite.org/windowfunctions.html select channel_id, text ,row_number() over (partition by channel_id order by identity_column) as id from ...


1

You have two issues: A typo in your GradeInfo table definition that should be a syntax error, but unfortunately sqlite is all too often too lenient about what it accepts: FOREIGN KEY (Student_ID) REFERENCES StudentInfo (Student_ID) FOREIGN KEY (CourseNo) REFERENCES CourseInfo (CourseNo), Notice the lack of comma. (EDIT: This actually works even though it ...


Only top voted, non community-wiki answers of a minimum length are eligible