In my recent post, Installing External Modules into SQL Server’s Python I had a look at just how simple it is to import external modules into Python so that they can be used within SQL Server.

In this post I’d like to show you a little something to demonstrate how we can integrate one of these modules into SQL Server and just how powerful this can be.

This is really just for fun and it may not really be something that you’d want to put out into production but when I happened to notice that there’s a Python module that interfaces with Google Translate, I wondered to myself if it’d be possible to write a procedure that could take a string and automatically translate it into our native language.

Import Googletrans

The first thing that you’re going to want to do is import the Googletrans module.  Check out Installing External Modules into SQL Server’s Python for full instructions on how to do this,

<code>pip googletrans</code>

Disable Outbound Firewall Rule

By default, SQL creates a rule in the Windows firewall to block access to the internet for your newly installed module.  For this to work, you’ll need to disable the ‘Block network access for R local user accounts in SQL Server’ rule.

Now onto the Fun Stuff

We’re going to execute some Python code inside the context of a SQL stored proc, let’s take a little look at the python code first.

import platform
from googletrans import Translator
translator = Translator()
outText = translator.translate(inOriginalText, dest = inDestLanguage, src = inOrigLanguage).text

As you can see, the code’s dead straight forward.  We’re going to call the translate method and pass in a few parameters, the original text that we want to translate, the destination language and the original language.

So let’s take a look at the proc itself…

CREATE PROC [dbo].[sp_translate] (@SrcText VARCHAR(1000), @OrigLanguage VARCHAR(20) = 'auto', @DestLanguage VARCHAR(20) = NULL)
AS
BEGIN
DECLARE @DestText VARCHAR(1000)
--use user's default language if @DestLanguage is NULL
IF @DestLanguage IS NULL
SELECT @DestLanguage = alias from sys.syslanguages WHERE langid = @@LANGID
--convert invalid @DestLanguage strings to something googletrans can understand
SET @DestLanguage = CASE @DestLanguage
WHEN 'British English' THEN 'English'
WHEN 'Traditional Chinese' THEN 'Chinese (Traditional)'
WHEN 'Simplified Chinese' THEN 'Chinese (Simplified)'
ELSE @DestLanguage
END
EXEC sp_execute_external_script
@language = N'python',
@script =
N'import platform
from googletrans import Translator
translator = Translator()
outText = translator.translate(inOriginalText, dest = inDestLanguage, src = inOrigLanguage).text
',
@params = N'@inOriginalText VARCHAR(1000),
@inDestLanguage VARCHAR(20),
@inOrigLanguage VARCHAR(20),
@outText VARCHAR(1000) OUTPUT',
@inOriginalText = @SrcText,
@inDestLanguage = @DestLanguage,
@inOrigLanguage = @OrigLanguage,
@outText = @DestText OUTPUT
WITH RESULT SETS UNDEFINED
SELECT @DestText
END

Let’s Break it Down and See What It’s Doing

The proc is going to take in three parameters,

• @SrcText – Source text, this is our original text.
• @OrigLanguage – The original language of the text, we default to auto here.
• @DestLanguage – The destination language, this is the language that we want to translate to.

CREATE PROC [dbo].[sp_translate] (@SrcText VARCHAR(1000), @OrigLanguage VARCHAR(20) = 'auto', @DestLanguage VARCHAR(20) = NULL)

When writing this proc, I decided that I wanted the destination language to be the users’s default language.  Now Googletrans takes in a destination language parameter and this parameter is pretty intuitive when it comes to the language that you give it.  It’ll happily take ‘English’,’French’,’Spanish’ etc…  So, assuming that @DestLanguage is left as it’s default NULL, we’ll lookup the default language from sys.languages.

IF @DestLanguage IS NULL
SELECT @DestLanguage = alias from sys.syslanguages WHERE langid = @@LANGID

Now, while I said that Googletrans was pretty intuitive when it comes to languages, there are a couple that will catch you out.  It doesn’t understand British English and both Traditional and Simplified Chinese need to be specified correctly, so let’s sort those out.

 IF @DestLanguage IS NULL
SET @DestLanguage = CASE @DestLanguage
WHEN 'British English' THEN 'English'
WHEN 'Traditional Chinese' THEN 'Chinese (Traditional)'
WHEN 'Simplified Chinese' THEN 'Chinese (Simplified)'
ELSE @DestLanguage
END

With all that done, we’re ready to run our Python code.  We’re going to pass in a bunch of input (@SrcText, @DestLanguage and @OrigLanguage) and an output parameter (@DestText).

EXEC sp_execute_external_script
@language = N'python',
@script =
N'import platform
from googletrans import Translator
translator = Translator()
outText = translator.translate(inOriginalText, dest = inDestLanguage, src = inOrigLanguage).text
',
@params = N'@inOriginalText VARCHAR(1000),
@inDestLanguage VARCHAR(20),
@inOrigLanguage VARCHAR(20),
@outText VARCHAR(1000) OUTPUT',
@inOriginalText = @SrcText,
@inDestLanguage = @DestLanguage,
@inOrigLanguage = @OrigLanguage,
@outText = @DestText OUTPUT
WITH RESULT SETS UNDEFINED

As the result was passed to the output parameter, @DestText we can now just select the value of that variable.

SELECT @DestText

Let’s See it in Action

We’re going to translate “Hello World” into my user’s default language, which in this case happens to be Norwegian.

Conclusion

I really just wanted to give you a little introduction into the sort of rich functionality that Python could bring into SQL Server.  With the many modules that are available, the possibilities are almost endless.  Imagine a proc that could give you real time exchange rates and perhaps have a look at what’s happening with the stock market.