Πώς μπορούμε να υπολογίσουμε τα ασφάλιστρα αυτοκινήτων που έχουμε σε SQL Server με χρήση Python (χωρίς API)
- Πώς βρίσκουμε τι δικαιώματα έχει ένας χρήστης σε βάση δεδομένων της Oracle - 1 Νοέμβριος 2024
- Πώς ενεργοποιούμε το Unified Auditing σε Oracle Database - 7 Οκτώβριος 2024
- Τι είναι η PostgreSQL και πως κάνουμε μία πλήρης εγκατάσταση - 2 Σεπτέμβριος 2024
Ας πούμε ότι έχουμε στον SQL Server έναν πίνακα με αυτοκίνητα και οδηγούς και θέλουμε να βρούμε το φθηνότερο ασφάλιστρο τους. Αυτό σίγουρα θα είναι εφικτό με ένα API / Web Service. Η λύση αυτή όμως θα απαιτούσε την συνεργασία των IT τμημάτων (μεταξύ της ασφαλιστικής και την δική μας) στη δημιουργία και τη χρήση του.
Τι ακριβώς θέλουμε να πετύχουμε
Με λίγα λόγια θέλουμε έχοντας έναν πίνακα στη βάση δεδομένων κάπως έτσι:
Να συμπληρωθεί η τιμή του ασφαλίστρου και να είναι κάπως έτσι:
Υπάρχει άλλη λύση;
Προφανώς για να υπάρχει το άρθρο υπάρχει και ο τρόπος 🙂 .
Μέ χρήση της βιβλιοθήκης selenium και pyodbc της Python μπορούμε ουσιαστικά να αντιγράψουμε τις ανθρώπινες κινήσεις που θα κάναμε στο browser του Chrome ώστε να υπολογίσουμε τα ασφάλιστρα και να ενημερώσουμε το πεδίο στη κάθε εγγραφή της βάσης.
Παρόμοια λύση είχαμε δει σε αυτό το άρθρο ώστε να μεταφράσουμε πεδία από τα Αγγλικά στα Ελληνικά.
Στο παράδειγμα μας θα χρησιμοποιήσω ένα site μιας τυχαίας ασφαλιστικής εταιρείας που έχει calculator. Δεν θα το αναφέρω από την στιγμή που το άρθρο υπάρχει για εκπαιδευτικούς μόνο λόγους.
Το site που θα χρησιμοποιήσουμε έχει αυτή την μορφή. Αρκετά πεδία χρειάζονται για την τιμολόγηση, κάποια από αυτά είναι dropdown και στο τέλος υπάρχει και το κουμπί του υπολογισμού.
Προαπαιτούμενα
Θα πρέπει να κατεβάσουμε την αντίστοιχη έκδοση που είναι ο chrome μας του Chromedriver.
Στο command prompt με την παρακάτω εντολή μας κάνει εγκατάσταση την python:
cmd python
Στο command prompt τρέχουμε τις εντολές αυτές ώστε να εγκατασταθούν τα modules που χρειαζόμαστε:
pip install –U selenium pip install pyodbc
Επίσης θα χρειαστούμε έναν χρήστη της βάσεις που έχει δικαιώματα στον πίνακα αυτόν.
Η προετοιμασία στον πινακάς μας
Θα φτιάξουμε τώρα έναν staging πίνακα που θα μπορούσε να γεμίζει τιμές από άλλους πίνακες που υπάρχουν στη βάση.
Καθώς όπως είδαμε στο site τα dropdown μενού λειτουργούν μόνο με προεπιλεγμένες τιμές θα πρέπει να μετατρέψουμε τα δεδομένα μας σε αυτές τις τιμές:
create table car_calc( id int identity(1,1) primary key, insurance varchar(10) default null, birth_d varchar(10), birth_m varchar(10), birth_y varchar(10), email varchar(50), plate varchar(10), plate2 varchar(10), cc varchar(10), value varchar(10), tk varchar(10), dmg varchar(10) default '0', duration varchar(10) default '6 μήνες', car_age varchar(10), sex varchar(10), licence_age varchar(20))
Αφού πρόκειται για παράδειγμα και δεν έχω άλλους πίνακες απλά θα κάνω insert 3 εγγραφές προσέχοντας όμως όσα πεδία είναι dropdown να έχω ακριβώς το ίδιο κείμενο:
insert into car_calc (birth_d,birth_m,birth_y,email,plate,plate2,cc,value,tk,dmg,duration,car_age,sex,licence_age) values ('24','01','1990','patatakia@hotmail.com','ΟΕΖ','5120','1600','2000','15773','2','6 μήνες','5 ετών','Γυναίκα','3') ,('15','05','2000','dokimi@hotmail.com','ΜΥΕ','2300','1000','4500','81100','0','6 μήνες','2 ετών','''Ανδρας','8') ,('12','03','1962','calc@hotmail.com','ΙΑΕ','3920','2400','15000','15770','1','6 μήνες','3 ετών','''Ανδρας','7'); select * from car_calc;
Η προετοιμασία για το Python script
Το πιο δύσκολο κομμάτι της διαδικασίας είναι αυτό. Θα χρειαστεί να κρατήσουμε από το site το xPath από κάθε κουτί ή λίστα που συμπληρωθεί, το κουμπί που πραγματοποιεί τον υπολογισμό και τέλος από το κουτί που θα εμφανίσει τα αποτελέσματα.
Κάνουμε δεξί κλικ στο καθένα – Inspect – Copy – Copy xPath:
Κρατάμε τις τιμές αυτές μία μία για χρήση μετέπειτα σε μεταβλητές π.χ.:
D_birthbox ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_Day"]'
Σε όσα έχουν dropdown θα χρειαστεί να κρατήσουμε την τιμή των Options προσθέτοντάς στο τέλος την παράμετρο της λίστας /li[@data-val=”%s”]’ και με αυτό τον τρόπο με το % duration_parameter στο τέλος θα πάρει σαν επιλογή την λίστα την τιμή της μεταβλητής αυτής:
duration_select_box_option ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_InsuraceDurationDropDownSelectBoxItOptions"]/li[@data-val="%s"]' % duration_parameter
Επίσης για τα dropdown θα χρειαστούμε να κρατήσουμε και το xPath που εμφανίζει τα options ώστε να μας αφήνει να τα επιλέξουμε. Κάνοντα έτσι μίμηση την διαδικασία του click:
duration_select_box ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_InsuraceDurationDropDownSelectBoxIt"]' driver.find_element_by_xpath(duration_select_box).click() time.sleep(1); driver.find_element_by_xpath(duration_select_box_option).click()
Τέλος δεν θα πρέπει να ξεχάσουμε να κρατήσουμε τo xPath από το κουμπί του υπολογισμού και το κουτιού που εμφανίζει το ποσό αντίστοιχα:
submit_button = '//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_Submit"]' output_box = '//*[@id="ResultsTable"]/tbody/tr[20]/td[2]/b'
Η επεξήγηση του κώδικα βήμα βήμα
Με .get στο webdriver δηλώνουμε την σελίδα που θα μας ανοίξει στον Chrome:
chromedriver_location = "C:\\Users\\smatzouranis\\Desktop\\python\\chromedriver" driver = webdriver.Chrome(chromedriver_location) driver.get('https://www............./calculator/')
Με το module pyodbc κάνουμε connect στη βάση:
conn = pyodbc.connect("Driver={SQL Server};" "Server=SMATZOURANISLP\SQL19;" "Database=db_test;" "username=python;" "password=python;" "trusted_connection = yes;")
Ανοίγουμε έναν cursor στον οποίον θα έχουμε το select query μας με τα δεδομένα από τη βάση:
cursor = conn.cursor() cursor.execute('select id,birth_d,birth_m,birth_y,email,plate,plate2,cc,value,tk,dmg,duration,car_age,sex,licence_age from car_calc') results = cursor.fetchall()
Πλέον μπορούμε στη μεταβλητή results έχουμε έναν δισδιάστατο πίνακα όπου results[i][0] ως i ορίζουμε την γραμμή του πίνακα και ως 0 την πρώτη στήλη του. Π.χ. results[0][4] είναι το email της πρώτης εγγραφής. Έτσι με ένα loop μπορούμε να κάνουμε περάσουμε ένα ένα όλα τα πεδία στα κελιά του site να κάνουμε τον υπολογισμό και να προχωρήσουμε στην επόμενη εγγραφή.
Ορίζουμε σε μια μεταβλητή το update statement που θα περνάει στη βάση μας το ποσό του ασφαλίστρου:
update = """update car_calc set insurance=? where id=?"""
Ο συνολικός κώδικας του Python script
#python from selenium import webdriver from selenium.common.exceptions import StaleElementReferenceException import pyodbc,time chromedriver_location = "C:\\Users\\smatzouranis\\Desktop\\python\\chromedriver" driver = webdriver.Chrome(chromedriver_location) driver.get('https://www............./calculator/') conn = pyodbc.connect("Driver={SQL Server};" "Server=SMATZOURANISLP\SQL19;" "Database=db_test;" "username=python;" "password=python;" "trusted_connection = yes;") output_box = '//*[@id="ResultsTable"]/tbody/tr[20]/td[2]/b' cursor = conn.cursor() cursor.execute('select id,birth_d,birth_m,birth_y,email,plate,plate2,cc,value,tk,dmg,duration,car_age,sex,licence_age from car_calc') results = cursor.fetchall() print(results) update = """update car_calc set insurance=? where id=?""" for i in range(len(results)): try: D_birthbox ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_Day"]' D_birth_parameter = results[i][1] driver.find_element_by_xpath(D_birthbox).clear() driver.find_element_by_xpath(D_birthbox).send_keys(D_birth_parameter) M_birthbox ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_Month"]' M_birth_parameter = results[i][2] driver.find_element_by_xpath(M_birthbox).clear() driver.find_element_by_xpath(M_birthbox).send_keys(M_birth_parameter) Y_birthbox ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_Year"]' Y_birth_parameter = results[i][3] driver.find_element_by_xpath(Y_birthbox).clear() driver.find_element_by_xpath(Y_birthbox).send_keys(Y_birth_parameter) mail_textbox ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_EmailCar"]' mail_parameter = results[i][4] driver.find_element_by_xpath(mail_textbox).clear() driver.find_element_by_xpath(mail_textbox).send_keys(mail_parameter) PLATEp1_textbox ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_RegistrationNumber1"]' PLATEp1_parameter = results[i][5] driver.find_element_by_xpath(PLATEp1_textbox).clear() driver.find_element_by_xpath(PLATEp1_textbox).send_keys(PLATEp1_parameter) PLATEp2_textbox ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_RegistrationNumber2"]' PLATEp2_parameter = results[i][6] driver.find_element_by_xpath(PLATEp2_textbox).clear() driver.find_element_by_xpath(PLATEp2_textbox).send_keys(PLATEp2_parameter) CC_textbox = '//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_CC"]' CC_parameter = results[i][7] driver.find_element_by_xpath(CC_textbox).clear() driver.find_element_by_xpath(CC_textbox).send_keys(CC_parameter) value_textbox = '//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_VehicleValue"]' value_parameter = results[i][8] driver.find_element_by_xpath(value_textbox).clear() driver.find_element_by_xpath(value_textbox).send_keys(value_parameter) TK_textbox = '//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_ZipCode"]' TK_parameter = results[i][9] driver.find_element_by_xpath(TK_textbox).clear() driver.find_element_by_xpath(TK_textbox).send_keys(TK_parameter) dmg_parameter = results[i][10] dmg_select_box_option ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_CrashCountDropDownSelectBoxItOptions"]/li[@data-val="%s"]' % dmg_parameter dmg_select_box ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_CrashCountDropDownSelectBoxIt"]' driver.find_element_by_xpath(dmg_select_box).click() time.sleep(1); driver.find_element_by_xpath(dmg_select_box_option).click() duration_parameter = results[i][11] duration_select_box_option ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_InsuraceDurationDropDownSelectBoxItOptions"]/li[@data-val="%s"]' % duration_parameter duration_select_box ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_InsuraceDurationDropDownSelectBoxIt"]' driver.find_element_by_xpath(duration_select_box).click() time.sleep(1); driver.find_element_by_xpath(duration_select_box_option).click() carage_parameter = results[i][12] carage_select_box_option ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_VehicleAgeDropDownSelectBoxItOptions"]/li[@data-val="%s"]' % carage_parameter carage_select_box ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_VehicleAgeDropDownSelectBoxItText"]' driver.find_element_by_xpath(carage_select_box).click() time.sleep(1); driver.find_element_by_xpath(carage_select_box_option).click() SEX_parameter = results[i][13] SEX_select_box_option ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_SexTypeDropDownSelectBoxItOptions"]/li[@data-val="%s"]' % SEX_parameter SEX_select_box ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_SexTypeDropDownSelectBoxIt"]' driver.find_element_by_xpath(SEX_select_box).click() time.sleep(1); driver.find_element_by_xpath(SEX_select_box_option).click() Y_parameter = results[i][14] Y_select_box_option ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_YearsDiplomaDropDownSelectBoxItOptions"]/li[@data-val="%s"]' % Y_parameter Y_select_box ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_YearsDiplomaDropDownSelectBoxItText"]' driver.find_element_by_xpath(Y_select_box).click() time.sleep(1); driver.find_element_by_xpath(Y_select_box_option).click() submit_button = '//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_Submit"]' driver.find_element_by_xpath(submit_button).click() time.sleep(4); price = driver.find_element_by_xpath(output_box).text cursor.execute(update,(price,i+1)) print(driver.find_element_by_xpath(output_box).text) except StaleElementReferenceException: pass conn.commit(); cursor.close(); driver.quit; conn.close(); exit();
Το αποτέλεσμα
Τρέχοντας λοιπόν το Python script θα δούμε ότι όταν κάνουμε select στον πίνακα θα έχει για το καθένα στη δεύτερη στήλη το ποσό του ασφαλίστρου:
Αν θέλουμε να το εκτελούμε μέσα από job στον SQL Server
Ενεργοποιούμε τη δυνατότητα στον SQL Server να τρέχει direct cmd script:
exec sp_configure 'show advanced option',1 go sp_configure 'xp_cmdshell',1 go reconfigure with override
Στην περίπτωση που θα το τρέχει ο SQL Server Agent. Φτιάχνουμε και ένα Job που θα περιέχει το full path με το όνομα του αρχείου που θα εκτελεί:
π.χ.
"C:\Users\smatzouranis\Desktop\python\python_chrome_automation_insurance.py"
Είναι έτοιμο το job προς εκτέλεση:
exec msdb.dbo.sp_start_job 'callPythonw'
Κλείνοντας θα πρέπει να αναφέρω ότι απλά αλλάζοντας τις παραμέτρους στο pyodbc το script θα μπορούσε να λειτουργήσει σε οποιαδήποτε συμβατή με αυτό βάση δεδομένων.