71 lines
2.2 KiB
Python
71 lines
2.2 KiB
Python
"""
|
||
Pomocný skript pro Clauda – spusť na Windows: python db_query.py
|
||
Výsledky zapíše do db_query_result.txt
|
||
"""
|
||
import fdb
|
||
import json
|
||
import sys
|
||
|
||
conn = fdb.connect(
|
||
dsn=r'localhost:c:\medicus 3\data\medicus.fdb',
|
||
user='SYSDBA',
|
||
password='masterkey',
|
||
charset='win1250'
|
||
)
|
||
cur = conn.cursor()
|
||
|
||
results = {}
|
||
|
||
# 1. Tabulky s klíčovými slovy (soubory, dokumenty)
|
||
cur.execute("""
|
||
SELECT RDB$RELATION_NAME
|
||
FROM RDB$RELATIONS
|
||
WHERE RDB$SYSTEM_FLAG = 0 AND RDB$VIEW_BLR IS NULL
|
||
ORDER BY RDB$RELATION_NAME
|
||
""")
|
||
all_tables = [row[0].strip() for row in cur.fetchall()]
|
||
|
||
keywords = ['FILE', 'SOUB', 'DOC', 'SCAN', 'OBRAZ', 'IMG', 'ATTACH',
|
||
'EXTERN', 'PRILOHA', 'ZPRAV', 'ARCH', 'PRIL', 'FOTO']
|
||
matches = [t for t in all_tables if any(k in t.upper() for k in keywords)]
|
||
results['tables_matching'] = matches
|
||
results['total_tables'] = len(all_tables)
|
||
|
||
# 2. Podrobnosti o nalezených tabulkách (sloupce + počet řádků)
|
||
table_details = {}
|
||
for t in matches:
|
||
try:
|
||
cur.execute(f"SELECT COUNT(*) FROM {t}")
|
||
count = cur.fetchone()[0]
|
||
except:
|
||
count = "error"
|
||
|
||
cur.execute("""
|
||
SELECT r.RDB$FIELD_NAME, f.RDB$FIELD_TYPE, f.RDB$FIELD_LENGTH
|
||
FROM RDB$RELATION_FIELDS r
|
||
JOIN RDB$FIELDS f ON f.RDB$FIELD_NAME = r.RDB$FIELD_SOURCE
|
||
WHERE r.RDB$RELATION_NAME = ?
|
||
ORDER BY r.RDB$FIELD_POSITION
|
||
""", (t,))
|
||
cols = [(row[0].strip(), row[1], row[2]) for row in cur.fetchall()]
|
||
table_details[t] = {"count": count, "columns": cols}
|
||
|
||
results['table_details'] = table_details
|
||
|
||
# 3. Uložit výsledky
|
||
output = []
|
||
output.append(f"=== VÝSLEDKY DB PRŮZKUMU ===\n")
|
||
output.append(f"Celkem tabulek v DB: {results['total_tables']}\n")
|
||
output.append(f"\nTabulky obsahující klíčová slova (soubory/dokumenty):\n")
|
||
for t in results['tables_matching']:
|
||
d = results['table_details'].get(t, {})
|
||
output.append(f"\n ** {t} ** (řádků: {d.get('count', '?')})")
|
||
for col in d.get('columns', []):
|
||
output.append(f" - {col[0]} (type={col[1]}, len={col[2]})")
|
||
|
||
with open('db_query_result.txt', 'w', encoding='utf-8') as f:
|
||
f.write('\n'.join(output))
|
||
|
||
print("Hotovo! Výsledky jsou v db_query_result.txt")
|
||
conn.close()
|