Julia Querying from SQLite

user_10639382 0
Viewed 841

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?

julia
2 Answers

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.

user_134950490

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)
user_106393820
Related