SQLite Forum

import mishandles table name
Login

import mishandles table name

(1) By Nolan (nolanw) on 2021-03-11 00:05:52 [link] [source]

Currently .import file table fails to respect namespaces:

sqlite3
SQLite version 3.34.1 2021-01-20 14:10:07
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode csv
sqlite> .version
SQLite 3.34.1 2021-01-20 14:10:07 10e20c0b43500cfb9bbc0eaa061c57514f715d87238f4d835880cd846b9ealt1
zlib version 1.2.11
gcc-10.2.0
sqlite> .import summary_0.csv temp.summary
sqlite> .schema
CREATE TABLE IF NOT EXISTS "temp.summary"(
  "uid" TEXT,
  "seqid" TEXT,
  "accession" TEXT,
  "name" TEXT,
  "description" TEXT,
  "type" TEXT,
  "molecule_type" TEXT,
  "sequence_version" TEXT,
  "gi_number" TEXT,
  "cds_count" TEXT,
  "gene_count" TEXT,
  "rna_count" TEXT,
  "repeat_region_count" TEXT,
  "length" TEXT,
  "source" TEXT
);
sqlite> select * from temp.summary;
Error: no such table: temp.summary
sqlite> select * from "temp.summary";
9451451,NZ_CP071129.1,NZ_CP071129,NZ_CP071129,"Caballeronia sp. M1242 chromosome%2C complete genome",chromosome,"genomic DNA",1,"",2667,2739,72,0,2932255,"Caballeronia sp. M1242"

I would expect this to create the summary table in the temp namespace.

(2) By Larry Brasfield (larrybr) on 2021-03-11 00:49:23 in reply to 1 [link] [source]

Because you are so fond of double-quoted identifiers in your SQL, I will tell you what effect they have. They are a way to tell the parser, "Whatever lies between these quotes is my identifier, no matter that it contains keywords, spaces, schema names, or odd punctuation (such as '.'). Because the SQLite parser does what it is told (in this case), it takes your identifier just as quoted.

Your DDL creates a regular table, in the main schema, with the misleading name "temp.summary". This is not the same as if you had written "temp"."summary".

(5) By Nolan (nolanw) on 2021-03-11 01:50:09 in reply to 2 [link] [source]

I think you overlooked the fact that I did not create the DDL, the SQLite .import command did.

(3) By Keith Medcalf (kmedcalf) on 2021-03-11 01:31:47 in reply to 1 [link] [source]

The CLI .import command can only create tables in the main schema. The tablename specifier in the command cannot be qualified with a schema name, the entire thing is taken as the table name -- including embedded dots.

If the specified tablename already exists in an attached schema, then the data will be imported into that table. The ordinary search order for the tablename is used. If the tablename is not found a new table is created in the main schema.

It is not possible to .import into a new table in any schema other than main using the CLI .import command.

(4) By Nolan (nolanw) on 2021-03-11 01:48:32 in reply to 3 [source]

Thanks, I guess this is a feature request rather than a bug.

I expect it is common when using import to need to import to a temporary table and then insert into another table the transformed rows of the temporary table.

The schema of the imported file may not be fully known and it would be nice to be able to rely on the file header rather than pre-creating a temporary table to import into.

(6.1) By Scott Robison (casaderobison) on 2021-03-11 02:59:58 edited from 6.0 in reply to 4 [link] [source]

You opened an in-memory database. That database is by definition transient (aka temporary) but it is known as "main".

So why not just import into a table named summary. Then if you want to do some manipulation of the data to go into a persistent file, just attach that file with a specified name:

attach 'somefile.db' as nottemp;

Then you can do all the data manipulation you want from the temporary database named main into the persistent database named nottemp.

insert into nottemp.sometable
select some-list-of-columns
from main.summary
where some-list-of-conditions

I used that very technique for a data import project last year at work, importing awful looking CSV into a memory-based database named main, then transformed the data into a nicer format in tables in a database that I attached from a file.