In this Python Python Database tutorial we want to learn about MySQL Connector in Python, so as you know that Python is popular language, and you can use Python in different fields like Web Development, GUI Development and scientific computing. some times when you are doing data analysis in Python, than you will need a database like MySQL database, in this article we want to talk about Python MySQL Connector, it is a library that provides an easy and efficient way to connect and interact with MySQL databases using Python.
What is MySQL Connector?
MySQL Connector is a library that allows Python developers to connect and communicate with MySQL databases. this library is written in pure Python and is compatible with both Python 2 and Python 3.
How to Install MySQL Connector ?
Now let’s learn that how we can install MySQL Connector, you can use pip for the installation like this.
|
1 |
pip install mysql-connector-python |
Connecting Python with MySQL Database
Before we interact with MySQL database, we need to establish a connection to it. for this we need to provide the necessary credentials such as host name, username, password and database name. this is an example of how to connect to a MySQL database, in here my database name is codeloop.
|
1 2 3 4 5 6 7 8 9 10 |
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="", database="codeloop" ) print(mydb) |
Now we have our database, make sure that you already have created your database, in our case our database name is codeloop, now it is time to create a table and we want to create books table in our MySQL database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
import mysql.connector # Connect to database mydb = mysql.connector.connect( host="localhost", user="root", password="", database="codeloop" ) # Create cursor object mycursor = mydb.cursor() # Create the books table mycursor.execute('''CREATE TABLE IF NOT EXISTS books ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, author VARCHAR(255) NOT NULL, price DECIMAL(10, 2) )''') print("Table 'books' created successfully") # Close cursor and connection mycursor.close() mydb.close() |
Now if you see we have this table, but we don’t have any data.
After that we have a connection to MySQL database, and we can execute SQL queries to read or modify data. the simplest way to execute a query is to create a cursor object from the connection and call execute() method like this, now we want to add some data to our Python MySQL database table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="", database="codeloop" ) mycursor = mydb.cursor() sql = "INSERT INTO books (name, author, price) VALUES (%s, %s, %s)" val = ("Codeloop Python", "Codeloop", 100) mycursor.execute(sql, val) mydb.commit() print(mycursor.rowcount, "record inserted.") |
Now if you check your MySQL Database, we have some data.
Now let’s select all data from our database. this code selects all records from books table and prints them to the console. fetchall() method retrieves all the rows returned by the query and returns them as a list of tuples. we use for loop to iterate over the list and print each tuple.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="", database="codeloop" ) mycursor = mydb.cursor() mycursor.execute("SELECT * FROM books") myresult = mycursor.fetchall() for x in myresult: print(x) |
This will be the result
Subscribe and Get Free Video Courses & Articles in your Email