Revision 1968
Added by Aaron Marcuse-Kubitza almost 13 years ago
| lib/sql.py | ||
|---|---|---|
| 238 | 238 |
def esc_name(db, name, **kw_args): |
| 239 | 239 |
return esc_name_by_module(util.root_module(db.db), name, **kw_args) |
| 240 | 240 |
|
| 241 |
def qual_name(db, schema, table): |
|
| 242 |
def esc_name(name): return sql.esc_name(db, name, preserve_case=True) |
|
| 243 |
return esc_name(schema)+'.'+esc_name(table) |
|
| 244 |
|
|
| 241 | 245 |
##### Querying |
| 242 | 246 |
|
| 243 | 247 |
def run_raw_query(db, *args, **kw_args): |
| ... | ... | |
| 346 | 350 |
elif module == 'MySQLdb': return db.insert_id() |
| 347 | 351 |
else: return None |
| 348 | 352 |
|
| 349 |
def truncate(db, table): |
|
| 350 |
check_name(table) |
|
| 351 |
return run_raw_query(db, 'TRUNCATE '+table+' CASCADE') |
|
| 353 |
def truncate(db, table, schema='public'): |
|
| 354 |
return run_query(db, 'TRUNCATE '+qual_name(db, schema, table)+' CASCADE') |
|
| 352 | 355 |
|
| 353 | 356 |
##### Database structure queries |
| 354 | 357 |
|
| ... | ... | |
| 419 | 422 |
else: raise NotImplementedError("Can't list constraint columns for "+module+
|
| 420 | 423 |
' database') |
| 421 | 424 |
|
| 422 |
def tables(db): |
|
| 425 |
def tables(db, schema='public', table_like='%'):
|
|
| 423 | 426 |
module = util.root_module(db.db) |
| 427 |
params = {'schema': schema, 'table_like': table_like}
|
|
| 424 | 428 |
if module == 'psycopg2': |
| 425 |
return values(run_query(db, "SELECT tablename from pg_tables " |
|
| 426 |
"WHERE schemaname = 'public' ORDER BY tablename")) |
|
| 427 |
elif module == 'MySQLdb': return values(run_query(db, 'SHOW TABLES')) |
|
| 429 |
return values(run_query(db, '''\ |
|
| 430 |
SELECT tablename |
|
| 431 |
FROM pg_tables |
|
| 432 |
WHERE |
|
| 433 |
schemaname = %(schema)s |
|
| 434 |
AND tablename LIKE %(table_like)s |
|
| 435 |
ORDER BY tablename |
|
| 436 |
''', |
|
| 437 |
params, cacheable=True)) |
|
| 438 |
elif module == 'MySQLdb': |
|
| 439 |
return values(run_query(db, 'SHOW TABLES LIKE %(table_like)s', params, |
|
| 440 |
cacheable=True)) |
|
| 428 | 441 |
else: raise NotImplementedError("Can't list tables for "+module+' database')
|
| 429 | 442 |
|
| 430 | 443 |
##### Database management |
| 431 | 444 |
|
| 432 |
def empty_db(db): |
|
| 433 |
for table in tables(db): truncate(db, table) |
|
| 445 |
def empty_db(db, schema='public', **kw_args): |
|
| 446 |
'''For kw_args, see tables()''' |
|
| 447 |
for table in tables(db, schema, **kw_args): truncate(db, table, schema) |
|
| 434 | 448 |
|
| 435 | 449 |
##### Heuristic queries |
| 436 | 450 |
|
Also available in: Unified diff
sql.py: Made truncate(), tables(), empty_db() schema-aware. Added qual_name(). tables(): Added option to filter tables by a LIKE pattern.