How to Run Raw SQL Queries with Sequel

How to run raw SQL queries with Sequel

I have a few pointers which may be useful:

You could simply do:

 @zonename = DB.fetch("SELECT * FROM zone WHERE dialcode = ? LIMIT 1", @dialcode).first

NB: you are ignoring the fact that there could be more results matching the criteria. If you expect multiple possible rows to be returned then you probably want to build an array of results by doing ...

    @zonename = DB.fetch("SELECT * FROM zone WHERE dialcode = ? LIMIT 1", @dialcode).all

and processing all of them.

The return set is a hash. If @zonename points to one of the records then you can do
```
 @zonename[:column_name] 
```

to refer to a field called "column_name". You can't do @zonename.column_name (you could actually decorate @zonename with helper methods using some meta-programming but let's ignore that for the moment).

Sequel is an excellent interface, the more you learn about it the more you'll like it.

How to put a raw SQL query in Sequel

You can do it a couple ways:

Use []:
```
DB["your sql string"]
```
Use fetch:
```
DB.fetch("your sql string")
```

How do you execute a query in Sequel Pro?

Use ⌘+R to execute the selected Query.

Alternatively, use the dropdown that appears at the bottom right of the query editor and select Run Current or Run Previous depending on where your text cursor is.

Is there a way to see the raw SQL that a Sequel expression will generate?

You can call sql on dataset:

db.select(:id).from(:some_table).where(:foo => 5).sql # => "SELECT `id` FROM `some_table` WHERE (`foo` = 5)"

For update queries you can do this:

db.from(:some_table).update_sql(:foo => 5) # => "UPDATE `some_table` SET `foo` = 5"

Some similar useful methods:

insert_sql
delete_sql
truncate_sql

How to sanitize raw SQL in a Ruby script

I don't know Sequel, but did you try standard insert method?

connection = Sequel.connect('...')
table_name = connection.from(:table_name)
# OR
# table_name = DB.from(:table_name)
# table_name = DB[:table_name]
table_name.insert(csv_row.to_h)

It's more reliable I believe, because you avoid difference between COLUMN_NAMES and record_values.

Multiple aggregate queries using the sequel gem

You can do that query without writing raw SQL with the following:

dataset = DB.select {[ 
  DB[:users].where(blah: 'blah').select { count('*') }.as(:users),
  DB[:contacts].where(blah: 'blah').select { count('*') }.as(:contacts) 
]}

dataset.first
# => { users: X, contacts: Y }

dataset.sql
# => "SELECT (SELECT count('*') FROM \"users\" WHERE (\"blah\" = 'blah')) AS \"users\", 
#            (SELECT count('*') FROM \"contacts\" WHERE (\"blah\" = 'blah')) AS \"contacts\""

How can I write a PostgreSQL query using Sequel without raw SQL?

DB[:expense_projects___p].where(:project_company_id=>user_company_id).
  left_join(:expense_items___i, :expense_project_id=>:project_id).
  select_group(:p__project_name, :p__project_id).
  select_more{count(:i__item_id)}.
  select_more{sum(:i__amount)}.to_a.to_json

How to execute raw SQL in Flask-SQLAlchemy app

Have you tried:

result = db.engine.execute("")

or:

from sqlalchemy import text

sql = text('select name from penguins')
result = db.engine.execute(sql)
names = [row[0] for row in result]
print names

Note that db.engine.execute() is "connectionless", which is deprecated in SQLAlchemy 2.0.

How to Run Raw SQL Queries with Sequel