Python
Introduction Database connection1 Database connection2 Create database Show database list Create table in database Show table list Insert data in database Insert many data Fetch single data Fetch all data Update data Delete data Manual CRUD Operation Dynamic CRUD Operation Django Web Application coming soon.... Machine Learning coming soon.... Artificial Intelligent coming soon.... Coming more codes.....Course Description ( Python )
Prerequisite basics knowledge
- Students must have basic knowledge of python and mysql database.
Following topics covered
- How to connect with database without using django.
- How to use python on localhost link xampp, wamp etc.
- How to create database using python on localhost.
- How to use html,css,bootstrap,javascript with python on localhost without using framework.
- How to insert data in database tabel on localhost.
- How to Update data in database on localhost.
- How fetch data from database on localhost.
- How to delete data from database and from table.
- Crud Operation in python without using framework.
Database connection with mysql connector
In this section you will learn how to connect with mysql-connector package or library MySql Database following steps.
1) run command : python -m pip install mysql-connector
If its done you will get a message: Successfully installed mysql-connector 2.9.. version
When its installed just import import mysql.connector
check code below
DB connection file: dbcon.py
import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
conn = mysql.connector.connect(host='localhost',user='root',password='',database='dbdemo')
dbcon = conn.cursor()
if dbcon:
print("Connected................!!!")
else:
print("Connection failed...........???")
Run command in console : python dbcon.py
Database Connection with pymysql
In this section we will learn how to connect with pymysql package or library with database following steps.
1) run command : pip install mysql-connector
When its installed just import import pymysql
DB connection file: dbcon.py
#How to create connection with database using pymysql library
import pymysql
try:
conn = pymysql.connect(host='localhost',user='root', password='', database='mydata')
dbcon=conn.cursor()
print("Database connected................!!!")
except:
print("Database connection failed...........???")
#dbcon.close() #// This must be commented if we are importing this file on another file
Run command in console : python dbcon.py
Create Database with python script
In this section we will learn how to create database by running single python script in console
Create new database by running python script first you have to connect with any database
DB connection file: dbcon.py
#How to create connection with database using pymysql library
import pymysql
try:
conn = pymysql.connect(host='localhost',user='root', password='', database='mydatadb')
dbcon=conn.cursor()
print("Database connected................!!!")
except:
print("Database connection failed...........???")
dbcon.close() #// This must be commented if we are importing this file on another file
Create new database by running python script first you have to connect with any database
DB connection file: creatdb.py
#How to create database in batabase using python in command line
from dbcon import conn, dbcon #We have to import database connection file from dbcon.py
try:
dbcon.execute("CREATE DATABASE dbdemo") #Create new database
print("Database has been created successfully ")
conn.commit()
except:
conn.rollback()
dbcon.close()
Show Databases List
In this section we will learn how to get all database list from database.
DB connection file: dbcon.py
#For connection with database using pymysql library
import pymysql
try:
conn = pymysql.connect(host='localhost',user='root', password='', database='')
dbcon=conn.cursor()
print("Database connected................!!!")
except:
print("Database connection failed...........???")
Process file: showdb.py
#How to show ot get all database name from database using command line
from dbcon import conn, dbcon #We have to import database connection file from dbcon.py
try:
#This will return as tuple ('wishapp','mydb','myprojectdb','inventory')
#countdb = dbcon.execute("show databases") #show databases
# print(f"There are {countdb} database in database")
# for db in dbcon:
# print(db)
#This will return as tuple as appdb, mydb, myprojectdb, inventory)
countdb = dbcon.execute("show databases") #show databases
print(f"There are {countdb} database in database")
for db in dbcon:
# print(db[0]) #This will show you vertical list
print(db[0],end=',') #This will show you horizontal list
conn.commit()
except:
conn.rollback()
dbcon.close()
Run command in console : python showdb.py
Create Table in Database
In this section we will learn how to create table in database by running python script
DB connection file: dbcon.py
#For connection with database using pymysql library
import pymysql
try:
conn = pymysql.connect(host='localhost',user='root', password='', database='dbdemo')
dbcon=conn.cursor()
print("Database connected................!!!")
except:
print("Database connection failed...........???")
Process file: createtable.py
#How to show ot get all database name from database using command line
from dbcon import conn, dbcon #We have to import database connection file from dbcon.py
try:
#create table=====================================
cdbtable = dbcon.execute("CREATE TABLE `users`(`id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `username` VARCHAR(100) NOT NULL, `email` VARCHAR(100) NOT NULL, `mobile` INT(100) NOT NULL, `city` VARCHAR(100) NOT NULL, `country` VARCHAR(100) NOT NULL)")
print(f"The table has been created in database")
conn.commit()
except:
conn.rollback()
dbcon.close()
Run command in console : python createtable.py
Show database tables list
In this section we will learn how to get all tables name from database
DB connection file: dbcon.py
#For connection with database using pymysql library
import pymysql
try:
conn = pymysql.connect(host='localhost',user='root', password='', database='dbdemo')
dbcon=conn.cursor()
print("Database connected................!!!")
except:
print("Database connection failed...........???")
Process file: showtable.py
#How to show ot get all tables name from database using command line
from dbcon import conn, dbcon #We have to import database connection file from dbcon.py
try:
countdb = dbcon.execute("SHOW TABLES") #show tables
print(f"There are {countdb} database in database")
for db in dbcon:
print(db)
print(db[0]) #This will show you vertical list
#print(db[0],end=',') #This will show you horizontal list
conn.commit()
except:
conn.rollback()
dbcon.close()
Run command in console : python showtable.py
Insert single data in database
In this section we will learn how to insert single data in database by running python script in console.
DB connection file: dbcon.py
#How to create connection with database using pymysql library
import pymysql
try:
conn = pymysql.connect(host='localhost',user='root', password='', database='mydata')
dbcon=conn.cursor()
print("Database connected................!!!")
except:
print("Database connection failed...........???")
# dbcon.close() #// This must be commented if we are importing this file on another file -->
Process file: insert.py
#How to insert data in batabase using python in command line
from dbcon import conn, dbcon #We have to import database connection file from dbcon.py
try:
sql ="INSERT INTO users(username,email,mobile,city, country) VALUES('kaman Kumar','kaman123@demo.com',444444444,'Lunknow','India')"
run = dbcon.execute(sql) #insert new data in database
if dbcon:
print(f"Data has been {run} inserted successfully")
conn.commit()
except:
print("Data has not been inserted")
conn.rollback()
dbcon.close()
Run command: python insert.py
Insert bulk datas in database
In this section we will learn how to insert many datas in database by running python script in console.
DB connection file: dbcon.py
#How to create connection with database using pymysql library
import pymysql
try:
conn = pymysql.connect(host='localhost',user='root', password='', database='mydata')
dbcon=conn.cursor()
print("Database connected................!!!")
except:
print("Database connection failed...........???")
# dbcon.close() #// This must be commented if we are importing this file on another file -->
Process file: insertall.py
#How to insert data in batabase using python in command line
from dbcon import conn, dbcon #We have to import database connection file from dbcon.py
try:
sql ="INSERT INTO users(username, email, mobile, city, country) VALUES(%s, %s, %s, %s, %s)"
data=[
("Haman Kumar","haman@demo.com",111111111,"Chandigarh","India"),
("Eaman Kumar","eaman@demo.com",222222222,"Delhi","India"),
("Laman Kumar","laman@demo.com",333333333,"Lucknow","India"),
("jaman Kumar","jaman@demo.com",444444444,"Hyderabad","India")
]
run = dbcon.executemany(sql,data) #insert multiples data in database with python single command
if dbcon:
print(f"Data has been {run} inserted successfully")
else:
print("Data has not been inserted")
conn.commit()
except:
print("Data has not been inserted")
conn.rollback()
dbcon.close()
Run command: python insertall.py
Fetch single datas from database
In this section we will learn how to fetch data from database by running python script in console.
DB connection file: dbcon.py
#How to create connection with database using pymysql library
import pymysql
try:
conn = pymysql.connect(host='localhost',user='root', password='', database='mydata')
dbcon=conn.cursor()
#print("Database connected................!!!")
except:
#print("Database connection failed...........???")
#dbcon.close() #// This must be commented if we are importing this file on another file -->
Process file: fetch.py
#How to fetch single data in batabase using python in command line
from dbcon import conn, dbcon #We have to import database connection file from dbcon.py
try:
#How to fetch first row user's data from database using python in command line
from dbcon import conn, dbcon
try:
dbcon.execute("SELECT id, username, email, mobile, city, country FROM users WHERE id=4")
# dbcon.execute("SELECT id, username, email, mobile, city, country FROM users")
data = dbcon.fetchone() #fetch first row user's data from database
print(data)
conn.commit()
except:
conn.rollback()
dbcon.close()
Run command: python fetch.py
Fetch all datas from database
In this section we will learn how to fetch all datas from database by running python script in console.
DB connection file: dbcon.py
#How to create connection with database using pymysql library
import pymysql
try:
conn = pymysql.connect(host='localhost',user='root', password='', database='mydata')
dbcon=conn.cursor()
#print("Database connected................!!!")
except:
#print("Database connection failed...........???")
#dbcon.close() #// This must be commented if we are importing this file on another file -->
Process file: fetchall.py
#How to fetch all the data from database using python in command line
from dbcon import conn, dbcon #We have to import database connection file from
try:
dbcon.execute("SELECT * FROM users")
rows = dbcon.fetchall()
for data in rows:
print(data)
conn.commit()
except:
conn.rollback()
dbcon.close()
Run command: python fetchall.py
Updat data in database
In this section we will learn how to update datas in database by running python script in console.
DB connection file: dbcon.py
#How to create connection with database using pymysql library
import pymysql
try:
conn = pymysql.connect(host='localhost',user='root', password='', database='mydata')
dbcon=conn.cursor()
#print("Database connected................!!!")
except:
#print("Database connection failed...........???")
#dbcon.close() #// This must be commented if we are importing this file on another file -->
Process file: update.py
#How to updata of user's data based on user id in database using python in command line
from dbcon import conn, dbcon #We have to import database connection file from
try:
sql ="UPDATE users SET username='Dammu Kumar', email='damuu@gmail.com', mobile=888888888, city='London',country='UK' WHERE id = 2"
dbcon.execute(sql) #fetch first row user's data from database
print(f"{dbcon.rowcount} Data has been updated successfully ")
conn.commit()
except:
conn.rollback()
dbcon.close()
Run command: python update.py
Delete data from database table
In this section we will learn how to delete data from database table by running python script in console.
DB connection file: dbcon.py
#How to create connection with database using pymysql library
import pymysql
try:
conn = pymysql.connect(host='localhost',user='root', password='', database='mydata')
dbcon=conn.cursor()
#print("Database connected................!!!")
except:
#print("Database connection failed...........???")
#dbcon.close() #// This must be commented if we are importing this file on another file -->
Process file: delete.py
#How to delete user based on user id in database using python in command line
from dbcon import conn, dbcon #We have to import database connection file from
try:
sql ="DELETE FROM users WHERE id = 2"
dbcon.execute(sql) #user will be deleted from database who's id=4 in database table
print(f"{dbcon.rowcount} Data has been updated successfully ")
conn.commit()
except:
conn.rollback()
dbcon.close()
Run command: python delete.py
