is there a way to import csv data into a sqlite db, while not explicitly typing the columns out-CodePudding

hoping not to use pandas for this.

#create a sqllite database
#step 1: create a connection

import sqlite3
conn = sqlite3.connect('db.sqlite')
sqlcursor = conn.cursor()

###--------------------------------STEP 2: IMPORT GEOSPATIAL DATA--------------------------------###
import csv
geofile = r'geodata.csv'
with open(geofile, 'r', newline='') as csvfile:
    csvdata = csv.reader(csvfile, delimiter=',')
    next(csvdata, None)
    csvrows = []
    for row in csvdata:
        if len(row[0]) < 5:
            row[0] = row[0].zfill(5)
        if len(row[1]) != 2:
            print('state: ' row[1])
        csvrows.append(tuple(row))

sqlcursor.executemany('SELECT * INTO geodata FROM VALUES(?,?,?,?,?,?);', csvrows)
conn.commit()

obviously this doesn't work, but for example in sql i can do something like this:

select * from
(
values
 ('207-544')
,('208-609')
,('208-601')
,('984-520')
,('989-318')
) AS m(combos)

so looking to use select into vs insert into, since i dont actually have a table - and don't want to make one beforehand

CodePudding user response：

If you don't want to store it in a file (as you don't want to create a table) you can do

conn = sqlite3.connect("file::memory:?cache=shared")

and to create the temp table (which would be a similar result as SELECT INTO), notice that you don't need to specify the column types

crt = 'CREATE TEMP TABLE t('
ins = 'INSERT INTO t VALUES('
for n in range(len(csvrows[0])):
    if n:
        crt  = ', '
        ins  = ', '
    crt  = f'c{n}'
    ins  = '?'
crt  = ');'
ins  = ');'
sqlcursor.execute(crt)
sqlcursor.executemany(ins, csvrows)

sqlite has a VALUES keyword that you can use in a SELECT UNION or similar, for example

sqlite> VALUES (0,1,2,3,4),(5,6,7,8,9) UNION SELECT * FROM sqlite_master;
0|1|2|3|4
5|6|7|8|9

but I don't think this could help.