· 8 years ago · Jun 23, 2018, 10:52 AM
1
2import pymysql
3
4# workDB_login = {"host": "localhost", "user": "root", "password": "", "database": "workDB"}
5
6
7def sukurti_lentele(login,pavadinimas):
8 """ sukuria lentele"""
9 # amazon_db_prisijungimas = {"host": "localhost", "user": "root", "password": "", "database": "amazon"}
10 db = pymysql.connect(login["host"], login["user"], login["password"], login["database"])
11 cursor = db.cursor()
12 sql = """CREATE TABLE IF NOT EXISTS `{pavadinimas}` ( `id` INT NOT NULL AUTO_INCREMENT ,
13 `Position` VARCHAR(600) NOT NULL ,
14 `Firm` VARCHAR(600) NOT NULL ,
15 `City` VARCHAR(30) NOT NULL ,
16 `Salary` VARCHAR(600) NULL DEFAULT NULL ,
17 `Link` VARCHAR(600) NOT NULL ,
18 PRIMARY KEY (`id`)) ENGINE = InnoDB CHARSET=utf8;""".format(pavadinimas=pavadinimas)
19 try: # kad nieko nemestu
20 cursor.execute(sql)
21 db.commit()
22 except:
23 print("nepavyko sukurti lenteles lenteles kurimo sql:\n {} ".format(sql))
24 print("bandau istrinti sena db")
25 db.rollback()
26 print("grazinu kaip buvo")
27 db.close()
28
29
30# sukurti_lentele(workDB_login,"testas")
31
32# Truncate table XXX
33
34def reset_table(login,tablename):
35 """ resets table"""
36 db = pymysql.connect(login["host"], login["user"], login["password"], login["database"])
37 cursor = db.cursor()
38 sql = """Truncate table {};""".format(tablename)
39 try: # kad nieko nemestu
40 cursor.execute(sql)
41 db.commit()
42 except:
43 print("nepavyko sukurti lenteles lenteles kurimo sql:\n {} ".format(sql))
44 print("bandau istrinti sena db")
45 db.rollback()
46 print("grazinu kaip buvo")
47 print("restartuota lentele {}".format(tablename))
48 db.close()
49
50# workDB_login = {"host": "localhost", "user": "root", "password": "", "database": "workDB"}
51#
52# sukurti_lentele(workDB_login,"CVbankasDB")
53
54
55
56# TODO pasiema is db lenteliu pavadinimus
57def patikrinti_db(sql, login):
58 """ pasiema sql komanta ir prisijungima prie db
59 :returns tuple su ataskymu"""
60 # login pavizdys
61 # amazon_db_prisijungimas = {"host": "localhost", "user": "root", "password": "", "database": "amazon"}
62 db = pymysql.connect(login["host"], login["user"], login["password"], login["database"])
63 cursor = db.cursor()
64 try: # kad nieko nemestu
65 cursor.execute(sql)
66 atsakymas = cursor.fetchall()
67 except:
68 atsakymas = "nepavyko pagauti visu"
69 print("grazinu db kaip buvo")
70 # Rollback in case there is any error
71 db.rollback()
72 db.close()
73 return atsakymas
74
75
76def vygdyti_sql_uzklausa(login, sql):
77 """ pasiema sql komanda ir prisijungima prie db ivygdo comanda"""
78 # login pavizdys
79 # amazon_db_prisijungimas = {"host": "localhost", "user": "root", "password": "", "database": "amazon"}
80 db = pymysql.connect(login["host"], login["user"], login["password"], login["database"], charset='utf8')
81 cursor = db.cursor()
82 duomenys = sql
83 # try:
84 cursor.execute(duomenys)
85 db.commit()
86 # except:
87 # print("--------\ngrazinu db kaip buvo sql uzklausa:\n {} \n--------".format(sql))
88 # # Rollback in case there is any error
89 # db.rollback()
90 db.close()
91
92
93# for x in didelis_masyvas:
94# print(x[0])
95# print("\n")
96# for y in x[1]:
97# # print(y)
98# insert = """INSERT INTO `filtruota` (`pavadinimas`,`kategorija`, `kaina`, `ivertinimas`, `stars`, `prime`, `foto`, `link`) VALUES ('{pavadinimas}','{kategorija}', '{kaina}', '{ivertinimas}', '{stars}', '{prime}', '{foto}', '{link}');""".format(
99# pavadinimas=y[1].translate({ord(c): None for c in "'`"}), kategorija=x[0][0], kaina=y[2], ivertinimas=y[3],
100# stars=y[4],prime=y[5], foto=y[6],link=y[7])
101# # print(insert)
102# # # print(insert)
103# vygdyti_sql_uzklausa(sql=insert,login=amazon_filtruota_prisijungimas)
104# print(y)
105
106
107# sukurti lentele
108# vygdyti_sql_uzklausa(sql="""CREATE TABLE `Whitelist` (
109# `id` int(255) NOT NULL AUTO_INCREMENT PRIMARY KEY,
110# `pavadinimas` varchar(999) NOT NULL,
111# `kategorija` varchar(999) NOT NULL,
112# `kaina` float NOT NULL,
113# `ivertinimas` float NOT NULL,
114# `stars` int(255) NOT NULL,
115# `prime` tinyint(1) NOT NULL,
116# `foto` varchar(999) NOT NULL,
117# `link` varchar(999) NOT NULL
118# ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
119# """, login=amazon_filtruota_prisijungimas)
120
121# viska sudeti
122
123# vygdyti_sql_uzklausa(sql=""" insert INTO `Whitelist` (
124# `pavadinimas`,
125# `kategorija`,
126# `kaina`,
127# `ivertinimas`,
128# `stars`,
129# `prime`,
130# `foto`,
131# `link`
132# )
133# select
134# `pavadinimas` as `pavadinimas`,
135# `kategorija` as `kategorija`,
136# `kaina` as `kaina`,
137# `ivertinimas` as `ivertinimas`,
138# `stars` as `stars`,
139# `prime` as `prime`,
140# `foto` as `foto`,
141# `link` as `link`
142# FROM `filtruota` WHERE kaina <= 25 and prime = 1 and stars >= 20;""", login=amazon_filtruota_prisijungimas)
143
144print("baigta")