MySQL Connection in Python with MYSQL Select Query in Python

When we want to connect MYSQL Database with application then we have to first consider about MYSQL Connection Driver and MySQL ConnectionString. So here we will describe MYSQL Database Connection in Python or MYSQL connection in Python with database in three ways. 

1. Using Pyodbc in MYSQL Connection
2. Using mysql.connector for MYSQL Connection
3. Using pymysql for MYSQL Connection

Fist thing we have to know that if we want to connect any type of Database organization, then we have to use Driver from particular database organization like MYSQL. So, Lets Start from pyodbc in python.

(1). Using Pyodbc in MYSQL Connection :

For using pyodbc, you have to install pyodbc through pip. Now, lets know the following connection string of pyodbc.

pyodbc.connect("DRIVER={Driver_Name}; User=Server_User; Password=server_Password; Server=localhost/server IP; Database=DatabaseName;")

So, Write following code to show database table’s record in following way:

import pyodbc 

mysqlcn = pyodbc.connect("DRIVER={MySQL ODBC 8.0 ANSI Driver}; User=sko; Password=xxxxxx; Server=xxx.xxx.xxx.xx; Database=mydatabase; ")
dbcursor = mysqlcn.cursor() 
dbcursor.execute("SELECT * FROM TestTable") 
myfield = dbcursor.fetchone() 
# Loop to fetch one by one data.
while myfield:
      # Print partuculat column data from table
print (myfield[0]) 
myfield = dbcursor.fetchone()

#Now Close cursor and connection
dbcursor.close()
mysqlcn.close() 

In this code we use “MySQL ODBC 8.0 ANSI Driver” in pyodbc. 

This will show following output:

Show MySQL Data in Listbox in Python

If you want to show data or you can use MYSQL Select Query in Python to show data in Listbox then following code will be help in you coding.

Show Database Record in Listbox:

import tkinter as mytk
from tkinter import *
import pyodbc 

MytkForm = mytk.Tk()
MytkForm.title('MySQL with pyodbc')
MytkForm.geometry("250x100")

# Create Listbox
MyTklist = mytk.Listbox(MytkForm)
#Clear Listbox
MyTklist.delete(0,END)
mysqlcn = pyodbc.connect("DRIVER={MySQL ODBC 8.0 ANSI Driver}; User=sko; Password=xxxxxx; Server=xxx.xxx.xxx.xx; Database=mydatabase;")
dbcursor = mysqlcn.cursor() 
#MySQL Select Query in Python
dbcursor.execute("SELECT * FROM TestTable") 
myfield = dbcursor.fetchone() 
while myfield:
    # Add Field data in Listbox
    MyTklist.insert(END,myfield[0]) 
    myfield = dbcursor.fetchone()

dbcursor.close()
mysqlcn.close()
MyTklist.pack()

MytkForm.mainloop()

OUTPUT: 

(2). Using mysql.connector for MYSQL Connection

In this point we will learn how to use mysql.connector for database connection and also learn how to List MYSQL data through column name in python. 

Mysql.connector ConnectionString in Python

mysql.connector.connect(host="XXX.XXX.XXX.XX", user="Database_User", password="Database_Password", database="DatabaseName")

Now Lets, see how to show data?

import mysql.connector
#ConnectionString
mydb = mysql.connector.connect(host="XXX.XXX.XXX.XX", user="sko", password="mypassword", database="mydatabase")
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM TestTable")
myresult = mycursor.fetchall()
for mycol in myresult:
   # Get Second Column's row List
   print(mycol[1])

mydb.close()
mycursor.close() 

OUTPUT:

In this given output, you can see, we show only second column data through “mycol[1]” this code. 

MySQL Column Value show through Column Name in Python in Listbox: 

As you saw before, we are showing data through Column Number, but if we have more columns and want to show particular column value then we have to determine Column Name. 

So, through following way you can use to show column value through Column Name. You have to add following code in cursor method.

mycursor = mydb.cursor(dictionary=True)

Lets see with full details:

import mysql.connector
import tkinter as mytk
from tkinter import *

MytkForm2 = mytk.Tk()
MytkForm2.title('MySQL with mysql.connector')
MytkForm2.geometry("270x120")

MyListData= mytk.Listbox(MytkForm2)
mydb = mysql.connector.connect(host="XXX.XXX.XXX.XX", user="sko", password="mypassword", database="mydatabase")

#Fetch Particular Column Value through Column name with using dictionary
mycursor = mydb.cursor(dictionary=True)

mycursor.execute("SELECT * FROM TestTable")
myresult = mycursor.fetchall()
for MyColName in myresult:
    MyListData.insert(END, MyColName ["MyDetails"]) 
mydb.close()
mycursor.close() 
MyListData.pack()

MytkForm2.mainloop()

Now, This will Show output according to Column Name.

(3). Using Pymysql for MYSQL Connection

You can use another connector in python that's call pymysql. First you have to install pymysql. After that you have to use following connection string.

pymysql.connect(host="XXX.XXX.XXX.XX", user="MySQLUsername", password="MYSQLPassword", database="MYSQLDatabase")

Then write following code to show data in Python.

import pymysql

# database connectionstring
connmysql = pymysql.connect(host="XXX.XXX.XXX.XX", user="sko", password="mypassword", database="mydatabase")
cursordb = connmysql.cursor()

# execute your query through execute().
cursordb.execute("SELECT * FROM TestTable")

# get all row through fetchall().
data = cursordb.fetchall()
for colnm in data:
    print(colnm[0])

# close connection
connmysql.close()

OUTPUT:

Conclusion: You can use any one of these method. You will find some performance differences. But you will find exact result using any connector in python.

So, this way you can Connect MYSQL in Python with step by step process.

• Tweet
• Share
• Share
• Share