Python Forum
how can a sqlite command like import be executed using sqlite3 (python)
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
how can a sqlite command like import be executed using sqlite3 (python)
#1
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
Reply
#2
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
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  need help with data analysing with python and sqlite Hardcool 2 300 Jan-30-2024, 06:49 AM
Last Post: Athi
  How to not open the context menu if a mouse gesture is executed? MicheliBarcello 2 639 Aug-22-2023, 02:47 PM
Last Post: deanhystad
  python sqlite autoincrement in primary column janeik 6 1,076 Aug-13-2023, 11:22 AM
Last Post: janeik
  return next item each time a function is executed User3000 19 2,176 Aug-06-2023, 02:29 PM
Last Post: deanhystad
  code not working when executed from flask app ThomasDC 1 836 Jul-18-2023, 07:16 AM
Last Post: ThomasDC
  Help with subtracting values using SQLite & Python Extra 10 3,288 May-10-2022, 08:36 AM
Last Post: ibreeden
  [Solved]Help with search statement-SQLite & Python Extra 1 1,016 May-06-2022, 07:38 PM
Last Post: Extra
  Help With Python SQLite Error Extra 10 14,561 May-04-2022, 11:42 PM
Last Post: Extra
  Python Sqlite georgebijum 0 1,030 May-04-2022, 10:12 AM
Last Post: georgebijum
  doubt about python tkinter and sqlite3 LONDER 2 2,119 Aug-14-2021, 08:48 AM
Last Post: ibreeden

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020