CODEWITHSUNDEEP
julia
imantha
imantha

Reputation: 3838

Julia Querying from SQLite

Following this tutorial https://www.youtube.com/watch?app=desktop&v=qUrtLJcehE0, I created a database called Movies. Within the database a table called movies was created and next an entry was also added,

using SQLite
db = SQLite.DB("Movies")
SQLite.execute(db,"CREATE TABLE IF NOT EXISTS movies(movie_id REAL,movie_name TEXT, location TEXT)")
SQLite.execute(db,"INSERT INTO movies (movie_id,movie_name,location) VALUES(1,'Avengers','USA')")

However now when I try to Query the entry as follows,

SQLite.Query(db, "SELECT * from movies")

I get the this error, Error: MethodError: no method matching SQLite.Query.(::SQLite.DB,::String). Any Ideas what I am doing wrong?

Upvotes: 2

Views: 1676

Answers (3)

Åsmund Hj
Åsmund Hj

Reputation: 161

The execute function is defined in DBInterface. Might not be obvious, but the following works:

begin
    using SQLite;
    using DBInterface:execute;
    using TypedTables, DataFrames

    db = SQLite.DB()

    ( (id = i, name="Row number $i", v= rand() ) for i in 1:1000 ) |> 
    Table |> 
    SQLite.load!(db, "test")

    execute(db, "select * from test") |> Table

end

Upvotes: 0

imantha
imantha

Reputation: 3838

To load a table using SQLite package,

using SQLite
using DataFrames

# Make a connection
db = SQLite.DB("Movies")

# To find out all tables available in schema
tbls = SQLite.tables(db)

# To load a specific table (movies table from Movies.db)
q = "SELECT * FROM movies"
data = SQLite.DBInterface.execute(db,q)

# To get as a dataframe
df = DataFrames.DataFrame(data)

Upvotes: 3

Nathan Boyer
Nathan Boyer

Reputation: 1474

I don't know SQL, but I think you want to use SQLite.execute again not SQLite.Query. SQLite.Query is a struct not a function, and it doesn't have any documentation. I don't think you are meant to call it externally. Further documentation is here.

Method error means you are calling something with the wrong arguments. The SQLite.Query struct expects all of the following arguments:

struct Query
    stmt::Stmt
    status::Base.RefValue{Cint}
    names::Vector{Symbol}
    types::Vector{Type}
    lookup::Dict{Symbol, Int}
end

The SQLite.execute function expects arguments in one of these forms:

  SQLite.execute(db::SQLite.DB, sql, [params])
  SQLite.execute(stmt::SQLite.Stmt, [params])

By convention in Julia, functions are all lowercase and types are capitalized.

Upvotes: 3

Related Questions