How To Connect MySQL Database In Python
What is Python
Python is an object-oriented high-level programming language and is used for rapid application development, due to the high-level built-in data structure it can be used as a scripting language to connect components together. The language is very simple to learn and there is no cost will be spent on program maintenance, the python has an extensive free library available in source or binary form. Most of the programmers prefer python because it offers increased productivity and the edit-test-debug cycle is extremely fast. Python supports a wide range of servers such as GadFly, mSQL, MySQL, Informix, Microsoft SQL Server 2000, and much more.
What Is MySQL Database
MySQL is the open-source database famous all around the world due to its key factors such as performance, ease of use and functionality, MySQL has now become perfect for web-based applications like Facebook, Twitter, and yahoo. It is the free source Relational Database Management System (RDBMS) and it uses Structured Query Language (SQL). it was first developed and marketed by a Swedish company called MYSQL AB and it has an open-source license that can be used by all types of business. MySQL seamlessly works on many OS with many programming languages like python, JAVA, C++, etc. It can support up to 50 million rows in a table with a size limit of up to 8 million terabytes (TB).
Connect MYSQL Database In Python
In this article, you will see information about how to connect to MySQL databases in Python. Before connecting the MySQL database in python we need the following detail of MySQL Server.
In order to connect Python To Work With MySQL, connect the database and create the object then execute the SQL query.
From the official site of MySQL, download and install the MySQL server.
in order to connect Python script to the MySQL database, download the mysql.connector and install it on your computer.
Connecting And Creating
Now connect to the MySQL database using username and password.
If you forget your username and password, use forget password or create a new user.
After the creation of the account, connect to the database using a username and password.
Finally, you are connected to the database.
To create a database, use the CREATE DATABASE and make sure that the database does not already exist, to see all the databases use SHOW DATABASES statement.
To store the information create tables in the database
In order to create tables, Create a code and run the code
The code will be executed and now you are connected to the database
Use CREATE TABLE command to create a table and now tables will be created, if you want to check the tables use the SHOW TABLES statement.
Primary Key is the unique value in the table and it helps you to find the unique value
To create a primary key, use the PRIMARY KEY statement.
Use the statement INT AUTO_INCREMENT PRIMARY KEY to identify each row uniquely with a number starting from 1 to infinity.
To drop the primary key use ALTER TABLE table_name DROP column_name statement
To add Primary Key to the existing table, use ALTER TABLE table_name ADD PRIMARY KEY(column_name)
For inserting data, Use INSERT INTO table_name (column_names) VALUES (data) statement and you can insert single or multiple row as per your convenience.
SELECT column_names FROM table_name statement is used to retrieve the data
To find a certain word use WHERE.
Use ORDER BY, Use SELECT column_names FROM table_name ORDER BY column_name statement and SELECT column_names FROM table_name ORDER BY column_name DESC statement to sort the result in ascending and descending order.
To DELETE records from the table, use DELETE FROM table_name WHERE condition statement.
Use update table_name SET column_name = new_value WHERE condition statement to update the data.
using connect() function
Install the mysql.connector and Error objects
use the connect() function to connect to the MySQL Server and it will accept four parameters: host, database, user and password.
Then it establishes a connection to the python_mysql database and returns a MySQLConnection object.
by using the is_connected() method, you can check if the connection has been established.
In case of any error, use try-except block
Finally close, using the close() method of the MySQLConnection object
Finally you connected MySQL database with python and you can now work on the database.