Preface

I want to use Microsoft Azure SQL database to advance my school project, It would be even more convenient if the program could access Microsoft's SQL database. I thought, I created it.

Caution

This article is a Microsoft Azure service and assumes you are creating a SQL database. (I made the default settings with sample data) スクリーンショット 2020-07-08 22.43.48.png

Required python modules

** pydbc ** ... A module that can access the database using a python program (The explanation of pydbc is just a simple explanation. I'm sorry if you want to know more details, but please check it on the web etc.)

If pydbc is MacOS, you can easily install it with pip, but there are differences in the installation method and driver file settings depending on the OS type, so I'm sorry if you want to know more details, but on the web etc. Please take a research on it.

Sample program

** Caution You can execute the program by setting XXXX in the program as follows. スクリーンショット 2020-07-08 23.27.59.png スクリーンショット 2020-07-08 23.28.16.png ・ First of XXXX, enter the character string written in the red part of the SQL database summary screen of your Microsoft Azure.

-In the second of XXXX, enter the character string written in the green part of the SQL database summary screen of your Microsoft Azure.

・ Third of XXXX, enter the character string to be entered in the login of the query editor screen of your Microsoft Azure SQL database. (It is the server administrator login created when creating the database)

-In the fourth of XXXX, enter the character string to be entered in the password of the query editor screen of your Microsoft Azure SQL database. (This is the password created when creating the database)

`sample.py`


import pyodbc


server = 'XXXX'
database = 'XXXX'
username = 'XXXX'
password = 'XXXX'
driver= '{ODBC Driver 17 for SQL Server}'

#Make a connection to the database
cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

#Creating table data
cursor.execute("CREATE TABLE [dbo].[Sample3](NumberNOTNULL,HeightfloatNOTNULL,WeightfloatNOTNULL);")

#Data registration
cursor.execute("INSERT INTO [dbo].[Sample3]VALUES(1,181.3,90.8)")

#Reference of all data
cursor.execute("SELECT * FROM [dbo].[Sample3]")

#Data output
data = cursor.fetchone()
print(data)

#End the connection to the database
cursor.close()
cnxn.commit()
cnxn.close()

result

There was a table in the database that was created on the local PC and registered the data!

(Diary 1) How to create, reference, and register data in the SQL database of Microsoft Azure service with python