Introduction to Python-MySQL and Creating database

In this Python-MySQL tutorial, we will see how to connect Python API to sql database in xampp server and creaing database in it.

Introduction

As we know that python supports database interactivity to create and manage the data present in database. So we can see how to use python to manage the sql database in xampp server.

XAMPP

XAMPP stands for Crossplatform, Apache, MariaDB,PHP and Perl. It is used to store data in a webserver and we can able to acess it using different programming languages like PHP, Perl, Python etc.

Let's see the info:

So, our main requirement is to have XAMPP to connect python with mysql database.

Steps:

1. Go to Apache Official site - https://www.apachefriends.org/download.html

2  If your Operating System is windows: Choose the xampp software and select any version.

    If your Operating System is Linux:Choose the xampp software and select any version.

    If your Operating System is OS-Mac:Choose the xampp software and select any version.

3.  Search for xampp in your PC and open it.

4.  After Download, click Install.

Now, your xampp is ready

 

Search for xampp in your PC and open it.

You will see like this:

Start Apache and MySQL modules.

So let's connect python to sql database. Python support mysql.connector module to interact with mysql database.

So we need to install and import it.

 

Syntax to install:

pip install mysql.connector

Let' discuss about steps to create database.

1. Import mysql.connector module
    Syntax:
    import mysql.connector

2. Create the connection object to connect with xampp server using the below credentials.
    Syntax:
    connection_obj = mysql.connector.connect(
                          host="localhost",
                          user="root",
                          password=""
                                                                        )
3. Create cursor object to execute the query.
    Syntax:
    connection_obj.cursor()

4. Write sql query to create database in xampp server.
    Syntax:
    cursor.execute("SQL Query")

SQL Query to create database:

CREATE DATABASE database_name

where, database_name is the database.

Example: In this program, we are creating a database named tutorialsinhand.

:1. Open python IDLE and create new file, type the following code and save it.

 2. Open the xampp server using the link - http://localhost/phpmyadmin/

 3. You can see that database named - tutorialsinhand is created.

#import the module
import mysql.connector

#create the connection by specifying the 
#hostname-localhost
#username-root
#password-''(empty)
connection_obj = mysql.connector.connect(
  host="localhost",
  user="root",
  password=""
)

#creating cursor object
cursor = connection_obj.cursor()

#create the database  tutorialsinhand
cursor.execute("CREATE DATABASE tutorialsinhand")

Checking:

 

Now create a table in the database using python.

We can create a table in a database using the following sql query:

CREATE TABLE tablename(column1 datatype,.................,)

where, tablename is the name of the table created in a database and column refers to the column inside a table with associated datatype.

Example:

In this example, we are creating the table named - market with 3 columns in  tutorialsinhand database.

Make sure that under connection object we have to sepcify the database name to create table under this database.

#import the module
import mysql.connector

#create the connection by specifying the 
#hostname-localhost
#username-root
#password-''(empty) and
#database is tutorialsinhand
connection_obj = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database="tutorialsinhand"
)

#creating cursor object
cursor = connection_obj.cursor()

#create the table named - market with 3 columns in  tutorialsinhand database
cursor.execute("CREATE TABLE market (market_name VARCHAR(10),market_area VARCHAR(10), rating INT)")

We can check this table is created or not in xampp by opening  http://localhost/phpmyadmin/

Click on the databasetutorialsinhand

If we click on Structure, we can see the colum details.