In this post, we will see how to connect a Python application with the MySQL Db called “DbManagerUser”, created in the post: MySQL – How to create a DB.
First of all, in order to install the MySQL connector for Python, we run the command pip install mysql-connector-python.
Then, we open Visual Studio Code and we define an entity called “UserItem” and a Python script called “CoreForMySQL”:
[USERITEM.PY]
class UserItem:
def __init__(self, userid, username, usertype):
self.UserId = userid
self.UserName = username
self.UserType = usertype
[COREFORMYSQL.PY]
import mysql.connector
from UserItem import UserItem
def ExtractListUsers():
# define list of Users
lstUser = []
try:
# MySQL server connection string definition
connect = mysql.connector.connect(user='test', password='$Test123',
host='127.0.0.1',
database='DbManageUser')
# Query definition
strSql = "select A.UserId, A.UserName, B.UserType from TabUser A inner join TabUserType B on A.UserType = B.IdUserType"
# Cursor definition used to run the query
cursor = connect.cursor()
# getting records from the query
cursor.execute(strSql)
# fetching all records from the cursor
records = cursor.fetchall()
# Putting all data in the lstUser array
for item in records:
objUser = UserItem(item[0], item[1], item[2])
lstUser.append(objUser)
# connection close
connect.close()
return lstUser
# define expects
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Access denied")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database does not exist")
else:
print(err)
# users list
listUser = ExtractListUsers()
# read the list
for item in listUser:
print(f"ID:{item.UserId} / UserName:{item.UserName} / Type:{item.UserType}")
Now, if we run the script, this will be the result:
