MySQL With Python

 

Setup For Work

---

step 1: Install MySQL in your system (Note* please remember root password)
 
step 2: create a database in the name 'db10'

step 3: create a virtual environment (if you don't know then visit here)

step 4: pip install mysql-connector-python

step 5: Now create a python script and try with the below code

Code

---

Connecting Database:

import psycopg2

import mysql.connector

def connect_my_db(db, user, password, host):

    try:

        connection = mysql.connector.connect(

        database=db, user = user, 

        password = password, host = host, 

        port = "3306")

        print("Database Connected")

        return connection

    except Exception as e:

        print("Unable to connect DB")

connection = connect_my_db('db10', 'root', '12345', 'localhost')

Create Table :

import mysql.connector

import db_connect

connection = db_connect.connect_my_db('db10', 'root', '12345', 'localhost')

cursor_object = connection.cursor()

query = ''' CREATE TABLE DemoTable (

    id int NOT NULL AUTO_INCREMENT,

    firstname varchar(20),

    lastname varchar(20),

    phone varchar(10),

    PRIMARY KEY(id)

); '''

try:

    cursor_object.execute(query)

    print("Table created successfully")

except Exception as e:

    print(e)

connection.commit()

connection.close()

Insert Data Into Table :

import mysql.connector

import db_connect

connection = db_connect.connect_my_db('db10', 'root', '12345', 'localhost')

cursor_object = connection.cursor()

query = '''INSERT INTO DemoTable 

(id, firstname, lastname, phone) 

VALUES (1, 'Kuntal', 'Samanta', 7407501378);'''

try:

    cursor_object.execute(query)

    print("Record created successfully")

except Exception as e:

    print(e)

connection.commit()

connection.close()

Fetching Data From Table:

import mysql.connector

import db_connect

connection = db_connect.connect_my_db('db10', 'root', '12345', 'localhost')

cursor_object = connection.cursor()

query = '''SELECT * FROM DemoTable'''

try:

    cursor_object.execute(query)

except Exception as e:

    print(e)

else:

    rows = cursor_object.fetchall()

    for i in rows:

        print("Your Name is : {0} {1}".format(i[1], i[2]))

Screenshot :

Quiz Level 1

Quiz Level 2

Request Me

Click Here For More