Comments (7)
Chyba na webu - http://www.psp.cz/sqw/hp.sqw?k=1305 - ta target tabulka se jmenuje osoby
, ne osoba
.
Identifikátor poslance, viz osoba:id_osoba.
Identifikátor člena vlády, viz osoba:id_osoba.
from od.
Typ akce, viz typ_prechodu:id_akce
tahle tabulka chybí
http://www.psp.cz/sqw/hp.sqw?k=1303
from od.
Číslo tisku, resp. část reference tisku. Pokud je tisk:id_druh in (41,45,46,47), pak do reference k číslu tisku patří i -E.
tisk:
neexistuje
http://www.psp.cz/sqw/hp.sqw?k=1303
from od.
Identifikátor orgánu, viz organ:id_organ
organ misto organy
http://www.psp.cz/sqw/hp.sqw?k=1303
from od.
Identifikátor schůze, viz i_schuze:id_schuze.
i_schuze misto schuze, taky i_bod_schuze
http://www.psp.cz/sqw/hp.sqw?k=1305
from od.
Tohle je minový pole. Nejen že párkrát je v popisku zmíněno víc vazeb (ne nutně víc vazeb, jen se to nějak rozděluje podle nějakýho jinýho sloupce), tak ani u těch, kde je jedna vazba, to neni celý úplně košér. Třeba ta tabulka neexistuje, ta datová integrita nesedí atd. Kód je zhruba takový, prozatim to vzdávám.
import os
import re
import json
cre = re.compile('(\w+):(\w+)')
with open('mapping.json') as f:
mapping = json.load(f)
fullnames = {mp['tabulka']: f'psp.{mp["tema"]}_{mp["tabulka"]}' for mp in mapping}
assert len(mapping) == len(fullnames)
# kvuli chybam ve schematu
fullnames['osoba'] = fullnames['osoby']
fullnames['org'] = fullnames['organy']
fullnames['organ'] = fullnames['organy']
fullnames['i_schuze'] = fullnames['schuze']
fullnames['i_bod_schuze'] = fullnames['bod_schuze']
for mp in mapping:
tbl = f'psp.{mp["tema"]}_{mp["tabulka"]}'
for sl in mp['sloupce']:
m = cre.findall(sl['popis'] or '')
if len(m) > 1:
# print('-', mp['tema'], mp['tabulka'], sl['sloupec'], 'vicero potencialnich fkeys', sl['popis'])
continue
if len(m) == 0:
continue
reftable, refcolumn = m[0]
if reftable in ('typ_prechodu', 'tisk'):
continue
fullreftable = fullnames[reftable]
fkname = 'fk_{}_{}_{}__{}_{}'.format(mp['tema'], mp['tabulka'], sl['sloupec'], reftable, refcolumn)
fk = f'ALTER TABLE {tbl} ADD CONSTRAINT {fkname} FOREIGN KEY ({sl["sloupec"]}) REFERENCES {fullreftable} ({refcolumn});'
print(fk)
from od.
Malej nástřel je tu 5366cc1
import os
import re
import json
import psycopg2
cre = re.compile('(\w+):(\w+)')
with open('mapping.json') as f:
mapping = json.load(f)
fullnames = {mp['tabulka']: f'psp.{mp["tema"]}_{mp["tabulka"]}' for mp in mapping}
assert len(mapping) == len(fullnames)
# kvuli chybam ve schematu
fullnames['osoba'] = fullnames['osoby']
fullnames['org'] = fullnames['organy']
fullnames['organ'] = fullnames['organy']
fullnames['i_schuze'] = fullnames['schuze']
fullnames['i_bod_schuze'] = fullnames['bod_schuze']
pg = psycopg2.connect(host='localhost')
for mp in mapping:
tbl = f'psp.{mp["tema"]}_{mp["tabulka"]}'
for sl in mp['sloupce']:
m = cre.findall(sl['popis'] or '')
if len(m) > 1:
# print('-', mp['tema'], mp['tabulka'], sl['sloupec'], 'vicero potencialnich fkeys', sl['popis'])
continue
if len(m) == 0:
continue
reftable, refcolumn = m[0]
if reftable in ('typ_prechodu', 'tisk'):
continue
fullreftable = fullnames[reftable]
fkname = 'fk_{}_{}_{}__{}_{}'.format(mp['tema'], mp['tabulka'], sl['sloupec'], reftable, refcolumn)
fk = f'ALTER TABLE {tbl} ADD CONSTRAINT {fkname} FOREIGN KEY ({sl["sloupec"]}) REFERENCES {fullreftable} ({refcolumn});'
with pg, pg.cursor() as cur:
cur.execute(f'ALTER TABLE {tbl} DROP CONSTRAINT IF EXISTS {fkname}')
try:
with pg, pg.cursor() as cur:
cur.execute(fk)
except (psycopg2.IntegrityError, psycopg2.ProgrammingError):
continue
sl['fkey'] = [fullreftable.replace('psp.', ''), refcolumn]
with open('mapping.json', 'w') as fw:
json.dump(mapping, fw, ensure_ascii=False, indent=2)
from od.
Related Issues (20)
- [volby] connection reset by peer
- [main] zapisuj do tmpdiru a pak atomicky přesuň HOT 1
- SQL cvičení: rozklíčování VZ
- [psp] nefunguje stahování ze zahraničních IP
- SQL cvičení: zakázky v kontextu HOT 2
- [all] fts pro obě db
- (nejen) SQL cvičení: rodinné vztahy poslanců v rámci obchodního rejstříku
- [psp] stahování nefunguje na novym Ubuntu (resp. OpenSSL 3.x)
- [volby] prezident 2023
- lxml.QName místo {{{
- Dávky z různých volebních exportů
- Eu fondy - programové období 2021 HOT 3
- SQLAlchemy 2 je venku HOT 1
- SQL cvičení: predikce výsledků podle dávek
- [volby] Bulkové zpracování senátních dat
- [volby] Použít CSV místo XML
- [justice] nove objekty ve schematu
- Párování komunálek na rejstřík a dál HOT 3
- [psp] senátní tisky
- [zakazky] spatna hodnota pro datum/cas
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from od.