This article here is all about databases and connecting these databases to Python. Thus, in this post, we will discuss how to connect to SQL server Python. So, lets proceed with the job at hand.

Database — What it is?

A database in reality is generally a collection of structured data, in a way that it can be very easily retrieved, accessed and managed. There are generally two types of databases namely : Flat Database and Relational Database. Relational databases are the most commonly used database system. Some examples of which are :

  • Oracle
  • IBM Db2
  • MySQL
  • SQL server
  • No SQL
  • MS Access

The most common among these is the MySQL database system, as it is comparatively the easiest to use.

Now, let’s discuss about MySQL …..

SQL is a standard language used for tabular or relational databases. SQL stands for Structure Query Language, and it allows for various operations like manipulation, design and also other queries. Thus, it is used for analyzing and getting insights from data.

Now, what is MySQL? MySQL is an open-source relational database management system which uses the Structured Query Language to perform the various operations.

Lets, proceed with connecting SQL server with Python

Connecting SQL server with Python is actually very easy to setup. For this, the dynamic nature of Python comes in very handy, as it is easily used to control and build queries. To top it all, both these tools, allow us to scale new peaks of automation and efficiency.

“pyodbc” — Now what’s this?

This is a library which allows relatively easier access to ODBC (Open Database Connectivity) databases. ODBC is a standard API application programming interface) used to access databases. This technology was developed by the SQL Access group.

The process starts —-> Connect

The foremost thing, that we need to do is to create a connection to the SQL server. How do we do it? We do this by employing the use of pyodbc.connect. We need to pass a connection string to this function. This, connection string needs to specify the DBMS Driver, Server and a database to connect to.

Suppose to say, we want to connect to the server “INXXX00023, 45890”, database “DB007″ , to do this we want to use the ” SQL Server Native Client 11.0 “.

We will be internal trusted connection, therefore, there is no need to enter username and password.

cn_str = ("Driver = {SQL Server Native Client 11.0};"
          "Server = INXXX00023, 45890;"
          "Database = DB007;"
          "Trusted_Connection = yes;")

# Initializing the connection
cn = pyodbc.connect(cn_str)

This was when we were connecting from a trusted connection. Now, what shall we do when accessing the database, using a non-trusted connection. In that case, we need to enter the username and password which we handn’t in the case of using the trusted connection. The username and password is the one which we generally use to access the server via SQL Server Management Studio.

Let’s say my username is Shubham, and the password is Shub1234. Then, we can connect in this way :

cn_str = ("Driver = {SQL Server Native Client 11.0};"
          "Server = INXXX00023, 45890;"
          "Database = DB007;"
          "Trusted_Connection = yes;"            
          "UID = Shubham;"
          "PWD = Shub1234;")

# Initialization
cn = pyodbc.connect(cn_str)

Thus, we are now connected to the database. So, now we can start performing the various SQL queries via Python.

Executing a Query — Connect to SQL Server Python

Note : Any and every query that we try to run on the SQL Server, will consist of a cursor initialization followed by the query execution. Also, a point to note here is that if we perform any changes inside the server, then, we need to commit the same to the server.

# initializing a cusror
cursor = cn.cursor()

/* This "cursor" object shall be used whenever we want to perform any query

Let’s perform our queries on the employees table, wherein we select the top 50 rows :

cursor.execute("SELECT TOP(50) * FROM employees")

Now, the operation gets performed, but in the server. Hence, we now need to extract this information to read it into Python.

Extracting the Data

To extract the queried data from SQL into Python, we will be using the “pandas” library. The pandas library provides us with a very easy function “read_sql” to read SQL data into Python. We also need to specify the connection.

Let’s see how to do it :

data = pd.read_sql("SELECT TOP(50) * FROM employees", cn)
# The above statement returns the top 50 rows from the employees table

How do we Edit, Insert and Update Data in SQL using Python

Now, that we have got our required information from the “employees” table, lets see how can we edit, insert and update the data. We should remember this point, that, when we go on to execute queries in SQL, the changes are temporary until and unless we commit them.

For example, lets see how to concatenate the fName and lName columns, to create a Name column.

cursor = cn.cursor()

# Using the alter table command, we need to add a new column named "Name"
cursor.execute("ALTER TABLE employees" +
               "ADD Name VARCHAR(30)")

# now update that column to contain fName + lName
cursor.execute("UPDATE employees" +
               "SET Name = fName + " " + lName")

# Now, lets commit these changes into the master table  
cn.commit()

Finally ….

Now, that we have extracted the data, we can perform our various manipulation tasks which we intended to in the first place. We can though, manipulate the data first and then extract or vice-versa, i.e. whichever way we find it easier to accomplish our task.

After extraction though, we can perform a great multitude of other useful tasks, which might have not been possible earlier.

Suppose, we need to perform daily reporting, while calculating some basic statistics, and also need to send the results via email. So, let’s see how do we do it.

View this gist on GitHub

Our task is now complete… The above code herein hence, automates our task of extracting weekly report, calculating the mean salary, standard deviation in the salary, max salary and the department of the max salary. It then, sends the key data to the receiving email (to whosoever it concerns, namely our boss in a general case)

🙂 🙂 🙂 🙂

SUMMING UP <—> Connect to SQL Server Python

In this post, as you have seen we discussed about the steps to get our job done of connect to SQL server Python. Thus, we are now well accomplished and ready to make connections to SQL servers or for that matter databases to Python. In some easy steps, we thus, set-up an efficient and automated workflow, wherein we extracted the weekly report and also sent it to the boss. Thus, we see that the combination of Python and SQL simply opens up new avenues which would otherwise have been improbable.

By and through this article, thus, I suppose I have made myself pretty clear. But, in case, you still have some doubts lingering. Then, please do write to me in the comments section and I am as always, ever-ready to help you. And, also solve your many queries and problems.

Until then bidding you Good-Bye !!! Ok, wait ….. before you go, you may check out my various other posts. Also, for the simple reason, that is, to enhance your knowledge on various other topics of importance. Also, where ??? Here…… 

Categorized in:

HowTo'sPython

Tagged in:

,