There are many variants of SQL. DB-API only solves for so much.
>From "[Python-ideas] Draft PEP on string interpolation" (=> f-strings)
https://groups.google.com/d/msg/python-ideas/6tfm3e2UtDU/8ugqbZtYAwAJ :
```quote
IIUC, to do this with SQL,
> sql(i'select {date:as_date} from {tablename}'
needs to be
['select ', unescaped(date, 'as_date'), 'from ', unescaped(tablename)]
so that e.g. sql_92(), sql_2011()
would know that 'select ' is presumably implicitly escaped
* https://en.wikipedia.org/wiki/SQL#Interoperability_and_standardization
* http://docs.sqlalchemy.org/en/rel_1_0/dialects/
* https://docs.djangoproject.com/en/1.7/ref/models/queries/#f-expressions
"Django F-Expressions"
```
And from the next message:
```quote
[... Django F-Objects, Q-Objects, deferred evaluation due to schema
dialects]
Each ORM (and DBAPI) [has] parametrization settings
(e.g. '%' or '?' or configuration_setting)
which should not collide with the f-string syntax.
* DBAPI v2.0
https://www.python.org/dev/peps/pep-0249/
* SQLite DBAPI
https://docs.python.org/2/library/sqlite3.html
https://docs.python.org/3/library/sqlite3.html
http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html#conjunctions
>>> s = select([(users.c.fullname +
... ", " + addresses.c.email_address).
... label('title')]).\
... where(users.c.id == addresses.c.user_id).\
... where(users.c.name.between('m', 'z')).\
... where(
... or_(
... addresses.c.email_address.like('%@aol.com'),
... addresses.c.email_address.like('%@msn.com')
... )
... )
>>> conn.execute(s).fetchall()
SELECT users.fullname || ? || addresses.email_address AS title
FROM users, addresses
WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND
(addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
(', ', 'm', 'z', '%@aol.com', '%@msn.com')
[(u'Wendy Williams, we...@aol.com',)]
>>> from sqlalchemy.sql import text
>>> s = text(
... "SELECT users.fullname || ', ' || addresses.email_address AS title "
... "FROM users, addresses "
... "WHERE users.id = addresses.user_id "
... "AND users.name BETWEEN :x AND :y "
... "AND (addresses.email_address LIKE :e1 "
... "OR addresses.email_address LIKE :e2)")
SQL
>>> conn.execute(s, x='m', y='z', e1='%@aol.com', e2='%@msn.com').fetchall()
[(u'Wendy Williams, we...@aol.com',)]
```
On Sat, Feb 22, 2020 at 12:35 AM David Mertz <me...@gnosis.cx> wrote:
> OMG, please no! Please, for all that is decent, do not use an ORM in any
> code I will ever need to look at!
>
> The SQL injection attack is just silly if you don't run arbitrary
> strings. Don't ever do that. But running a query that is hard coded as
> text, with just a few parameters filled in (the DB-API is handy) is good...
> yes, check those parameters for sanity and permission first.
>
> On Fri, Feb 21, 2020 at 10:39 PM Bruce Leban <br...@leban.us> wrote:
>
>>
>> On Fri, Feb 21, 2020 at 5:53 AM <minecraft2...@gmail.com> wrote:
>>
>>> The idea is to add a new string prefix 's' for SQL string. This string
>>> doesn't do anything in Python, unlike b"" or f"" strings, but interactive
>>> Python shells like IPython or Jupyter can parse the following characters as
>>> SQL syntax instead of Python syntax and give SQL syntax highlighting and
>>> autocompletion, and if they are configured correctly, they can do column
>>> name autocompletion. Unfortunately when I try to type s"select * from
>>> table" it gave me syntax error instead, so I think this need to be
>>> implemented in Python language itself instead of module
>>>
>>
>> First, as to SQL specifically, writing literal SQL in code is a bad idea.
>> It's easy to have bugs, especially sql injection. You should use an ORM at
>> the very least a SQL builder. Instead of:
>>
>> sf"select * from sometable where name = '{userName}'"
>>
>> you would write something like:
>>
>> sql.query(SomeTable).filter_by(name=userName).all()
>>
>> And I believe the same thing applies to HTML and just about anything else
>> that has a complicated enough syntax that this idea would be useful for.
>>
>> Second, if I had a strong reason to do something like this, I'd want to
>> use a function that enabled me to add run-time sanity checking (at least
>> during development and testing phase):
>>
>> _html_(f"This is a <b><i>{adverb} bad</b></i> example.")
>>
>> and in production that function would just return the value untouched.
>>
>> --- Bruce
>> _______________________________________________
>> Python-ideas mailing list -- python-ideas@python.org
>> To unsubscribe send an email to python-ideas-le...@python.org
>> https://mail.python.org/mailman3/lists/python-ideas.python.org/
>> Message archived at
>> https://mail.python.org/archives/list/python-ideas@python.org/message/GVTWOF2KDOHO4E4EM4CAESDVDETR5S6R/
>> Code of Conduct: http://python.org/psf/codeofconduct/
>>
>
>
> --
> Keeping medicines from the bloodstreams of the sick; food
> from the bellies of the hungry; books from the hands of the
> uneducated; technology from the underdeveloped; and putting
> advocates of freedom in prisons. Intellectual property is
> to the 21st century what the slave trade was to the 16th.
> _______________________________________________
> Python-ideas mailing list -- python-ideas@python.org
> To unsubscribe send an email to python-ideas-le...@python.org
> https://mail.python.org/mailman3/lists/python-ideas.python.org/
> Message archived at
> https://mail.python.org/archives/list/python-ideas@python.org/message/VRU4A7BQK5OZ6SA4QIQEBLZFGKNO7JAM/
> Code of Conduct: http://python.org/psf/codeofconduct/
>
_______________________________________________
Python-ideas mailing list -- python-ideas@python.org
To unsubscribe send an email to python-ideas-le...@python.org
https://mail.python.org/mailman3/lists/python-ideas.python.org/
Message archived at
https://mail.python.org/archives/list/python-ideas@python.org/message/J7VV3QVCW4BJZUXX723OMOC6T4WFEGYI/
Code of Conduct: http://python.org/psf/codeofconduct/
