Python from 0 to Microsoft SQL Server! A tutorial to try step to step

Hi Guys,

This is not the usual post. 

It is a little different post because i am in holidays with my family and because it is born from an idea of my 13 yo son Jacopo. 

Since he already use Python, Javascript and other Languages while my knowledge is strongly oriented towards Microsoft SQL Server and databases, we thought about a quick tutorial on phyton.

Let’s start from the installation to get to query a database in sql server with Phyton

And then what’s cooler than learning the basics of a new language in minutes?

If you want to take a look at his YouTube programming channel and maybe subscribe click here JacopoTech  (actually only in italian language)

Enjoy the reading mates!

 

Python from 0. Installation

Python is today a well-known language with which you can do everything.
It is really worth getting to know it and trying it out for all projects from the smallest to the largest.

Its installation is really simple, just download the installation files from the official site of Python.

So, what are you waiting for? click here  https://www.python.org/downloads/ to download the latest version, 3.10.6

Double click on the executable...

..to start the installation:

Remember to check the box "Add Python 3.10 to PATH"

Click Install Now.

 

Wait until the setup is finished.

Pyton is now up and running!

Now you will find in the program bar the item highlighted in green.

Clicking on the IDLE voice will open a command shell

Pyton is in your hands!

The first python program!

Let's do our very first (and simple) python program: we want to sum two numbers.

Type the following commands into an editor or into the IDLE shell:

x = int(input("Type a number: "))
y = int(input("Type another number: "))

sum = str(x+y)

print(f"The sum is: {sum}")

Save the file:

I Saved the script in a file named sum.py

From the command line type

cd <path> 

python sum.py 

The script is running!

You can also run your script from the IDLE shell from RUN / RUN module 

You can also convert your script into an executabe with the auto-py-to-exe program.

OK the first step is achieved, so it is time to see how to access to a database.

In this case we want to read and write data from a Microsoft SQL Server database but others rmdbms are supported.

...To SQL Server. Access to Data

Now comes the most interesting part.

Suppose we have SQL Server already installed and working.

First install the pyobdc library.

From the command line type: pip install pyodbc

Create a new .py file using a text editor.

Import the pyodbc library

Then we should configure the connection string:

Set the right server name and the right database name.

The "." character in the server property means "current server name". Otherwise you can use the name of the instance of your SQL server.

I set the database property to "Empty" because this is the name of the my database.

Now we can specify the text of the query to query the database EMPTY.

In this example, I have an ORDTES table that has an ID field. 

To read the values of this field the text of my SQL will be:

Remember to use an ALIAS for the field name.

For example: "ID as VID". VID is called alias.

The execute method will execute the "SELECT ID AS VID FROM ORDTES" T-SQL command.

 

Our command will return all the rows of the table ORDTES, so we need a loop to read them. 

In the following example I used a while statement for the loop.

Each time the fetchone method  is called a new row is read.

At the end you need to close the cursor and the connection.

 

This is the entire source code:

To easily execute your script just press F5 or choose RUN /RUN MODULE.

Et voilà...

I get only one row because my table ORDTES has only one row.

 

This is the content of the table:

 

Of course you can run all the T-SQL commands that Microsoft SQL Server provides.
You can enter, update and delete data.
All through python.

Wow mates, that's all for today!

I imagine who half an hour ago had never used python and now knows how to install it, use it in a very basic way and ...connect it to a SQL Server database.

I hope you enjoyed this tutorial!

If you find an advertisement that genuinely interests you simply click it from on my blog, please. You will help me keep writing more and more interesting content. 

~Luke (& Jacopo)

 

 

Previous post: What is new in the CTP 2.1 of SQL Server 2022? New T-SQL commands!