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).


using CSV
using DataFrames
using SQLite

Creating the database table:

   db = SQLite.DB(dbname)

        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)
        FOREIGN KEY (apn) 
            REFERENCES propertyinfo (apn)
        FOREIGN KEY (apn)
            REFERENCES residential (apn)

Here are a few rows of example data for the csv file, parcel-small.csv :


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:
Possible fix, define
  promote_type(::Type{Union{}}, ::Type{String})
  [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.

I had the same issue with 1.7.0. My code was working fine, then I installed the MarketTechnicals package and from that point I have been unable to fix. I’m not skilled enough to understand the true underlying cause but your fix worked for me, thanks.

the code failing for me was…
df = DataFrame(DBInterface.execute(db, sql))

I’m spending more time trying to solve this type of issue than coding recently so I’m getting very paranoid about adding new packaged or updating anything.

Can you post the output of ] st -m So we can see what versions of packages you are running?

Julia v 1.7.0
Julia language support 1.5.6
Windows 10

using DataFrames
using DataFramesMeta
using Dates
using SQLite
using XLSX
using HTTP
using JSON
using FileIO
using Observables
using Colors
using Statistics, Random
using Missings
using CSV
using Indicators

[621f4979] AbstractFFTs v1.0.1
[1520ce14] AbstractTrees v0.3.4
[79e6a3ab] Adapt v3.3.1
[27a7e980] Animations v0.4.1
[4fba245c] ArrayInterface v3.2.1
[67c07d97] Automa v0.8.2
[13072b0f] AxisAlgorithms v1.0.1
[b99e7846] BinaryProvider v0.5.10
[fa961155] CEnum v0.4.1
[336ed68f] CSV v0.9.11
[8be319e6] Chain v0.4.10
[d360d2e6] ChainRulesCore v1.11.2
[9e997f8a] ChangesOfVariables v0.1.1
[944b1d66] CodecZlib v0.7.0
[a2cac450] ColorBrewer v0.4.0
[35d6a980] ColorSchemes v3.15.0
[3da002f7] ColorTypes v0.11.0
[c3611d14] ColorVectorSpace v0.9.8
[5ae59095] Colors v0.12.8
[34da2185] Compat v3.40.0
[d38c429a] Contour v0.5.7
[a8cc5b0e] Crayons v4.0.4
[a10d1c49] DBInterface v2.5.0
[9a962f9c] DataAPI v1.9.0
[a93c6f00] DataFrames v1.3.0
[1313f7d8] DataFramesMeta v0.10.0
[864edb3b] DataStructures v0.18.11
[e2d170a0] DataValueInterfaces v1.0.0
[b429d917] DensityInterface v0.4.0
[31c24e10] Distributions v0.25.34
[ffbed154] DocStringExtensions v0.8.6
[da5c29d0] EllipsisNotation v1.1.3
[8f5d6c58] EzXML v1.1.0
[c87230d0] FFMPEG v0.4.1
[7a1cc6ca] FFTW v1.4.5
[5789e2e9] FileIO v1.11.2
[48062228] FilePathsBase v0.9.17
[1a297f60] FillArrays v0.12.7
[53c48c17] FixedPointNumbers v0.8.4
[59287772] Formatting v0.4.2
[b38be410] FreeType v4.0.0
[663a7486] FreeTypeAbstraction v0.9.5
[f7f18e0c] GLFW v3.4.1
[e9467ef8] GLMakie v0.4.7
[5c1252a2] GeometryBasics v0.4.1
[a2bd30eb] Graphics v1.1.1
[3955a311] GridLayoutBase v0.6.4
[42e2da0e] Grisu v1.0.2
[cd3eb016] HTTP v0.9.17
[615f187c] IfElse v0.1.1
[a09fc81d] ImageCore v0.9.3
[82e4d734] ImageIO v0.5.9
[70c4c096] Indicators v0.8.1
[9b13fd28] IndirectArrays v1.0.0
[d25df0c9] Inflate v0.1.2
[83e8ac13] IniFile v0.5.0
[842dd82b] InlineStrings v1.1.0
[a98d9a8b] Interpolations v0.13.4
[8197267c] IntervalSets v0.5.3
[3587e190] InverseFunctions v0.1.2
[41ab1584] InvertedIndices v1.1.0
[92d709cd] IrrationalConstants v0.1.1
[f1662d9f] Isoband v0.1.1
[c8e1da08] IterTools v1.4.0
[82899510] IteratorInterfaceExtensions v1.0.0
[692b3bcd] JLLWrappers v1.3.0
[682c06a0] JSON v0.21.2
[5ab0869b] KernelDensity v0.6.3
[b964fa9f] LaTeXStrings v1.3.0
[2ab3a3ac] LogExpFunctions v0.3.5
[1914dd2f] MacroTools v0.5.9
[ee78f7c6] Makie v0.15.3
[20f20a25] MakieCore v0.1.3
[dbb5928d] MappedArrays v0.4.1
[7eb4fadd] Match v1.2.0
[0a4f8689] MathTeXEngine v0.2.1
[739be429] MbedTLS v1.0.3
[7269a6da] MeshIO v0.4.9
[e1d29d7a] Missings v1.0.2
[66fc600b] ModernGL v1.1.3
[e94cdb99] MosaicViews v0.3.3
[77ba4419] NaNMath v0.3.5
[f09324ee] Netpbm v1.0.2
[510215fc] Observables v0.4.0
[6fe1bfb0] OffsetArrays v1.10.8
[52e1d378] OpenEXR v0.3.2
[bac558e1] OrderedCollections v1.4.1
[90014a1f] PDMats v0.11.5
[f57f5aa1] PNGFiles v0.3.12
[19eb6ba3] Packing v0.4.2
[5432bcbf] PaddedViews v0.5.10
[69de0a69] Parsers v2.1.2
[eebad327] PkgVersion v0.1.1
[995b91a9] PlotUtils v1.0.15
[647866c9] PolygonOps v0.1.2
[2dfb63ee] PooledArrays v1.4.0
[21216c6a] Preferences v1.2.2
[08abe8d2] PrettyTables v1.2.3
[92933f4c] ProgressMeter v1.7.1
[1fd47b50] QuadGK v2.4.2
[c84ed2f1] Ratios v0.4.2
[3cdcf5f2] RecipesBase v1.2.1
[189a3867] Reexport v1.2.2
[05181044] RelocatableFolders v0.1.3
[ae029012] Requires v1.1.3
[79098fc4] Rmath v0.7.0
[fdea26ae] SIMD v3.3.1
[0aa819cd] SQLite v1.3.0
[7b38b023] ScanByte v0.3.0
[6c6a2e73] Scratch v1.1.0
[91c51154] SentinelArrays v1.3.8
[65257c39] ShaderAbstractions v0.2.8
[992d4aef] Showoff v1.0.3
[73760f76] SignedDistanceFields v0.4.0
[a2af1166] SortingAlgorithms v1.0.1
[276daf66] SpecialFunctions v1.8.1
[cae243ae] StackViews v0.1.1
[aedffcd0] Static v0.4.0
[90137ffa] StaticArrays v1.2.13
[82ae8749] StatsAPI v1.1.0
[2913bbd2] StatsBase v0.33.13
[4c63d2b9] StatsFuns v0.9.14
[09ab397b] StructArrays v0.6.3
[3783bdb8] TableTraits v1.0.1
[bd369af6] Tables v1.6.0
[a110ec8f] Temporal v0.8.1
[62fd8b95] TensorCore v0.1.1
[731e570b] TiffImages v0.5.2
[3bb67fe8] TranscodingStreams v0.9.6
[5c2747f8] URIs v1.3.0
[1cfade01] UnicodeFun v0.4.1
[ea10d353] WeakRefStrings v1.4.1
[efce3f68] WoodburyMatrices v0.5.5
[fdbf4ff8] XLSX v0.7.8
[a5390f91] ZipFile v0.9.4
[6e34b625] Bzip2_jll v1.0.8+0
[83423d85] Cairo_jll v1.16.1+0
[5ae413db] EarCut_jll v2.2.3+0
[2e619515] Expat_jll v2.2.10+0
[b22a6f82] FFMPEG_jll v4.4.0+0
[f5851436] FFTW_jll v3.3.10+0
[a3f928ae] Fontconfig_jll v2.13.93+0
[d7e528f0] FreeType2_jll v2.10.4+0
[559328eb] FriBidi_jll v1.0.10+0
[0656b61e] GLFW_jll v3.3.5+1
[78b55507] Gettext_jll v0.21.0+0
[7746bdde] Glib_jll v2.68.3+1
[3b182d85] Graphite2_jll v1.3.14+0
[2e76f6c2] HarfBuzz_jll v2.8.1+1
[905a6f67] Imath_jll v3.1.2+0
[1d5cc7b8] IntelOpenMP_jll v2018.0.3+2
[c1c5ebd0] LAME_jll v3.100.1+0
[dd4b983a] LZO_jll v2.10.1+0
[e9f186c6] Libffi_jll v3.2.2+1
[d4300ac3] Libgcrypt_jll v1.8.7+0
[7e76a0d4] Libglvnd_jll v1.3.0+3
[7add5ba3] Libgpg_error_jll v1.42.0+0
[94ce4f54] Libiconv_jll v1.16.1+1
[4b2f31a3] Libmount_jll v2.35.0+0
[38a345b3] Libuuid_jll v2.36.0+0
[856f044c] MKL_jll v2021.1.1+2
[e7412a2a] Ogg_jll v1.3.5+0
[18a262bb] OpenEXR_jll v3.1.1+0
[458c3c95] OpenSSL_jll v1.1.10+0
[efe28fd5] OpenSpecFun_jll v0.5.5+0
[91d4177d] Opus_jll v1.3.2+0
[2f80f16e] PCRE_jll v8.44.0+0
[30392449] Pixman_jll v0.40.1+0
[f50d1b31] Rmath_jll v0.3.0+0
[76ed43ae] SQLite_jll v3.37.0+0
[02c8fc9c] XML2_jll v2.9.12+0
[aed1982a] XSLT_jll v1.1.34+0
[4f6342f7] Xorg_libX11_jll v1.6.9+4
[0c0b7dd1] Xorg_libXau_jll v1.0.9+4
[935fb764] Xorg_libXcursor_jll v1.2.0+4
[a3789734] Xorg_libXdmcp_jll v1.1.3+4
[1082639a] Xorg_libXext_jll v1.3.4+4
[d091e8ba] Xorg_libXfixes_jll v5.0.3+4
[a51aa0fd] Xorg_libXi_jll v1.7.10+4
[d1454406] Xorg_libXinerama_jll v1.1.4+4
[ec84b674] Xorg_libXrandr_jll v1.5.2+4
[ea2f1a96] Xorg_libXrender_jll v0.9.10+4
[14d82f49] Xorg_libpthread_stubs_jll v0.1.0+3
[c7cfdc94] Xorg_libxcb_jll v1.13.0+3
[c5fb5394] Xorg_xtrans_jll v1.4.0+3
[9a68df92] isoband_jll v0.2.2+0
[0ac62f75] libass_jll v0.15.1+0
[f638f0a6] libfdk_aac_jll v2.0.2+0
[b53b4c65] libpng_jll v1.6.38+0
[f27f6e37] libvorbis_jll v1.3.7+0
[1270edf5] x264_jll v2021.5.5+0
[dfaa095f] x265_jll v3.5.0+0
[0dad84c5] ArgTools
[56f22d72] Artifacts
[2a0f44e3] Base64
[ade2ca70] Dates
[8bb1440f] DelimitedFiles
[8ba89e20] Distributed
[f43a241f] Downloads
[9fa8497b] Future
[b77e0a4c] InteractiveUtils
[4af54fe1] LazyArtifacts
[b27032c2] LibCURL
[76f85450] LibGit2
[8f399da3] Libdl
[37e2e46d] LinearAlgebra
[56ddb016] Logging
[d6f4376e] Markdown
[a63ad114] Mmap
[ca575930] NetworkOptions
[44cfe95a] Pkg
[de0858da] Printf
[3fa0cd96] REPL
[9a3f8284] Random
[ea8e919c] SHA
[9e88b42a] Serialization
[1a1011a3] SharedArrays
[6462fe0b] Sockets
[2f01184e] SparseArrays
[10745b16] Statistics
[4607b0f0] SuiteSparse
[fa267f1f] TOML
[a4e569a6] Tar
[8dfed614] Test
[cf7118a7] UUIDs
[4ec0a83e] Unicode
[e66e0078] CompilerSupportLibraries_jll
[deac9b47] LibCURL_jll
[29816b5a] LibSSH2_jll
[c8ffd9c3] MbedTLS_jll
[14a3606d] MozillaCACerts_jll
[4536629a] OpenBLAS_jll
[05823500] OpenLibm_jll
[83775a58] Zlib_jll
[8e850b90] libblastrampoline_jll
[8e850ede] nghttp2_jll
[3f19e933] p7zip_jll