def package_files_in_scope(scope=Scope.private_scope()):
envdir = _ENV_NAME_SHARED_PATH if scope == Scope.public_scope() or os.environ.get(_ENV_NAME_USER_PATH, "") == "" \
else _ENV_NAME_USER_PATH
path = os.environ.get(envdir, "")
if os.path.isdir(path):
return os.listdir(path)
return []
def _get_packages_by_user(self,
owner='',
scope: Scope = Scope.private_scope()):
scope_num = 1 if scope == Scope.private_scope() else 0
if scope_num == 0 and owner == '':
owner = "dbo"
query = "DECLARE @principalId INT; \
DECLARE @currentUser NVARCHAR(128); \
SELECT @currentUser = "******"?;\n"
else:
query += "CURRENT_USER;\n"
query += "SELECT @principalId = USER_ID(@currentUser); \
SELECT name, language, scope \
FROM sys.external_libraries AS elib \
WHERE elib.principal_id=@principalId \
AND elib.language='{language_name}' AND elib.scope={scope_num} \
ORDER BY elib.name ASC; \
GO".format(language_name=self._language_name,
scope_num=scope_num)
return self._pyexecutor.execute_sql_query(query, owner)
def package_exists_in_scope(sql_package_name: str, scope=None) -> bool:
if scope is None:
# default to user path for every user but DBOs
scope = Scope.public_scope() if (os.environ.get(
_ENV_NAME_USER_PATH, "") == "") else Scope.private_scope()
package_files = package_files_in_scope(scope)
return any([
_is_package_match(sql_package_name, package_file)
for package_file in package_files
])
def _drop_all_ddl_packages(conn):
pkgs = _get_sql_package_table(conn)
for pkg in pkgs:
try:
SQLPackageManager(conn)._drop_sql_package(pkg['name'], scope=Scope.private_scope())
except Exception:
pass
def _install_from_pypi(self,
target_package: str,
upgrade: bool = False,
version: str = None,
install_dependencies: bool = True,
scope: Scope = Scope.private_scope(),
out_file: str = None):
if not install_dependencies:
raise ValueError(
"Dependencies will always be installed - "
"single package install without dependencies not yet supported."
)
if version is not None:
target_package = target_package + "==" + version
with tempfile.TemporaryDirectory() as temporary_directory:
pipdownloader = PipDownloader(self._connection_info,
temporary_directory,
target_package,
language_name=self._language_name)
target_package_file = pipdownloader.download_single()
self._install_from_file(target_package_file,
scope,
upgrade,
out_file=out_file)
def test_scope():
_remove_all_new_packages(pkgmanager)
package = os.path.join(path_to_packages, "testpackageA-0.0.1.zip")
def get_location():
import testpackageA
return testpackageA.__file__
_revotesterconnection = sqlmlutils.ConnectionInfo(server="localhost",
database="AirlineTestDB",
uid="Tester",
pwd="FakeT3sterPwd!")
revopkgmanager = SQLPackageManager(_revotesterconnection)
revoexecutor = SQLPythonExecutor(_revotesterconnection)
revopkgmanager.install(package, scope=Scope.private_scope())
private_location = revoexecutor.execute_function_in_sql(get_location)
pkg_name = "testpackageA"
pyexecutor.execute_function_in_sql(check_package,
package_name=pkg_name,
exists=False)
revopkgmanager.uninstall(pkg_name, scope=Scope.private_scope())
revopkgmanager.install(package, scope=Scope.public_scope())
public_location = revoexecutor.execute_function_in_sql(get_location)
assert private_location != public_location
pyexecutor.execute_function_in_sql(check_package,
package_name=pkg_name,
exists=True,
class_to_check='ClassA')
revopkgmanager.uninstall(pkg_name, scope=Scope.public_scope())
revoexecutor.execute_function_in_sql(check_package,
package_name=pkg_name,
exists=False)
pyexecutor.execute_function_in_sql(check_package,
package_name=pkg_name,
exists=False)
def get_packages_by_user(self, owner='', scope: Scope =Scope.private_scope()):
has_user = (owner != '')
query = "DECLARE @principalId INT; \
DECLARE @currentUser NVARCHAR(128); \
SELECT @currentUser = "******"%s;\n"
else:
query += "CURRENT_USER;\n"
query += "SELECT @principalId = USER_ID(@currentUser); \
SELECT name, language, scope \
FROM sys.external_libraries AS elib \
WHERE elib.principal_id=@principalId \
AND elib.language='Python' AND elib.scope={0} \
ORDER BY elib.name ASC;".format(1 if scope == Scope.private_scope() else 0)
return self._pyexecutor.execute_sql_query(query, owner)
def uninstall(self, package_name: str, scope: Scope = Scope.private_scope()):
"""Remove Python package from a SQL Server Python environment.
:param package_name: Package name to remove on the SQL Server.
:param scope: Specifies whether to uninstall packages from private or public scope. Default is private scope.
This uninstalls packages from a private path for the SQL principal you connect as. If your principal has the
db_owner role, you can also specify scope as public. This will uninstall packages from a public path for all
users. Note: if you connect as dbo, you can only uninstall packages from the public path.
"""
print("Uninstalling " + package_name + " only, not dependencies")
self._drop_sql_package(package_name, scope)
def _remove_all_new_packages(manager):
libs = {
dic['external_library_id']: (dic['name'], dic['scope'])
for dic in _get_sql_package_table(connection)
}
original_libs = {
dic['external_library_id']: (dic['name'], dic['scope'])
for dic in originals
}
for lib in libs:
pkg, sc = libs[lib]
if lib not in original_libs:
print("uninstalling" + str(lib))
if sc:
manager.uninstall(pkg, scope=Scope.private_scope())
else:
manager.uninstall(pkg, scope=Scope.public_scope())
else:
if sc != original_libs[lib][1]:
if sc:
manager.uninstall(pkg, scope=Scope.private_scope())
else:
manager.uninstall(pkg, scope=Scope.public_scope())
def install(self,
package: str,
upgrade: bool = False,
version: str = None,
install_dependencies: bool = True,
scope: Scope = Scope.private_scope()):
"""Install Python package into a SQL Server Python Services environment using pip.
:param package: Package name to install on the SQL Server. Can also be a filename.
:param upgrade: If True, will update the package if it exists on the specified SQL Server.
If False, will not try to update an existing package.
:param version: Not yet supported. Package version to install. If not specified,
current stable version for server environment as determined by PyPi/Anaconda repos.
:param install_dependencies: If True, installs required dependencies of package (similar to how default
pip install or conda install works). False not yet supported.
:param scope: Specifies whether to install packages into private or public scope. Default is private scope.
This installs packages into a private path for the SQL principal you connect as. If your principal has the
db_owner role, you can also specify scope as public. This will install packages into a public path for all
users. Note: if you connect as dbo, you can only install packages into the public path.
>>> from sqlmlutils import ConnectionInfo, SQLPythonExecutor, SQLPackageManager
>>> connection = ConnectionInfo(server="localhost", database="AirlineTestsDB")
>>> pyexecutor = SQLPythonExecutor(connection)
>>> pkgmanager = SQLPackageManager(connection)
>>>
>>> def use_tensorflow():
>>> import tensorflow as tf
>>> node1 = tf.constant(3.0, tf.float32)
>>> return str(node1.dtype)
>>>
>>> pkgmanager.install("tensorflow")
>>> ret = pyexecutor.execute_function_in_sql(connection=connection, use_tensorflow)
>>> pkgmanager.uninstall("tensorflow")
"""
if not install_dependencies:
raise ValueError(
"Dependencies will always be installed - "
"single package install without dependencies not yet supported."
)
if os.path.isfile(package):
self._install_from_file(package, scope, upgrade)
else:
self._install_from_pypi(package, upgrade, version,
install_dependencies, scope)
def _get_authorization(scope: Scope) -> str:
return "AUTHORIZATION dbo" if scope == Scope.public_scope() else ""
# pip install sqlmlutils
import sqlmlutils
from sqlmlutils.packagemanagement.scope import Scope
# Connect to the Database
# NOTE: need to do this for *each* database!
# Uncomment this version if you want to connect to a Docker container.
#conn = sqlmlutils.ConnectionInfo(server="localhost,52433", database="ExpenseReports", uid="sa", pwd="SomeBadP@ssword3")
# Uncomment this version if you want to connect to a local installation of SQL Server 2019.
#conn = sqlmlutils.ConnectionInfo(server="localhost", database="ExpenseReports")
sqlpy = sqlmlutils.SQLPythonExecutor(conn)
pkgmanager = sqlmlutils.SQLPackageManager(conn)
# Install a package as dbo, allowing anybody to use it.
# Upgrade if there is a newer version than what is already installed.
pkgmanager.install("werkzeug", upgrade=True, scope=Scope.public_scope())
# By default, install just for your account if you are not a sysadmin.
# You can also specify a private scope.
pkgmanager.install("termcolor")
# We can also install and uninstall our own custom packages.
#pkgmanager.uninstall("my_custom_package", scope = Scope.public_scope())
#pkgmanager.install(package = "C:\\Temp\\MyCustomPackage\\dist\\my_custom_package-0.0.1-py3-none-any.whl", scope = Scope.public_scope())
def _get_default_scope(self):
query = "SELECT IS_SRVROLEMEMBER ('sysadmin') as is_sysadmin"
is_sysadmin = self._pyexecutor.execute_sql_query(query)["is_sysadmin"].iloc[0]
return Scope.public_scope() if is_sysadmin == 1 else Scope.private_scope()
