用户提供的MSSQL数据库的关键/Django设置 [英] Pivotal/Django settings for user provided MSSQL database

问题描述

我在Pivotal Cloud Foundry上部署了django应用程序.在开发过程中，我只是将内置的sqlite数据库与UI结合在一起(不需要保留数据，因此推送/删除不是问题).从那以后，我在本地服务器(Azure ..但在本地)上开发了一个SQL Server后端.我的组织不允许使用公共IP服务，因此除Pivotal中的spring应用程序外，不允许任何其他操作.

I deployed a django application on Pivotal Cloud Foundry. While in development, I just stuck with the built in sqlite database while getting the UI together (didn't need to retain data so pushing/deleting wasn't an issue). I've since developed an SQL Server back end in an on-prem server (Azure..but on prem). My organization doesn't allow public IP services, so anything other than spring applications in Pivotal isn't allowed.

在Windows笔记本电脑上，与数据库(settings.py)对话没有问题:

On my Windows laptop, I have no issue speaking to the database (settings.py):

'''
DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'HOST': 'xxx.database.windows.net',
        'Port': '',
        'NAME': 'Django_Admin',
        'OPTIONS':{
            'driver': 'ODBC Driver 17 for SQL Server',
            'username': 'xxx',
            'PWD': '***', 
            'Authentication': 'ActiveDirectoryPassword',
        }
    }
}
'''

但是，当我部署到PCF时，我收到错误消息(''01000'，" [01000] [unixODBC] [驱动程序管理器]无法打开lib'SQL Server的ODBC驱动程序17':找不到文件(0)(SQLDriverConnect))"我尝试的任何驱动程序都出现错误... 17,13，w/e ...

When I deploy to PCF, however, I receive the error "('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")" And I get the error for any driver I try...17,13,w/e...

我使用数据库的连接字符串在PCF中创建了用户提供的服务.

I created a user provided service in PCF using my database's connection string.

如何在我的settings.py中调用该用户提供的服务?如果它是PCF提供的服务，我找到了如何称呼它，但是由于它是用户提供的服务，我又怎么称呼它?

推荐答案

ODBC只是用于连接数据库的标准框架.要执行任何操作，您需要一个ODBC驱动程序来与您的实际数据库对话.您正在尝试使用ODBC， sql_server.pyodbc ，但是在运行应用程序的应用程序容器中未安装SQL Server的ODBC驱动程序.我敢打赌这是专有驱动程序，因此默认情况下无法安装.

ODBC is just a standard framework for connecting to databases. To do anything with it, you need an ODBC driver to talk to your actual database. You are trying to use ODBC, sql_server.pyodbc, but the ODBC driver for SQL Server isn't installed in the application container where your app is running. I'm betting that is a proprietary driver so it just can't be installed by default.

我还没有专门使用Python完成此操作，但是我已经已安装SQL用于PHP的Server ODBC驱动程序，我认为它应该大致相同(请让我知道是否行不通).

I haven't done this specifically with Python, but I have installed SQL Server ODBC drivers for PHP, and I think it should be roughly the same process (do let me know if this doesn't work).

1. 使用Cloud Foundry中的多重构建包支持.首先使用apt-buildpack安装ODBC驱动程序.有关使用apt-buildpack的信息，请参见此处.然后使用Python buildpack实际设置您的应用.

1. Use the multi-buildpack support in Cloud Foundry. First use the apt-buildpack to install the ODBC drivers. See here for using apt-buildpack. Then use the Python buildpack to actually set up your app.

这是您想要的 apt.yml 文件.它告诉apt-buildpack要安装什么，并为其SQL Server ODBC驱动程序和mssql-tools安装Microsoft软件包，这是可选的，但有助于验证您的初始设置.

This is the apt.yml file that you'll want. It tells apt-buildpack what to install and it installs the Microsoft packages for their SQL Server ODBC driver and mssql-tools, optional but helpful for validating your initial set up.

---
keys:
- https://packages.microsoft.com/keys/microsoft.asc
repos:
- deb [arch=amd64] https://packages.microsoft.com/ubuntu/18.04/prod bionic main
packages:
- msodbcsql17
- mssql-tools
- unixodbc-dev

然后使用像这样的manifest.yml文件来推送您的应用程序.这将设置apt-buildpack使其首先运行并安装上面的依赖项.然后它将运行Python buildpack.它还将绑定您的MSSQL服务，并将必需的env变量 ACCEPT_EULA 设置为 Y ，以便apt-buildpack可以安装Microsoft软件包，而无需您手动进行接受他们的EULA.

Then use a manifest.yml file like this to push your app. This will set up the apt-buildpack to run first and install the dependencies above. Then it will run the Python buildpack. It will also bind your MSSQL services, and it sets the env variable ACCEPT_EULA to Y which is required so that the apt-buildpack can install the Microsoft packages without requiring you to manually accept their EULA.

---
applications:
- name: <app-name>
  buildpacks:
  - https://github.com/cloudfoundry/apt-buildpack
  - python_buildpack
  env:
    ACCEPT_EULA: Y
  services:
  - mssql-server-db

将具有此内容的 .profile 文件添加到项目的根目录，即在运行 cf push 的位置.这样会将mssqltools放在路径上，还告诉unixODBC在哪里可以找到您的ODBC配置.

Add a .profile file with this contents to the root of your project, i.e. from where you run cf push. This puts the mssqltools on the path and also tells unixODBC where to find your ODBC config.

# the 0 indicates that apt-buildpack is the first in the list
#  if you change the order of buildpacks you need to update this too
export PATH=$PATH:/home/vcap/deps/0/apt/opt/mssql-tools/bin/

# point to the odbcinst.ini file
#  that file needs to have the correct path to the ODBC driver shared library
#  it should be right, but if you change the order of the buildpacks then that
#  would need updated too
export ODBCSYSINI=$HOME/odbc-cfg/

最后，创建一个文件夹 odbc-cfg (或任何您想调用的文件夹，它只需要与在步骤#中为 ODBCSYSINI 设置的路径匹配即可.4)也在您的项目的根目录中.在其中放入文件 odbcinst.ini .在里面放这个信息.这将设置驱动程序.

Lastly, make a folder odbc-cfg (or whatever you want to call it, it just needs to match the path set for ODBCSYSINI in step #4) also in the root of your project. Inside that put the file, odbcinst.ini. Inside that put this info. That will set up the driver.

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.2.1
UsageCount=1

这样，继续并推送您的应用程序.它应该登台运行，并且您应该看到两个buildpack都在运行.Apt buildpack将安装ODBC驱动程序，而Python buildpack将设置您的应用程序.然后，您的应用应该启动并运行.

With that, go ahead and push your app. It should stage and you should see both buildpacks run. Apt buildpack will install the ODBC driver, and Python buildpack will set up your app. Then your app should start and run.

如果有任何问题，请运行命令 cf ssh< app>-t -c"/tmp/lifecycle/launcher/home/vcap/app bash"".这会将您放入容器中，并具有所有env变量源，就像您的应用程序一样.然后运行 sqlcmd -S< db-host>-U< user> 并确认您可以连接到数据库.

If you have any trouble, run the command cf ssh <app> -t -c "/tmp/lifecycle/launcher /home/vcap/app bash ''". This will put you in the container and have all the env variables source just like for your app. Then run sqlcmd -S <db-host> -U <user> and validate that you can connect to your DB.

最后几点注意事项:

• apt buildpack将需要互联网访问才能从Ubuntu&Microsoft存储库.在登台时需要此功能.如果暂存容器没有Internet访问权限，则需要配置代理.

• The apt buildpack is going to need internet access to download packages from Ubuntu & Microsoft repositories. This will be needed at staging time. If you don't have internet access from the staging container, you will need to configure a proxy.

您的容器将需要能够连接到数据库.这意味着IP必须是可路由的，并且没有防火墙阻止访问.您可能还需要让平台操作员添加应用程序安全组规则，以允许对服务器的出站访问.如果无法使用 sqlcmd 连接，则可能是网络问题，请尝试进行基本的网络故障排除，以确保可以连接到服务器.

Your container will need to be able to connect to the database. That means the IP has to be routable and no firewalls blocking access. You may also need to have your platform operator add an application security group rule to allow outbound access to your server. If you are unable to connect with sqlcmd, it's likely a network issue so try basic network troubleshooting to make sure you can connect to your server.

Microsoft ODBC驱动程序的版本将更改.当您阅读本文时，它可能会更新.当前版本反映了发布此更新时的版本.

The version of the Microsoft ODBC driver will change. It maybe newer when you read this. The current version reflects the version at the time this update was posted.

希望有帮助！

这篇关于用户提供的MSSQL数据库的关键/Django设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！