AWS Lambda函数可使用Python连接到SQL Server [英] AWS Lambda function to connect to SQL Server with Python

问题描述

很长一段时间以来，我一直在尝试使用AWS Lambda函数连接到SQL Server.

I've been stuck trying to connect to an SQL Server using AWS Lambda functions for a long while now.

为此，我尝试使用任何库(尝试使用pyodbc，pypyodbc等)，将所有内容打包到一个zip文件中并上传代码.

To do so i'm trying to use any library (tried with pyodbc, pypyodbc, etc), packaging everything into a zip file and uploading the code.

每个库的代码几乎相同，但是错误有所不同.

The code is pretty much the same for every library, but the errors are different.

代码:

import pypyodbc

def lambda_handler(event, context):
    conn = pypyodbc.connect('DRIVER={SQL Server};'
                      'SERVER=1.1.1.1;'
                      'DATABASE=dbname;'
                      'UID=user;'
                      'PWD=pwd')

    cur = conn.cursor()

    cur.execute("SELECT * FROM Table")

    item_count = 0

    for row in cur:
        item_count += 1

    print(item_count)

    cur.close()
    conn.close()

    return item_count

我涵盖的常见问题: -我要在zip文件中添加项目内容，而不是文件夹. -我还在zip文件中添加了运行代码所需的库.

Common issues that i have covered: - I'm adding to the zip the project contents, not the folder. - I'm also adding to the zip file the libraries needed for the code to run.

如果我尝试使用pyodbc ，则我上传的zip文件如下所示:

If i try to use pyodbc, the zip i'm uploading looks like this:

.idea (dir)
pyodbc (dir)
lambda_function.py
pyodbc.pyd

我得到的错误:

Unable to import module 'lambda_function': No module named pyodbc

在搜索了很长时间之后，我找不到任何有帮助的东西.只有一个评论说pyodbc需要在linux环境中安装才能使lambda函数起作用.但是我没有可用的环境，也不知道是否能解决这个问题.

After searching for quite a while about this, i couldn't find anything that helps. Only one comment that said that pyodbc needed to be instaled on a linux enviroment for the lambda function to work. But i don't have that Enviroment available, also i don't know if that will fix this.

如果我尝试使用pypyodbc ，则我正在上传的zip格式如下:

If i try to use pypyodbc, the zip i'm uploading looks like this:

我得到的错误:

module initialization error: 'ODBC Library is not found. Is LD_LIBRARY_PATH set?'

为此，我尝试安装其他stackoverflow帖子(python-pyodb，unixodbc)建议的多个python软件包，但每次都失败.

For this one i tried to install multiple python packages suggested by other stackoverflow posts (python-pyodb, unixodbc), but i failed every time.

然后有一条评论说确保将本地ODBC库放在zip部署程序包的lib文件夹下"

Then there was one comment around saying "Make sure to put native ODBC libraries under the lib folder in your zip deployment package"

也许有帮助吗?我不知道如何获取本机ODBC库.

Maybe that is some help? I don't know how to get native ODBC libraries..

哦，还有最后一件事.如果我从本地计算机运行它们，这两个库都可以工作.我可以访问目标服务器.如果我从lambda函数执行此操作，它将失败.

Oh and one last thing. Both libraries work if i run them from my local machine. I can get access to the target server. It fails if i do it from the lambda function.

希望有人可以帮助我，显然还有整个互联网.

Hopefully someone can help me and, apparently, the whole internet with this.

推荐答案

• 您需要知道Lambda在本地/var/task/
中复制函数
• 使用Lambda官方AMI创建实例 https://docs.aws.amazon.com/lambda/latest/dg/current-supported-versions.html
• 启动实例，登录
• yum install gcc gcc-c++
• 进入/home/ec2-user
• 从以下位置下载最后一个unixodbc管理器: ftp://ftp.unixodbc.org/pub/unixODBC/
• wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.5.tar.gz
• tar xvzf unixODBC-2.3.5.tar.gz
• cd unixODBC-2.3.5

• 使用正确的sysconfdir值进行配置

  • you need to know Lambda copy your function in local /var/task/
  • create a instance using Lambda official AMI https://docs.aws.amazon.com/lambda/latest/dg/current-supported-versions.html
  • start instance, login
  • yum install gcc gcc-c++
  • go in to /home/ec2-user
  • Download the last unixodbc manager from: ftp://ftp.unixodbc.org/pub/unixODBC/
  • wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.5.tar.gz
  • tar xvzf unixODBC-2.3.5.tar.gz
  • cd unixODBC-2.3.5

  • configure it with the correct sysconfdir value

    ./configure --sysconfdir=/var/task --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/home

    [ODBC Driver 13 for SQL Server] Description=Microsoft ODBC Driver 13 for SQL Server Driver=/var/task/msodbcsql/msodbcsql/lib64/libmsodbcsql-13.1.so.9.1 UsageCount=1

    [ODBC Driver 13 for SQL Server] Description=Microsoft ODBC Driver 13 for SQL Server Driver=/var/task/msodbcsql/msodbcsql/lib64/libmsodbcsql-13.1.so.9.1 UsageCount=1

    • 在计算机上的同一根目录中，创建文件odbc.ini

    • On your computer, in the same root directory create file 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

    [ODBC Driver 13 for SQL Server] Driver = ODBC Driver 13 for SQL Server Description = My ODBC Driver 13 for SQL Server Trace = No

    在python程序上使用pyodbc:

    on your python program use pyodbc:

    import pyodbc def lambda_handler(event, context): server = "xxxxxxxxxxxxxxxxxxxx" database = "xxxxxxxxxxxxxxxxxxxx" username = "xxxxxxxxxxxxxxxxxxxx" password = "xxxxxxxxxxxxxxxxxxxx" cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password) cursor = cnxn.cursor() ...other things....

    import pyodbc def lambda_handler(event, context): server = "xxxxxxxxxxxxxxxxxxxx" database = "xxxxxxxxxxxxxxxxxxxx" username = "xxxxxxxxxxxxxxxxxxxx" password = "xxxxxxxxxxxxxxxxxxxx" cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password) cursor = cnxn.cursor() ...other things....

    现在玩游戏！

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