Untitled
unknown
python
a year ago
7.7 kB
5
Indexable
Never
import pymysql.cursors import os from config import ip class Data: def __init__(self): self.datasql= pymysql.connect(host=ip, user='burak', password='Burak123.felek', database='101m', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor) self.gsmsql= pymysql.connect(host=ip, user='burak', password='Burak123.felek', database='gsm', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor) self.datacursor = self.datasql.cursor() self.gsmcursor = self.gsmsql.cursor() self.kardessayi = 0 self.btarafkuzensayi = 0 self.atarafkuzensayi = 0 def get_detail(self, ad = None, soyad = None, yil = None): sql = "" if ad and soyad and yil: sql = "SELECT * FROM 101m WHERE ADI=%s AND SOYADI=%s AND DOGUMTARIHI REGEXP %s" else: if ad and soyad: sql = "SELECT * FROM 101m WHERE ADI=%s AND SOYADI=%s" self.datacursor.execute(sql, (ad, soyad, yil) if yil else (ad, soyad)) all = self.datacursor.fetchall() alls = [] for i in all: i.pop("id") i.update({"YAKINLIKDURUMU": "Kendisi"}) alls.append(i) return alls def get_gsm(self, tc=None, gsm=None): if tc: self.sql = "SELECT TC,GSM FROM gsm WHERE TC=%s" else: if gsm: self.sql = "SELECT TC,GSM FROM gsm WHERE GSM=%s" self.gsmcursor.execute(self.sql, (tc,) if tc else (gsm,)) all = self.gsmcursor.fetchall() return all def get_family(self, tc): sql = "SELECT * FROM 101m WHERE TC=%s" self.datacursor.execute(sql, (tc,)) kendisi = self.datacursor.fetchone() alls = [] kendisi.pop("id") kendisi.update({"YAKINLIKDURUMU": "Kendisi"}) alls.append(kendisi) try: self.datacursor.execute(sql, (kendisi["ANNETC"],)) annesi = self.datacursor.fetchone() annesi.pop("id") annesi.update({"YAKINLIKDURUMU": "Annesi"}) alls.append(annesi) except: pass try: self.datacursor.execute(sql, (kendisi["BABATC"],)) babasi = self.datacursor.fetchone() babasi.pop("id") babasi.update({"YAKINLIKDURUMU": "Babası"}) alls.append(babasi) except: pass try: sql = "SELECT * FROM 101m WHERE BABATC=%s AND ANNETC=%s" self.datacursor.execute(sql, (kendisi["BABATC"], kendisi["ANNETC"])) kardesler = self.datacursor.fetchall() for kardes in kardesler: if kardes["TC"] != tc: kardes.pop("id") self.kardessayi += 1 kardes.update({"YAKINLIKDURUMU": "Kardeşi"}) alls.append(kardes) except: pass try: self.datacursor.execute(sql, (babasi["BABATC"], babasi["ANNETC"])) babasininkardesler = self.datacursor.fetchall() for kardes in babasininkardesler: if kardes["TC"] != babasi["TC"]: kardes.pop("id") kardes.update({"YAKINLIKDURUMU": "Babasının Kardeşi"}) alls.append(kardes) except: pass try: self.datacursor.execute(sql, (annesi["BABATC"], annesi["ANNETC"])) annesininkardesler = self.datacursor.fetchall() for kardes in annesininkardesler: if kardes["TC"] != annesi["TC"]: kardes.pop("id") kardes.update({"YAKINLIKDURUMU": "Annesinin Kardeşi"}) alls.append(kardes) except: pass try: for kardes in babasininkardesler: sql = "SELECT * FROM 101m WHERE BABATC=%s OR ANNETC=%s" self.datacursor.execute(sql, (kardes["TC"], kardes["TC"])) btarafkuzenler = self.datacursor.fetchall() for x in btarafkuzenler: if kardes["TC"] != babasi["TC"]: x.pop("id") self.btarafkuzensayi += 1 x.update({"YAKINLIKDURUMU": "Baba Taraf Kuzen"}) alls.append(x) except: pass try: for kardes in annesininkardesler: sql = "SELECT * FROM 101m WHERE BABATC=%s OR ANNETC=%s" self.datacursor.execute(sql, (kardes["TC"], kardes["TC"])) atarafkuzenler = self.datacursor.fetchall() for x in atarafkuzenler: if kardes["TC"] != annesi["TC"]: x.pop("id") self.atarafkuzensayi += 1 x.update({"YAKINLIKDURUMU": "Anne Taraf Kuzen"}) alls.append(x) except: pass try: sql = "SELECT * FROM 101m WHERE BABATC=%s OR ANNETC=%s" self.datacursor.execute(sql, (kendisi["TC"], kendisi["TC"])) cocuklar = self.datacursor.fetchall() temp = [] for x in cocuklar: x.pop("id") x.update({"YAKINLIKDURUMU": "Çocuğu"}) alls.insert(1, x) except: pass kendisi.update({"KARDES": self.kardessayi, "BABATARAFKUZEN": self.btarafkuzensayi, "ANNETARAFKUZEN": self.atarafkuzensayi, "KUZEN": self.atarafkuzensayi + self.btarafkuzensayi}) self.clear() return alls def clear(self): self.kardessayi = 0 self.btarafkuzensayi = 0 self.atarafkuzensayi = 0 def withq(self, q, db): if db == "data": sql = q self.datacursor.execute(sql) result = self.datacursor.fetchall() return result if db == "mernis": sql = "select * from secmen2015 where TC=%s" self.merniscursor.execute(sql, (q,)) result = self.merniscursor.fetchall() return result data = Data() text = """ TC: {} Ad Soyad: {} {} Anne Adı: {} Baba Adı: {} Doğum Tarihi: {} Yakınlık Durumu: {} Nufus İl: {} Nufus İlçe: {} """ text2 = """ TC: {} Ad Soyad: {} {} Anne Adı: {} Baba Adı: {} Doğum Tarihi: {} Yakınlık Durumu: {} Kardeş Sayısı: {} Baba Taraf Kuzen: {} Anne Taraf Kuzen: {} Toplam Kuzen: {} """ while True: i = int(input(""" 1) Sorgu(Tekil) 2) Sorgu(Sülale) 3) Gsm 4) Query 5) Exit Seçiminiz: """)) if i == 1: ad = input("AD: ") soyad = input("Soyad: ") yil = input("Doğum Yılı: ") users = data.get_detail(ad, soyad, yil=yil if yil else None) os.system("clear") for u in users: print(text.format(u["TC"],u["ADI"],u["SOYADI"],u["ANNEADI"],u["BABAADI"],u["DOGUMTARIHI"], u["YAKINLIKDURUMU"], u["NUFUSIL"], u["NUFUSILCE"])) if i == 2: tc = input("TC: ") us = data.get_family(tc) os.system("clear") for i,u in enumerate(us): if i == 0: print(text2.format(u["TC"],u["ADI"],u["SOYADI"],u["ANNEADI"],u["BABAADI"],u["DOGUMTARIHI"], u["YAKINLIKDURUMU"], u["KARDES"], u["BABATARAFKUZEN"], u["ANNETARAFKUZEN"], u["KUZEN"])) else: print(text.format(u["TC"],u["ADI"],u["SOYADI"],u["ANNEADI"],u["BABAADI"],u["DOGUMTARIHI"], u["YAKINLIKDURUMU"], u["NUFUSIL"], u["NUFUSILCE"])) if i == 3: opt = int(input(""" 1) TC 2) GSM Seçiminiz: """)) if opt == 1: tc = input("TC: ") all = data.get_gsm(tc=tc) if opt == 2: gsm = input("GSM: ") all = data.get_gsm(gsm=gsm) print(all) if i == 4: q = input("TC: ") #q = f""SELECT # *, # CASE # WHEN TC = '{tc}' THEN 'Kendisi' # WHEN ANNETC IN (SELECT ANNETC FROM 101m WHERE TC = '{tc}') OR BABATC IN (SELECT BABATC FROM 101m WHERE TC = '{tc}') THEN 'Kardeşi' # WHEN TC IN (SELECT ANNETC FROM 101m WHERE TC = '{tc}') OR TC IN (SELECT BABATC FROM 101m WHERE TC = '{tc}') THEN 'Ebeveyn' # END AS yakınlık #FROM # 101m #WHERE # ANNETC = (SELECT ANNETC FROM 101m WHERE TC = '{tc}') OR BABATC = (SELECT BABATC FROM 101m WHERE TC = '{tc}') OR TC = '{tc}';"" res = data.withq(q, "data") print(*res, sep = "\n") if i == 5: exit()