MethodError: promote_type error saving DataFrame to SQLite database

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.

To answer my own question …

Looking in promotions.jl in base, one can find the syntax for promote_type

Adding this line eliminates the error

Base.promote_type(::Type{Union{}}, ::Type{String}) = String

Later, I got some additional variations of the same error. The complete set of fixes ended up being:

    Base.promote_type(::Type{Union{}}, ::Type{String}) = String
    Base.promote_type(::Type{Union{}}, ::AbstractString) = String
    Base.promote_type(::Type{Union{}}, ::String) = String

Which version of WeakRefStrings do you have? From the logs you posted it doesn’t seem to be the last one (1.4.1) and the bug doesn’t seem to happen with that version.