Working with Python &
SQL
Procedure To Follow In Python To Work With MySQL
• Connect to the database.
• Create an object for your database.
• Execute the SQL query.
• Fetch records from the result.
• Informing the Database if you make any changes in the table.
Install mysql.connector for Python
We need mysql.connector to connect Python Script to the MySQL database.
Download the mysql.connector install it on your computer.
Now, check whether you have installed the mysql.connector correctly or not using the
following code.
import mysql.connector
If the above code runs without any errors, then you have successfully installed
mysql.connector, and it is ready to use.
2. Connecting And Creating Database
Now, we will connect to the database using username and password of MySQL. If you don't remember
your username or password, create a new user with a password.
Now, connect to the database using your username and password.
## Connecting to the database
## importing 'mysql.connector' as mysql for convenient
import mysql.connector as mysql
## connecting to the database using 'connect()' method
## it takes 3 required parameters 'host', 'user', 'passwd'
db = mysql.connect(
host = "localhost",
user = "root",
passwd = "dbms"
)
print(db) # it will print a connection object if everything is fine
<mysql.connector.connection_cext.CMySQLConnection object at 0x0000020C26A84C50>
2.1. Creating Databases
Now, we will create a database with the name registrationdata.
To create a database in MySQL, we use CREATE DATABASE database_name statement.
import mysql.connector as mysql
db = mysql.connect(
host = "localhost",
user = "root",
passwd = "dbms"
)
## creating an instance of 'cursor' class which is used to execute the 'SQL' statements in 'Python'
cursor = db.cursor()
## creating a databse called 'registrationdata'
## 'execute()' method is used to compile a 'SQL' statement
## below statement is used to create tha 'datacamp' database
cursor.execute("CREATE DATABASE registrationdata")
2.2. Creating Tables
Creating tables in the database to store the information.
Before creating tables, we have to select a database first.
Use the CREATE TABLE table_name to create a table in the selected database.
import mysql.connector as mysql
db = mysql.connect(
host = "localhost",
user = "root",
passwd = "dbms",
database = "registrationdata"
)
cursor = db.cursor()
## creating a table called 'users' in the 'registrationdata' database
cursor.execute("CREATE TABLE users (name VARCHAR(255), user_name VARCHAR(255))")
3. Inserting Data
Inserting data into table to store it. Use INSERT INTO table_name (column_names) VALUES (data)
statement to insert into the table.
import mysql.connector as mysql
db = mysql.connect( host = "localhost", user = "root", passwd = "dbms",database =
"registrationdata"
)
cursor = db.cursor()
## defining the Query
query = "INSERT INTO users (name, user_name) VALUES (%s, %s)"
## storing values in a variable
values = (“Arul", “Murugan")
## executing the query with values
cursor.execute(query, values)
## to make final output we have to run the 'commit()' method of the database object
db.commit()
print(cursor.rowcount, "record inserted")
4. Select Data
To retrieve the data from a table we use, SELECT column_names FROM table_name statement.
import mysql.connector as mysql
db = mysql.connect( host = "localhost", user = "root",
passwd = "dbms", database = "registrationdata"
)
cursor = db.cursor()
## defining the Query
query = "SELECT * FROM users"
## getting records from the table
cursor.execute(query)
## fetching all records from the 'cursor' object
records = cursor.fetchall()
## Showing the data
for record in records:
print(record)
4. Select Data
UPDATE keyword is used to update the data of a record or records.
UPDATE table_name SET column_name = new_value WHERE condition statement is used to update the
value of a specific row.
import mysql.connector as mysql
db = mysql.connect(
host = "localhost",
user = "root",
passwd = "dbms",
database = "registrationdata"
)
cursor = db.cursor()
## defining the Query
query = "UPDATE users SET name = 'Arun' WHERE id = 1"
## executing the query
cursor.execute(query)
## final step to tell the database that we have changed the table data
db.commit()
