Python Sql Server connection with AWS Lambda смотреть онлайн

The connection requires the SQL server drivers and unixobdc drivers to create a connection. However these must be compiled for the platform they will be deployed on and are not included in AWS Lambda instances by default.

#Fire up an EC2 instance and connect to it with ssh using your key file
ssh -i {path to your key file} ec2-user@{your server address}
Sudo yum install docker
sudo service docker start
sudo docker run -it --rm --entrypoint bash -e ODBCINI=/var/task -e ODBCSYSINI=/var/task -v "$PWD":/var/task lambci/lambda:build-python3.6

# 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
### IMPORTANT ####
# youtube doesnt allow angle brackets in descriptions
# replace % with right angle backet

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 .

### IMPORTANT ####
# youtube doesnt allow angle brackets in descriptions
# replace $ with left angle bracket
# replace % with right angle backet

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={server address};PORT=1433;DATABASE=RDSAdmin;UID=sa;PWD={your DB password}");crsr = conn.cursor();rows = crsr.execute("select @@VERSION").fetchall();print(rows);crsr.close();conn.close()'


cd ..
tar -zcvf pyodbcLambdaPackage.tar.gz /task

#start new terminal
sudo docker ps -a
sudo docker cp {containerId}:/var/pyodbcLambdaPackage.tar.gz .

scp -i {path to your keyfile} ec2-user@{EC2-ip-address}:/home/ec2-user/pyodbcLambdaPackage.tar.gz .\Downloads\

#Thanks to Carloches for the orginial version of these instructions
https://gist.github.com/carlochess