Using IronPython to talk to SQL Server

Question

Post reply

Using IronPython to talk to SQL Server

g.britton

SSChampion

Points: 13863
More actions
June 11, 2016 at 7:26 pm

#311179

Comments posted to this topic are about the item Using IronPython to talk to SQL Server
Gerald Britton, Pluralsight courses

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 1

Question is, why should I use this over the other options with Python and SQL Server?

g.britton SSChampion Points: 13863 More actions · Answer 2

xsevensinzx (6/13/2016)
Question is, why should I use this over the other options with Python and SQL Server?

if you want to use SMO objects, you need .NET support. CPython doesn't provide it out of the box. IronPython does. That's really it.

Gerald Britton, Pluralsight courses

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 3

You may have finally wet my appetite for a language other than T-SQL. I can see me liking this a whole lot more than PowerShell. Thanks, Gerald.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

g.britton SSChampion Points: 13863 More actions · Answer 4

Jeff Moden (6/13/2016)
You may have finally wet my appetite for a language other than T-SQL. I can see me liking this a whole lot more than PowerShell. Thanks, Gerald.

You're welcome!

Gerald Britton, Pluralsight courses

the sqlist SSCrazy Points: 2398 More actions · Answer 5

I have been using Python 3.* for few years now and I think is the best auxiliary tool for a DBA. You can do anything you need and not only with SOL Server but with any platform. I use it mainly in my ETL processes. I prefer to use Python over SSIS because it gives you a more simpler and granular approach, which you build and control from start to finish.

In regards to IronPython, it is too bad that they haven't went to use Python 3 yet any only for SMO I would not switch to it even though it would be very nice to have access to. Maybe somebody will build some SMO module in Python 3 and you wouldn't need IronPython anymore....

Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist

g.britton SSChampion Points: 13863 More actions · Answer 6

the sqlist (6/13/2016)
I have been using Python 3.* for few years now and I think is the best auxiliary tool for a DBA. You can do anything you need and not only with SOL Server but with any platform. I use it mainly in my ETL processes. I prefer to use Python over SSIS because it gives you a more simpler and granular approach, which you build and control from start to finish.

Now, that's interesting! I'd be really curious to see how you do that. FWIW our approach to using SSIS is quite granular (usually one table per package and some master packages to dispatch everything). SSIS also has some pretty sophisticated techniques for managing threads and buffers. All do-able in Python of course, though the development effort (and subsequent support) might be daunting.

In regards to IronPython, it is too bad that they haven't went to use Python 3 yet any only for SMO I would not switch to it even though it would be very nice to have access to. Maybe somebody will build some SMO module in Python 3 and you wouldn't need IronPython anymore....

Maybe you?

Gerald Britton, Pluralsight courses

the sqlist SSCrazy Points: 2398 More actions · Answer 7

Now, that's interesting! I'd be really curious to see how you do that.

Simple, I am connecting to the source sever, whatever platform it may be, querying it, or downloading if is an FTP or a website, and then loading/inserting data to the target server, usually SQL, and than do whatever I need with it. Sometimes I do transformations inside Python if they can't be done in SQL, i.e. SRID projection conversion for spatial data. I also handle the errors and the logins the way I want. I simply prefer to write simple code instead of dragging and dropping and then "painting" arrows... I built some simple modules that I reuse when I connect, executing queries, manipulating files, which have logging incorporated and emails sending if somethings goes wrong. SSIS is a fine platform but it is kind of complicated especially when it comes to debugging, deploying.

In regards to IronPython, it is too bad that they haven't went to use Python 3 yet any only for SMO I would not switch to it even though it would be very nice to have access to. Maybe somebody will build some SMO module in Python 3 and you wouldn't need IronPython anymore....
Maybe you?

I am not an advanced Python developer at that level just a DBA. No need for irony here.

Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist

g.britton SSChampion Points: 13863 More actions · Answer 8

the sqlist (6/13/2016)
Now, that's interesting! I'd be really curious to see how you do that.
Simple, I am connecting to the source sever, whatever platform it may be, querying it, or downloading if is an FTP or a website, and then loading/inserting data to the target server, usually SQL, and than do whatever I need with it. Sometimes I do transformations inside Python if they can't be done in SQL, i.e. SRID projection conversion for spatial data. I also handle the errors and the logins the way I want. I simply prefer to write simple code instead of dragging and dropping and then "painting" arrows...

You might want to take a look at BIML, It can do all you describe with no drag and drop, give you the full power of C# or vb.net and directly generate correct XML for the SSIS engine (dtexec) to process efficiently.

I built some simple modules that I reuse when I connect, executing queries, manipulating files, which have logging incorporated and emails sending if somethings goes wrong. SSIS is a fine platform but it is kind of complicated especially when it comes to debugging, deploying.

Hmmm...I've not found that. Still, different strokes for different folks.

In regards to IronPython, it is too bad that they haven't went to use Python 3 yet any only for SMO I would not switch to it even though it would be very nice to have access to. Maybe somebody will build some SMO module in Python 3 and you wouldn't need IronPython anymore....

Maybe you?
I am not an advanced Python developer at that level just a DBA. No need for irony here.

LOL! (though actually that's not irony. My question was sincere. Lots of folks contribute to Python.)

Gerald Britton, Pluralsight courses

Bosko Vukov Old Hand Points: 339 More actions · Answer 9

There is very little reason to use IronPython when CPython offers so many great (well tested) libraries for everything you could possibly need (and code snippets are available on the net and they mostly work without any modification). AFAIK SMO can still be used as COM object, and COM is accessible from CPython using win32com.client library (in windows).

g.britton SSChampion Points: 13863 More actions · Answer 10

Bosko Vukov (6/13/2016)
There is very little reason to use IronPython when CPython offers so many great (well tested) libraries for everything you could possibly need (and code snippets are available on the net and they mostly work without any modification). AFAIK SMO can still be used as COM object, and COM is accessible from CPython using win32com.client library (in windows).

I don't think you can import the namespace from COM objects. However, I'd be happy to be wrong.

note this:

How do I know which methods and properties are available?
Good question. This is hard! You need to use the documentation with the products, or possibly a COM browser. Note however that COM browsers typically rely on these objects registering themselves in certain ways, and many objects to not do this. You are just expected to know.

from win32com

That is a very compelling reason to use IronPython, I believe. Also, note that IronPython can use regular Python libraries as well as .NET libraries.

Gerald Britton, Pluralsight courses

Bosko Vukov Old Hand Points: 339 More actions · Answer 11

You could be right. At some point (after upgrading to SQL 2005) we switched from VBScript + COM to

C# code (for scripting all our objects into search engine).