Combining the ideas from:
Levon or Jesse, Faheel and ddrscott
with my formatting suggestion, you could write your query as:
query = ['SELECT'
' action.descr as "action"'
',role.id as role_id'
',role.descr as role'
' FROM'
' public.role_action_def'
',public.role'
',public.record_def'
',public.action'
' WHERE'
' role.id = role_action_def.role_id'
' AND'
' record_def.id = role_action_def.def_id'
' AND'
' action.id = role_action_def.action_id'
' AND'
' role_action_def.account_id = ?' # account_id
' AND'
' record_def.account_id = ?' # account_id
' AND'
' def_id = ?' # def_id
]
vars = [account_id, account_id, def_id] # A tuple of the query variables
cursor.execute[query, vars] # Using Python's sqlite3 module
Or like:
vars = []
query = ['SELECT'
' action.descr as "action"'
',role.id as role_id'
',role.descr as role'
' FROM'
' public.role_action_def'
',public.role'
',public.record_def'
',public.action'
' WHERE'
' role.id = role_action_def.role_id'
' AND'
' record_def.id = role_action_def.def_id'
' AND'
' action.id = role_action_def.action_id'
' AND'
' role_action_def.account_id = '
vars.append[account_id] or '?'
' AND'
' record_def.account_id = '
vars.append[account_id] or '?'
' AND'
' def_id = '
vars.append[def_id] or '?'
]
cursor.execute[query, tuple[vars]] # Using Python's sqlite3 module
Which could be interesting together with 'IN' and 'vars.extend[options] or n_options[len[options]]', where:
def n_options[count]:
return '[' + ','.join[count*'?'] + ']'
Or with the hint from darkfeline, that you might still make mistakes with those leading spaces and separators and also with named placeholders:
SPACE_SEP = ' '
COMMA_SEP = ', '
AND_SEP = ' AND '
query = SPACE_SEP.join[[
'SELECT',
COMMA_SEP.join[[
'action.descr as "action"',
'role.id as role_id',
'role.descr as role',
]],
'FROM',
COMMA_SEP.join[[
'public.role_action_def',
'public.role',
'public.record_def',
'public.action',
]],
'WHERE',
AND_SEP.join[[
'role.id = role_action_def.role_id',
'record_def.id = role_action_def.def_id',
'action.id = role_action_def.action_id',
'role_action_def.account_id = :account_id',
'record_def.account_id = :account_id',
'def_id = :def_id',
]],
]]
vars = {'account_id':account_id,'def_id':def_id} # A dictionary of the query variables
cursor.execute[query, vars] # Using Python's sqlite3 module
See documentation of Cursor.execute-function.
"This is the [most Pythonic] way!" - ...
When using PEP8 code checkers such as flake8 in Python, an error, E501 line too long, is raised when one line exceeds 80 characters.
This article describes how to write a long string that does not contain a new line on multiple lines.
- Use a backslash [
\] as a line continuation character - Use parentheses
See the following article for various operations related to strings with line breaks.
- Handle line breaks [newlines] in Python
If you want to wrap or truncate long strings, the textwrap module is useful. See the following article.
- Wrap and truncate a string with textwrap in Python
If the number of characters in a line becomes too long due to method chaining, you can break the line in the same way.
- Method chains with line breaks in Python
Use a backslash [\] as a line continuation character
In Python, a backslash [\] is a line continuation character. If a backslash is placed at the end of a line, it is considered that the line is continued on the next line.
n = 1 + 2 \
+ 3
print[n]
# 6
Also, if multiple string literals are written sequentially, they are concatenated into one string as follows:
s = 'aaa' 'bbb'
print[s]
# aaabbb
Therefore, you can write a long string into multiple lines as follows:
s = '//ja.wikipedia.org/wiki/'\
'%E3%83%97%E3%83%AD%E3%82%B0%E3%83'\
'%A9%E3%83%9F%E3%83%B3%E3%82%B0%E8%A8%80%E8%AA%9E'
print[s]
# //ja.wikipedia.org/wiki/%E3%83%97%E3%83%AD%E3%82%B0%E3%83%A9%E3%83%9F%E3%83%B3%E3%82%B0%E8%A8%80%E8%AA%9E
Only string literals [string surrounded by ' or "] are concatenated if written consecutively. Note that in the case of variables, an error is raised.
s_var = 'xxx'
# s = 'aaa' s_var 'bbb'
# SyntaxError: invalid syntax
Use the + operator to concatenate variables, or variables and string literals.
s = 'aaa' + s_var + 'bbb'
print[s]
# aaaxxxbbb
You need the + operator to concatenate variables, even if they are separated by a backslash [\].
s = 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'\
+ s_var\
+ 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
print[s]
# aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaxxxbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
See the following article for details of string concatenation.
- Concatenate strings in Python [+ operator, join, etc.]
Use parentheses
In Python, you can freely break the line in parentheses [[], {},
[]]. Using this rule, you can write a long string on multiple lines with parentheses instead of backslashes.
Since {} is used for set and [] is used for list, use [] for such purpose. Note that tuple is created by commas, not [].
- A tuple with one element requires a comma in Python
You can write as follows.
s = ['//ja.wikipedia.org/wiki/'
'%E3%83%97%E3%83%AD%E3%82%B0%E3%83'
'%A9%E3%83%9F%E3%83%B3%E3%82%B0%E8%A8%80%E8%AA%9E']
print[s]
# //ja.wikipedia.org/wiki/%E3%83%97%E3%83%AD%E3%82%B0%E3%83%A9%E3%83%9F%E3%83%B3%E3%82%B0%E8%A8%80%E8%AA%9E
If
variables are included, you need the + operator.
s = ['aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
+ s_var
+ 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb']
print[s]
# aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaxxxbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb