Introduction to Python SQL Server Connection

Python SQL server connection is created with code readability so that programmers may communicate their ideas in fewer lines of code. Python language, which is widely used. Python can also be used in conjunction with SQL. To connect the MySQL database, we need a mysql connector in python. MySQL connector is working as middleware between python and MySQL database. The python application connects to the MySQL database using the MySQL connector.

Overview of Python SQL Server Connection

• We need a library to establish a connection between the SQL server and python. SQL and python are both widely used. SQL is the de facto standard of python in the world of databases.
• It’s quite simple to put this together. We may easily take advantage of python’s dynamic nature to control and generate SQL queries. Furthermore, SQL server connection with python is very easy as we have connections with other databases.

How to Connect Python SQL Server?

The below steps show how to connect the python SQL server. To connect to the python SQL server, we first need to install pyodbc, python, visual studio, and MS python extension.

1. In the first step, we check whether or not python is installed on our system. After that, we check the same by logging into python and checking the version of python.

Code:

python –version
python

Output:

In the above figure, we can see that it will show the python version as 3.10.2. Also, we have login into the python console.

2. After checking the python installation in this step, we install the pyodbc using the pip command.

Code:

pip install pyodbc

Output:

3. After installing the pyodbc, we import the pyodbc module into our script. To import the pyodbc module, we need to use the import keyword. We have created a server.con file for creating a connection.

Code:

import pyodbc

Output:

4. After importing the pyodbc module in this step, we set the connection string with the SQL database server. We are using test DB to connect the SQL database server.

Code:

py_con = pyodbc.connect p('connection string')

Output:

5. After the connection string is set up in this step, we create the cursor object from the connection and execute the SQL command. We are executing the “select @@version” command to check the version of the database server.

Code:

py_cur = py_con.cursor()
py_cur.execute ("select @@version")

Output:

6. After executing the query, we close the cursor and DB connection. We are closing the cursor and database by using the comparative method.

Code:

py_cur.close()
connection.close()

Output:

Python SQL Server Connection Install

Below are the steps which were used in the python SQL server connection:

1. The connect method of the mysql.The connector module is used to establish a link between the python and the database server of MySQL. In the method call, we supply the database information such as hostname, username, and password, and this method will return the connection object. If we use the same system for MySQL and python, we need to use the hostname as localhost.

2. To connect the MySQL database, we first need to install it on our system. After installing MySQL, open the command prompt and create a user which was used to connect the MySQL database through python.

3. If suppose pip is not installed in our machine, we need to install the same in our system. Pip is used to install a mysql-connector for connecting to the database server.

4. After installing pip on our system, the next step is to install a MySQL connector.

The below example shows installing MySQL connector by using pip.

Code:

pip install mysql-connector

Output:

5. To check the installation of the mysql connector, we need to go into the python shell and execute the below command as follows. To use mysql connector in python first, we need to import the same using the import keyword.

Code:

import mysql.connector

Output:

6. After importing the module, we connect it to the MySQL database using the following example.

The below example shows connect to the MySQL database as follows:

Code:

import mysql.connector
py_my = mysql.connector.connect(
host = "localhost",
user = "root",
password = "Mysql@123"
)
print ("Connected")

Output:

In the above example, we can see for connecting to the MySQL database server; first, we have imported the mysql connector module. Then we have created a connection object for the use of connection purpose.

Also, we need to give the hostname of the database server. Also, we have given a username and password for authentication purposes.

7. Creating the database: In this step, we create the database name db_server database into the MySQL database server using python code. We are using the “create database name_of_database” statement for creating a database.

Code:

import mysql.connector
py_my = mysql.connector.connect(
host = "localhost",
user = "root",
password = "Mysql@123"
)
py_cur = py_my.cursor()
py_cur.execute("CREATE DATABASE db_server")
print ("Database created.")

Output:

We can also crosscheck the database created into the MySQL database server while logging in to the server.

Code:

show databases;

Output:

8. We create the table in the db_server database in this step. We are creating the table name as db_table.

Code:

import mysql.connector
py_my = mysql.connector.connect(
host = "localhost",
user = "root",
password = "Mysql@123",
database = "db_server"
)
py_cur = py_my.cursor()
py_cur.execute ("CREATE TABLE db_table (stud_name VARCHAR(10), stud_addr VARCHAR(20));")
print ("table created.")

Output:

9. Insert data into MySQL table: In this step, we are inserting data into the MySQL table as follows.

Code:

import mysql.connector
py_my = mysql.connector.connect(
host = "localhost",
user = "root",
password = "Mysql@123",
database = "db_server"
)
py_cur = py_my.cursor()
py_cur.execute("insert into db_table values ('ABC', 'Sydney');")
print ("Record created.")

Output:

Conclusion

We need the library to establish a connection between the SQL server and python. SQL and python are both widely used. Python SQL server connection is created with code readability in mind so that programmers may communicate their ideas in fewer lines of code. Python language, which is widely used.