在AWS Lambda中无法使用pydobc在Python 3.6中使用pyodbc连接到SQL Server [英] Unable to use pydobc to connect to SQL Server using pyodbc in Python 3.6 in AWS Lambda

问题描述

我一直在努力，无法弄清楚如何通过pydobc和AWS Lambda使用python 3.6连接到SQL Server.

I have been struggling, and am unable to figure out how to connect to SQL Server using python 3.6 via pydobc and AWS Lambda.

我按照AWS提供的说明创建了一个Amazon Linux AMI，可以在其上安装Microsoft ODBC驱动程序(v13和v17)，将unixODBC升级到受支持的版本，并获取我的python代码以连接到AWS RDS SQL Server实例.

I followed the instructions provided by AWS to create an Amazon Linux AMI on which I was able to install the Microsoft ODBC drivers (v13 and v17), upgrade the unixODBC to a supported version, and get my python code to connect to the AWS RDS SQL Server instance.

但是，我无法弄清楚如何成功打包这些更改，以将该代码部署到AWS Lambda并使其正常工作.

However, I have not been able to figure out how to package those changes successfully to deploy this code to AWS Lambda and have it work.

我得到两个错误之一，具体取决于我如何尝试引用ODBC驱动程序.使用这种语法

I get one of two errors, depending on how I try to reference the ODBC driver. Using this syntax

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=servername.account.region.rds.amazonaws.com,port;DATABASE=database;UID=user;PWD=password'

我得到了错误:

"errorMessage": "('01000', \"[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found (0) (SQLDriverConnect)\")",

我尝试使用其他驱动程序别名(用于SQL Server的ODBC驱动程序17，用于SQL Server的ODBC驱动程序13)，具有相同的结果.

I have tried using other driver aliases (ODBC Driver 17 for SQL Server, ODBC Driver 13 for SQL Server), with the same results.

使用语法:

    cnxn = pyodbc.connect('DRIVER=lib/libmsodbcsql-13.so;SERVER=servername.account.region.rds.amazonaws.com,port;DATABASE=database;UID=user;PWD=password')

我得到了错误:

'IM004', "[IM004] [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed (0) (SQLDriverConnect)")

.ZIP部署文件中包含以下代码:

I have the following code in my .ZIP deployment file:

1. simple_db.py-创建连接的我的代码
2. pyodbc.so-来自>://github.com/Miserlou/lambda-packages/tree/master/lambda_packages/pyodbc )
3. odbcinst.ini-尝试使用Linux版本的odbcinst列出驱动程序.
4. lib/libmsodbcsql-13.so-从Amazon Linux安装中复制
5. libodbc.so.2-也可以从Amazon Linux安装中复制，尝试部署unixODBC版本.

1. simple_db.py - My code to create the connection
2. pyodbc.so - Lambda version of pyodbc from https://github.com/Miserlou/lambda-packages/tree/master/lambda_packages/pyodbc)
3. odbcinst.ini - Attempt to use a Linux-version of odbcinst to list the driver(s).
4. lib/libmsodbcsql-13.so - Copied from the Amazon Linux install
5. libodbc.so.2 - Copied from Amazon Linux install as well, attempt to deploy unixODBC version.

我弄弄了目录，并从/usr/lib64目录中添加了更多libodbc *.*文件，但是到目前为止没有任何效果.以及带来整个msodbcsql目录(带有/etc，/include，/lib64和/share).

I've toyed around with directories, and adding more libodbc*.* files from the /usr/lib64 directory, but nothing has worked so far. As well as bringing over the entire msodbcsql directory (with /etc, /include, /lib64, and /share).

任何帮助将不胜感激！

推荐答案

基于此答案:您将需要编译unixODBC，并使用其共享库.然后安装MS驱动程序，获取其共享库并收集在一起

You will need to compile unixODBC, take its shared libraries. Then install the MS driver, takes its shared libs and gather together

# Start a container that mimic the lambda environment
docker run -it --rm --entrypoint bash  -e ODBCINI=/var/task -e ODBCSYSINI=/var/task -v "$PWD":/var/task  lambci/lambda:build-python2.7

# Then, download ODBC source code, compile and take the output
curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.5.tar.gz -O
tar xvzf unixODBC-2.3.5.tar.gz
cd unixODBC-2.3.5
./configure  --sysconfdir=/var/task  --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/home
make install
cd ..
mv /home/* .
mv unixODBC-2.3.5 unixODBC-2.3.5.tar.gz /tmp/

# Install MSsql odbc driver
curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo
ACCEPT_EULA=Y yum -y install msodbcsql
export CFLAGS="-I/var/task/include"
export LDFLAGS="-L/var/task/lib"

# Then you can install pyodbc (or pip install -t . -r requirements.txt)
pip install pyodbc -t .

cp -r /opt/microsoft/msodbcsql .

cat <<EOF > odbcinst.ini
[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/var/task/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2
UsageCount=1
EOF

cat <<EOF > odbc.ini
[ODBC Driver 13 for SQL Server]
Driver      = ODBC Driver 13 for SQL Server
Description = My ODBC Driver 13 for SQL Server
Trace       = No
EOF

# Test if it works
python -c "import pyodbc; print(pyodbc.drivers());"
python -c 'import pyodbc;conn = pyodbc.connect("DRIVER={ODBC Driver 13 for SQL Server}; SERVER=YOUr_SERVER:ADD;PORT=1443;DATABASE=TestDB;UID=SA;PWD=<YourStrong!Passw0rd>");crsr = conn.cursor();rows = crsr.execute("select @@VERSION").fetchall();print(rows);crsr.close();conn.close()'

这篇关于在AWS Lambda中无法使用pydobc在Python 3.6中使用pyodbc连接到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！