In order to work with MySQL using Python, you must have some knowledge of SQL
Before diving deep, let's understand
MySQL is an Open-Source database and one of the best type of RDBMS (Relational Database Management System). Co-founder of MySQLdb is Michael Widenius's, and also MySQL name derives from the daughter of Michael.
Install MySQL in Linux/Unix:
Download RPM package for Linux/Unix from Official site: https://www.mysql.com/downloads/
In terminal use following command
rpm -i <Package_name>
Example rpm -i MySQL-5.0.9.0.i386.rpm
To check in Linux
mysql --version
Install MySQL in Windows
Download MySQL database exe from official site and install as usual normal installation of software in Windows.
For Python 2.7 or lower install using pip as:
pip install mysql-connector
For Python 3 or higher version install using pip3 as:
pip3 install mysql-connector
To test database connection here we use pre-installed MySQL connector and pass credentials into connect() function like host, username and password.
Syntax to access MySQL with Python:
import mysql.connector db_connection = mysql.connector.connect( host="hostname", user="username", passwd="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>
Here output shows the connection created successfully.
Syntax to Create new database in SQL is
CREATE DATABASE "database_name"
Now we create database using Python in MySQL
import mysql.connector db_connection = mysql.connector.connect( host= "localhost", user= "root", passwd= "root" ) # creating database_cursor to perform SQL operation db_cursor = db_connection.cursor() # executing cursor with execute method and pass SQL query db_cursor.execute("CREATE DATABASE my_first_db") # get list of all databases db_cursor.execute("SHOW DATABASES") #print all databases for db in db_cursor: print(db)
Output:
Here above image shows the my_first_db database is created
Let's create a simple table "student" which has two columns.
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() #Here creating database table as student' db_cursor.execute("CREATE TABLE student (id INT, name VARCHAR(255))") #Get database table' db_cursor.execute("SHOW TABLES") for table in db_cursor: print(table)
Output:
('student',)
Let's create an Employee table with three different columns. We will add a primary key in id column with AUTO_INCREMENT constraint
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() #Here creating database table as employee with primary key db_cursor.execute("CREATE TABLE employee(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), salary INT(6))") #Get database table db_cursor.execute("SHOW TABLES") for table in db_cursor: print(table)
Output:
('employee',) ('student',)
Alter command is used for modification of Table structure in SQL. Here we will alter Student table and add a primary key to the id field.
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() #Here we modify existing column id db_cursor.execute("ALTER TABLE student MODIFY id INT PRIMARY KEY")
Output:
Here below you can see the id column is modified.
Let's perform insertion operation in MySQL Database table which we already create. We will insert data oi STUDENT table and EMPLOYEE table.
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)" #Execute cursor and pass query as well as student data db_cursor.execute(student_sql_query) #Execute cursor and pass query of employee and data of employee db_cursor.execute(employee_sql_query) db_connection.commit() print(db_cursor.rowcount, "Record Inserted")
Output:
2 Record Inserted
Thanks for reading ❤
If you liked this post, share it with all of your programming buddies!
Follow us on Facebook | Twitter
☞ Python Programming Tutorial - Full Course for Beginners
☞ MySQL Database Tutorial for Beginners to Advanced Part 1/2
☞ Python Connect MySQL Database
#python #mysql #database #web-development