Open In App

How to Connect Python with SQL Database?

Last Updated : 22 Aug, 2022
Improve
Improve
Like Article
Like
Save
Share
Report

Python is a high-level, general-purpose, and very popular programming language. Basically, it was designed with an emphasis on code readability, and programmers can express their concepts in fewer lines of code. We can also use Python with SQL. In this article, we will learn how to connect SQL with Python using the ‘MySQL Connector Python module. The diagram given below illustrates how a connection request is sent to MySQL connector Python, how it gets accepted from the database and how the cursor is executed with result data.

SQL connection with Python

Connecting MySQL with Python

To create a connection between the MySQL database and Python, the connect() method of mysql.connector module is used. We pass the database details like HostName, username, and the password in the method call, and then the method returns the connection object.

The following steps are required to connect SQL with Python:

Step 1: Download and Install the free MySQL database from here.

Step 2: After installing the MySQL database, open your Command prompt.

Step 3: Navigate your Command prompt to the location of PIP. Click here to see, How to install PIP?

Step 4: Now run the commands given below to download and install “MySQL Connector”. Here, mysql.connector statement will help you to communicate with the MySQL database.

Download and install “MySQL Connector”

pip install mysql-connector-python 

 

Step 5: Test MySQL Connector

To check if the installation was successful, or if you already installed “MySQL Connector”, go to your IDE and run the given below code :

import mysql.connector

If the above code gets executed with no errors, “MySQL Connector” is ready to be used.

Step 6: Create Connection

Now to connect SQL with Python, run the code given below in your IDE.

Python3




# Importing module
import mysql.connector
 
# Creating connection object
mydb = mysql.connector.connect(
    host = "localhost",
    user = "yourusername",
    password = "your_password"
)
 
# Printing the connection object
print(mydb)


Output:

Here, in the above code:

Code Info

Creating MySQL Database

To create a database, we will use CREATE DATABASE database_name statement and we will execute this statement by creating an instance of the ‘cursor’ class.

Python3




import mysql.connector
 
mydb = mysql.connector.connect(
    host = "localhost",
    user = "yourusername",
    password = "your_password"
)
 
# Creating an instance of 'cursor' class
# which is used to execute the 'SQL'
# statements in 'Python'
cursor = mydb.cursor()
 
# Creating a database with a name
# 'geeksforgeeks' execute() method
# is used to compile a SQL statement
# below statement is used to create
# the 'geeksforgeeks' database
cursor.execute("CREATE DATABASE geeksforgeeks")


Output:

If the database with the name ‘geeksforgeeks’ already exists then you will get an error, otherwise no error. So make sure that the new database that you are creating does not have the same name as the database already you created or exists previously. Now to check the databases that you created, use “SHOW DATABASES” – SQL statement i.e. cursor.execute(“SHOW DATABASES”)

Python3




import mysql.connector
 
mydb = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "1234"
)
 
# Creating an instance of 'cursor' class
# which is used to execute the 'SQL'
# statements in 'Python'
cursor = mydb.cursor()
 
# Show database
cursor.execute("SHOW DATABASE")
 
for x in cursor:
  print(x)


Output:

Creating Tables

Now to create tables in a database, first, we have to select a database and for that, we will pass database = “NameofDatabase” as your fourth parameter in connect() function. Since we have created a database with the name ‘geekforgeeks’ above, so we will use that and create our tables. We will use CREATE TABLE gfg (variableName1 datatype, variableName2 datatype) statement to create our table with the name ‘gfg’.

Python3




import mysql.connector
 
mydb = mysql.connector.connect(
    host = "localhost",
    user = "yourusername",
    password = "your_password",
    database = "geeksforgeeks"
)
 
cursor = mydb.cursor()
 
# Creating a table called 'gfg' in the
# 'geeksforgeeks' database
cursor.execute("CREATE TABLE gfg (name VARCHAR(255), user_name VARCHAR(255))")


Output:

If the table with the name ‘gfg’ already exists, you will get an error, otherwise no error. So make sure that the new table that you are creating does not have the same name as the table already you created or exists previously. Now to check tables that you created, use “SHOW TABLES” – SQL statement i.e. cursor.execute(“SHOW TABLES”).

Python3




import mysql.connector
 
mydb = mysql.connector.connect(
    host = "localhost",
    user = "root
    password = "1234",
    database = "geeksforgeeks"
)
 
cursor = mydb.cursor()
 
# Show existing tables
cursor.execute("SHOW TABLES")
 
for x in cursor:
  print(x)


Output:

Notes:

  • mysql.connector allows Python programs to access MySQL databases.
  • connect() method of the MySQL Connector class with the arguments will connect to MySQL and would return a MySQLConnection object if the connection is established successfully.
  • user = “yourusername”, here “yourusername” should be the same username as you set during MySQL installation.
  • password = “your_password”, here “your_password” should be the same password as you set during MySQL installation.
  • cursor() is used to execute the SQL statements in Python.
  • execute() method is used to compile a SQL statement.


Previous Article
Next Article

Similar Reads

How To Connect and run SQL queries to a PostgreSQL database from Python
This article focus on connecting to a PostgreSQL database from Python. Installation:Install PostgreSQL, If you haven't installed it.We need to install the psycopg2 library to connect to a PostgreSQL database. Open the command prompt and run the below command to install psycopg2pip3 install psycopg2Creating a Database You can create a Database in 2
2 min read
Connect MySQL database using MySQL-Connector Python
While working with Python we need to work with databases, they may be of different types like MySQL, SQLite, NoSQL, etc. In this article, we will be looking forward to how to connect MySQL databases using MySQL Connector/Python.MySQL Connector module of Python is used to connect MySQL databases with the Python programs, it does that using the Pytho
2 min read
PostgreSQL - Connect To PostgreSQL Database Server in Python
The psycopg database adapter is used to connect with PostgreSQL database server through python. Installing psycopg: First, use the following command line from the terminal: pip install psycopg If you have downloaded the source package into your computer, you can use the setup.py as follows: python setup.py build sudo python setup.py installCreate a
4 min read
How to connect to SQLite database that resides in the memory using Python ?
In this article, we will learn how to Connect an SQLite database connection to a database that resides in the memory using Python. But first let brief about what is sqlite. SQLite is a lightweight database software library that provides a relational database management system. Generally, it is a server-less database that can be used within almost a
3 min read
Connecting to SQL Database using SQLAlchemy in Python
In this article, we will see how to connect to an SQL database using SQLAlchemy in Python. To connect to a SQL database using SQLAlchemy we will require the sqlalchemy library installed in our python environment. It can be installed using pip - !pip install sqlalchemyThe create_engine() method of sqlalchemy library takes in the connection URL and r
3 min read
Interface Python with an SQL Database
Python is an easy-to-learn language and connectivity of python with any SQL database is a much-desired option to have the persistence feature. Python is an object-oriented programming language and it is open source. Newcomers to the software industry including school children too can learn Python easily. Python can be downloaded easily according to
8 min read
Read SQL database table into a Pandas DataFrame using SQLAlchemy
To read sql table into a DataFrame using only the table name, without executing any query we use read_sql_table() method in Pandas. This function does not support DBAPI connections. read_sql_table()Syntax : pandas.read_sql_table(table_name, con, schema=None, index_col=None, coerce_float=True, parse_dates=None, columns=None, chunksize=None) Paramete
2 min read
SQL USE Database Statement
SQL(Structured Query Language) is a standard Database language that is used to create, maintain and retrieve the data from relational databases like MySQL, Oracle, etc. It is flexible and user-friendly. In SQL, to interact with the database, the users have to type queries that have certain syntax, and use command is one of them. The use command is
2 min read
Connect to MySQL using PyMySQL in Python
In this article, we will discuss how to connect to the MySQL database remotely or locally using Python. In below process, we will use PyMySQL module of Python to connect our database. What is PyMySQL? This package contains a pure-Python MySQL client library, based on PEP 249. Requirements : MySQL Server – one of the following : MySQL >= 5.5Maria
2 min read
Matplotlib.pyplot.connect() in Python
Matplotlib is a library in Python and it is numerical - mathematical extension for NumPy library. Pyplot is a state-based interface to a Matplotlib module which provides a MATLAB-like interface. matplotlib.pyplot.connect() Function This method is used to connect an event with string s to a function. Syntax: matplotlib.pyplot.connect(s, func) Parame
3 min read