Microsoft Will Support Python In SQL Server 2017 (infoworld.com) 98
There was a surprise in the latest Community Technology Preview release of SQL Server 2017. An anonymous reader quotes InfoWorld:
Python can now be used within SQL Server to perform analytics, run machine learning models, or handle most any kind of data-powered work. This integration isn't limited to enterprise editions of SQL Server 2017, either -- it'll also be available in the free-to-use Express edition... Microsoft has also made it possible to embed Python code directly in SQL Server databases by including the code as a T-SQL stored procedure. This allows Python code to be deployed in production along with the data it'll be processing. These behaviors, and the RevoScalePy package, are essentially Python versions of features Microsoft built for SQL Server back when it integrated the R language into the database...
An existing Python installation isn't required. During the setup process, SQL Server 2017 can pull down and install its own edition of CPython 3.5, the stock Python interpreter available from the Python.org website. Users can install their own Python packages as well or use Cython to generate C code from Python modules for additional speed.
Except it's not yet available for Linux users, according to the article. "Microsoft has previously announced SQL Server would be available for Linux, but right now, only the Windows version of SQL Server 2017 supports Python."
An existing Python installation isn't required. During the setup process, SQL Server 2017 can pull down and install its own edition of CPython 3.5, the stock Python interpreter available from the Python.org website. Users can install their own Python packages as well or use Cython to generate C code from Python modules for additional speed.
Except it's not yet available for Linux users, according to the article. "Microsoft has previously announced SQL Server would be available for Linux, but right now, only the Windows version of SQL Server 2017 supports Python."
Always catching up to PostgreSQL! (Score:4, Informative)
PostgreSQL [postgresql.org] has had Python support [postgresql.org] for years. It also has R support [joeconway.com].
But to be fair to SQL Server, it is a very good RDBMS. Both SQL Server and PostgreSQL make MySQL look really, really, really bad. Even SQLite is making MySQL look pathetic these days!
Re: (Score:1)
How so? My company (a big one) is moving away from Oracle and SQLServer to Postgres.
What's the problem?
Re: (Score:2, Funny)
Is it webscale, or does it use joins?
Re: (Score:2)
Is it webscale, or does it use joins?
Yes to both.
Re: Always catching up to PostgreSQL! (Score:2)
I think it has sharding
Re: (Score:2)
Meh. (Score:1)
PostgreSQL can do (server-side) python. Also tcl, perl, lua, javascript (V8), shell, R. Perhaps I've forgotten some.
a little late to the party (Score:3, Informative)
You've been able to use Python for a while in Postgres [postgresql.org], MySQL [mysqltutorial.org], SQLite [python.org], and even DB2 [ibm.com].
I can't quite figure out why anybody would want to use Microsoft SQL Server.
Re: (Score:3, Informative)
Easy to use. Works well. Tons of features. Free to a point. After that, inexpensive.
Re: (Score:3, Insightful)
As opposed to Postgresql - which is easy to use, works well, has tons of features, and is free in every sense of the word.
Although I will concede that if you're already locked into being a MS shop and use .net for your applications then SqlServer might make sense.
Re: (Score:2)
Re: (Score:3)
...but the cost is negligible.
3 grand for 10 seats? That's negligible? PostgreSQL is $0 for unlimited seats.
Re:a little late to the party (Score:5, Interesting)
Re: (Score:2)
Re: (Score:3)
The time it would take to learn and migrate to a new system would quickly eat up any cost savings
Yes, that's what I said: if you're already locked in to MS and have already paid for the licenses you may as well stay locked in. I developed on SQLServer for several years and liked it. But if I was starting a new project today I'd definitely go with Postgresql.
Re: (Score:2)
What this "lock in" you're talking about? SQL Server can use ANSI standard SQL, and can export schema and data in simple flat text files.
Re: (Score:1, Interesting)
Being able to use SQL Server Management Studio makes up for any cost savings that PostgreSQL might deliver.
SSMS makes it trivial to manage and develop for SQL Server, and it makes the admins and devs extremely efficient.
There aren't any development tools for PostgreSQL that come close to being as capable and efficient as SSMS is.
pgAdmin [pgadmin.org] is really half arsed compared to SSMS, in my experience.
pgAdmin III was a shitty wxWidgets-based app. Even something as simple as copy and pasting was all buggered the last
Re: (Score:1, Insightful)
Could we use the command line? Of course. Would it be sensible for us to use such a limited interface? Absolutely not!
Maybe you work with small, 6-table MySQL DBs where your most complex query is "select * from wp_posts where id = 5;".
The rest of us are working in the real world.
We're working with multi-terabyte or even multi-petabyte DBs with tens of thousands of tables. We're writing complex queries that are thousands of lines long.
We want proper syntax highlighting and autocompletion when editing SQL que
Re: (Score:1)
Could we use the command line? Of course. Would it be sensible for us to use such a limited interface? Absolutely not!
Maybe you work with small, 6-table MySQL DBs where your most complex query is "select * from wp_posts where id = 5;".
Yeah, great deduction Sherlock. If someone is advocating the command line, you immediately think they're using WordPress since those technologies go hand in hand with one another. About par for the course for someone who relies on SSMS.
We want proper syntax highlighting and autocompletion when editing SQL queries because those things make us a lot more efficient.
We want to be able to incrementally work on our complex queries without having to retype them into a command line client, and without having to drop them into a file and run it through the command line client again and again, and without having to open several different terminal windows/tabs just to do this.
Autocomplete, hmm. Do you even know what readline is? You know you can edit your queries in vim, too, right? Why would you need to open several terminal windows? You're lost! You give the impression that if you were in a bash shell, you would be completely out of your depth.
We want to be able to view the schemata of five or six tables at once while we're writing our queries.
What you're proposing is that we only use manual hammers to build a house, while what we're actually trying to do is build an entire district of skyscrapers using modern tools.
Maybe efficiency doesn't matter to a hobbyist like you. But efficiency matters a great deal to those of us professionals who don't have time to waste with shitty tooling. We need the best, even if that means paying a little bit of money to get it. Our time isn't free, so we don't use shitty tools just to save a couple of dollars, especially when our lost time costs way more than that.
SSMS gives us the tooling we need. PostgreSQL or MySQL's shitty command line clients don't.
Y
Re: a little late to the party (Score:2)
I've used both and I'm far more productive in SSMS than I ever would be in vim because unlike vim SSMS is designed for SQL management and makes life way easier. The whole point of computers is to make things easier and trying to manage multiple databases and queries using bash and vim would be painful beyond belief. You can work in such a wildly inefficient manner if your pathetically fragile ego requires that to make you feel superior but the sensible people will use tools (including the command line) that
Re: (Score:3)
Our solution to this problem was to get a Jetbrains license for their suite. We already were buying Intellij (Java/web) and Resharper for .NET projects and this gave us data grip. While data grip is a relatively new product, it does all the basics with a much better UI, syntax highlighting and supports all of the databases we use including PostgreSQL, SQL Server, MySQL and Oracle. We now have one tool for all the databases we use instead of multiple. In our case, we have one enterprise app that uses SQL S
Re: (Score:2)
Easy to use. Works well. Tons of features. Free to a point. After that, inexpensive.
After that, not so inexpensive... $14k/core [microsoft.com] for the Enterprise edition at "no level" list price is pretty harsh. You could build a pretty sweet database server for the licensing money. That said, for an organization that doesn't have any OSS culture and thus doesn't understand anything that doesn't answer an RFQ it's okay. If we somehow managed to get approval for a PostgreSQL server with no vendor backing us up I fear that some sales droid would convince some higher-ups somewhere to go Oracle, DB2, SAP/SAS
Re: a little late to the party (Score:1)
You don't have to use the most expensive version and I doubt that enterprise support for PostgreSQL is free either.
Re: Because... (Score:2)
No, not wanting to waste vast sums of money for no business benefit syndrome.
Re: (Score:2, Insightful)
Re: (Score:2, Troll)
I didn't bash Microsoft SQL Server. But good relational databases are a dime a dozen; I can't even imagine any features Microsoft SQL Server could have to make me want to go through the trouble of switching and take the risk inherent in using a Microsoft-proprietary product.
Re: (Score:2)
Postgres, since a long time, like SQL Server now, allows *embedding* Python in the database run stored procedures.
The MySQL and DB2 links you provided are not the same. They are simply about calling stored procedures from Python clients. Pretty much every relational database and programing language can do the latter.
Embedding within the database, on the hand, is a more exotic and a very useful feature.
Re: (Score:2)
Embedding within the database, on the hand, is a more exotic and a very useful feature.
For licensing and vendor lock-in.
Re: (Score:2)
> For licensing and vendor lock-in.
How exactly? That makes no sense.
They embedded an open source language. Previously, the extensions were via CLR.
If you mean it is non-standard SQL, nearly every major database today supports non-standard extensions - both proprietary and open source.
Re: (Score:2)
How exactly? That makes no sense.
The interface is not standardized. Same code running in a real application tier has no such dependencies on database specific language bindings to operate.
Re: (Score:2)
There is a good reason to have that code at the database tier and not in the "real application tier" aka client client tier. And this reason is partly the same reason as why we do stored procedures. We want the data to be resolved and reduced before it leaves the database tier, not sorted out in the client tier. This also allows for centralizing business logic rather than have it spread out among various client implementations. And the non-standardness is no different than doing non-standard stuff that we a
Re: (Score:2)
Corporate policy. It does make sense if large parts of your IT infrastructure already use MS SQL server, especially applications from external vendors. Adding another database increases costs and complexity for maintenance and support.
And it's inexpensive enough for the little money saved by using the free PostgreSQL not to make any noticeable difference. SQLite is very nice for certain applications but does not scale well (by des
Re: (Score:2)
Re: (Score:2)
Probably because it has deep integration with windows networks and security that most businesses run on, coupled with the fact it's a proven reliable, fast, and highly scalable RDBMS. MySQL for example just isn't reliable, last time I ran it it would corrupt the data store on disk and you had to run a fix tool provided with MySQL to get the server to even start and load your database again.
Beyond that though it has great surrounding services for ETL, analysis, and reporting, coupled with clean and easy inte
Re: (Score:2)
I just asked a question.
That's lock-in, not a technical advantage, as are most of the other things you list. Those are compelling reasons if you have already invested heavily in Microsoft. But they are not a good reason to, say, pick up Microsoft SQL Server and install it on Linux.
Re: (Score:2)
"That's lock-in, not a technical advantage, as are most of the other things you list."
Call it what you want, there's real practical benefit in being able to have centralised security configuration. Knowing that when you lock out a user account on the domain, that they also can no longer log into every database server and so on has massive practical benefit.
"Well, and there are several enterprise-grade relational databases that don't come from Microsoft and don't come with Microsoft's strings attached: Oracl
Re: (Score:2)
Geez, man, get a hold of yourself. I didn't "pretend" anything, nor do I "hate Microsoft". For example, I think the Linux community should have adopted C# and Mono widely and have argued extensively for that here.
Really? Where is the source? Where is the open source developer community? As far as I can tell, there are jus [microsoft.com]
Re: (Score:2)
You're making it pretty clear by the fact you can't even answer these questions for yourself that you have no idea what the fuck you're talking about.
Even if I do do your research for you wants the point where you're clearly a zealot? The fundamental fact you're making assertions about a peace of software you're demonstrably highlighting you have no idea alone means that any discussion with you is a losing proposition.
If you genuinely had an open mind you wouldn't be calling something you have never used, a
Re: (Score:2)
Absolutely correct. Hence my statement I can't quite figure out why anybody would want to use Microsoft SQL Server.
I did, before posting even. That's how I included the link I included. I can't find any open source repository or release; it's not in the Ubuntu
Re: (Score:2)
"So you actually know nothing about MS SQL Server yourself, you just like it because... what?"
This is precisely the point I'm making - you say you want to learn, but you're not listening. I pointed out that I've worked with many other RDBMS in the past. Oracle is unnecessarily convoluted and proprietary just for the sake of trying to sell specialist training, though it is powerful and performant. MySQL is a joke - the very fact it even has (or had) to be bundled with a tool to fix broken datafiles is in its
Re: (Score:2)
I didn't make "sweeping comments", I asked a question.
I am listening, but you haven't told me anything I didn't already know: MS SQL Server seems to be a reasonably good and stable product on Windows. It doesn't seem to be open source (despite your claims) and only seems to have Linux pre-releases. Saying that it can "easily hold it's own a
Re: (Score:2)
But there we are again - changing the terms of the discussion, your problem with it now is merely that you're complaining it isn't open source (hint: it's in preview still), and that it has incomplete Linux support.
Yet here is your original post where you apparently didn't make a sweeping comment and where you claim you merely asked a question:
"You've been able to use Python for a while in Postgres [postgresql.org], MySQL [mysqltutorial.org], SQLite [python.org], and even DB2 [ibm.com].
I can't quite figure
Re: (Score:2)
Absolutely I want to retract it! I now understand that there is still a vocal population if Microsoft zealots out there to whom "integrates with Windows domains" and "has a Windows GUI" are actually desirable features that trump "has predictable long-term pricing and availability" and "doesn't lock you into one vendor".
Re: (Score:2)
Well, thank you for admitting you were wrong at least, I guess.
Shame you've still decided to bask in wilful ignorance though by refusing to listen to a word anyone else with actual experience of multiple products is telling you, and still amazed you think lock-in is even possible in a product that supports standard SQL and doesn't force you into any extensions (which just about all SQL RDBMS have btw) but hey, you don't know much so I shouldn't be too surprised that you're again mouthing off about something
It's a trap! (Score:2)
It's a trap!
Microsoft got one thing right... (Score:3)
Re: (Score:1)
one of the main reasons of this is Postgresql's lack of parallelism at the query level
Nonsense [enterprisedb.com]. Shilling is bad enough, but at least get your facts straight.
Re: (Score:2)
Even if this was true, which I doubt as I've seen SQL Server choke on bad locking from poorly written apps, the cost savings from not buying sql server licenses means you get bigger servers or instance types (if aws) versus going with Microsoft. You can throw hardware at the problem.
Just because you can doesn't mean you should (Score:2)
If python integration is anything like .NET or java language bindings:
1. You won't see any performance benefit vs. shared memory
2. You will get hit with all of the downsides of ignoring separation dogma for temporary expediency.
Re: (Score:3)
You will get hit with all of the downsides of ignoring separation dogma
Write your business logic in the language which is most appropriate and run it where it's most convenient. The "separation dogma" was a fad that has passed.
Re: (Score:2)
Write your business logic in the language which is most appropriate and run it where it's most convenient. The "separation dogma" was a fad that has passed.
RO0OFL nice. Almost thought you were being serious.
Re: (Score:2)
Re: (Score:2)
You sound like one of those Java/Hibernate guys: "Why would I write a 20 line function in the database when I can write a few hundred lines of boilerplate, configuration, and interface code that works almost as well?" Do you still use Struts?
I don't drink Java/struts/hibernate. I have written more extended procedures than I care to admit over the years and today find myself completely unable to retroactively justify any of those decisions.
I understand the appeal of these things I know why people want to use them. I also know from experience in non-trivial environments these tendencies have cost prohibitive consequences.
On the low end you can in get away with virtually every bad practice you want.. it doesn't matter. Sloppiness does not scale
Re: (Score:2)
So let's get this straight. You have databases that optimize SQL queries because of the narrow scope of that language, and somehow you think throwing in random code blocks in some other language where optimization for the data set is just about impossible and you don't think you're going to get serious performance hits?
There are certainly times when stored procedures may be necessary, but I've made it my policy to avoid them wherever possible. Using convenience as an argument for overriding a pretty sound p
Re: (Score:2)
Re: (Score:3)
somehow you think throwing in random code blocks in some other language where optimization for the data set is just about impossible and you don't think you're going to get serious performance hits?
You're missing the point. Data access uses sql no matter what language your business logic is written in and no matter where the application code runs.
The advantage of having that logic reside inside the DB server versus in a container or separate app server is that you save all the network traffic moving data back and forth; the app code is the same in either place and is just as modular either way. All that changes is where it's deployed.
Re: (Score:2)
The advantage of having that logic reside inside the DB server versus in a container or separate app server is that you save all the network traffic moving data back and forth
You accomplish this by running your code on the same computer in a separate process using standardized data access APIs.
the app code is the same in either place and is just as modular either way. All that changes is where it's deployed.
I have a bad feeling about this. Someone points out the obvious when application becomes the database you lose the ability for application to leverage multiple databases or connect to different databases or have the ability to isolate application from the database due to application resource constraints.. and your response.. no no it's still the same application it can just as easily conn
Re: (Score:2)
you lose the ability for application to leverage multiple databases or connect to different databases
Not at all. Foreign tables or db linking is every bit as efficient as your alternatives.
or have the ability to isolate application from the database due to application resource constraints
Possibly, there is no one size fits all. But these days the availability of cores and memory weakens that argument too.
Re: (Score:1)
Meh. (Score:2)
Re: (Score:2)
You think a db can serve just one app, apparently. My current db serves hundreds, no way they can all be installed there. Another serves the corporate dwh. Installing apps on that one will make me do horrible things to you. Don't make me, bro!
Where I come from, having many applications accessing the same database directly is a disaster for maintainability. Not that it's good for everything, but 3-tier is about more than just security.
The orgs I work for have rather higher security requirements than garden variety stuff so I doubt I'd ever be able to put the service layer on the same box as a database, but for smaller, purely internal applications I totally would.
Anyway my point is, aside from some very specific things (like doing nested set
pymssql (Score:2)
Meh. Pymssql works pretty well. Am I missing something?
Yeah right (Score:1)