In this tutorial, you'll learn how to install MySQL, connect, create databases, and create tables, along with much more.
In order to work with MySQL using Python, you must have some knowledge of SQL
Before diving deep, let's understandWhat is MySQL?
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.How to Install MySQL
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-126.96.36.199.i386.rpm
To check in Linux
Install MySQL in Windows
Download MySQL database exe from official site and install as usual normal installation of software in Windows.Install MySQL Connector Library for Python
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-connectorTest the MySQL Database connection with Python
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" )
import mysql.connector db_connection = mysql.connector.connect( host="localhost", user="root", passwd="root" ) print(db_connection)
<mysql.connector.connection.MySQLConnection object at 0x000002338A4C6B00>
Here output shows the connection created successfully.Creating Database in MySQL using Python
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)
Here above image shows the my_first_db database is createdCreate a Table in MySQL with Python
Let's create a simple table "student" which has two columns.
CREATE TABLE student (id INT, name VARCHAR(255))
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)
('student',)Create a Table with Primary Key
Let's create an Employee table with three different columns. We will add a primary key in id column with AUTO_INCREMENT constraint
CREATE TABLE employee(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), salary INT(6))
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)
('employee',) ('student',)ALTER table in MySQL with Python
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.
ALTER TABLE student MODIFY id INT PRIMARY KEY
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")
Here below you can see the id column is modified.
Insert Operation with MySQL in Python:
Let's perform insertion operation in MySQL Database table which we already create. We will insert data oi STUDENT table and EMPLOYEE table.
INSERT INTO student (id, name) VALUES (01, "John") INSERT INTO employee (id, name, salary) VALUES(01, "John", 10000)
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")
2 Record Inserted
Thanks for reading ❤
If you liked this post, share it with all of your programming buddies!
When you plan to develop a website, then you look forward to getting a complete solution, and hiring a Web Development Company in Chicago is the best result for this need.
Do you wonder what a full-stack developer is?
Let’s understand it further with this post…
A full stack developer is the one who apprehends the complete web development process and can fit with his expertise on any level of the development process.
However, finding a highly qualified full-stack developer is a tad tricky part of the business, as you need to understand and adopt the right technical expertise to initiate your web project.
If you are worried of picking the wrong resource, which could only hamper your business revenue goal through the website development then you need to consider the right approach suggested in this post, which can further help you hire full stack developer for your specific business needs.
What Does All Full Stack Developer do?
When it comes to web application development, then there is a different set of development, like for frontend and backend. They can be bifurcated into two basic divisions; one who builds the interfaces and rest who builds software engines behind it.
A full stack web developer can perform all these aspects, ranging from databases, front-end (client side) to server-side scripts. These developers have the capability of coding and even designing to a certain extent, also they can manage the complete project.
Full Stack Developers Are Responsible For:
Technical expertise of a full-stack developer involves:
Hiring a Full Stack programmer from HireFullStackDeveloperIndia is a lucrative deal, as we have a strong brood of highly experienced developers, who understand every bit of technology and the new market trends, and accordingly, update their knowledge bank.
Our development procedure involves a flexible assignation of different expertise of technologies and has catered our development services to many renowned brands globally.
Our team of full-stack developers is well-equipped with the complete knowledge of front-end and back-end technologies, which together offer a great solution. Our team holds the expertise to handle and manage the entire development and implementation process, whether it is for a website, web app or mobile app.
We are the master of many different technologies and have comprehensive knowledge about pertinent technologies consisting of MEAN stack, front end, back end, UI, server infrastructure, etc.
This is the sole reason, we are picked by a maximum number of businesses across the globe, needless to say, and we are competent enough to accomplish the entire development process based on the requirements of the esteemed clientele.
So without any further delay, simply get in touch with us, and help your business to scale higher…
You can hire the extremely talented and experienced team of full stack developer to form us at the most reasonable cost of $12-$18 hr and mark a change in your business with an effective web solution.
Best Mobile App Development Company India, WebClues Global is one of the leading web and mobile app development company. Our team offers complete IT solutions including Cross-Platform App Development, CMS & E-Commerce, and UI/UX Design.
We are custom eCommerce Development Company working with all types of industry verticals and providing them end-to-end solutions for their eCommerce store development.
Know more about Top E-Commerce Web Development Company