How can we calculate our car insurance premiums in SQL Server using Python (no API)
Certifications:
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
Let's say we have in SQL Server a table of cars and drivers and we want to find their cheapest premium. This will certainly be possible with one API / Web Service. However, this solution would require the cooperation of the IT departments (between the insurance company and ours) in its creation and use.
What exactly do we want to achieve?
In short we want to have a table in the database like this:
Fill in the premium price and it should look something like this:
Is there another solution?
Obviously, for the article to exist, there is also the way 🙂 .
Using the library selenium and pyodbc her Python we can essentially copy the human movements we would do in his browser Chrome so that we can calculate the premiums and update the field in each database record.
We had seen a similar solution in this the article to translate fields from English to Greek.
In our example I will use a random insurance company site that has a calculator. I will not mention it since the article exists for educational purposes only.
The site we will use has this format. Several fields are needed for pricing, some of them are dropdowns and at the end there is also the calculation button.
Prerequisites
We should download the corresponding version that is our chrome Chromedriver.
In the command prompt, install python with the following command:
cmd pythonIn the command prompt we run these commands to install the modules we need:
pip install –U selenium pip install pyodbc
We will also need a database user who has rights to this table.
The preparation on our table
We will now make one staging table which could be populated with values from other tables present in the base.
As we saw on the site dropdown menus only work with default values we should convert our data to these values:
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))
Since this is an example and I don't have any other tables, I will simply insert 3 records, making sure that the fields that are dropdowns have exactly the same text:
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;
Preparing for the Python script
The hardest part of the process is this. We will need to keep it from the site xPath from each box or list that is completed, the button that performs the calculation and finally from the box that will display the results.
Doing right click to each – Inspect – Copy – Copy xPath:
We keep these values one by one for later use in variables e.g.:
D_birthbox ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_Day"]'
For those that have a dropdown, we will need to keep their price Options appending the list parameter to the end /li[@data-val=”%s”]' and in this way with the % duration_parameter at the end it will take as an option the list the value of this variable:
duration_select_box_option ='//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_InsuraceDurationDropDownSelectBoxItOptions"]/li[@data-val="%s"]' % duration_parameter
Also for the dropdown we will need to keep the xPath which displays the options to let us choose them. Thus imitating his process 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()
Finally, we should not forget to keep it xPath from the calculation button and the box showing the amount respectively:
submit_button = '//*[@id="ContentPlaceHolderDefault_MainPlaceHolder_CarCalculator_6_Submit"]' output_box = '//*[@id="ResultsTable"]/tbody/tr[20]/td[2]/b'
The explanation of the code step by step
With .get to webdriver we declare the page that will open to us on Chrome:
chromedriver_location = "C:\\Users\\smatzouranis\\Desktop\\python\\chromedriver"
driver = webdriver.Chrome(chromedriver_location)
driver.get('https://www............./calculator/')
With the module pyodbc connect to the base:
conn = pyodbc.connect("Driver={SQL Server};"
"Server=SMATZOURANISLP\SQL19;"
"Database=db_test;"
"username=python;"
"password=python;"
"trusted_connection = yes;")
We open one cursor in which we will have our select query with the data from the database:
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()
Now we can in the results variable we have one two-dimensional table where results[i][0] as i we define the row of the table and as 0 its first column. E.g. results[0][4] is the email of the first registration. So with one loop we can pass one by one all the fields in the cells of the site to do the calculation and proceed to the next record.
We assign to a variable the update statement which will pass to our base the amount of the insurance premium:
update = """update car_calc set insurance=? where id=?"""
The overall code of the 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();
The result
So running it Python script we will see that when we select the table it will have for each in the second column the amount of the premium:
If we want to run it through a job in SQL Server
We enable the feature on SQL Server to run direct cmd script:
exec sp_configure 'show advanced option',1 go sp_configure 'xp_cmdshell',1 go reconfigure with override
In the event that it will be run by SQL Server Agent. We also create a Job that will contain the full path with the name of the file it will execute:
e.g.
"C:\Users\smatzouranis\Desktop\python\python_chrome_automation_insurance.py"
The job is ready to run:
exec msdb.dbo.sp_start_job 'callPythonw'
In closing I should mention that simply by changing the parameters to pyodbc the script could work on anyone database compatible with it.
