PSET8 Mashup.db Error (Places)

Question

I need a little help on Mashup PSET8.

Using phpLiteAdmin or sqlite3, create a table in mashup.db called places that has these twelve fields, in this order:

At this point, I already created my places table with these variables

country_code TEXT

postal_code INTEGER

place_name TEXT

admin_name1 TEXT

admin_code1 TEXT

admin_name2 TEXT

admin_code2 TEXT

admin_name3 TEXT

admin_code3 INTEGER

latitude INTEGER

longitude REAL

accuracy INTEGER

I compile these commands and I receive the following error...

~/workspace/pset8/mashup/ $ sqlite3 mashup.db SQLite version 3.8.2 2013-12-06 14:53:30 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .separator "\t" sqlite> .import US.txt places CREATE TABLE places(...) failed: duplicate column name:

I don't know what to do.

Answer 1

It looks like you are not in the same directory as when you ran phpliteadmin to create the table. If you sqlite3 mashup.db and mashup.db does not exist, it will be created. If that happened, then there is no places table. This is the message you get when you execute the import and the table doesn't already exist. [Another possibility is that there is a mismatch in database names. I can't tell you how many times I typed finanance.db in pset7!]

This is addressed in the spec FAQ's:

CREATE TABLE places(…​) failed: duplicate column name
If you see this message upon running .import in sqlite3, odds are you haven’t run sqlite3 in the same directory as mashup.db. If so, exit sqlite3 with .exit, cd to your mashup directory, and then re-run sqlite3 mashup.db.

---

Unsolicited advice: You may find the pset easier if you make postal_code TEXT instead of integer. It preserves the leading 0's, it handles globalisation (which is not important to the exercise), and it makes search much easier.