Coverage for src/pyensae/sql/database_core.py : 85%
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"""
2@file
4@brief @see cl Database
5"""
6import os
7import sys
8import math
9import re
10import time
11import decimal
12import sqlite3 as SQLite
13import datetime
14import numpy
15from .database_exception import ExceptionSQL, DBException
16from .database_core2 import DatabaseCore2
18module_odbc = None
21class DatabaseCore(DatabaseCore2):
22 """
23 Core methods for class @see cl Database.
25 @var _engine engine type (SQLite is the only available)
26 @var _sql_file database file, if it does not exist, it will be created.
27 """
29 _sql_keywords = ["order", "by", "select", "from", "group", "where", "as", "like", "upper", "collapse", "join", "union",
30 "inner", "default", "id", "double", "text", "varchar", "float", "long", "Decimal"]
32 _SQL_conversion_types = {"": float,
33 "TEXT": str,
34 "text": str,
35 "INTEGER": int,
36 "FLOAT": float,
37 "REAL": float,
38 "float": float,
39 "numeric": float,
40 "LONG": int,
41 "int": int,
42 "varchar": str,
43 "VARCHAR": str,
44 "Decimal": decimal.Decimal,
45 "DATETIME": datetime.datetime,
46 "smallint": int,
47 "bigint": float, }
49 _engines = ["SQLite", "MySQL", "ODBCMSSQL"]
50 _field_option = ["PRIMARYKEY", "AUTOINCREMENT", "AUTOFILL"]
52 def __init__(self, sql_file, engine="SQLite", user=None, password=None,
53 host="localhost", LOG=None, attach=None):
54 """
55 Creates a database object.
57 @param sql_file database file database file (use ``:memory:`` to avoid creating a file and using only memory)
58 it can also contain several files separated by ;
59 ``name_file ; nickname,second_file ; ...``
60 @param engine SQLite or MySQL (if it is installed), ODBCMSSQL
61 @param user user if needed
62 @param host to connect to a MSSQL database
63 @param password password if needed
64 @param LOG LOG function, if None, choose ``print``
65 @param attach dictionary ``{nickname: filename}``,
66 list of databases to attach
68 @warning If the folder does not exist, it will be created
70 Parameter *dbfile* can be of type
71 `sqlite3.Connection <https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection>`_.
72 """
74 # attach cases
75 if attach is None:
76 attach = {}
77 else:
78 attach = attach.copy()
80 if isinstance(sql_file, str):
82 for e in DatabaseCore._engines:
83 if sql_file.startswith(e + ":::"):
84 engine = e
85 sql_file = sql_file[len(e) + 3:]
86 if "###" in sql_file:
87 host, sql_file = sql_file.split("###")
88 break
90 if ";" in sql_file:
91 li = [s.strip() for s in sql_file.split(";")]
92 sql_file = li[0]
93 rest = li[1:]
94 for s in rest:
95 ok = s.split(",")
96 if len(ok) != 2:
97 raise DBException( # pragma: no cover
98 "unable to find an alias in %r" % s)
99 nick = ok[0].strip()
100 file = ",".join(ok[1:])
101 attach[nick] = file.strip()
102 elif sql_file.startswith(":"):
103 if sql_file != ":memory:":
104 raise FileNotFoundError( # pragma: no cover
105 "unable to interpret file: %r" % sql_file)
107 # some initialization
108 self._password = password
109 self._user = user
110 self._host = host
112 # the rest
113 if LOG is None:
114 def blind(*li, **p): # pragma: no cover
115 pass
116 LOG = blind # pragma: no cover
117 self.LOG = LOG
119 if isinstance(LOG, dict):
120 raise TypeError( # pragma: no cover
121 "fLOG should be a function, not a dictionary")
122 if isinstance(self.LOG, dict):
123 raise TypeError( # pragma: no cover
124 "LOG should be a function, not a dictionary")
126 if engine == "SQLite":
127 self._sql_file = sql_file
128 self._engine = engine
130 elif engine == "ODBCMSSQL":
131 raise DBException( # pragma: no cover
132 "Unable to connect to a SQL server.")
134 else:
135 raise DBException( # pragma: no cover
136 "unfounded engine %s in %s" %
137 (engine, ", ".join(
138 DatabaseCore._engines)))
140 # write a file able to build a database summary
141 if isinstance(sql_file, str) and not self.isMemory():
142 folder = os.path.split(sql_file)[0]
143 if len(folder) > 0 and not os.path.exists(folder):
144 os.makedirs(folder)
145 summary = os.path.join(folder, "temp_quick_look_up.py")
146 if not os.path.exists(summary):
147 #cwd = os.path.join (os.path.abspath (os.path.split (__file__) [0]), "..", "..")
148 #fi = os.path.split (sql_file) [1]
150 if hasattr(DatabaseCore, "SCRIPT_LOOKUP"):
151 script = DatabaseCore.SCRIPT_LOOKUP
152 lines = script.split("\n")
153 lines = [li if "__CWD__ =" not in li else
154 li.replace(
155 "(__file__)",
156 "(r'%s')" %
157 os.path.realpath(__file__))
158 for li in lines]
159 script = "\n".join(lines)
160 script = script.replace(
161 "python quick_look_up.py",
162 "%s quick_look_up.py" %
163 sys.executable)
164 self.LOG("creating script ", summary)
165 try:
166 f = open(summary, "w")
167 f.write(script)
168 f.close()
169 except IOError:
170 self.LOG("unable to write ", summary)
172 self._attach = attach
173 self._buffer_insert = []
174 self._buffer_insert_s = 0
176 if isinstance(sql_file, str) and self.isMemory():
177 self._connection = SQLite.connect(self._sql_file)
178 elif isinstance(sql_file, SQLite.Connection):
179 self._connection = sql_file
180 self._sql_file = ":memory:"
182 def isMSSQL(self):
183 """
184 Says if the syntax is MS SQL Server.
185 """
186 if self._engine == "ODBCMSSQL":
187 return True
188 return False
190 def isMemory(self):
191 """
192 Tells if the Database takes place in memory (``:memory:``).
193 """
194 return self._sql_file == ":memory:"
196 ##########################################################################
197 # connection
198 ##########################################################################
200 def SetBufferInsert(self, n):
201 """
202 This function offers the possibility to postpone the insertion,
203 they will be processed all at the time during when method commit is called.
205 @param n number of insertion to postpone
206 """
207 self._buffer_insert_s = n
209 def is_connected(self):
210 """
211 Says if the database is connected.
213 @return "_connection" in self.__dict__
214 """
215 return "_connection" in self.__dict__
217 @staticmethod
218 def regex_match(exp, st):
219 "Applies a regular expression. Static method to insert in a SQL query."
220 return 0 if re.compile(exp).search(st) is None else 1
222 @staticmethod
223 def idaytodate(dayint, year, month, day):
224 "Date conversion. Static method to insert in a SQL query."
225 try:
226 d = datetime.datetime(year, month, day)
227 day = datetime.datetime(year, month, day + 1) - d
228 cur = d + day * dayint
229 return str(cur).split()[0]
230 except Exception as e:
231 return str(e)
233 @staticmethod
234 def isectoday(sec):
235 "Date conversion. Static method to insert in a SQL query."
236 if sec < 0:
237 return "negative time"
238 elif sec >= 86400:
239 return "out of day"
240 else:
241 s = int(sec)
242 h = s / 3600
243 m = (s % 3600) / 60
244 s %= 60
245 return "%02d:%02d:%02d" % (h, m, s)
247 @staticmethod
248 def itimestamp(t, year, month, day):
249 "Date conversion. Static method to insert in a SQL query."
250 d = DatabaseCore.idaytodate(int(t / 86400), year, month, day)
251 s = DatabaseCore.isectoday(int(t - 86400. * int(t / 86400)))
252 return d + " " + s
254 @staticmethod
255 def string_to_date(s):
256 "Date conversion. Static method to insert in a SQL query."
257 d = int(s[:2])
258 m = int(s[3:5])
259 y = int(s[6:])
260 return datetime.datetime(y, m, d)
262 @staticmethod
263 def _special_function_init_():
264 _list_special_function = [
265 ("log", math.log, 1, "log(s) --> float", "log"),
266 ("exp", math.exp, 1, "exp(s) --> float", "exp"),
267 ("len", len, 1, "len(s) --> int", "string length"),
268 ("lower",
269 lambda s:s.lower(),
270 1,
271 "lower(s) --> string",
272 "lower case"),
273 ("upper",
274 lambda s:s.upper(),
275 1,
276 "upper(s) --> string",
277 "upper case"),
278 ("isubstring", lambda sub, s: 1 if sub in s else 0,
279 2, "isubstring(sub,str) --> {0,1}", "return 1 if str includes sub, 0 otherwise"),
280 ("match", DatabaseCore.regex_match,
281 2, "match(regex,str) --> {0,1}", "return 1 if str matches the regular expression exp, 0 otherwise"),
282 ("idaytodate", DatabaseCore.idaytodate,
283 4, "idaytodate (day, 1970, 1, 1) --> str", "date if day is the number of days since 01/01/1970"),
284 ("itimestamp", DatabaseCore.itimestamp,
285 4, "itimestamp (t, 1970, 1, 1) --> str", "date,time if t is the number of seconds since 01/01/1970"),
286 ("isectoday", DatabaseCore.isectoday,
287 1, "isectoday (isec) --> str", "time if isec is the number of seconds since midnight"),
288 ]
289 return _list_special_function
291 def connect(self, odbc_string=None):
292 """
293 Opens a connection to the database.
295 @param odbc_string use a different odbc string
296 """
297 if self.isMemory():
298 if "_connection" not in self.__dict__:
299 raise DBException( # pragma: no cover
300 "It is a database in memory, the database should already be connected.")
301 else:
302 if "_connection" in self.__dict__:
303 raise RuntimeError("A previous connection was not closed.")
305 if self._engine == "SQLite":
306 self._connection = SQLite.connect(self._sql_file)
307 # elif self._engine == "MySQL" : self._connection =
308 # MySQLdb.connect (self._host, self._user, self._password,
309 # self._sql_file)
310 elif self._engine == "ODBCMSSQL": # pragma: no cover
312 if odbc_string is None:
313 temp = ["DRIVER={SQL Server Native Client 10.0}", # {SQL Server}",
314 "SERVER=%s" % self._host,
315 "DATABASE=%s" % self._sql_file,
316 "Trusted_Connection=yes",
317 "MARS_Connection=yes",
318 # "MultipleActiveResultSets=True",
319 #"Integrated Security=SSPI",
320 ]
321 #temp = ["DSN=%s" % self._sql_file ]
322 if self._user is not None:
323 temp.append("UID=%s" % self._user)
324 if self._password is not None:
325 temp.append("PASSWORD=%s" % self._password)
326 st = ";".join(temp)
327 self.LOG("connection string ", st)
328 self._connection = module_odbc.connect(st)
329 else:
330 st = odbc_string
331 self.LOG("connection string ", st)
332 self._connection = module_odbc.connect(st)
334 else:
335 raise DBException( # pragma: no cover
336 "This engine does not exists (%r)" % self._engine)
338 for func in DatabaseCore._special_function_init_():
339 self.add_function(func[0], func[2], func[1])
341 for k, v in self._attach.items():
342 self.attach_database(v, k)
344 def close(self):
345 """
346 Closes the database.
347 """
348 if self.isMemory():
349 # we should not close, otherwise, we lose the data
350 pass
351 else:
352 self._check_connection()
353 self._connection.close()
354 del self._connection
356 def commit(self):
357 """
358 Calls this function after any insert request.
359 """
360 self._check_connection()
362 for s in self._buffer_insert:
363 self._connection.execute(s)
364 del self._buffer_insert[:]
366 self._connection.commit()
368 ##########################################################################
369 # access part
370 ##########################################################################
372 def get_file(self, attached_db=False):
373 """
374 Gets database file.
376 @param attached_db if True, add the list of attached databases
377 @return the database file
378 """
379 if attached_db:
380 files = [self._sql_file]
381 att = self.get_attached_database_list(True)
382 for alias, file in att:
383 files.append("%s,%s" % (alias, file))
384 temp = ";".join(files)
385 if self._engine != "SQLite":
386 if self._host is None:
387 temp = "%s:::%s" % (self._engine, temp)
388 else:
389 temp = "%s:::%s###%s" % (self._engine, self._host, temp)
390 return temp
391 else:
392 return self._sql_file
394 def has_table(self, table):
395 """
396 Says if the table belongs to the database.
398 @param table table name
399 @return boolean
400 """
401 return table in self.get_table_list("." in table)
403 def has_index(self, index):
404 """
405 Says if the index belongs to the database.
407 @param index index name
408 @return boolean"""
409 return index in [s[0] for s in self.get_index_list()]
411 def get_index_on_table(self, table, full=False):
412 """
413 Returns the list of indexes on a specific table.
415 @param table table
416 @param full if True returns all fields, otherwise, returns only the index names
417 @return list of the index on this table
418 """
419 indexes = self.get_index_list()
420 if full:
421 return [la for la in indexes if la[1] == table]
422 return [la[0] for la in indexes if la[1] == table]
424 def get_column_type(self, table, column):
425 """
426 Returns the column type of a table.
428 @param table table name
429 @param column column name
430 @return type (python class)
431 """
432 self._check_connection()
433 cols = self.get_table_columns_list(table)
434 for c in cols:
435 if c[0] == column:
436 return c[1]
437 raise DBException(
438 "column %s were not found in table %s" %
439 (column, table))
441 def get_index_list(self, attached="main"):
442 """
443 Returns the list of indexes.
445 @param attached if main, returns the index for the main database, otherwise, for an attached database
446 @return list of tuple (index_name, table, sql_request, fields)
447 """
448 self._check_connection()
449 if attached == "main":
450 request = """ SELECT name,tbl_name,sql
451 FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) AS temptbl
452 WHERE type='index' ORDER BY name;"""
453 else:
454 request = """ SELECT name,tbl_name,sql
455 FROM (SELECT * FROM %s.sqlite_master) AS temptbl
456 WHERE type='index' ORDER BY name;""" % attached
457 select = self._connection.execute(request)
459 exp = re.compile("[(]([a-zA-Z0-9_,]+)[)]")
460 res = []
461 for a, b, c in select:
462 fi = exp.findall(c)
463 if len(fi) != 1:
464 raise DBException( # pragma: no cover
465 "Unable to extract index fields from %r" % c)
466 fi = tuple(s.strip() for s in fi[0].split(","))
467 res.append((a, b, c, fi))
468 select.close()
469 #self.LOG ("number of indices ", len (res))
470 select = res
472 res = []
473 if attached == "main":
474 res = select
475 else:
476 for el in select:
477 res.append((el[0], attached + "." + el[1], el[2], el[3]))
478 #self.LOG ("number of indices ", len (res))
480 if attached == "main":
481 attach = self.get_attached_database_list()
482 for a in attach:
483 if a in ("main", "temp"):
484 continue
485 r = self.get_index_list(a)
486 res.extend(r)
488 return res
490 def get_attached_database_list(self, file=False):
491 """
492 Returns all the attached database (avoid the temporary ones and the main one).
494 @param file ask for file also
495 @return a list of tuple (alias, file)
496 """
497 if self.isMSSQL():
498 return [] # pragma: no cover
499 else:
500 cur = self._connection.cursor()
501 cur.execute("PRAGMA database_list;")
502 res = cur.fetchall()
503 cur.close()
504 res = [r for r in res if r[1] != "temp" and r[1] != "main"]
505 if file:
506 return [(r[1], r[2]) for r in res]
507 else:
508 return [r[1] for r in res]
510 def get_table_list(self, add_attached=False):
511 """
512 Returns the list of tables.
514 @param add_attached if True, add the list of tables included in the attached databases
515 @return the table list
516 """
517 self._check_connection()
518 if self.isMSSQL(): # pragma: no cover
519 request = """ SELECT TABLE_NAME FROM (
520 SELECT TABLE_NAME, OBJECTPROPERTY(object_id(TABLE_NAME), N'IsUserTable') AS type
521 FROM INFORMATION_SCHEMA.TABLES) AS temp_tbl
522 WHERE type = 1 ORDER BY TABLE_NAME"""
523 else:
524 request = """ SELECT name
525 FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) AS temptbl
526 WHERE type in('table','temp') AND name != 'sqlite_sequence' ORDER BY name;"""
528 select = self._connection.execute(request)
529 res = []
530 for el in select:
531 res.append(el[0])
533 if add_attached:
534 att = self.get_attached_database_list()
535 for at in att:
536 if at == "temp":
537 continue
538 sql = "SELECT name FROM %s.sqlite_master" % at
539 vie = self._connection.execute(sql)
540 vie = ["%s.%s" % (at, v[0]) for v in vie]
541 res.extend(vie)
542 return res
544 def get_table_columns(self, table, dictionary=False):
545 """
546 See @see me get_table_columns_list.
548 Example (`dictionary == False`):
549 ::
550 [('fid', <type 'int'>), ('fidp', <type 'int'>), ('field', <type 'str'>)]
552 Or (`dictionary = True`):
553 ::
554 {0: ('fid', <type 'int'>), 1: ('fidp', <type 'int'>), 2: ('field', <type 'str'>)}
555 """
556 return self.get_table_columns_list(table, dictionary)
558 def get_table_columns_list(self, table, dictionary=False):
559 """
560 Returns all the columns for a table.
562 @param table table name
563 @param dictionary returns the list as a dictionary
564 @return a list of tuple (column name, Python type)
566 Example (`dictionary == False`):
568 ::
569 [('fid', <type 'int'>), ('fidp', <type 'int'>), ('field', <type 'str'>)]
571 Or (`dictionary = True`):
573 ::
575 {0: ('fid', <type 'int'>), 1: ('fidp', <type 'int'>), 2: ('field', <type 'str'>)}
576 """
577 if "." in table:
578 prefix = table.split(".")[0] + "."
579 table = table.split(".")[1]
580 else:
581 # table = table
582 prefix = ""
583 cur = self._connection.cursor()
585 if self.isMSSQL(): # pragma: no cover
586 prf = "" if len(prefix) == 0 else prefix + "."
587 sql = """SELECT * FROM (SELECT OBJECT_NAME(c.OBJECT_ID) TableName,c.name AS ColumnName,t.name AS TypeName
588 FROM sys.columns AS c
589 JOIN sys.types AS t ON c.user_type_id=t.user_type_id
590 ) AS ttt
591 WHERE ttt.TableName = '%s%s'""" % (prf, table)
592 cur.execute(sql)
593 else:
594 cur.execute("PRAGMA %stable_info(%s)" % (prefix, table) + ";")
596 res = cur.fetchall()
597 cur.close()
598 res = [(r[1], DatabaseCore._SQL_conversion_types[r[2]]) for r in res]
599 if dictionary:
600 dic = {}
601 for i in range(0, len(res)):
602 dic[i] = res[i]
603 return dic
604 else:
605 return res
607 def get_table_nb_lines(self, table):
608 """
609 Returns the number of lines in a table (or number of observations).
611 @param table table name
612 @return integer
613 """
614 sql = "SELECT COUNT(*) FROM " + table + ";"
615 cur = self._connection.cursor()
616 cur.execute(sql)
617 res = cur.fetchall()
618 cur.close()
619 return res[0][0]
621 def len(self, table):
622 """
623 Returns the number of lines of table ``table``.
625 @param table table
626 @return int
627 """
628 return self.get_table_nb_lines(table)
630 def get_table_nfirst_lines(self, table, n=1):
631 """
632 Returns the *n* first lines.
634 @param table table name
635 @param n number of asked lines
636 @return integer
637 """
638 sql = "SELECT * FROM %s ;" % table
639 cur = self._connection.cursor()
640 cur.execute(sql)
641 if n <= 1:
642 res = [cur.fetchone()]
643 else:
644 res = []
645 for line in cur:
646 n -= 1
647 if n <= -1:
648 break
649 res.append(line)
650 cur.close()
651 return res
653 def get_sql_columns(self, request):
654 """
655 Returns the columns name for a SQL request.
657 @param request SQL request
658 @return list of columns name
659 """
660 cur = self.execute(request)
661 col_name_list = [tuple[0] for tuple in cur.description]
662 cur.close()
663 return col_name_list
665 ##########################################################################
666 # execution
667 ##########################################################################
669 class _cross_product_iter:
671 """
672 Iterator for CROSS.
673 """
675 def __init__(self, db, request):
676 com = re.compile("^(.*)(--.*)$")
677 lines = request.split("\n")
678 clean = []
679 for li in lines:
680 r = com.match(li)
681 if r is not None:
682 li = li[:r.span(1)[1]]
683 clean.append(li.strip())
684 req = " ".join(clean)
685 cross = re.compile(" *CROSS +([ a-zA-Z_0-9,]+?) +"
686 "PLACE +([,a-zA-Z_0-9()]+?) +"
687 "FROM +([a-zA-Z_0-9]+?)( +AS +[_a-z0-9])? +ORDER +BY "
688 "+([ a-zA-Z_0-9,]+?)( +WHERE(.*?))?( +LIMIT +([0-9]+)?)?$", re.IGNORECASE)
689 db.LOG("cross product", req)
690 find = cross.search(req)
691 self.request = request
692 self.find = find
693 self.db = db
695 if self.find is None:
696 return
698 gr = self.find.groups()
699 key, value, table, count_as, order, where, _, __, limit = gr
700 if limit is not None:
701 limit = int(limit)
702 db.LOG("parameters ", [key, value, table, order, where, limit])
703 fkey = key.split(",")
704 fval = value.split(",")
705 if where is None:
706 where = ""
708 nkey = len(fkey)
709 #nval = len (fval)
710 sql = "SELECT %s,%s FROM %s %s ORDER BY %s" % (
711 key, value, table, where, order)
712 cur = self.db.execute(sql)
713 data = {}
714 #tot = nkey + nval
715 for sample in cur:
716 key = sample[:nkey]
717 val = sample[nkey:]
718 if key not in data:
719 data[key] = []
720 data[key].append(val)
721 cur.close()
723 keys = sorted(data.keys())
725 if nkey == 1:
726 temp = [[str(k[0]) + ';' + s for s in fval] for k in keys]
727 else:
728 temp = [
729 [",".join([str(_s) for _s in k]) + ';' + s for s in fval] for k in keys]
730 self.description = []
731 for t in temp:
732 self.description.extend(t)
733 self.description = [(k, None) for k in self.description]
735 matrix = []
736 pos = 0
737 while True:
738 stil = 0
739 line = []
740 for k in keys:
741 v = data[k]
742 if pos < len(v):
743 stil += 1
744 line.extend(list(v[pos]))
745 else:
746 line.extend([None for f in fval])
747 if stil > 0:
748 matrix.append(line)
749 pos += 1
750 if limit is not None and pos >= limit:
751 break
752 else:
753 break
754 self.matrix = matrix
755 self.pos = 0
757 def is_working(self):
758 return self.find is not None
760 def __iter__(self):
761 """
762 iterator
763 """
764 return self
766 def __next__(self):
767 """
768 iterator
769 """
770 if "matrix" not in self.__dict__:
771 raise StopIteration # pragma: no cover
772 if self.pos < len(self.matrix):
773 n = self.pos
774 self.pos += 1
775 return self.matrix[n]
776 else:
777 raise StopIteration
779 def close(self):
780 pass
782 def _analyse(self, request, header=False):
783 """
784 Analyses the request does it contains cross product.
786 @param request request
787 @param header add a header in the first line
788 @return None or an iterator
790 Example:
792 ::
794 CROSS f1,f2,f3
795 PLACE a,b,c
796 FROM table
797 ORDER BY f8
798 WHERE f9 == ' ' -- optional
799 """
800 if "CROSS" not in request.upper():
801 return None
802 iter = DatabaseCore._cross_product_iter(self, request)
803 if not iter.is_working():
804 return None
805 else:
806 return iter
808 def execute(self, request, nolog=False):
809 """
810 Opens a cursor with a query and return it to the user.
812 @param request SQL request
813 @param nolog if True, do not log anything
814 @return cursor
816 .. exref::
817 :title: run a select command on a table
818 :tag: SQL
820 ::
822 t = Database (file)
823 cur = t.execute ("SELECT * FROM table1 ;")
824 for f in cur :
825 print(f)
826 cur.close ()
828 There is another case outside SQL syntax to build cross product. Syntax:
830 ::
832 CROSS f1,f2,f3
833 FROM table
834 PLACE a,b,c
835 ORDER BY f8
836 WHERE f9 == ' ' -- optional
838 The request must begin by CROSS
839 """
840 res = self._analyse(request)
841 if res is not None:
842 return res
843 else:
844 # classic ways
845 self._check_connection()
846 cur = self._connection.cursor()
847 dat = time.perf_counter()
848 try:
849 if not nolog:
850 lines = request.split("\n")
851 if len(lines) > 20:
852 self.LOG("SQL ", "\n".join(
853 [repr(x) for x in lines[:20]]))
854 else:
855 self.LOG("SQL ", "\n".join([repr(x) for x in lines]))
856 cur.execute(request)
857 dat2 = time.perf_counter()
858 if dat2 - dat > 10:
859 self.LOG("SQL end") # pragma: no cover
860 except Exception as e:
861 raise ExceptionSQL(
862 "unable to execute a SQL request (1)(file %s)" %
863 self.get_file(),
864 e,
865 request) from e
866 return cur
868 def execute_view(self, request, add_column_name=False, nolog=True):
869 """
870 Opens a cursor with a query and returns the result into a list.
872 @param request SQL request
873 @param add_column_name add the column name before the first line
874 @param nolog if True, do not log anything
875 @return cursor
877 Example:
879 ::
881 t = Database (file)
882 view = t.execute_view ("SELECT * FROM table1 ;")
883 """
884 cur = self.execute(request, nolog=nolog)
885 if add_column_name:
886 col_name_list = [tuple[0] for tuple in cur.description]
887 res = [col_name_list] + list(cur)
888 else:
889 res = list(cur)
890 cur.close()
891 if not nolog and (len(res) == 0 or len(res) > 1e4):
892 self.LOG("execute_view ", len(res), "results") # pragma: no cover
893 return res
895 def execute_script(self, script, nolog=True, close=True):
896 """
897 Opens a cursor and run a script.
899 @param script SQL script
900 @param nolog if True, do not log anything
901 @param close close the cursor
902 @return cursor
903 """
904 self._check_connection()
905 if not nolog: # pragma: no cover
906 lines = script.split("\n")
907 if len(lines) > 20:
908 self.LOG("SQL start + ",
909 "\n".join([repr(x) for x in lines[:20]]))
910 else:
911 self.LOG("SQL start + ",
912 "\n".join([repr(x) for x in lines]))
913 cur = self._connection.cursor()
914 res = cur.executescript(script)
915 if close:
916 cur.close()
917 if not nolog:
918 self.LOG("SQL end") # pragma: no cover
919 else:
920 return res
922 ##########################################################################
923 # extra functions
924 ##########################################################################
926 def attach_database(self, db, alias):
927 """
928 Attaches another database.
930 @param db database to attach
931 @param alias database alias
932 """
933 if isinstance(db, str):
934 self.LOG("ATTACH DATABASE '%s' TO '%s' ALIAS %s" % (db, db, alias))
935 self.execute("ATTACH DATABASE '%s' AS %s;" % (db, alias))
936 else: # pragma: no cover
937 self.LOG(
938 "ATTACH DATABASE '%s' TO '%s' ALIAS %s" %
939 (db._sql_file, self._sql_file, alias))
940 self.execute(
941 "ATTACH DATABASE '%s' AS %s;" %
942 (db.get_file(), alias))
944 def add_function(self, name, nbparam, function):
945 """
946 Adds a function which can be used as any other SQL function (strim, ...).
948 @param name function name (it does not allow _)
949 @param nbparam number of parameters
950 @param function function to add
951 """
952 if "_" in name:
953 raise RuntimeError( # pragma: no cover
954 "SQLite does not allow function name with _")
955 self._check_connection()
956 if self._engine == "SQLite":
957 self._connection.create_function(name, nbparam, function)
959 ##########################################################################
960 # creation function
961 ##########################################################################
963 def create_index(self, indexname, table, columns, unique=False):
964 """
965 Creates an index on a table using some columns.
967 @param indexname index name
968 @param table table name
969 @param columns list of columns
970 @param unique any value in the columns is unique?
971 """
972 if not isinstance(columns, list) and not isinstance(columns, tuple):
973 columns = [columns]
975 if "." in table:
976 prefix = table.split(".")[0] + "."
977 table = table.split(".")[1]
978 else:
979 prefix = ""
980 # table = table
982 self.LOG("index create ", indexname, table, columns, unique)
983 if unique:
984 sql = "CREATE UNIQUE INDEX %s%s ON %s (%s);" % (
985 prefix, indexname, table, ",".join(columns))
986 else:
987 sql = "CREATE INDEX %s%s ON %s (%s);" % (
988 prefix, indexname, table, ",".join(columns))
989 self.execute(sql)
991 def create_table(self, table, columns, temporary=False, nolog=False):
992 """
993 Creates a table.
995 @param table table name
996 @param columns columns definition, dictionary { key:(column_name,python_type) }
997 if PRIMARYKEY is added, the key is considered as the primary key.
998 @param temporary if True the table is temporary
999 @param nolog @see me execute
1000 @return cursor
1002 Example for *columns*:
1004 ::
1006 columns = { -1:("key", int, "PRIMARYKEY", "AUTOINCREMENT"),
1007 0:("name",str), 1:("number", float) }
1009 """
1010 if self._engine == "SQLite" and table == "sqlite_sequence":
1011 raise DBException( # pragma: no cover
1012 "unable to create a table named sql_sequence")
1014 tables = self.get_table_list()
1015 if table in tables:
1016 raise DBException( # pragma: no cover
1017 "table %r is already present, it cannot be added" % table)
1019 if temporary:
1020 sql = "CREATE TEMPORARY TABLE " + table + "("
1021 else:
1022 sql = "CREATE TABLE " + table + "("
1023 col = []
1024 for c, val in columns.items():
1025 if self.isMSSQL(): # pragma: no cover
1026 if isinstance(val[1], tuple):
1027 v, li = val[1]
1028 else:
1029 v, li = val[1], 2048
1031 if li > 8000:
1032 col.append(val[0] + " TEXT")
1033 elif v is str:
1034 col.append(val[0] + " VARCHAR(%d)" % li)
1035 elif v is int:
1036 col.append(val[0] + " INTEGER")
1037 elif v is float:
1038 col.append(val[0] + " FLOAT")
1039 elif v is numpy.int64:
1040 col.append(val[0] + " INTEGER")
1041 elif v is numpy.float64:
1042 col.append(val[0] + " FLOAT")
1043 elif v is decimal.Decimal:
1044 col.append(val[0] + " Decimal")
1045 elif v is datetime.datetime:
1046 col.append(val[0] + " DATETIME")
1047 else:
1048 raise DBException( # pragma: no cover
1049 "unable to add column " +
1050 str(c) +
1051 " ... " +
1052 str(val) +
1053 " v= " +
1054 str(v))
1055 else:
1056 if isinstance(val[1], tuple):
1057 v, li = val[1]
1058 else:
1059 v, li = val[1], 2048
1061 if v is str:
1062 col.append(val[0] + " TEXT")
1063 elif v is int:
1064 col.append(val[0] + " INTEGER")
1065 elif v is float:
1066 col.append(val[0] + " FLOAT")
1067 elif v is numpy.int64:
1068 col.append(val[0] + " INTEGER")
1069 elif v is numpy.float64:
1070 col.append(val[0] + " FLOAT")
1071 elif v is decimal.Decimal:
1072 col.append(val[0] + " Decimal")
1073 elif v is datetime.datetime:
1074 col.append(val[0] + " DATETIME")
1075 else:
1076 raise DBException( # pragma: no cover
1077 "unable to add column " +
1078 str(c) +
1079 " ... " +
1080 str(val) +
1081 " v= " +
1082 str(v))
1084 fval = val[2:]
1085 for v in fval:
1086 if v not in DatabaseCore._field_option:
1087 raise DBException( # pragma: no cover
1088 "an option is unexpected %s should be in %s" %
1089 (v, str(
1090 DatabaseCore._field_option)))
1092 if "PRIMARYKEY" in val:
1093 if val[1] != int:
1094 raise DBException(
1095 "unable to create a primary key on something differont from an integer (%s)" %
1096 str(val))
1097 col[-1] += " PRIMARY KEY"
1098 if "AUTOINCREMENT" in val:
1099 if self.isMSSQL():
1100 col[-1] += " IDENTITY(0,1)"
1101 else:
1102 col[-1] += " AUTOINCREMENT"
1104 sql += ",\n ".join(col)
1105 sql += ");"
1106 return self.execute(sql, nolog=nolog)
1108 ##########################################################################
1109 # deletion
1110 ##########################################################################
1112 def remove_table(self, table):
1113 """
1114 Removes a table.
1116 @param table table name
1117 @return return a cursor
1118 """
1119 self.execute("DROP TABLE %s" % table)
1121 ##########################################################################
1122 # modification
1123 ##########################################################################
1125 def _insert_sql(self, table, insert_values):
1126 """
1127 Builds the sql for an insert request.
1129 @param table table name
1130 @param insert_values dictionary or a list
1131 @return string
1132 """
1133 if isinstance(insert_values, dict):
1134 keys = []
1135 values = []
1136 for k, v in insert_values.items():
1137 keys.append(k)
1138 if v is None:
1139 values.append('')
1140 elif isinstance(v, str):
1141 v = "'" + str(v).replace("'", "''") + "'"
1142 values.append(v)
1143 elif isinstance(v, datetime.datetime):
1144 v = "'" + str(v) + "'"
1145 values.append(v)
1146 else:
1147 values.append(str(v))
1148 keys = ",".join(keys)
1149 values = ",".join(values)
1150 sql = "INSERT INTO %s (%s) VALUES (%s)" % (table, keys, values)
1151 return sql
1152 elif isinstance(insert_values, (tuple, list)):
1153 values = []
1154 for v in insert_values:
1155 if v is None:
1156 values.append('')
1157 elif isinstance(v, str):
1158 v = "'" + str(v).replace("'", "''") + "'"
1159 values.append(v)
1160 elif isinstance(v, datetime.datetime):
1161 v = "'" + str(v) + "'"
1162 values.append(v)
1163 else:
1164 values.append(str(v))
1165 values = ",".join(values)
1166 sql = "INSERT INTO %s VALUES (%s)" % (table, values)
1167 return sql
1168 else:
1169 raise TypeError( # pragma: no cover
1170 "unexpected type: " + str(type(insert_values)))
1172 def insert(self, table, insert_values, cursor=None, nolog=True):
1173 """
1174 Inserts into a table.
1176 @param table table name
1177 @param insert_values values to insert (a list of dictionary or a single dictionary)
1178 @param cursor if *cursor is not None*, use it, otherwise creates a new one
1179 @param nolog if True, do not log anything
1180 @return sql request or None if several insertion were sent (result is too long)
1182 @warning The commit is not done and must be done to stored these modifications.
1183 """
1184 if isinstance(insert_values, list):
1185 # we expect several insertion
1186 if self._engine != "SQLite":
1187 for d in insert_values:
1188 self.insert(table, d, cursor)
1189 else:
1190 if isinstance(insert_values[0], dict):
1191 ins = {}
1192 for k in insert_values[0]:
1193 ins[k] = ":" + k
1194 sql = self._insert_sql(table, ins)
1195 else:
1196 q = tuple('?' for _ in insert_values[0])
1197 sql = self._insert_sql(table, q).replace("'", "")
1199 sql = sql.replace("'", "")
1200 try:
1201 if not nolog: # pragma: no cover
1202 if len(sql) > 1000:
1203 self.LOG("SQLs", sql[:1000])
1204 else:
1205 self.LOG("SQLs", sql)
1206 self._connection.executemany(sql, insert_values)
1207 return ""
1208 except Exception as e:
1209 raise ExceptionSQL( # pylint: disable=W0707
1210 "Unable to execute a SQL request (3) (cursor %r) (file %r)" %
1211 (str(cursor), self.get_file()), e, sql)
1213 elif isinstance(insert_values, dict):
1214 sql = self._insert_sql(table, insert_values)
1216 try:
1217 if not nolog: # pragma: no cover
1218 if len(sql) > 1000:
1219 self.LOG("SQLs", sql[:1000])
1220 else:
1221 self.LOG("SQLs", sql)
1222 if cursor is not None:
1223 cursor.execute(sql)
1224 else:
1225 if self._buffer_insert_s > 0:
1226 self._buffer_insert.append(sql)
1228 if len(self._buffer_insert) >= self._buffer_insert_s:
1229 for s in self._buffer_insert:
1230 self._connection.execute(s)
1231 del self._buffer_insert[:]
1232 else:
1233 self._connection.execute(sql)
1235 return sql
1236 except Exception as e:
1237 raise ExceptionSQL( # pylint: disable=W0707
1238 "unable to execute a SQL request (2) (cursor %r) (file %r)" %
1239 (str(cursor), self.get_file()), e, sql)
1241 else:
1242 raise DBException( # pragma: no cover
1243 "insert: expected type (list of dict or dict) instead of %s" %
1244 (str(
1245 type(insert_values))))
1247 def update(self, table, key, value, values):
1248 """
1249 Updates some values ``WHERE key=value``.
1251 @param table table to update
1252 @param key key
1253 @param value WHERE key = value
1254 @param values values to be updated
1256 @warning The commit is not done and must be done
1257 to stored these modifications.
1258 """
1260 self._check_values(values)
1261 self._check_connection()
1262 alls = []
1263 for k, v in values.items():
1264 if k != key:
1265 if isinstance(v, (str, datetime.datetime)):
1266 alls += ["%s='%s'" % (k, str(v))]
1267 else:
1268 alls += ["%s=%s" % (k, str(v))]
1269 if isinstance(value, str):
1270 sql = "UPDATE %s SET %s WHERE %s='%s'" % (
1271 table, ",".join(alls), key, value.replace("'", "''"))
1272 elif isinstance(value, datetime.datetime):
1273 sql = "UPDATE %s SET %s WHERE %s='%s'" % (
1274 table, ",".join(alls), key, str(value))
1275 else:
1276 sql = "UPDATE %s SET %s WHERE %s=%s" % (
1277 table, ",".join(alls), key, value)
1279 try:
1280 self._connection.execute(sql)
1281 return sql
1282 except Exception as e: # pragma: no cover
1283 raise ExceptionSQL( # pylint: disable=W0707
1284 "Unable to execute a SQL request (4) (file %r)" %
1285 self.get_file(), e, sql)