Untitled

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()