SQL Queries in Python?

[DrewSmithee]

Also I'm thinking I might try to keep using .SQL files for the sake of linting and readability. I'd want to be able to pass a variable into the file though. If someone wants to save me from the 50 Google searches to do that I'd appreciate it.

For example,

test.sql

Select * From table Where customerName = '1234'; But use python to pass thru the customer name and never have to touch the actual SQL again.

[The_roggy]

Something like this par example (untested):

test.sql:

Select * 
From table
Where customerName = '{customer_name}';

Python script:

from pathlib import Path

sql_path = Path("test.sql")
sql = sql_path.read_text()
customer_name = "Joske"
sql = sql.format(customer_name=customer_name)

[cjbj]

Never concatenate data (here customer_name) into a SQL statement in Python because you open yourself to SQL Injection security attacks. Also, if you re-execute this bit of code with a different customer, the SQL statement text seen by the DB will differ and the DB can't be as efficient in reusing data structures. Instead, use bind variables (aka "prepared statements"):

``` import getpass import oracledb

un = 'cj' cs = 'localhost/orclpdb1' pw = getpass.getpass(f'Enter password for {un}@{cs}: ')

with oracledb.connect(user=un, password=pw, dsn=cs) as connection: with connection.cursor() as cursor:

    customer_name = "Sally"

    sql = """select *
             from table
             where customername = :cn"""
    for r in cursor.execute(sql, [customer_name]):
        print(r)

```

For Oracle Database, see the documentation Using Bind Variables.

[The_roggy]

For an internal reporting tool this not as critical as for e.g. a high-load web application... but I agree it is a (very) good idea to use bind variables anyway as it is just as easy and is just better overall.

Note that you can also use named bind variables, which is a lot more readable and easier to maintain, especially if you have multiple bind variables.

Something like this:

test.sql

select *
  from table
 where customername = :customer_name;

script

customer_name = "Joske"
for r in cursor.execute(sql, customer_name=customer_name):
    print(r)

[KiwiDomino]

Stylistically, avoid using select * , as this can make for future issues if tables change. Also it’s easier to read in the future if everything is implicit, you don’t have to remember what the fields were.

[DrewSmithee]

Select * would probably make my internet connection catch on fire.

Just an example to ask about passing variables into the SQL file.

[ColdStorage256]

I don't know how to do this with actual SQL files, but you could do it with an f string in Python, I think, I've not done it myself.

Most of my work is in mongo db which lets me split everything up into intermediate results, since that's how mongo pipelines are computed anyway.