tepmorysandbox/python-to-sql repository overview

This docker image, built on top of microsoft/mssql-server-linux, allows to push and pull data from a Microsoft MS SQL database.

The key advantage of using microsoft/mssql-server-linux, as image source, is to start with an official driver (ODBC Driver 13 for SQL Server) already configured. Espeacially, driver installation and configuration can be bothersome (matching version of linux OS, MS SQL Driver and Python data manipulation libraries,...).

Dialoguing with an Azure MS SQL Database using :

• sqlalchemy ORM : an entire pandas DataFrames can be pushed into a MS-SQL Database hosted in Azure: df.sql_to(<database name>,<sqlalchemy engine>).

• pydobc driver : pyodbc allows to transmit queries from Python to MS SQL by : cursor.execute("SELECT * FROM <database name>;").

Note: we assume that a MS SQL Azure database server is already set-up and configured.

⁠General instruction

⁠1. MS SQL setup

Allow host's IP address to connect (write and read) on the existing MS SQL database

⁠2. Daloguing via SQL queries

import pyodbc

server = '<server name>'
database = '<db name>'
username = '<db admin login>'
password = '<db admin password>'
driver= '{ODBC Driver 13 for SQL Server}'

cnxnpyodbc.connect('DRIVER='+driver+';PORT=1433;SERVER='+server+';PORT=1443;DATABASE='+database+';UID='+username+';PWD='+ password)


cursor = cnxn.cursor()
cursor.execute("CREATE TABLE PIVOT1 (id int,customer varchar(255))")
cursor.execute("INSERT INTO PIVOT1 VALUES (1,'Bob'),(2,'Alice'),(3,'Vitalik'),(4,'Satoshi')")
cnxn.commit()

cursor.execute("SELECT * FROM PIVOT1;")
row = cursor.fetchone()
while row:
print (str(row[0]) + " " + str(row[1]))
row = cursor.fetchone()

⁠3. Pushing Pandas DataFrame

import pyodbc
import pandas as pd;
import sqlalchemy;
from sqlalchemy import create_engine

engine = create_engine('mssql+pyodbc://<db admin login>:<db admin password>@<server name>:1433/<db name>?driver=ODBC+Driver+13+for+SQL+Server')
dat=pd.DataFrame({'id':[1,2,3,4],'contract':["Bob","Alice","Vitalik","Satoshi"]})
dat.to_sql("PIVOT2",con=engine)

⁠QuickStart

Note: we assume that python scripts will be located on the following directory : ./scripts The container contains already a python boilerplate as example.

⁠run from this image

mkdir /scripts
docker run  -t -d --name mycontainer  -v $(pwd)/scripts:/scripts python-to-sql bash
docker exec -it mycontainer /bin/bash

⁠Built from dockerfile

sudo docker image build -t pythonsql .

Note: DockerFile require to host a python file named code.py. This file can be empty or filled with python commands.