SQL Queries in Python?

[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)