How to get a row from a database (SQLite3) into a string from Julia?

user_19191892 0
Viewed 91

Julia, SQLite3

Hi! I have an SQLite3 database and want to get information from it into a string, not DataFrame.

# Connection to database
db = SQLite.DB(raw"pictures.sqlite")
# Creation of table
SQLite.execute(db, "DROP TABLE IF EXISTS Files")
SQLite.execute(db, "CREATE TABLE IF NOT EXISTS Files
                            (ID INTEGER PRIMARY KEY AUTOINCREMENT, 
                            FullName TEXT,
                            creation_date TEXT,
                            change_date TEXT,
                            size  INTEGER,
                            UNIQUE (ID))")
#Another code
#This is a way to add rows to database (it works)
DBInterface.execute(db, 
            "INSERT INTO Files (FullName, creation_date, change_date, size) 
            VALUES
            ('$fileName', '$cdate', '$mdate', '$fileSize')
        ")
#Then i am trying to get one row into a string
strq = SQLite.DBInterface.execute(db, "SELECT * FROM Files WHERE ID=3")
#I can't transfornm these to string

I'm writing on Julia and need help!!

julia sqlite
1 Answers

TLDR:

CSV.write(stdout, strq);

Explanation

The API of SQLite.jl assumes using DBInterface.execute for querying the database. This method yields an object of type SQLite.Query.

Let's have a look what can we do with it:

julia> methodswith(SQLite.Query, supertypes=true)
[1] eltype(q::SQLite.Query) in SQLite at ~/.julia/packages/SQLite/wpQeE/src/tables.jl:51
[2] isempty(q::SQLite.Query) in SQLite at ~/.julia/packages/SQLite/wpQeE/src/tables.jl:15
[3] iterate(q::SQLite.Query) in SQLite at ~/.julia/packages/SQLite/wpQeE/src/tables.jl:94
[4] iterate(q::SQLite.Query, rownumber) in SQLite at ~/.julia/packages/SQLite/wpQeE/src/tables.jl:100

You can see one important thing - you can do whatever you want with SQLite.Query as long as it matches the Tables.jl interfaces. So you are not obliged to use DataFrames but there is no "pure string" implementation, such thing would not make any sense neither.

However, there are many interfaces for Tables. The one you need is CSV.jl will exactly give you something like "string representation" of your SQL table.

Try this with your code (I inserted one row to my table to see the results):

julia> CSV.write(stdout, strq);
ID,FullName,creation_date,change_date,size
1,somefilename,somecdate,somemfate,0
user_99577100
Related