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:

MySQL Connect using Pyodbc 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: 

MySQL Data Show in Listbox in Python



(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:

MySQL Connect using mysql.connector


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.
MySQL Data in ListBox using column name in python


(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.

0 comments: