Closure
Closure
Home » Databases » Microsoft SQL Server » How can we do field translation in SQL Server using Python (no API)

How can we do field translation in SQL Server using Python (no API)

How can we do field translation in SQL Server using Python (no API)
, , , Leave a comment
How can we do field translation in SQL Server using Python (no API)
Latest posts by Stratos Matzouranis (see all)

Python as a scripting language gives unlimited possibilities for automation. In this article we will see how in collaboration with SQL Server it is possible to translate the entire table field into Greek with one click through google translate.

First we should open the site with chrome on https://translate.google.gr/?hl=en .

In each of the two boxes of the source text and the translation we do: right click – Inspect – Copy – Copy xPath and keep the 2 values.

In our case it is:

input_box = '//*[@id="source"]'

output_box='/html/body/div[3]/div[2]/div[1]/div[2]/div[1]/div[1]/div[2]/div[3]/div[1]/div[2]/div/span[1]/span'
How can we do field translation in SQL Server using Python (no API)
How can we do field translation in SQL Server using Python (no API)

In SQL Server we create the table we will do the test

CREATE TABLE [dbo].[googleit](

       [id] [int] IDENTITY(1,1) NOT NULL,

       [en_text] [varchar](4000) NULL,

       [gr_text] [varchar](4000) NULL

)

GO

We add two values we want to translate.

insert into dbo.googleit (en_text) values(‘Hello, have a nice day’)

insert into dbo.googleit (en_text) values(‘Lets test the possibilities’)
How can we do field translation in SQL Server using Python (no API)

We enable the SQL Server to run direct cmd script (this step is optional because if we don't want to, we can run the .py file directly at the end and not have SQL Server run it for us).

exec sp_configure 'show advanced option',1

go

sp_configure 'xp_cmdshell',1

go

reconfigure with override

In the event that the SQL Server Agent will run it. 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.py"
How can we do field translation in SQL Server using Python (no API)

Let's see what we need to install now for Python.

Requirements

Download chromedrive to the folder that will be the py file with the code. (attention we should download the corresponding version which is our chrome): Chromedriver

In the command prompt, install python with the following command: python cmd

In the command prompt we run these commands to install the modules we need:

pip install –U selenium

pip install pyodbc

Steps

After we are ready we create a file named python_chrome_automation.py and start and write our code that you mention below:

#Για αρχή καλούμε τις βιβλιοθήκες που βάλαμε

from selenium import webdriver

from selenium.common.exceptions  import StaleElementReferenceException

import pyodbc,time

#Κάνουμε odbc connection με τη βάση βάζοντας τις ανάλογες πληροφορίες

conn = pyodbc.connect("Driver={SQL Server};"

                                                "Server=SMATZOURANISLP\SQL19;"

                                                "Database=translation;"

                                                "username=python;"

                                                "password=python;"

                                               "trusted_connection = yes;")

#το path που είναι ο chromedriver

chromedriver_location = "C:\\Users\\smatzouranis\\Desktop\\python\\chromedriver"

driver = webdriver.Chrome(chromedriver_location)

#Το site που θα ανοίξει ο chrome στην προκειμένη περίπτωση το google translate

driver.get('https://translate.google.gr/?hl=el')

#στις δύο αυτές παραμέτρους το xPath από το κουτί του source και target πεδίου του site που βρήκαμε πριν.

input_box = '//*[@id="source"]'

output_box = '/html/body/div[3]/div[2]/div[1]/div[2]/div[1]/div[1]/div[2]/div[3]/div[1]/div[2]/div/span[1]/span'

cursor = conn.cursor()

cursor.execute('select * from googleit')

results = cursor.fetchall()

update = """update googleit set gr_text=? where id=?"""

for i in range(len(results)):

                try:

                                driver.find_element_by_xpath(input_box).clear()

                                value_in = results[i][1]

                                driver.find_element_by_xpath(input_box).send_keys(value_in)

                                time.sleep(4);

                                translated_text = driver.find_element_by_xpath(output_box).text

                                cursor.execute(update,(translated_text,i+1))

                except StaleElementReferenceException:

                                pass      

conn.commit();

cursor.close();

driver.quit;

conn.close();

exit();

The result

Now calling the job after a few seconds depending on how many records we have and then with a select on our table it displayed the translation.

exec msdb.dbo.sp_start_job 'callPythonw'

select * from googleit
How can we do field translation in SQL Server using Python (no API)

We can also create a procedure that we will call so that it enters the text we want to translate and returns the results as soon as the job is finished.

exec translation.dbo.trans 'its ok for me'

The procedure code

/*
alter  procedure trans 
@text nvarchar(max) = null
as
set nocount on;
--truncate table translation.dbo.googleit;
insert into translation.dbo.googleit (en_text) values(@text);
exec msdb.dbo.sp_start_job 'callPythonw';
while ((select session_id from msdb.dbo.sysjobactivity with(nolock)  where job_id = (select job_id from msdb..sysjobs where name = 'callPythonw') and job_history_id is not null and start_execution_date >=  DATEADD(dd, -1,  GETDATE())
) is null)
begin
waitfor delay '00:00:02'
end
select top 1 en_text,gr_text from translation.dbo.googleit where en_text=@text 
go
*/
Share it

Leave a reply