Coverage for /home/martinb/.local/share/virtualenvs/camcops/lib/python3.6/site-packages/cardinal_pythonlib/sql/literals.py : 33%

Hot-keys on this page
r m x p toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1#!/usr/bin/env python
2# cardinal_pythonlib/sql/literals.py
4"""
5===============================================================================
7 Original code copyright (C) 2009-2021 Rudolf Cardinal (rudolf@pobox.com).
9 This file is part of cardinal_pythonlib.
11 Licensed under the Apache License, Version 2.0 (the "License");
12 you may not use this file except in compliance with the License.
13 You may obtain a copy of the License at
15 https://www.apache.org/licenses/LICENSE-2.0
17 Unless required by applicable law or agreed to in writing, software
18 distributed under the License is distributed on an "AS IS" BASIS,
19 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
20 See the License for the specific language governing permissions and
21 limitations under the License.
23===============================================================================
25**Functions to manipulate raw SQL.**
27"""
29from typing import Generator
31from cardinal_pythonlib.datetimefunc import DateLikeType, DateTimeLikeType
33COMMA = ","
34SQUOTE = "'"
35DOUBLE_SQUOTE = "''"
38# =============================================================================
39# SQL elements: literals
40# =============================================================================
42def sql_string_literal(text: str) -> str:
43 """
44 Transforms text into its ANSI SQL-quoted version, e.g. (in Python ``repr()``
45 format):
47 .. code-block:: none
49 "some string" -> "'some string'"
50 "Jack's dog" -> "'Jack''s dog'"
51 """
52 # ANSI SQL: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
53 # <character string literal>
54 return SQUOTE + text.replace(SQUOTE, DOUBLE_SQUOTE) + SQUOTE
57sql_quote_string = sql_string_literal # synonym
60def sql_date_literal(dt: DateLikeType) -> str:
61 """
62 Transforms a Python object that is of duck type ``datetime.date`` into
63 an ANSI SQL literal string, like '2000-12-31'.
64 """
65 # ANSI SQL: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
66 # <date string>
67 return dt.strftime("'%Y-%m-%d'")
70def sql_datetime_literal(dt: DateTimeLikeType,
71 subsecond: bool = False) -> str:
72 """
73 Transforms a Python object that is of duck type ``datetime.datetime`` into
74 an ANSI SQL literal string, like ``'2000-12-31 23:59:59'``, or if
75 ``subsecond=True``, into the (non-ANSI) format
76 ``'2000-12-31 23:59:59.123456'`` or similar.
77 """
78 # ANSI SQL: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
79 # <timestamp string>
80 # ... the subsecond part is non-ANSI
81 fmt = "'%Y-%m-%d %H:%M:%S{}'".format(".%f" if subsecond else "")
82 return dt.strftime(fmt)
85def sql_comment(comment: str) -> str:
86 """
87 Transforms a single- or multi-line string into an ANSI SQL comment,
88 prefixed by ``--``.
89 """
90 """Using -- as a comment marker is ANSI SQL."""
91 if not comment:
92 return ""
93 return "\n".join(f"-- {x}" for x in comment.splitlines())
96# =============================================================================
97# Reversing the operations above
98# =============================================================================
100def sql_dequote_string(s: str) -> str:
101 """
102 Reverses :func:`sql_quote_string`.
103 """
104 if len(s) < 2 or s[0] != SQUOTE or s[-1] != SQUOTE:
105 raise ValueError("Not an SQL string literal")
106 s = s[1:-1] # strip off the surrounding quotes
107 return s.replace(DOUBLE_SQUOTE, SQUOTE)
110# =============================================================================
111# Processing SQL CSV values
112# =============================================================================
114def gen_items_from_sql_csv(s: str) -> Generator[str, None, None]:
115 """
116 Splits a comma-separated list of quoted SQL values, with ``'`` as the quote
117 character. Allows escaping of the quote character by doubling it. Returns
118 the quotes (and escaped quotes) as part of the result. Allows newlines etc.
119 within the string passed.
120 """
121 # csv.reader will not both process the quotes and return the quotes;
122 # we need them to distinguish e.g. NULL from 'NULL'.
123 # log.warning('gen_items_from_sql_csv: s = {0!r}', s)
124 if not s:
125 return
126 n = len(s)
127 startpos = 0
128 pos = 0
129 in_quotes = False
130 while pos < n:
131 if not in_quotes:
132 if s[pos] == COMMA:
133 # end of chunk
134 chunk = s[startpos:pos] # does not include s[pos]
135 result = chunk.strip()
136 # log.warning('yielding: {0!r}', result)
137 yield result
138 startpos = pos + 1
139 elif s[pos] == SQUOTE:
140 # start of quote
141 in_quotes = True
142 else:
143 if pos < n - 1 and s[pos] == SQUOTE and s[pos + 1] == SQUOTE:
144 # double quote, '', is an escaped quote, not end of quote
145 pos += 1 # skip one more than we otherwise would
146 elif s[pos] == SQUOTE:
147 # end of quote
148 in_quotes = False
149 pos += 1
150 # Last chunk
151 result = s[startpos:].strip()
152 # log.warning('yielding last: {0!r}', result)
153 yield result