Revision 2467
Added by Aaron Marcuse-Kubitza over 12 years ago
sql.py | ||
---|---|---|
431 | 431 |
try: |
432 | 432 |
create_query = 'CREATE' |
433 | 433 |
if temp: create_query += ' TEMP' |
434 |
create_query += ' TABLE '+into.to_str(db)+' AS '+query
|
|
434 |
create_query += ' TABLE '+into.to_str(db)+' AS\n'+query
|
|
435 | 435 |
|
436 | 436 |
return run_query(db, create_query, params, *args, **kw_args) |
437 | 437 |
# CREATE TABLE AS sets rowcount to # rows in query |
... | ... | |
478 | 478 |
|
479 | 479 |
# DISTINCT ON columns |
480 | 480 |
if distinct_on != []: |
481 |
query += ' DISTINCT'
|
|
481 |
query += '\nDISTINCT'
|
|
482 | 482 |
if distinct_on is not distinct_on_all: |
483 | 483 |
query += ' ON ('+(', '.join(map(parse_col, distinct_on)))+')' |
484 | 484 |
|
485 | 485 |
# Columns |
486 |
query += ' '
|
|
486 |
query += '\n'
|
|
487 | 487 |
if fields == None: query += '*' |
488 | 488 |
else: query += ', '.join(map(parse_col, fields)) |
489 | 489 |
|
490 | 490 |
# Main table |
491 |
query += ' FROM '+table0.to_str(db)
|
|
491 |
query += '\nFROM '+table0.to_str(db)
|
|
492 | 492 |
|
493 | 493 |
# Add joins |
494 | 494 |
left_table = table0 |
... | ... | |
500 | 500 |
conds.append((sql_gen.Col(table_not_null_col(db, table), table), |
501 | 501 |
None)) |
502 | 502 |
|
503 |
query += ' '+join_.to_str(db, left_table)
|
|
503 |
query += '\n'+join_.to_str(db, left_table)
|
|
504 | 504 |
|
505 | 505 |
left_table = table |
506 | 506 |
|
507 | 507 |
missing = True |
508 | 508 |
if conds != []: |
509 |
query += ' WHERE '+(' AND '.join(('('+sql_gen.ColValueCond(l, r)
|
|
509 |
query += '\nWHERE\n'+('\nAND\n'.join(('('+sql_gen.ColValueCond(l, r)
|
|
510 | 510 |
.to_str(db)+')' for l, r in conds))) |
511 | 511 |
missing = False |
512 | 512 |
if order_by != None: |
513 |
query += ' ORDER BY '+sql_gen.as_Col(order_by, table0).to_str(db)
|
|
514 |
if limit != None: query += ' LIMIT '+str(limit); missing = False
|
|
513 |
query += '\nORDER BY '+sql_gen.as_Col(order_by, table0).to_str(db)
|
|
514 |
if limit != None: query += '\nLIMIT '+str(limit); missing = False
|
|
515 | 515 |
if start != None: |
516 |
if start != 0: query += ' OFFSET '+str(start)
|
|
516 |
if start != 0: query += '\nOFFSET '+str(start)
|
|
517 | 517 |
missing = False |
518 | 518 |
if missing: warnings.warn(DbWarning( |
519 | 519 |
'SELECT statement missing a WHERE, LIMIT, or OFFSET clause: '+query)) |
... | ... | |
545 | 545 |
|
546 | 546 |
# Build query |
547 | 547 |
query = 'INSERT INTO '+table.to_str(db) |
548 |
if cols != None: query += ' ('+', '.join(cols)+')'
|
|
549 |
query += ' '+select_query
|
|
548 |
if cols != None: query += '\n('+', '.join(cols)+')'
|
|
549 |
query += '\n'+select_query
|
|
550 | 550 |
|
551 | 551 |
if returning != None: |
552 | 552 |
returning_name = copy.copy(returning) |
553 | 553 |
returning_name.table = None |
554 | 554 |
returning_name = returning_name.to_str(db) |
555 |
query += ' RETURNING '+returning_name
|
|
555 |
query += '\nRETURNING '+returning_name
|
|
556 | 556 |
|
557 | 557 |
if embeddable: |
558 | 558 |
assert returning != None |
... | ... | |
567 | 567 |
function = sql_gen.Table(function_name, func_schema).to_str(db) |
568 | 568 |
|
569 | 569 |
function_query = '''\ |
570 |
CREATE FUNCTION '''+function+'''() RETURNS '''+return_type+''' |
|
571 |
LANGUAGE sql |
|
572 |
AS $$'''+mogrify(db, query, params)+''';$$; |
|
570 |
CREATE FUNCTION '''+function+'''() |
|
571 |
RETURNS '''+return_type+''' |
|
572 |
LANGUAGE sql |
|
573 |
AS $$ |
|
574 |
'''+mogrify(db, query, params)+'''; |
|
575 |
$$; |
|
573 | 576 |
''' |
574 | 577 |
run_query(db, function_query, recover=True, cacheable=True, |
575 | 578 |
log_ignore_excs=(DuplicateFunctionException,)) |
... | ... | |
619 | 622 |
values.append(value) |
620 | 623 |
|
621 | 624 |
# Build query |
622 |
if values != []: query = ' VALUES ('+(', '.join(labels))+')'
|
|
625 |
if values != []: query = 'VALUES ('+(', '.join(labels))+')' |
|
623 | 626 |
else: query = None |
624 | 627 |
|
625 | 628 |
return insert_select(db, table, cols, query, values, *args, **kw_args) |
... | ... | |
636 | 639 |
query = 'UPDATE '+sql_gen.as_Table(table).to_str(db)+'\nSET\n' |
637 | 640 |
query += ',\n'.join((sql_gen.to_name_only_col(col, table).to_str(db)+' = ' |
638 | 641 |
+sql_gen.as_Value(new_value).to_str(db) for col, new_value in changes)) |
639 |
if cond != None: query += ' WHERE '+cond.to_str(db)
|
|
642 |
if cond != None: query += '\nWHERE\n'+cond.to_str(db)
|
|
640 | 643 |
|
641 | 644 |
return query |
642 | 645 |
|
Also available in: Unified diff
sql.py, sql_gen.py: Reformatted generated SQL for presentability by adding newlines