Try to extract Azure SQL Server data table with pyodbc

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

Trial premise

--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 ...

Anyway install & initial setup

install pyodbc from the command prompt

--As usual, at pip.

pip install pyodbc

Microsoft ODBC Driver 13 for SQL Server -Install from here

Let's write in python.

## 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()

Case 1: Count the number of items (number of rows) in the table

###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

Try to extract Azure SQL Server data table with pyodbc
Extract the Azure SQL Server data table with pyodbc and try to make it numpy array / pandas dataframe
Try to extract Azure document DB document with pydocumentdb
Try to extract the features of the sensor data with CNN
Try converting to tidy data with pandas
Try to aggregate doujin music data with pandas
[In-Database Python Analysis Tutorial with SQL Server 2017] Step 2: Import data to SQL Server using PowerShell
How to extract non-missing value nan data with pandas
How to extract non-missing value nan data with pandas
ODBC access to SQL Server from Linux with Python
Try to create a battle record table with matplotlib from the data of "Schedule-kun"
Try to factorial with recursion
Extract Twitter data with CSV
Access the host SQL Server with python27 / pyodbc on the container
Try to process Titanic data with preprocessing library DataLiner (Append)
Try to get data while port forwarding to RDS with anaconda.
Try to process Titanic data with preprocessing library DataLiner (Encoding)
Try to process Titanic data with preprocessing library DataLiner (conversion)
How to extract features of time series data with PySpark Basics
Try to extract a character string from an image with Python3
[In-Database Python Analysis Tutorial with SQL Server 2017] Step 1: Download sample data
Try to solve the shortest path with Python + NetworkX + social data
Try to get CloudWatch metrics with re: dash python data source
Try to process Titanic data with preprocessing library DataLiner (Drop edition)
Use Azure SQL Database with SQLAlchemy
Try to operate Facebook with Python
How to deal with imbalanced data
Try to profile with ONNX Runtime
Try to put data in MongoDB
How to Data Augmentation with PyTorch
Generate fake table data with GAN
Try to output audio with M5STACK
Try data parallelism with Distributed TensorFlow
Feel free to knock 100 data sciences with Google Colab and Azure Notebooks!
Try to extract specific data from JSON format data in object storage Cloudian/S3
(Diary 1) How to create, reference, and register data in the SQL database of Microsoft Azure service with python
Azure table storage with PTVS Flask app
Try logging in to qiita with Python
[In-Database Python Analysis Tutorial with SQL Server 2017]
Try working with binary data in Python
Convert Excel data to JSON with python
Send data to DRF API with Vue.js
Convert FX 1-minute data to 5-minute data with Python
Try to predict cherry blossoms with xgboost
Quickly try to visualize datasets with pandas
First YDK to try with Cisco IOS-XE
Try to generate an image with aliasing
How to read problem data with paiza