vishavjeet

singh.vishavjeet11@gmail.com

DataBase Connection and Run SQL

Posted on March 11, 2021



#Create Connection

import mysql.connector

#Exercise 1: Show Databse And Create Database

# db = mysql.connector.connect( host="localhost", user="root", password="")

# print(db)

# cursor = db.cursor()

# cursor.execute("SHOW DATABASES")

# for x in cursor:

# print(x)

#

# cursor.execute("CREATE DATABASE python_demo_db")

#Exercise 2: Create Table

# db = mysql.connector.connect( host="localhost", user="root", password="", database="python_demo_db")

# cursor = db.cursor()

#cursor.execute("CREATE TABLE players (name VARCHAR(255), country VARCHAR(64), total_run int(1), total_match int(1), player_type VARCHAR(64))")

#Exercise 3: use the ALTER TABLE keyword:

# db = mysql.connector.connect( host="localhost", user="root", password="", database="python_demo_db")

# cursor = db.cursor()

#sql="ALTER TABLE players ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY"

#sql="ALTER TABLE players DROP id"

#sql="ALTER TABLE players ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY AFTER total_match"

#sql="ALTER TABLE players ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY FIRST"

# cursor.execute(sql)

#Exercise 4: Insert data into playes table

# db = mysql.connector.connect( host="localhost", user="root", password="", database="python_demo_db")

# cursor = db.cursor()

# sql = "INSERT INTO players (name, country, total_run, total_match, player_type) VALUES (%s, %s, %s, %s, %s)"

# val = ("Sourav Ganguli", "INIDIA" ,11363 ,311, "India")

# cursor.execute(sql, val)

#print("1 record inserted, ID:", cursor.lastrowid)

#Exercise 5: Insert Many data into playes table

# db = mysql.connector.connect( host="localhost", user="root", password="", database="python_demo_db")

# cursor= db.cursor()

# sql = "INSERT INTO players (name, country, total_run, total_match, player_type) VALUES (%s, %s, %s, %s, %s)"

# val = [

# ("Sachin", "INIDIA" ,18426 ,463, "India"),

# ("Sachin", "INIDIA" ,18426 ,463, "India"),

# ("Sachin", "INIDIA" ,18426 ,463, "India"),

# ("Sachin", "INIDIA" ,18426 ,463, "India"),

# ]

# cursor.executemany(sql, val)

#print("1 record inserted, ID:", cursor.lastrowid)

#Exercise 6: Dynamic Sql Qquery run by input

# db = mysql.connector.connect( host="localhost", user="root", password="", database="python_demo_db")

# cursor = db.cursor()

# sql=input("ENTER SQL COMMAND HERE:> ")

# try:

# cursor.execute(sql)

# print(f"Total {cursor.rowcount} record inserted in players table.")

# except :

# print("Wrong Command, Please type correct SQL command")

# print(f"Total {cursor.rowcount} record inserted in players table.")

#Exercise 7: Fetch all row from playes table

# db = mysql.connector.connect( host="localhost", user="root", password="", database="python_demo_db")

# cursor= db.cursor()

# cursor.execute("SELECT * FROM players")

# result = cursor.fetchall()

#

# for x in result:

# print(x)

# Exercise 8: Fetch one row from playes table

db = mysql.connector.connect(host="localhost", user="root", password="", database="python_demo_db")

cursor = db.cursor()

cursor.execute("SELECT * FROM players")

result = cursor.fetchone()

print(result)

db.commit()

print(f"Total {cursor.rowcount} record inserted in players table.")


Sign In for comment and like the post.