Connect Python with MySQL
Firstly we have to connect the MySQL server to Python using Python MySQL Connector, which is a Python driver that integrates Python and MySQL. To do so we have to install the Python-MySQL-connector module and one must have Python and PIP, preinstalled on their system.
Installation:
To install Python-mysql-connector type the below command in the terminal.
pip install mysql-connector-python
Connecting to MySQL server:
import mysql connector and connect to MySQL server using connect() method.
# import mysql-connector to connect MySQL server
import mysql.connector
conn = mysql.connector.connect(
host='localhost',
user='username',
password='password'
)
# check if connected or not
print(conn)
# disconnect to server
conn.close()
Create Database:
After establishing connection to MySQL server create database by creating a ‘cursor()’ object and execute commands using ‘execute()’ method. Command to create database is,
CREATE DATABASE DATABASE_NAME
creating MySQL database in python.
import mysql.connector
conn = mysql.connector.connect(
host='localhost',
user='username',
password='password'
)
# creating cursor object
cursor = conn.cursor()
# creating database
cursor.execute("CREATE DATABASE GFG")
Output:
Create Table:
Command for creating a table is,
CREATE TABLE (
col_name_1 data_type,
col_name_2 data_type,
:
:
col_name_n data_type );
Python code for creating table,
import mysql.connector
conn = mysql.connector.connect(
host='localhost',
user='username',
password='password'
database='gfg'
)
# creating cursor object
cursor = conn.cursor()
# creating table
table_st = """CREATE TABLE GEEKS (
NAME VARCHAR(20) NOT NULL,
ID INT,
LANGUAGE VARCHAR(20)
)"""
# created
cursor.execute(table_st)
# display created tables
cursor.execute("show tables")
# disconnect from server
conn.close()
Output:
Inserting data into table:
Insert into query is used to insert table data into MySQL table. Command used to insert data into table is,
INSERT INTO TABLE_NAME ( COL_1,COL_2,....,COL_N)
VALUES ( COL1_DATA,COL2_DATA,......,COLN_DATA)
Python code for inserting data into tables,
import mysql.connector
conn = mysql.connector.connect(
host='localhost',
user='username',
password='password'
database='gfg'
)
# creating cursor object
cursor = conn.cursor()
# insert command/statement
insert_st = """INSERT INTO GEEKS(NAME,ID,LANGUAGE)
VALUES("geek1","1234","eng")"""
# row created
cursor.execute(insert_st)
# save changes
conn.commit()
# disconnect from server
conn.close()
Output:
Insert Multiple Rows in MySQL Table in Python
MySQL is an open-source, Relational Database Management System (RDBMS) that stores data in a structured format using rows and columns. It is software that enables users to create, manage, and manipulate databases. So this is used in various applications across a wide range of industries and domains.
To run the MySQL server in our systems we need to install it from the MySQL community. In this article, we will learn how to insert multiple rows in a table in MySQL using Python.
To install the MySQL server refer to MySQL installation for Windows and MySQL installation for macOS.
Contact Us