Error ")" near first line when building SQLite database

Question

I am trying to a build an SQLite database from an existing CSV file following the steps from this answer. However I already get an error in the second step where the database should be created from the defined table with

sqlite3 worldcities.db < worldcities.sql

The error message reads

Error: near line 1: near ")": syntax error

My worldcities.sql file looks like this:

CREATE TABLE worldcities(
   city TEXT NOT NULL,
   city_ascii TEXT NOT NULL,
   lat REAL NOT NULL,
   lng REAL NOT NULL,
   country TEXT NOT NULL,
   iso2 TEXT NOT NULL,
   iso3 TEXT NOT NULL,
   admin_name TEXT NOT NULL,
   capital TEXT NOT NULL,
   population INT NOT NULL,
   id INT NOT NULL,
);

What did I do wrong here?

I am surprised by the `,` before the `)`. Maybe SQLite also is. — Yunnosch, Oct 05 '20 at 19:23

score 1 · Accepted Answer · answered Oct 05 '20 at 19:34

1

I suspect the orphaned , just before your )to cause your problem.

I tried here https://extendsclass.com/sqlite-browser.html
this

CREATE TABLE newtoy2 (MID INT, BID INT);

without a problem.

But this, having the same problem I suspect in your code

CREATE TABLE newtoy1 (MID INT, BID INT,);

gets the same error you quote: near ")": syntax error

Which confirms my suspicion.

answered Oct 05 '20 at 19:34

Yunnosch

26,130
9
42
54

An *orphaned ,* is a typo and a reason for a question to be closed. – forpas Oct 05 '20 at 20:08
Or it is a misunderstanding of syntax which, pointed out, results in a helpful insight. – Yunnosch Oct 06 '20 at 06:41

score 1 · Answer 2 · answered Oct 05 '20 at 19:49

There was an extra comma ',' before end of block. Commas are used after all column description but not after the last column description.

Try and Run this below code.

   create table worldcities(
   city TEXT NOT NULL,
   city_ascii TEXT NOT NULL,
   lat REAL NOT NULL,
   lng REAL NOT NULL,
   country TEXT NOT NULL,
   iso2 TEXT NOT NULL,
   iso3 TEXT NOT NULL,
   admin_name TEXT NOT NULL,
   capital TEXT NOT NULL,
   population INT NOT NULL,
   id INT NOT NULL);

Error ")" near first line when building SQLite database

2 Answers2