Python Forum

I don't know how to do exactly the following using python sqlite3

SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
sqlite> .mode csv
sqlite> DROP TABLE IF EXISTS cities;
sqlite> CREATE TABLE cities(
   ...>   name TEXT NOT NULL,
   ...>   population INTEGER NOT NULL 
   ...> );
sqlite> .import city.csv cities
sqlite>
sqlite> select * from cities;
name,population
Abilene,115930
Akron,217074
Albany,93994
Albuquerque,448607
Alexandria,128283
Allentown,106632
...

I can figure out how to bulk load using this if the table is first created:

import csv
import sqlite3

db_filename = 'mydb.db'
csv_filename = 'src/California/city.csv'

SQL = """
insert into citypop (city, population) values (:city, :population)
"""

with open(csv_filename, 'rt') as csv_file:
    csv_reader = csv.DictReader(csv_file)

    with sqlite3.connect(db_filename) as conn:
        cursor = conn.cursor()
        cursor.execute("create table if not exists citypop (city, population);")
        cursor.executemany(SQL, csv_reader)

But I'd just love to use the direct method shown in first listing.
I did get sqlite3.mode = 'csv' to work, but don't know how to do that with .import city.csv cities

I think I'm getting too picky here. the second code snippet is close enough.
I'd like to be able to issue any sqlite3 command from the python package, and that can be done using
(pseudo code)

cursor.enable_load_extension(True)
cursor.load_extension(...)
enable_load_extension(False)

I'll use the second snippet

Larz60+

Larz60+