Coverage for src/pyensae/sql/database_join_group.py : 74%
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"""
7import re
8import copy
11class DatabaseJoinGroup:
13 """
14 This class is not neant to be working alone.
15 It contains functions for a database able to build
16 SQL requests for frequent needs such as join SQL requests.
17 @see cl Database
18 """
20 class JoinTreeNode:
22 """define a node meant to be included in a graph to define a big join
23 """
25 def __init__(self, table,
26 parent_key=None,
27 key=None,
28 where=None,
29 prefix=None,
30 avoid_prefix=False):
31 """constructor
32 this node defines a join on two tables (parent_table, table)
33 on two keys (parent_key, key). The keys can be tuple or string.
35 @param table table name
36 @param parent_key None if it is the root
37 @param key None if it is the root
38 @param where clause where
39 where is a where clause defined as a dictionary: example:
41 @code
42 { "field": ("==", value),
43 ("table","field"): (">=", value) }
44 @endcode
46 You may add field not connected to a table,
47 they will not taken into account.
48 @param prefix add a prefix, avoid different fields collide
49 @param avoid_prefix avoid using a prefix to build SQL queries, use syntax ``( ... ) AS ...``
50 """
51 self.table = table
52 self.parent_key = parent_key
53 self.key = key
54 self.where = {}
55 self.predecessor = None
56 self.successor = []
57 self.prefix = None
58 self._count_as = 0
59 self._avoid_prefix = avoid_prefix
60 if self.parent_key is None and self.key is not None:
61 raise KeyError( # pragma: no cover
62 "parent_key is missing")
64 if where is not None:
65 if not isinstance(where, dict):
66 raise TypeError( # pragma: no cover
67 "parameter where: only dict or None expected (not %s)" % str(
68 type(where)))
69 for k, v in where.items():
70 if not isinstance(k, tuple):
71 k = tuple(k.split("."))
72 if len(k) == 1:
73 self.where[k[0]] = v
74 elif len(k) == 2:
75 if k[-2] == self.table:
76 self.where[k[-1]] = v
77 else:
78 raise ValueError( # pragma: no cover
79 "not able to deal with than clause %s:%s" %
80 (str(k), str(v)))
82 def __str__(self):
83 """usual
84 """
85 mes = ["*nb succ: %d" % len(self.successor)]
86 for k, v in sorted(self.__dict__.items()):
87 if k not in ["table", "parent_key", "where", "key"]:
88 continue
89 s = k + " " * (12 - len(k))
90 s += str(v)
91 mes.append(s)
92 i = 0
93 for n in self.successor:
94 r = str(n)
95 li = r.split("\n")
96 li = [" " + s2 for s2 in li]
97 r = "\n".join(li)
98 mes.append("node %d" % i)
99 mes.append(r)
100 i += 1
101 return "\n".join(mes) + "\n"
103 def append(self, n):
104 """add a successor
105 @param n new successor
106 """
107 if n.predecessor is not None:
108 raise ValueError( # pragma: no cover
109 "This node was already added in another part of the tree. You must duplicate it.")
110 self.successor.append(n)
111 n.predecessor = self
113 def get_nb_successor(self):
114 """
115 @return the number of successors
116 """
117 return len(self.successor)
119 def check_prefix(self, nb=-1):
120 """
121 @param nb index of this node is the predecessor list of successor
122 check the prefixes, all one if there is none
123 """
124 if self._avoid_prefix:
125 self.PREFIX = ""
126 return
127 if "PREFIX" in self.__dict__:
128 return
129 if self.prefix is None:
130 if nb == -1:
131 self.prefix = ""
132 else:
133 self.prefix = chr(97 + nb)
134 for i, n in enumerate(self.successor):
135 n.check_prefix(i)
136 self.PREFIX = "" if self.prefix is None else self.prefix
137 self.PREFIX = self._build_predecessor_prefix() + self.PREFIX
139 def _build_predecessor_prefix(self):
140 """
141 private method
142 """
143 if self._avoid_prefix:
144 return ""
145 r = ""
146 n = self.predecessor
147 while n is not None:
148 r += n.prefix
149 n = n.predecessor
150 return r
152 def clean(self):
153 """
154 remove all sql,fields members
155 """
156 if "SELECT" in self.__dict__:
157 del self.__dict__["SELECT"]
158 del self.__dict__["FIELDS"]
159 del self.__dict__["PREFIX"]
160 for n in self.successor:
161 n.clean()
163 def _in_select(self, db):
164 """return the SQL select on the table
165 @param db database
166 @return list of tuple (fieldas, table, field, which), where
167 """
168 fields = db.get_table_columns_list(self.table)
169 where = {}
170 for f, t in fields:
171 if f in self.where:
172 where["%s.%s" % (self.table, f)] = self.where[f]
173 elif (self.table, f) in self.where:
174 where["%s.%s" % (self.table, f)] = self.where
175 elif "." in f:
176 table, z = f.split(".")[-2:]
177 if table == self.table and z in self.where:
178 where["%s.%s" % (table, z)] = self.where[z]
180 prefix = self.PREFIX
181 fas = []
182 for f, t in fields:
183 fas.append((prefix + f, self.table, f, True))
184 return fas, where
186 def _build_select(self, db, fas, where, tfrom=None):
187 """build a select SQL request
188 @param db database
189 @param fas list of tuple table,f,fas
190 @param where where clause
191 @param tfrom from clause, if None, --> self.table
192 @return string
193 """
194 lines = []
195 lines.append("SELECT")
196 alkey = True
197 if len(fas) > 0:
198 mx = max([len(s[0]) for s in fas]) + 1
199 name_changed = 0
200 for well in fas:
201 fn, t, fo = well[:3]
202 if fn == self.key:
203 if alkey:
204 alkey = False
205 doit = True
206 else:
207 doit = False
208 else:
209 doit = True
211 if doit:
212 s = " " * (mx - len(fn))
213 if len(well) == 4 and well[3]:
214 s = " %s%s AS %s," % (fo, s, fn)
215 name_changed += 1
216 else:
217 s = " %s," % fn
218 lines.append(s)
220 lines[-1] = lines[-1][:-1] # kill the last comma
221 else:
222 raise Exception("fas should not be empty")
224 if tfrom is None:
225 tfrom = self.table
227 if "\n" in tfrom:
228 lines.append(
229 "FROM (\n%s) AS temp_tbl%d" %
230 (tfrom, self._count_as))
231 self._count_as += 1
232 else:
233 lines.append("FROM %s" % tfrom)
235 if len(where) > 0:
236 wh = db._build_where_condition(where)
237 lines.append(wh)
239 return "\n".join(lines)
241 def _find_in_fas(self, fas, a, b):
242 """find a,b in fas (column 1 and 2)
243 @param fas list [ (new_name, table, name)]
244 @param a table name
245 @param b name
246 """
247 for name, tbl, field in fas:
248 if a == tbl and b == field:
249 return name
250 raise ValueError( # pragma: no cover
251 "unable to find field %s.%s in (%s)" %
252 (a, b, str(fas)))
254 def _build_join(self, db, fas, select, n):
255 """
256 see :meth:`build_sql <pyensae.sql.database_join_group.build_sql>`
258 @param db database
259 @param fas list [(new_name, table, name)]
260 @param select condition
261 @param n node
262 """
264 other_select = n.SELECT
265 parent_key = n.parent_key
266 key = n.key
268 other_select = other_select.split("\n")
269 other_select = [" " + s for s in other_select]
270 other_select = "\n".join(other_select)
271 select = select.split("\n")
272 select = [" " + s for s in select]
273 select = "\n".join(select)
275 res = self._build_select(db, fas, {}, select)
276 res += "\nINNER JOIN (\n"
277 res += other_select + ")"
278 res += "\nON "
280 ppref = n.predecessor.PREFIX
281 pref = n.PREFIX
283 if isinstance(parent_key, str):
284 if parent_key.startswith("<PREFIX>"):
285 a, b = parent_key[8:].split(".")
286 parent_key = (self._find_in_fas(fas, a, b),)
287 else:
288 parent_key = (ppref + parent_key,)
290 if key.startswith("<PREFIX>"):
291 a, b = key[8:].split(".")
292 key = (self._find_in_fas(fas, a, b),)
293 else:
294 key = (pref + key,)
296 else:
297 pk = []
298 k = []
299 for m, n in zip(parent_key, key):
300 if m.startswith("<PREFIX>"):
301 a, b = m[8:].split(".")
302 pk.append(self._find_in_fas(fas, a, b))
303 else:
304 pk.append(ppref + m)
306 if n.startswith("<PREFIX>"):
307 a, b = n.split(".")
308 k.append(self._find_in_fas(fas, a, b))
309 else:
310 k.append(pref + n)
312 parent_key = tuple(pk)
313 key = tuple(k)
315 oni = []
316 for k, l in zip(parent_key, key):
317 oni.append("%s == %s" % (k, l))
318 oni = " AND \n ".join(oni)
319 res += oni
320 return res
322 def build_sql(self, db):
323 """
324 build the sql request
326 @param db database
328 The function adds two attributes:
329 - SELECT: sql request for a node
330 - FIELDS: list of [ (final_name, table, original_name)
331 """
332 self.check_prefix()
334 for n in self.successor:
335 n.build_sql(db)
337 fas, where = self._in_select(db)
338 fields = [f[:3] for f in fas]
339 select = self._build_select(db, fas, where)
341 if self.get_nb_successor() == 0:
342 pass
343 else:
344 for n in self.successor:
345 fields.extend(n.FIELDS)
346 select = self._build_join(db, fields, select, n)
348 self.SELECT = select
349 self.FIELDS = fields
351 def __init__(self):
352 """
353 constructor
354 """
355 self._count_as = 0
357 ##################################################
358 # the class itself: multiple joins using this tree
359 ##################################################
361 def inner_joins(self, root, execute=False, create_index=False, created_table=None,
362 duplicate_column=True, order=None, unique=False, distinct=False,
363 fields=None, nolog=True):
364 """create several SQL inner join requests (included into each others)
365 @param root JoinTreeNode (the root)
366 @param execute if True, execute the query
367 @param create_index if True, creates an index on the second table if it does not exist: it accelerates the inner join
368 @param created_table if execute is True, you must specify a table name to be created
369 @param duplicate_column do not include columns from the second table if their name is already in the first one
370 @param order order clause, list of 2-tuple (column, way) way is None or DESC
371 @param unique unique or not
372 @param distinct add the keyword DISTINCT
373 @param fields restriction to fields given by fields or no restriction if None
374 @param nolog if True, do not log the query
375 @return SQL request, list of fields ("source", "new name")
377 @warning Some options are not available yet:
378 - create_index True
379 - duplicate_column False
380 - order != []
381 - unique True
383 @todo Three tasks (however, this won't probably happen)
384 - Finish The function inner_joins (parameters create_index, duplicate_column, order, unique).
385 - Improve the handling of keyword DISTINCT
386 - Handle keyword fields
387 """
388 if order is None:
389 order = []
390 if create_index:
391 raise RuntimeError( # pragma: no cover
392 "create_index = True: this option is not available")
393 if not duplicate_column:
394 raise RuntimeError( # pragma: no cover
395 "duplicate_column = False: this option is not available")
396 if len(order) > 0:
397 raise RuntimeError( # pragma: no cover
398 "order != []: this option is not available")
399 if unique:
400 raise RuntimeError( # pragma: no cover
401 "unique = True: this option is not available")
402 if fields is not None:
403 raise RuntimeError( # pragma: no cover
404 "fields != None: this option is not possible yet %s." %
405 (str(fields)))
407 root.build_sql(self)
408 select = root.SELECT
409 fields = root.FIELDS
411 if distinct:
412 if not select.startswith("SELECT"):
413 raise ValueError("algorithm problem") # pragma: no cover
414 select = "SELECT DISTINCT" + select[len("SELECT"):]
416 if execute:
417 if created_table is None:
418 raise RuntimeError( # pragma: no cover
419 "unable to execute the SQL query: not specified name for the table to create")
420 if created_table in self.get_table_list():
421 raise ValueError("table %r already exists" % created_table)
423 select = "CREATE TABLE %s AS \n" % created_table + select
424 self.execute(select, nolog=nolog)
426 return select, fields
428 ##################################################
429 # the other methods
430 ##################################################
432 def _build_where_condition(self, where, add_keyword_where=True):
433 """builds a where condition (including the WHERE keyword)
434 @param where condition where to interpret
435 @code
436 { "field": ("==", value) }
437 @endcode
438 @param add_keyword_where add the keyword where ?
439 @return sql syntax
441 @todo This function should deal with a tree to express AND and OR logical links.
442 (However, this probably won't happen.)
443 """
444 sql = ""
445 if where is not None and len(where) > 0:
446 if add_keyword_where:
447 sql += " WHERE "
448 if isinstance(where, str):
449 sql += where
450 elif isinstance(where, dict):
451 a = []
452 for k, v in where.items():
453 if v[1] not in ['==', '<=', '>=', '>', '<', '!=']:
454 v = (v[1], v[0])
455 if v[1] not in ['==', '<=', '>=', '>', '<', '!=']:
456 raise ValueError( # pragma: no cover
457 "unable to understand where %s,%s " %
458 (k, str(v)))
459 if v[1] == '==' and self.isMSSQL():
460 v = (v[0], '=')
461 v = (v[0], " %s " % v[1])
462 if isinstance(v[0], str):
463 if "'" in v[0]:
464 s = k + v[1] + "'" + v[0].replace("'", "''") + "'"
465 else:
466 s = k + v[1] + "'" + v[0] + "'"
467 else:
468 s = k + v[1] + str(v[0])
469 a.append(s)
470 sql += " AND ".join(a)
471 else:
472 raise ValueError( # pragma: no cover
473 "unable to interpret this where condition %s" %
474 (str(where)))
475 return sql
477 def histogram(self, table, columns, col_sums=None, values=None, sql_add=None,
478 execute=False, created_table=None, new_column="histogram",
479 nolog=False):
480 """
481 create a SQL request to compute an histogram
483 @param table table
484 @param columns column or columns (in a tuple) to be histogrammized
485 @param col_sums candidate columns for a sum
486 @param values specific values, several cases:
487 - if None: does a GROUP BY
488 - if dictionary of tuple: ``{'cat1':('val1', 'val2', ...) }``
489 then groups together several values into one category
490 - if list of float: does an histogram on a real variable
491 @param new_column name of the new column
492 @param sql_add string to be added at the end of the SQL request
493 @param execute if True, execute the request
494 @param created_table the histogram can be stored into a table whose name is given by this parameter
495 @param nolog if True, do not log the query
496 @return SQL request
498 """
499 if col_sums is None:
500 col_sums = []
501 if isinstance(columns, str):
502 columns = (columns,)
504 cols = self.get_table_columns_list(table)
505 for column in columns:
506 if column not in [x[0] for x in cols]:
507 raise ValueError(
508 "%r is not a column of table %r\n- columns:\n%r" %
509 (column, table, "\n".join(
510 [
511 str(x) for x in cols])))
513 if sql_add is None or len(sql_add) == 0:
514 sql_add = ""
515 else:
516 sql_add = ",\n " + sql_add
518 sum_column = []
519 for c in col_sums:
520 s = "SUM(%s) AS sum_%s" % (c, c)
521 sum_column.append(s)
522 str_sum = ", ".join(sum_column)
523 if len(str_sum) > 0:
524 str_sum = ", " + str_sum
526 if values is None:
527 sql = "SELECT %s AS %s, COUNT(%s) AS %s_nb%s%s\nFROM %s\nGROUP BY %s" % \
528 (", ".join(columns),
529 new_column,
530 "*",
531 new_column,
532 str_sum,
533 sql_add,
534 table,
535 ", ".join(columns))
536 select = sql
538 elif isinstance(values, dict):
539 values_rev = {}
540 for k, vv in values.items():
541 for v in vv:
542 if v not in values_rev:
543 values_rev[v] = []
544 values_rev[v].append(k)
545 for k, v in values_rev.items():
546 if len(v) > 1:
547 raise ValueError( # pragma: no cover
548 "a category is shared by several values %r and %r" %
549 (k, ", ".join(v)))
550 for k in values_rev:
551 values_rev[k] = values_rev[k][0]
553 def filterfunctionhistogramdict1(v):
554 return values_rev.get(v, "none")
556 def filterfunctionhistogramdict2(a, b):
557 return values_rev.get((a, b), "none")
559 def filterfunctionhistogramdict3(a, b, c):
560 return values_rev.get((a, b, c), "none")
562 def filterfunctionhistogramdict4(a, b, c, d):
563 return values_rev.get((a, b, c, d), "none")
565 def filterfunctionhistogramdict5(a, b, c, d, e):
566 return values_rev.get((a, b, c, d, e), "none")
568 self.add_function(
569 "filterfunctionhistogramdict1",
570 1,
571 filterfunctionhistogramdict1)
572 self.add_function(
573 "filterfunctionhistogramdict2",
574 2,
575 filterfunctionhistogramdict2)
576 self.add_function(
577 "filterfunctionhistogramdict3",
578 3,
579 filterfunctionhistogramdict3)
580 self.add_function(
581 "filterfunctionhistogramdict4",
582 4,
583 filterfunctionhistogramdict4)
584 self.add_function(
585 "filterfunctionhistogramdict5",
586 5,
587 filterfunctionhistogramdict5)
589 st = ",".join(["a", "b", "c", "d", "e"][:len(cols)])
590 sql = "\n -- def filterfunctionhistogramdict%d (%s) : return %s.get (%s, 'none')\n\n" % (
591 len(columns), st, str(values_rev), st)
592 sql += "\n SELECT " + \
593 ",\n ".join([x[0] for x in cols])
594 sql += ",\n filterfunctionhistogramdict%d (%s) AS histo_temp_col" % (
595 len(columns), ", ".join(columns),)
596 sql += "\n FROM %s" % table
597 sql = "(" + sql + ") AS temp_tbl%d" % self._count_as
598 self._count_as += 1
600 select = "SELECT histo_temp_col AS %s,COUNT(histo_temp_col) AS %s_nb\n %s\n %s\nFROM %s\nGROUP BY histo_temp_col" % \
601 (new_column, new_column, str_sum, sql_add, sql)
603 elif isinstance(values, list):
604 values = sorted(copy.copy(values))
605 values2 = values[1:] + [max(1e10, max(values) + 1), ]
606 names = list(values)
607 couple = list(zip(range(0, len(values)), values, values2, names))
609 def filterfunctionhistogramlist(v):
610 for i, x, x_, n in couple:
611 if v < x_:
612 return n
613 raise RuntimeError( # pragma: no cover
614 "unable to process, " +
615 str(v) +
616 " is a value higher than 1e10")
617 self.add_function(
618 "filterfunctionhistogramlist",
619 1,
620 filterfunctionhistogramlist)
622 sql = ""
623 sql += "\n SELECT " + ",\n ".join([x[0] for x in cols])
624 sql += ",\n filterfunctionhistogramlist (%s) AS histo_temp_col" % (
625 ", ".join(columns),)
626 sql += "\n FROM %s" % table
627 sql = "(" + sql + ")"
629 select = "SELECT histo_temp_col AS %s,COUNT(histo_temp_col) AS %s_nb\n %s\n %s\nFROM %s\nGROUP BY histo_temp_col" % \
630 (new_column, new_column, str_sum, sql_add, sql)
632 else:
633 raise TypeError( # pragma: no cover
634 "values has not a type (%s) not in [None, dict, list]" %
635 (str(
636 type(values))))
638 if execute:
639 if created_table is None:
640 raise RuntimeError( # pragma: no cover
641 "unable to execute the SQL query: not specified name for the table to create")
642 if created_table in self.get_table_list():
643 raise RuntimeError( # pragma: no cover
644 "table %r already exists" % created_table)
646 select = "CREATE TABLE %s AS \n" % created_table + select
647 self.execute(select, nolog=nolog)
649 return select
651 def inner_join(self, table1, table2, field1, field2=None, where=None, execute=False,
652 create_index=True, created_table=None, prefix="", duplicate_column=True,
653 prefix_all="", order=None, unique=True, params=None, nolog=True):
654 """create a SQL inner join request
655 @param table1 first table
656 @param table2 second table
657 @param field1 inner join on field1 from table1
658 @param field2 inner join on field2 from table2 (if None --> field2 = field1
659 @param where where clause (if None, do not add it), dictionary or string
660 @param execute if True, execute the query
661 @param create_index if True, creates an index on the second table if it does not exist: it accelerates the inner join
662 @param created_table if execute is True, you must specify a table name to be created
663 @param prefix prefix for fields from the second table
664 @param duplicate_column do not include columns from the second table if their name is already in the first one
665 @param prefix_all prefix for all fields
666 @param order order clause, list of 2-tuple (column, way) way is None or DESC
667 @param unique unique or not
668 @param params special parameters for inner_joins method
669 @param nolog if True, do not log the query, otherwise, skip that part
670 @return SQL request, list of fields ("source", "new name")
671 """
672 if order is None:
673 order = []
674 if params is None:
675 params = {}
676 if field2 is None:
677 field2 = field1
679 cols1 = self.get_table_columns_list(table1)
680 cols1 = [f[0] for f in cols1]
681 if len(cols1) == 0:
682 raise ValueError( # pragma: no cover
683 "table %r has no field" % table1)
685 joinsm = table2 == '________________'
686 if joinsm:
687 cols2 = [f[1] for f in params["fields"]]
688 if len(cols2) == 0:
689 raise RuntimeError( # pragma: no cover
690 "imported table has no field")
691 table2 = params["sql"].split("\n")
692 table2 = [" " + s for s in table2]
693 table2 = "\n".join(table2)
694 table2 = "(%s)" % (table2.strip("\n\r "),)
695 else:
696 cols2 = self.get_table_columns_list(table2)
697 cols2 = [f[0] for f in cols2]
698 if len(cols2) == 0:
699 raise Exception("table %s has no field" % table2)
701 if isinstance(field1, tuple):
702 for k in field1:
703 if k not in cols1:
704 raise ValueError( # pragma: no cover
705 "unable to find field %r in table %r" %
706 (k, table1))
707 for k in field2:
708 if k not in cols2:
709 raise ValueError( # pragma: no cover
710 "unable to find field %r in table %r" %
711 (k, table2))
712 else:
713 if field1 not in cols1:
714 raise ValueError( # pragma: no cover
715 "unable to find field %r in table %r" %
716 (field1, table1))
717 if field2 not in cols2:
718 raise ValueError( # pragma: no cover
719 "unable to find field %r in table %r" %
720 (field2, table2))
721 field1 = (field1,)
722 field2 = (field2,)
724 if create_index and joinsm:
725 li = self.get_index_list()
726 ind = False
727 for name, tbl_name, sql in li:
728 if tbl_name != table2:
729 continue
730 fields = re.compile("[(](\\w*)[)]").search(sql).groups()
731 if len(fields) == 0:
732 continue
733 field = fields[0]
734 if field not in field2:
735 continue
736 ind = True
738 if not ind:
739 self.LOG( # pragma: no cover
740 "creating an index on table %r, field %r" %
741 (table2, ", ".join(field2)))
742 self.create_index("index_" + table2.replace(".", "_") + "_" + "_".join(field2),
743 table2, field2, unique=unique)
745 keyfields = {}
746 for k in field1:
747 if k in cols2:
748 if k not in field2:
749 keyfield = ("%s.%s" % (table1, k), table1 + "_" + k)
750 else:
751 keyfield = ("%s.%s" % (table1, k), k)
752 else:
753 keyfield = (k, k)
754 keyfields[k] = keyfield
756 if "." in table1:
757 ptable1 = table1.split(".")[1]
758 else:
759 ptable1 = table1
760 if "." in table2:
761 ptable2 = table2.split(".")[1]
762 else:
763 ptable2 = table2
765 fields = []
766 for c in cols1:
767 if ":" in c:
768 continue
769 if c in keyfields:
770 fields.append(keyfields[c])
771 elif c in cols2:
772 if duplicate_column:
773 fields.append(
774 ("%s.%s" %
775 (ptable1, c), "%s_%s" %
776 (ptable1, c)))
777 else:
778 fields.append(("%s.%s" % (ptable1, c), c))
779 else:
780 fields.append((c, c))
782 for c in cols2:
783 if ":" in c:
784 continue
785 if c in field2:
786 continue
787 if c in cols1:
788 if duplicate_column:
789 fields.append(
790 ("%s.%s" %
791 (ptable2, c), prefix + "%s_%s" %
792 (ptable2, c)))
793 else:
794 fields.append((c, prefix + c))
796 mx = max([len(f[0]) for f in fields]) + 1
797 rem = params.get("as_remove", None)
799 if rem is None:
800 fields = [(f[0] + " " * (mx - len(f[0])), prefix_all + f[1])
801 for f in fields]
802 else:
803 cfields = fields
804 fields = []
805 for f in cfields:
806 a = f[0] + " " * (mx - len(f[0]))
807 b = (prefix_all + f[1]).replace(rem, "")
808 if b in cols1:
809 b = (prefix_all + f[1]).replace(rem, "_")
810 fields.append((a, b))
812 all = [" AS ".join(f) for f in fields]
813 select = ",\n ".join(all)
814 select = "SELECT " + select + "\nFROM %s\n" % table1
816 if unique:
817 select += "INNER JOIN %s\n" % table2
818 else:
819 select += "JOIN %s\n" % table2
821 nb = 0
822 for k1, k2 in zip(field1, field2):
823 if nb > 0:
824 select += " AND "
825 else:
826 select += "ON "
828 if k1 in cols2:
829 select += "%s.%s " % (table1, k1)
830 else:
831 select += "%s " % k1
833 if k2 in cols1 and not joinsm:
834 select += "== %s.%s\n" % (table2, k2)
835 else:
836 select += "== %s\n" % k2
838 nb += 1
840 if where is not None and len(where) > 0:
841 select += "WHERE " + where + "\n"
843 if order is not None and len(order) > 0:
844 te = []
845 for o in order:
846 if isinstance(o, tuple):
847 if o[0] in cols1 and o[0] in cols2:
848 te.append(ptable1 + "." + o[0] + " " + o[1])
849 else:
850 te.append(o[0] + " " + o[1])
851 else:
852 if o in cols1 and o in cols2:
853 te.append(ptable1 + "." + o)
854 else:
855 te.append(o)
856 select += "ORDER BY " + ", ".join(te) + "\n"
858 #select += ";"
860 if execute:
861 if created_table is None:
862 raise RuntimeError( # pragma: no cover
863 "unable to execute the SQL query: not specified name for the table to create")
864 if created_table in self.get_table_list():
865 raise ValueError( # pragma: no cover
866 "table %r already exists" % created_table)
868 select = "CREATE TABLE %s AS \n" % created_table + select
869 self.execute(select, nolog=nolog)
871 fields = [(a_.strip(), b_) for a_, b_ in fields]
872 return select, fields