In this Python MySQL article we learn about Python MySQL Database with MySQL Connector , for connecting of Python code to Mysql database, there are different libraries, before this i had an article that how you can use MySQLDb with Python, but this time we want to use MySQL Connector.
What is MySQL Connector ?
It is MySQL driver written in Python which does not depend on MySQL C client libraries and implements the DB API v2.0 specification (PEP-249).
Installation
First of all you need to install mysql connector, you can simply use pip for the installation.
|
1 |
pip install mysql-connector-python |
Also you need to download and install Wamp Server, because we want to use Wamp Server as virtual server.
Creating MySQL Database in Python
OK now let’s create our MySQL Database using Python code, we are going to create the database based on the user input.
|
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 as mc try: mydb = mc.connect( host = "localhost", user= "root", password ="" ) dbname = input("Enter Your Database Name : ") cursor = mydb.cursor() cursor.execute("CREATE DATABASE {} ".format(dbname)) print("Database Created Successfully ") except mc.Error as e: print("Database Creation Failed") |
OK the first thing we need to connect our Python code with the local host, as i have already said that we are using Wamp Server, you can use mysql.connector.connect() function for this.
|
1 2 3 4 5 6 7 |
mydb = mc.connect( host = "localhost", user= "root", password ="" ) |
In this code we are going to get the database name from the user input, and according to that input we want to create our database in mysql.
|
1 |
dbname = input("Enter Your Database Name : ") |
For executing the query you need to create the object of the cursor.
|
1 |
cursor = mydb.cursor() |
At the end we need to execute our database creation query.
|
1 |
cursor.execute("CREATE DATABASE {} ".format(dbname)) |
Run your code give the database name, in here i want to create a database at name of geekscoders.
And if you see Wamp Server our database is created.
Learn More on TKinter
- How to Create Countdown Timer with Python & TKinter
- Create GUI Applications with Python & TKinter
- Python TKinter Layout Management
- How to Create Label in TKinter
- How to Create Buttin in Python TKinter
- Build Music Player in Python TKinter
- Python GUI Programming with TKinter
- TKinter VS PyQt, Which one is Good
- Creating Custom Widgets in TKinter
How to Check MySQL Database Connection in Python
After database creation, we want to check the database connection, you can use this code for checking mysql database connection in python.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
import mysql.connector as mc try: mydb = mc.connect( host = "localhost", user='root', password = "", database = "geekscoders" ) print("Connection Established Successfully") except mc.Error as e: print("There Is No Connection") |
Run the code and you will receive positive response from the database, because we have correct name for the database.
How to Insert Data to MySQL Database in Python
Now we want to insert some data in our MySQL database, first of all you need to create a table in your geekscoders database, iam going to call my table name users. and add three fields id, name and age. this is the structure for our users table make sure that you make the id auto increment.
And this is the code for inserting the data.
|
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 27 28 29 30 31 32 33 34 |
import mysql.connector as mc try: mydb = mc.connect( host="localhost", user="root", password="", database = "geekscoders" ) mycursor = mydb.cursor() name = input("Please Enter Your Name : ") age = input("Please Enter Your Age : ") query = "INSERT INTO users (name, age) VALUES (%s, %s)" value = (name, age) mycursor.execute(query, value) mydb.commit() print("Data Inserterd Successfully") except mc.Error as e: print("Unable To Insert Data") |
We want to insert data according to the user input.
|
1 2 |
name = input("Please Enter Your Name : ") age = input("Please Enter Your Age : ") |
This is our query for inserting the data.
|
1 2 |
query = "INSERT INTO users (name, age) VALUES (%s, %s)" value = (name, age) |
Also you need to execute your query and commit the database.
|
1 2 |
mycursor.execute(query, value) mydb.commit() |
Run the code give name and age, this will be the result.
Now check your Wamp Server, we have the data.
How to Select Data From MySQL Database in Python
In this part we want to select or retrieve our data from mysql database in Python using mysql connector. so this is the code for selecting the data.
|
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 27 28 29 30 |
import mysql.connector as mc try: dbname = input("Please Enter Database Name : ") tablename = input("Please Enter Table Name : ") mydb = mc.connect( host="localhost", user="root", password="", database = dbname ) mycursor = mydb.cursor() mycursor.execute("SELECT * FROM {} ".format(tablename)) result = mycursor.fetchall() for data in result: print(data) except mc.Error as e: print("Error") |
We want to select the data according to user input by entering database name and table name.
|
1 2 |
dbname = input("Please Enter Database Name : ") tablename = input("Please Enter Table Name : ") |
This is the query for selecting the data.
|
1 |
mycursor.execute("SELECT * FROM {} ".format(tablename)) |
Also we need to fetch all the data from the cursor object using fetchall() method, if you want one data than you can use fetchone().
|
1 |
result = mycursor.fetchall() |
Run the code and this is the result.
How to Update Data in MySQL with Python
For updating the data you can use this code, just we have connected our python code to the Wamp Server using mysql connector and after that we create the object of cursor and execute the query.
|
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 |
import mysql.connector as mc try: mydb=mc.connect( host="localhost", user="root", password="", database="geekscoders" ) mycursor = mydb.cursor() query = "UPDATE users SET name = 'geeksupdated' WHERE name = 'geekscoders'" mycursor.execute(query) mydb.commit() print(mycursor.rowcount, "record affected") except mc.Error as e: print("Updating Failed ") |
Now check your Wamp Server, our first row is updated.
How to Delete Data From MySQL in Python
So now this is the code for deleting data, and we want to delete our first row in the database table.
|
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 |
import mysql.connector as mc try: mydb=mc.connect( host="localhost", user="root", password="", database="geekscoders" ) mycursor = mydb.cursor() query = "DELETE FROM users WHERE name = 'geeksupdated'" mycursor.execute(query) mydb.commit() print(mycursor.rowcount, "record deleted") except mc.Error as e: print("Deleting Failed") |
Run the code and check your Wamp Server we don’t have the first record in the table.
