Memo up to the point where you bring the data on SQL Server on Microsoft Azure to your hand (local pc) and make it a pandas data frame for python
PC OS: Windows 10
Python: Python 2.7
python libraries: pyodbc
,pandas
, numpy
--Table settings on Azure (SQL Server)
- server: abc_server.database.windows.net
- database: abc_database
- username: abc_user
- password: abc_password
――Please rewrite each ʻabc ...` when setting on Azure as appropriate.
--Allow the Azure SQL Server FireWall settings
--Otherwise, authentication will moss ...
pyodbc
from the command prompt--As usual, at pip
.
pip install pyodbc
Microsoft ODBC Driver 13 for SQL Server -Install from here
## libraries
import pyodbc
## initial setting
##As mentioned above, please change the set value as appropriate.
server = 'abc_server.database.windows.net'
database = 'abc_database'
username = 'abc_user'
password = 'abc_password'
##Function definition
###Define DB connection
def db_connection(sv=server, db=database, un=username, pw=password):
cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+sv+';DATABASE='+db+';UID='+un+';PWD='+ pw)
return cnxn.cursor()
###Issue SQL
def query_output(sql):
cursor.execute(sql)
row = cursor.fetchone()
while row:
print row[0]
row = cursor.fetchone()
###DB connection
cursor = db_connection()
### SalesLT.Customer is a table as a template
sql = 'select count(*) from SalesLT.Customer;'
query_output(sql) #The result is 847
Continuing from the above, Create a table here.
Reference
Recommended Posts