sqlite3.OperationalError: no such column: cities

I am trying to insert the values of my tuple "cities_list" into the "cities" table. However, I keep getting an error that says: "sqlite3.OperationalError: no such column: cities_name". Any help would be appreciated. Thanks.

import sqlite3

connection = sqlite3.connect("cities.db")
cursor = connection.cursor()

cursor.execute("create table if not exists cities(city_id REAL PRIMARY KEY NOT NULL, cities_name text, city_population integer)")
    
cities_list = [('Minneapolis', '425,336'),
    ('St. Paul', '307,193'),
    ('Dallas', '1,288,000'),
    ('Memphis', '628, 127'),
    ('San Francisco', '815,201'),
    ('Milwaukee', '569,330'),
    ('Denver', '711,463'),
    ('Phoenix', '1,625,000'),
    ('Chicago', '2,697,000'),
    ('New York', '8,468,000')]

cursor.executemany("insert or ignore into cities values (cities_name, city_population)", cities_list)

# print
for row in cursor.execute("select * from cities"):
    print(row)

# print specific rows

print('******************************************')
cursor.execute("select * from cities where cities_name=:c", {"c": "Minneapolis"})
search = cursor.fetchall()
print(search)

connection.close()

I expected the values to be printed, but an error occurred.

CodePudding user response：

There are several problems in your code.

First of all, when you use "IF NOT EXIST" construct, your DDL statement will be a NO-OP if the target table already exists in the opened database. If the existing table was created using a different DDL statement, you might get a poorly traceable issue, unless you understand what is going on and look for it. In a larger code base you should be extra careful, because it might be more difficult to locate the problem.

The second problem is also with your DDL statement. If you declare your primary key column as

city_id REAL PRIMARY KEY NOT NULL

you must supply city_id for each row in the INSERT statement. Further, you should not declare PK as REAL. Use INTEGER or TEXT depending on the actual values. DO NOT use floats as PK. Unless you have predetermined city_ids, you probably should use the following instead to let the engine generate PKs automatically:

city_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL

Next, the correct INSERT statement is:

INSERT INTO cities(cities_name, city_population) VALUES (?, ?)

The IGNORE clause is only relevant if you supply the PK or a value for a unique field. Your DDL does not define any unique fields and your INSERT statement does not include the PK column, so OR IGNORE clause in this case is pointless.

Finally, the population values should be supplied as INTEGER, not as TEXT. SQLite does not complain here, unless you declare your table as STRICT. However, most likely your intention is to have population as integer values and by supplying text values, you are going have issues down the road. Note, I have not fixed this part, because the code works as is (I only fixed issues which resulted in runtime errors).

The full working code is here:

import sqlite3

connection = sqlite3.connect("cities.db")
cursor = connection.cursor()

cursor.execute("create table if not exists cities(city_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, cities_name text, city_population integer)")
    
cities_list = [('Minneapolis', '425,336'),
    ('St. Paul', '307,193'),
    ('Dallas', '1,288,000'),
    ('Memphis', '628, 127'),
    ('San Francisco', '815,201'),
    ('Milwaukee', '569,330'),
    ('Denver', '711,463'),
    ('Phoenix', '1,625,000'),
    ('Chicago', '2,697,000'),
    ('New York', '8,468,000')]

cursor.executemany("insert into cities(cities_name, city_population) values (?, ?)", cities_list)
connection.commit()

# print
for row in cursor.execute("select * from cities"):
    print(row)

# print specific rows

print('******************************************')
cursor.execute("select * from cities where cities_name=:c", {"c": "Minneapolis"})
search = cursor.fetchall()
print(search)

connection.close()

You can verify the code in this fiddle.