👉Python with MySQL Connectivity
Python with MySQL Connectivity: Integrating Python with MySQL is essential for managing data efficiently in various applications. This guide covers MySQL installation, connector setup, and performing database operations with Python.
👉What is MySQL?
Python with MySQL Connectivity: MySQL is an open-source RDBMS (Relational Database Management System) widely used for handling large data volumes. It was co-founded by Michael Widenius, and its name is derived from his daughter “My.”
👉How to Install MySQL Connector Python on Windows and Linux
👉Install MySQL in Linux/Unix:
👉Python with MySQL Connectivity
- Download the RPM package from the official MySQL site: MySQL Downloads
- Use the following command in the terminal:
rpm -i <Package_name>
Example: rpm -i MySQL-5.0.9.0.i386.rpm 3. Verify installation using:
mysql –version
👉Python with MySQL Connectivity Install MySQL in Windows:
- Download the MySQL database .exe file from the official site.
- Follow the standard installation steps.
👉Installing MySQL Connector Library for Python
👉For Python 2.7 or lower:
pip install mysql-connector
👉For Python 3 or higher:
pip3 install mysql-connector
👉Testing MySQL Database Connection with Python
To test the database connection, use the connect() method with credentials like host, username, and password.
👉Example:
import mysql.connector
db_connection = mysql.connector.connect(
host=”localhost”,
user=”root”,
passwd=”root”
)
print(db_connection)
👉Output: <mysql.connector.connection.MySQLConnection object at 0x000002338A4C6B00>
👉Creating a Database in MySQL Using Python
👉Syntax:
CREATE DATABASE “database_name”
👉Example:
import mysql.connector
db_connection = mysql.connector.connect(
host=”localhost”,
user=”root”,
passwd=”root”
)
db_cursor = db_connection.cursor()
db_cursor.execute(“CREATE DATABASE my_first_db”)
db_cursor.execute(“SHOW DATABASES”)
for db in db_cursor:
print(db)
👉Creating a Table in MySQL with Python
👉SQL Syntax:
CREATE TABLE student (id INT, name VARCHAR(255))
👉Example:
import mysql.connector
db_connection = mysql.connector.connect(
host=”localhost”,
user=”root”,
passwd=”root”,
database=”my_first_db”
)
db_cursor = db_connection.cursor()
db_cursor.execute(“CREATE TABLE student (id INT, name VARCHAR(255))”)
db_cursor.execute(“SHOW TABLES”)
for table in db_cursor:
print(table)
👉Output: (‘student’,)
👉Creating a Table with Primary Key
👉SQL Syntax:
CREATE TABLE employee(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), salary INT(6))
👉Example:
import mysql.connector
db_connection = mysql.connector.connect(
host=”localhost”,
user=”root”,
passwd=”root”,
database=”my_first_db”
)
db_cursor = db_connection.cursor()
db_cursor.execute(“CREATE TABLE employee(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), salary INT(6))”)
db_cursor.execute(“SHOW TABLES”)
for table in db_cursor:
print(table)
Output: (’employee’,) (‘student’,)
👉Altering Tables in MySQL with Python
👉SQL Syntax:
ALTER TABLE student MODIFY id INT PRIMARY KEY
👉Example:
import mysql.connector
db_connection = mysql.connector.connect(
host=”localhost”,
user=”root”,
passwd=”root”,
database=”my_first_db”
)
db_cursor = db_connection.cursor()
db_cursor.execute(“ALTER TABLE student MODIFY id INT PRIMARY KEY”)
👉Insert Operation with MySQL in Python
👉SQL Syntax:
INSERT INTO student (id, name) VALUES (01, “John”)
INSERT INTO employee (id, name, salary) VALUES (01, “John”, 10000)
👉Example:
import mysql.connector
db_connection = mysql.connector.connect(
host=”localhost”,
user=”root”,
passwd=”root”,
database=”my_first_db”
)
db_cursor = db_connection.cursor()
student_sql_query = “INSERT INTO student(id, name) VALUES(01, ‘John’)”
employee_sql_query = “INSERT INTO employee (id, name, salary) VALUES (01, ‘John’, 10000)”
db_cursor.execute(student_sql_query)
db_cursor.execute(employee_sql_query)
db_connection.commit()
print(db_cursor.rowcount, “Record Inserted”)
👉Output: 2 Record Inserted.
