def checkdif(table1, table2): #έλεγχος μεγεθών πινάκων
sql1 = f"select * from {table1}"
cursor.execute(sql1)
size1 = len(cursor.fetchall())
sql2 = f"select * from {table2}"
cursor.execute(sql2)
size2 = len(cursor.fetchall())
print("size1", size1)
print("size2", size2)
def moneyfr(
): #τι ποσοστό όλων των κόμβων ανά μήνα είναι και financial και γαλλικοί
refer = ('2014-01-', '2014-02-', '2014-03-', '2014-04-', '2014-05-',
'2014-06-', '2014-07-', '2014-08-', '2014-09-', '2014-10-',
'2014-11-', '2014-12-')
frenchx = [
"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct",
"Nov", "Dec"
]
frenchy = []
for month in refer:
#sql = "select distinct tran.AcquiringAccountHolder from Transactions_New as tran inner join EUTL_AccountHolders as acc on tran.AcquiringAccountHolder=acc.holderName where acc.country='DE' inner join EUTL_AccHolderClassification as class on acc.rawCode=class.holder where class.category='financial'"
sql00 = f"""select distinct tran.acquiringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.acquiringaccountholder = acc.holdername
and acc.rawcode=class.holder
and TransactionDate LIKE '{month}%'"""
sql01 = f"""select distinct tran.transferringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.transferringaccountholder = acc.holdername
and acc.rawcode=class.holder
and TransactionDate LIKE '{month}%'"""
cursor.execute(sql00)
acqall = cursor.fetchall()
cursor.execute(sql01)
transall = cursor.fetchall()
duplall = acqall + transall
sql2 = f"""select distinct tran.acquiringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.acquiringaccountholder = acc.holdername
and acc.country='FR'
and acc.rawcode=class.holder
and category='financial'
and TransactionDate LIKE '{month}%'"""
sql3 = f"""select distinct tran.transferringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.transferringaccountholder = acc.holdername
and acc.country='FR'
and acc.rawcode=class.holder
and category='financial'
and TransactionDate LIKE '{month}%'"""
cursor.execute(sql2)
acq = cursor.fetchall()
cursor.execute(sql3)
trans = cursor.fetchall()
#sql1= ("tran.AcquiringAccountHolder")
#cursor.execute(sql)
#result = cursor.fetchall()
dupllist = acq + trans
frenchy.append(
len(get_unique(dupllist)) * 100 / len(get_unique(duplall)))
#print(len(acq),len(trans),len(get_unique(dupllist)))
axs[0, 1].plot(frenchx, frenchy)
axs[0, 1].set_title('French')
def moneyde(
): #τι ποσοστό όλων των κόμβων ανά μήνα είναι και financial και γερμανικοί
refer = ('2008-04-', '2009-04-', '2010-04-', '2011-04-', '2012-04-',
'2013-04-', '2014-04')
germanx = ['08', '09', '10', '11', '12', '13', '14']
germany = []
for month in refer:
#sql = "select distinct tran.AcquiringAccountHolder from Transactions_New as tran inner join EUTL_AccountHolders as acc on tran.AcquiringAccountHolder=acc.holderName where acc.country='DE' inner join EUTL_AccHolderClassification as class on acc.rawCode=class.holder where class.category='financial'"
#sql0 = f"SELECT * FROM Transactions_New WHERE TransactionDate LIKE '{month}%'"
sql00 = f"""select distinct tran.acquiringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.acquiringaccountholder = acc.holdername
and acc.rawcode=class.holder
and TransactionDate LIKE '{month}%'"""
sql01 = f"""select distinct tran.transferringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.transferringaccountholder = acc.holdername
and acc.rawcode=class.holder
and TransactionDate LIKE '{month}%'"""
cursor.execute(sql00)
acqall = cursor.fetchall()
cursor.execute(sql01)
transall = cursor.fetchall()
duplall = acqall + transall
sql2 = f"""select distinct tran.acquiringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.acquiringaccountholder = acc.holdername
and acc.country='DE'
and acc.rawcode=class.holder
and category='financial'
and TransactionDate LIKE '{month}%'"""
sql3 = f"""select distinct tran.transferringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.transferringaccountholder = acc.holdername
and acc.country='DE'
and acc.rawcode=class.holder
and category='financial'
and TransactionDate LIKE '{month}%'"""
cursor.execute(sql2)
acq = cursor.fetchall()
cursor.execute(sql3)
trans = cursor.fetchall()
#sql1= ("tran.AcquiringAccountHolder")
#cursor.execute(sql)
#result = cursor.fetchall()
dupllist = acq + trans
germany.append(
len(get_unique(dupllist)) * 100 / len(get_unique(duplall)))
#print(len(acq),len(trans),len(get_unique(dupllist)))
axs[1, 0].plot(germanx, germany, 'tab:green')
axs[1, 0].set_title('Germany')
def sector20(): #σεκτορ 20 για ολλανδία γαλλία γερμανία
refer = ('2014-01-', '2014-02-', '2014-03-', '2014-04-', '2014-05-',
'2014-06-', '2014-07-', '2014-08-', '2014-09-', '2014-10-',
'2014-11-', '2014-12-')
combx = [
"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct",
"Nov", "Dec"
]
comby = []
for month in refer:
sql00 = f"""select tran.acquiringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.acquiringaccountholder = acc.holdername
and acc.rawcode=class.holder
and TransactionDate LIKE '{month}%'"""
sql01 = f"""select tran.transferringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.transferringaccountholder = acc.holdername
and acc.rawcode=class.holder
and TransactionDate LIKE '{month}%'"""
cursor.execute(sql00)
acqall = cursor.fetchall()
cursor.execute(sql01)
transall = cursor.fetchall()
duplall = acqall + transall
sql = f"""select tran.acquiringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.acquiringaccountholder = acc.holdername
and acc.rawcode=class.holder
and class.sector='20'
and TransactionDate LIKE '{month}%'"""
sql1 = f"""select tran.transferringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.transferringaccountholder = acc.holdername
and acc.rawcode=class.holder
and class.sector='20'
and TransactionDate LIKE '{month}%'"""
cursor.execute(sql)
acq = cursor.fetchall()
cursor.execute(sql1)
trans = cursor.fetchall()
dupllist = acq + trans
comby.append(
len(get_unique(dupllist)) * 100 / len(get_unique(duplall)))
#print(len(acq),len(trans),len(get_unique(dupllist)))
sec[1].plot(combx, comby)
def mineral(): #διυλιστήρια
refer = ('2014-01-', '2014-02-', '2014-03-', '2014-04-', '2014-05-',
'2014-06-', '2014-07-', '2014-08-', '2014-09-', '2014-10-',
'2014-11-', '2014-12-')
mineralx = [
"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct",
"Nov", "Dec"
]
mineraly = []
for month in refer:
sql00 = f"""select distinct tran.acquiringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.acquiringaccountholder = acc.holdername
and acc.rawcode=class.holder
and TransactionDate LIKE '{month}%'"""
sql01 = f"""select distinct tran.transferringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.transferringaccountholder = acc.holdername
and acc.rawcode=class.holder
and TransactionDate LIKE '{month}%'"""
cursor.execute(sql00)
acqall = cursor.fetchall()
cursor.execute(sql01)
transall = cursor.fetchall()
duplall = acqall + transall
sql = f"""select tran.acquiringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.acquiringaccountholder = acc.holdername
and acc.rawcode=class.holder
and (class.sector='2' or class.sector='21')
and TransactionDate LIKE '{month}%'"""
sql1 = f"""select tran.transferringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.transferringaccountholder = acc.holdername
and acc.rawcode=class.holder
and (class.sector='2' or class.sector='21')
and TransactionDate LIKE '{month}%'"""
cursor.execute(sql)
acq = cursor.fetchall()
cursor.execute(sql1)
trans = cursor.fetchall()
dupllist = acq + trans
mineraly.append(
len(get_unique(dupllist)) * 100 / len(get_unique(duplall)))
#print(len(acq),len(trans),len(get_unique(dupllist)))
sec[0].plot(mineralx, mineraly)
def sector20():
refer = ('2008-04-', '2009-04-', '2010-04-', '2011-04-', '2012-04-',
'2013-04-', '2014-04')
combx = ['2008', '2009', '2010', '2011', '2012', '2013', '2014']
comby = []
for month in refer:
sql00 = f"""select distinct tran.acquiringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.acquiringaccountholder = acc.holdername
and acc.rawcode=class.holder
and TransactionDate LIKE '{month}%'"""
sql01 = f"""select distinct tran.transferringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.transferringaccountholder = acc.holdername
and acc.rawcode=class.holder
and TransactionDate LIKE '{month}%'"""
cursor.execute(sql00)
acqall = cursor.fetchall()
cursor.execute(sql01)
transall = cursor.fetchall()
duplall = acqall + transall
sql = f"""select tran.acquiringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.acquiringaccountholder = acc.holdername
and acc.rawcode=class.holder
and class.sector='20'
and TransactionDate LIKE '{month}%'"""
sql1 = f"""select tran.transferringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.transferringaccountholder = acc.holdername
and acc.rawcode=class.holder
and class.sector='20'
and TransactionDate LIKE '{month}%'"""
cursor.execute(sql)
acq = cursor.fetchall()
cursor.execute(sql1)
trans = cursor.fetchall()
dupllist = acq + trans
comby.append(
len(get_unique(dupllist)) * 100 / len(get_unique(duplall)))
#print(len(acq),len(trans),len(get_unique(dupllist)))
sec[1].plot(combx, comby)
def mineral():
refer = ('2008-12-', '2009-12-', '2010-12-', '2011-12-', '2012-12-',
'2013-12-', '2014-12')
mineralx = ['2008', '2009', '2010', '2011', '2012', '2013', '2014']
mineraly = []
for month in refer:
sql00 = f"""select distinct tran.acquiringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.acquiringaccountholder = acc.holdername
and acc.rawcode=class.holder
and TransactionDate LIKE '{month}%'"""
sql01 = f"""select distinct tran.transferringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.transferringaccountholder = acc.holdername
and acc.rawcode=class.holder
and TransactionDate LIKE '{month}%'"""
cursor.execute(sql00)
acqall = cursor.fetchall()
cursor.execute(sql01)
transall = cursor.fetchall()
duplall = acqall + transall
sql = f"""select tran.acquiringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.acquiringaccountholder = acc.holdername
and acc.rawcode=class.holder
and (class.sector='2' or class.sector='21')
and TransactionDate LIKE '{month}%'"""
sql1 = f"""select tran.transferringaccountholder
from transactions_new as tran, eutl_accountholders as acc, eutl_accholderclassification as class
where tran.transferringaccountholder = acc.holdername
and acc.rawcode=class.holder
and (class.sector='2' or class.sector='21')
and TransactionDate LIKE '{month}%'"""
cursor.execute(sql)
acq = cursor.fetchall()
cursor.execute(sql1)
trans = cursor.fetchall()
dupllist = acq + trans
mineraly.append(
len(get_unique(dupllist)) * 100 / len(get_unique(duplall)))
#print(len(acq),len(trans),len(get_unique(dupllist)))
sec[0].plot(mineralx, mineraly)
def create_database():
cursor.execute(
"CREATE DATABASE IF NOT EXISTS {} DEFAULT CHARACTER SET 'utf8'".format(
DB_NAME))
print("Database {} created".format((DB_NAME)))
def counting(table1): #μέγεθος πίνακα
sql1 = f"select * from {table1}"
cursor.execute(sql1)
size1 = len(cursor.fetchall())
print("size:", size1)
def ignite(): #διαλέγω τη βάση
sql = ("USE EU_ETS")
cursor.execute(sql)