My apologies that the MWE example is somewhat involved to setup.
Overview: I’m loading a .csv file into a DataFrame, then I want to save that DataFrame to an SQLite database. I’m using Julia 1.6.2 (Windows).
Preliminaries:
using CSV
using DataFrames
using SQLite
Creating the database table:
dbname="property.db"
db = SQLite.DB(dbname)
DBInterface.execute(db,"
CREATE TABLE IF NOT EXISTS parcel (
apn text not null,
parcel text not null,
acmna integer not null,
arcadia integer not null,
earcadia integer not null,
triangle integer not null,
camel integer not null,
puc text,
okmail integer not null,
lat real,
long real,
PRIMARY KEY (apn)
FOREIGN KEY (apn)
REFERENCES owner (apn)
ON DELETE RESTRICT
DEFERRABLE INITIALLY DEFERRED
FOREIGN KEY (apn)
REFERENCES propertyinfo (apn)
ON DELETE RESTRICT
DEFERRABLE INITIALLY DEFERRED
FOREIGN KEY (apn)
REFERENCES residential (apn)
ON DELETE RESTRICT
DEFERRABLE INITIALLY DEFERRED
);
")
Here are a few rows of example data for the csv file, parcel-small.csv :
apn,parcel,acmna,arcadia,earcadia,triangle,camel,puc,okmail,lat,long
17347062C,173-47-062C,0,0,1,1,0,0131,1,33.49506748,-111.9380067
17347062B,173-47-062B,0,0,1,1,0,0131,1,33.49507362,-111.9376848
17347062A,173-47-062A,0,0,1,1,0,0151,1,33.49509066,-111.9373146
17347061,173-47-061,0,0,1,1,0,9700,0,33.49545436,-111.9351501
17347060,173-47-060,0,0,1,1,0,0131,1,33.49518552,-111.9367811
17347059,173-47-059,0,0,1,1,0,0131,1,33.49518982,-111.9364666
Here is the code to read the .csv into a DataFrame:
pint = Dict([("puc",String)])
df_small = CSV.File("parcel-small.csv";types=pint) |> DataFrame
The data is read in and put into a dataframe:
Row │ apn parcel acmna arcadia earcadia triangle camel puc okmail lat long
│ String String Int64 Int64 Int64 Int64 Int64 String Int64 Float64 Float64
─────┼──────────────────────────────────────────────────────────────────────────────────────────────────────
1 │ 17347062C 173-47-062C 0 0 1 1 0 0131 1 33.4951 -111.938
2 │ 17347062B 173-47-062B 0 0 1 1 0 0131 1 33.4951 -111.938
3 │ 17347062A 173-47-062A 0 0 1 1 0 0151 1 33.4951 -111.937
4 │ 17347061 173-47-061 0 0 1 1 0 9700 0 33.4955 -111.935
5 │ 17347060 173-47-060 0 0 1 1 0 0131 1 33.4952 -111.937
6 │ 17347059 173-47-059 0 0 1 1 0 0131 1 33.4952 -111.936
The .csv is read into the dataframe with the appropriate types, which match the types defined for the database table.
Now comes the step to save the DataFrame to the “parcel” table in the SQLite database:
ulia> Stat = SQLite.load!(df_small,db,"parcel")
ERROR: MethodError: promote_type(::Type{Union{}}, ::Type{String}) is ambiguous. Candidates:
promote_type(::Type{Union{}}, ::Type{T}) where T in Base at promotion.jl:224
promote_type(::Type{T}, ::Type{String}) where T<:WeakRefStrings.InlineString in WeakRefStrings at C:\Users\amgough\.julia\packages\WeakRefStrings\a3jYm\src\inlinestrings.jl:
44
Possible fix, define
promote_type(::Type{Union{}}, ::Type{String})
Stacktrace:
[1] add_or_widen!(val::String, col::Int64, nm::Symbol, dest::Tables.EmptyVector, row::Int64, updated::Base.RefValue{Any}, L::Base.SizeUnknown)
@ Tables C:\Users\amgough\.julia\packages\Tables\gg6Id\src\fallbacks.jl:150
<more error lines>
Hmm… don’t know how to fix that. My DataFrame, and database, types are plain vanilla - Strings, Integers, and Float64.