# Convert sqlite data to dataframe without nullable type

**URL:** <https://discourse.julialang.org/t/convert-sqlite-data-to-dataframe-without-nullable-type/689>\
**Category:** General Usage\
**Created:** [December 2, 2016, 6:03am UTC](https://discourse.julialang.org/t/convert-sqlite-data-to-dataframe-without-nullable-type/689 "2016-12-02T06:03:43Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Christopher\_Fisher](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/christopher_fisher/32/26132_2.png) [@Christopher\_Fisher](https://discourse.julialang.org/u/Christopher_Fisher)\
**Post date:** [December 2, 2016, 6:03am UTC](https://discourse.julialang.org/t/convert-sqlite-data-to-dataframe-without-nullable-type/689/1 "2016-12-02T06:03:43Z")

</div>

Hi all-

I am working with an sqlite database, but don’t really want to use the nullableArrays type. Is there a way to read the database as a regular dataframe or to easily convert to a regular dataframe?

Thanks.

---

<div class="post-metadata">

**Author:** ![Christopher\_Fisher](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/christopher_fisher/32/26132_2.png) [@Christopher\_Fisher](https://discourse.julialang.org/u/Christopher_Fisher)\
**Post date:** [December 2, 2016, 11:12am UTC](https://discourse.julialang.org/t/convert-sqlite-data-to-dataframe-without-nullable-type/689/2 "2016-12-02T11:12:25Z")

</div>

I would like to bring up some related issues, which ultimately motivated my original question. Unbeknownst to me, df[:column] .== x incorrectly returned an array of all false. For example, Nullable(1) == 1 is false, which, as a user, is not what I would expect, and is not consistent with the rest of the language. The solution is verbose and not consistent with the rest of the language:

f(x) = x == 1  
map(f,x,;lift =true)

Moreover, its not clear to me how to extend this approach such that f() has more arguments.

I also found problems with redefining functions. Consider the following:

```julia
using SQLite
db = SQLite.DB("chinook.db")
df = SQLite.query(db,"SELECT * from artists")
myfunction(x) = x == 1
map(myfunction,df[:ArtistId],lift=true)

#Redefine myfunction and get incorrect result
myfunction(x) = x == 2
map(myfunction,df[:ArtistId],lift=true)
#However, this works properly
myfunction(2)

```

The database can be found [here.](http://www.sqlitetutorial.net/sqlite-sample-database/) map() appears to be using the original definition of myfunction.

As a user, I understand the need for dealing with missing values. However, the current situation seems less than ideal. Multiple dispatch should be able to handle compact Julia syntax, such as df[:col1] + 1 and the like. Otherwise, it feels like I am recreating base Julia. The problems with redefining functions and testing equality are disconcerting. I need to trust that the code is working as reasonably expected, especially for simple things. I’m not sure what is the proper venue for this, but I hope the feedback is useful somehow.

---

<div class="post-metadata">

**Author:** ![nalimilan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nalimilan/32/147_2.png) [@nalimilan](https://discourse.julialang.org/u/nalimilan)\
**Post date:** [December 2, 2016, 1:58pm UTC](https://discourse.julialang.org/t/convert-sqlite-data-to-dataframe-without-nullable-type/689/3 "2016-12-02T13:58:51Z")

</div>

Indeed data frames are not yet ready to use with `NullableArray`. See [this thread](https://discourse.julialang.org/t/teaching-data-analysis-with-julia-what-to-do-about-dataframes-and-all-that/422) and [this announcement](https://discourse.julialang.org/t/announcement-dataframes-0-9-0-planned-for-february/).

I’m not sure what’s the best way to work with SQLite.jl at the moment, but if you don’t have any missing values, just convert columns to standard `Array` after importing the data.

---

<div class="post-metadata">

**Author:** ![Christopher\_Fisher](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/christopher_fisher/32/26132_2.png) [@Christopher\_Fisher](https://discourse.julialang.org/u/Christopher_Fisher)\
**Post date:** [December 2, 2016, 2:24pm UTC](https://discourse.julialang.org/t/convert-sqlite-data-to-dataframe-without-nullable-type/689/4 "2016-12-02T14:24:25Z")

</div>

Thanks for the information. Its good to know that the transition will be complete soon. It was not clear to me whether the solution to the “lifting” problem will ultimately include the ability to use typical Julia syntax (e.g. df[:col1] + 1 etc.) Since you are involved in this effort, can you clarify?

I’ll go ahead and convert to to standard arrays as you a suggested. Thanks again.

---

<div class="post-metadata">

**Author:** ![nalimilan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nalimilan/32/147_2.png) [@nalimilan](https://discourse.julialang.org/u/nalimilan)\
**Post date:** [December 2, 2016, 3:45pm UTC](https://discourse.julialang.org/t/convert-sqlite-data-to-dataframe-without-nullable-type/689/5 "2016-12-02T15:45:31Z")

</div>

> [@Christopher\_Fisher](#):
>
> It was not clear to me whether the solution to the “lifting” problem will ultimately include the ability to use typical Julia syntax (e.g. df[:col1] + 1 etc.) Since you are involved in this effort, can you clarify?

It will likely be a little more complex than that: lifting will be enabled for broadcasting operations, so you would write e.g. `df[:col1] .+ 1`. But the recommended way of working with data frames will be via high-level APIs which will lift automatically (see the announcement for details).

---

<div class="post-metadata">

**Author:** ![Matt\_Amos](https://avatars.discourse-cdn.com/v4/letter/m/4af34b/32.png) [@Matt\_Amos](https://discourse.julialang.org/u/Matt_Amos)\
**Post date:** [May 8, 2017, 2:10pm UTC](https://discourse.julialang.org/t/convert-sqlite-data-to-dataframe-without-nullable-type/689/6 "2017-05-08T14:10:22Z")

</div>

Probably not the best solution, but this works for me:

```julia
function convertNullableDF(df::DataFrame)::DataFrame
  for col in 1:ncol(df)
    df[col] = oftype(repeat([get(df[1, col])],inner=2), df[col])
  end
  return df
end

```

---

<div class="post-metadata">

**Author:** ![davidanthoff](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/davidanthoff/32/223493_2.png) [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)\
**Post date:** [May 8, 2017, 4:41pm UTC](https://discourse.julialang.org/t/convert-sqlite-data-to-dataframe-without-nullable-type/689/7 "2017-05-08T16:41:07Z")

</div>

You can use [IterableTables.jl](https://github.com/davidanthoff/IterableTables.jl) for that in the following way:

```julia
using IterableTables, SQLite, DataFrames

db = SQLite.DB(joinpath(Pkg.dir("SQLite"),"test","Chinook_Sqlite.sqlite"))

df = DataFrame(SQLite.Source(db, "SELECT name FROM sqlite_master WHERE type='table';"))

```

This will create a `DataFrame` that uses `DataArray`s for its columns, the standard storage for `DataFrame`s.

If you want the columns to be just a regular `Array`, you can use a [Query.jl](https://github.com/davidanthoff/Query.jl):

```julia
using Query

df = @from i in SQLite.Source(db, "SELECT name FROM sqlite_master WHERE type='table';") begin
     @select {name=get(i.name)}
     @collect DataFrame
end

```

I have various plans for features in both IterableTables and Query that would make it easier to convert all columns from a source table into columns that can’t hold missing values, but not sure when exactly I’ll get to implementing those.

---

<div class="post-metadata">

**Author:** ![Christopher\_Fisher](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/christopher_fisher/32/26132_2.png) [@Christopher\_Fisher](https://discourse.julialang.org/u/Christopher_Fisher)\
**Post date:** [May 8, 2017, 8:37pm UTC](https://discourse.julialang.org/t/convert-sqlite-data-to-dataframe-without-nullable-type/689/8 "2017-05-08T20:37:23Z")

</div>

Thanks for the recommendations. @davidanthoff I look forward to the new features.

---

<div class="post-metadata">

**Author:** ![NightMachinary](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nightmachinary/32/14196_2.png) [@NightMachinary](https://discourse.julialang.org/u/NightMachinary)\
**Post date:** [February 28, 2021, 4:11pm UTC](https://discourse.julialang.org/t/convert-sqlite-data-to-dataframe-without-nullable-type/689/9 "2021-02-28T16:11:10Z")

</div>

Have there been any updates since then?

---

<div class="post-metadata">

**Author:** ![davidanthoff](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/davidanthoff/32/223493_2.png) [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)\
**Post date:** [March 1, 2021, 12:26am UTC](https://discourse.julialang.org/t/convert-sqlite-data-to-dataframe-without-nullable-type/689/10 "2021-03-01T00:26:30Z")

</div>

Yes, see [here](https://www.queryverse.org/Query.jl/stable/standalonequerycommands/#The-@dissallowna-command-1).