Complete Guide: Connecting MySQL Database with Python using mysql.connector, SQLAlchemy, and Pandas

Namaste friends I hope you are doing fine are you struggling with establishing a connection in Python connecting python to a mySQL database involves using a suitable python library to establish the connection and interact with the database while connecting python to a mySQL database you may encounter some common problems including incorrect connection parameters ensure that you provide accurate host user password and database name values that match your database configuration access privileges make sure the user specified in the connection has the necessary privileges to access the database network of firewall issues check that you can establish a network connection to the MySQL server and that any firewalls are not blocking the connection incompatible versions ensure that the installed python package is compatible with the version of MySQL you are using encoding issues if you encounter encoding related problems ensure that the character encoding settings of the database and python are compatible missing dependencies verify that you have installed all the required dependencies such as the MySQL client library or other system level dependencies remember to handle exceptions and errors appropriately in your code to gracefully handle any issues that may arise while connecting to the mySQL database I am subhojit and I welcome you to our tutorial video on setting up a connection to a mySQL database using Python and effectively debugging any errors that may arise along the way throughout this tutorial I'll provide clear and concise explanations along with relevant examples to ensure that you fully understand the concepts I encourage you to follow along and try the code Snippets yourself as hands-on experience is the key to Mastery I use Visual Studio code AKA vs code for my coding requirements you may use pycharm or Jupiter notebook or any of your favorite coding tool to practice along vs code offers several benefits that make it a popular choice amongst developers cross-platform support vs code is available for Windows Mac OS and Linux allowing developers to work seamlessly across different operating systems lightweight and fast vs code is known for its Speed and Performance it has a lightweight architecture making it quick to start up and responsive even when working with large projects extensibility and customization vs code provides a vast ecosystem of extensions that enhance its functionality you can customize and tailor your coding environment by installing extensions for specific languages Frameworks and tools intellisense and code completion vs code offers intelligent core completion and suggestions helping developers write code more efficiently it provides contextual hints Auto completion and documentation pop-ups which can significantly speed up the coding process integrated terminal es code includes an integrated terminal allowing developers to run commands execute scripts and perform other tasks without leaving the editor this feature enhances productivity by providing a seamless development experience debugging support vs code provides robust debugging capabilities for various programming languages it supports breakpoints variable inspection step through debugging and other essential debugging features to download and install Visual Studio code follow these steps on your browser go to the official Visual Studio code website at code.visualstudio.com download on the website you will see a big download for your operating system button click on the button that corresponds to your operating system Windows Mac OS or Linux after clicking the download button the installer file start downloading once the download is complete locate the installer file in your computer's downloads folder or the specified destination folder double click on the installer file to run it follow the on-screen instructions to complete the installation launch the application and you will see this screen before proceeding with Visual Studio code I would like to highlight a concept called an environment to create an environment in Python you can use a virtual environment tool called venv if you are using Anaconda you can use the tool called conda these tools allow you to create isolated environments where you can install specific versions of packages without affecting your system-wide python installation I'll go to Windows Explorer and I'll create a new folder named test Python scripts I right click create new folder test the score python or clips I'll open this folder copy the address from the address bar and in the windows search bar I'll type CMD to open the command prompt and here I'll type command python Dash m p e n v and the folder path that we copied I'll press enter and it gives me an error message saying unable to create directory over here if you see my username it's space Chandra and python doesn't like faces so this time what I'll do is I'll encapsulate the path within double quotes and execute the command once again press enter and in the background you will see within the folder some new folders and a file is created we'll wait for this to return to the command prompt so now it is ready we'll now come back to the visual studio code window over here there is an environment called tutorials that is selected to select a different environment I'll click on open folder I created this environment test underscore python underscore scripts I'll select it and now this will change to test Python scripts and these are the components within that particular folder I'll click on this new file and type the name MySQL underscore connect s41 dot pi any python file has the extension dot py I'll press enter and here is the editor window here's a description of the main components of the vs code screen activity bar situated along the side of the window the activity bar consists of icons representing different views and functionalities of PS code the default icons include Explorer Source control run and debug extensions and search sidebar the sidebar appears on the left side of the window and contains additional views and panels the default sidebar includes Explorer which display the file and folder structure of your project and other panels like Source control extensions and outline editor area this is the central part of the screen where you write your code the editor area is tabbed allowing you to work with multiple files simultaneously each tab represents an open file and you can switch between them by clicking on the respective tabs integrated terminals vs code includes an integrated terminal at the bottom of the window it allows you to execute commands run scripts and perform various tasks without leaving the editor you can now start writing and executing python code within the virtual environment any packages you install will be isolated within this environment and won't affect your system-wide python installation we will now move on to the next important topic called packages a python package is a way of organizing and distributing python modules and related code to facilitate code reuse and modular programming it provides a structured hierarchy for organizing python code into directories and files allowing for better organization maintenance and sharing of code across projects some packages are a part of the Python installation on your computer and it gets transferred to the environment that you create for example if I were to import a package called OS it will do so easily because the moment I created this environment it got transferred into this but if I were to import mysql.connector you will see that the visual code editor has highlighted it and if you hover the mouse on this particular package it says it is not accessed in pylans which means even though it is inside my system it is not a part of this particular environment so I'll have to install it to download python packages you can use the python package manager called pip tip allows you to easily search download and install packages from the python package index which is Pi Pi dot org or any other package repositories on pipi.org if I type MySQL dot connector and press enter this is the connector that is available I can also install it from the command prompt in this search box I'll type PMD run as administrator yes and if I type if install I SQL dot connector it says that the package is already installed on my system however since this is not present in this environment I'll come over here in the terminal window and type pip install MySQL dot connector and press enter on the command prompt like we saw it is installed on my system but it's not present in this particular environment so therefore it is downloading and it is installing it within this particular environment got a message successfully installed mysql.connector version 2.2.9 along with that we also got a message that there is a new release of pip installer available this is the command which is supposed to be executed so I'll copy this paste it in the terminal window and let's upgrade the PIP installer by pressing enter it will uninstall the older version of pip that is 22.3 and then it will attempt to install 23.1.2 so it has now successfully upgraded the PIP installer we saw that this was highlighted but now that highlighter is gone which means this package is available in this particular environment now that we have imported the mysql.connector into this editor window we will create a connection string a connection string is declared in a variable and we will type MySQL dot connector dot connect open parenthesis PS code editor will close the parenthesis automatically now we will type the parameters host equal to localhost comma user equal to trial one which is the username of the database password equal to come at one two three mama database equal to trial underscore schema we'll press enter next we will create a sir object which will hold the connection dot cursor next we will add the SQL statement cursor dot execute open parenthesis select star from trial underscore [Music] Dot the table name Trail underscore data after that we need an object which will hold all the records that it will fetch so records equal to cursor Dot H all open parenthesis and close parenthesis basically it's a list and we will display it in our terminal window print records after that we would want to close the cursor object cursor Dot close then we will close the connection and that's it we'll try to execute this and find out if we get any errors so I'll click on run now there are two options start debugging and run without debugging if you click on run without debugging this will execute from the start to the end without stopping anywhere so I'll click on it and in the terminal window you will see that the code is being executed and it has given some error so this is the error authentication plugin caching sh8 underscore password is not supported now here comes a scenario wherein you are using one package but actually you were supposed to use a different package so mysql.connector is the package which we installed but instead we are supposed to use my SQL Dash connector Dash python I'll press enter and this is now downloading and installing the MySQL connector for python in this environment it has successfully installed it if you are overwhelmed with the content in the terminal window you can come over here type clear and this will become a clean slate let's try running this again now this time it says you have an error in your SQL syntax check the manual that corresponds to your MySQL version for the right syntax to use near dot data okay so the error with the caching stuff is gone now here the table name is incorrect I'll just correct it hit the underscore and let's execute this again so these are the records which it fetched from the table in the database we can tweak this particular piece of code further import mysql.connector as MSC and we will replace this mysql.connector with MSC dot connect it works the same we can add comments to this particular code with a hash sign that's it any changes we make it is highlighted over here on the Explorer icon which says one unsaved file the moment I save this this highlighted part is gone which means there are no unsaved changes in this particular window before we proceed you can help me boost my confidence please like the video if you like the video you can subscribe to the channel to get notified on new content share your thoughts in the comments section share the video with your friends and colleagues let's try creating the same code with two more packages SQL Alchemy and pandas I'll click on this new file button again and this time I'll create a file MySQL underscore connect underscore 2 dot i press enter and this opens up in a new tab for this particular code we will have to import two packages in the previous code we used import here we will import it in a different way from SQL Alchemy import V8 underscore engine so we can either import the entire package or we can just import parts of the package using this particular statement and the next is import and as as e d which is the Alias name if you notice both of these packages are not a part of this particular environment so we will have to install it first I'll clear the terminal window and then clip install SQL Alchemy enter so it's downloading and installing the packages it has now successfully installed the SQL Alchemy package next I'll install pandas enter so it has successfully installed the pandas package as well if we do not want to install these one by one we can go ahead type pip install SQL hear me space pandas press enter since we have already installed these in this environment it is saying requirement already satisfied but you can see that we can install multiple packages with a single pip install command like this so the highlights are gone and we can now use these packages in our code in this code I will store the information of the database in variables and then create a connection string out of it when we use the engine of SQL Alchemy there is a certain syntax which we use to create this particular connection string we call it a database URL and the URL should follow a format like this which I will be entering into a comment over here I'll just comment it out so MySQL colon front slash front slash user colon password at Host colon Port database so let's start creating it I'll put it in a variable pnx equal to create engine my SQL Plus I my SQL an front slash front slash Plus username username is the name of the variable which we have declared then again a colon Plus password variable Plus at the rate Plus host name variable Plus colon Plus now the port is numeric which we will have to convert it into string Str function and give the port a variable inside it Plus front slash Plus the database which is trial underscore schema present in the database variable we can print this particular string in the terminal window to find if there are any issues with it I'll put a hash comment this out and print the string on screen let's see if it complies with this over here so run and without debugging so MySQL plus pi MySQL colon front slash front slash and it is correct so now I'll remove this command and try running it so it says no module Pi MySQL and it gives an error let's install this particular module tip install by MySQL successfully installed by MySQL let's run this again so we don't have the error to connect to the database so con equal to pnx Dot connect let's see if it connects that is a huge error over here again can't connect to mySQL server on at the rate one two three at localhost so here's the catch we are using at the rate in this particular string for representing the host name but before that we have the password and the password has at the rate again which means the moment it identifies that there is at the rate over there it will think that whatever is present after at the rate it will be the hostname but in our case the host name is only localhost and not 123 at localhost so we'll have to replace this at the rate with percent 40 which is nothing but an escape character and I'll run this to see if this time it creates the connection without any problems so now this particular error is also gone next comes pandas we will use pandas to execute the SQL query on the database so let's write the SQL query you will and query is the variable equal to PD if you remember we just use the Alias PD over here while importing the package PD dot read underscore SQL underscore query open parenthesis and over here we will put the SQL query select trial underscore schema Dot underscore data and the connection is on CE this SQL query is run we will get the records which we will save in our data frame so PD Dot data frame open parenthesis and we will pass the SQL query variable over here next we would want to print the output so we can print TF the data frame itself and finally there is just a connection to close so c o n n Dot close let me clear the terminal window and execute this piece of code again so we have got this records it's just like a preview so you don't see all the records but the number of rows are two three zero seven and the number of columns are five I can go ahead and print only the number of rows and columns by typing dot shape after DF over here let me clear this terminal window and once I execute this instead of giving me the actual records it is just giving me the number of rows and columns so this was the code with mySQL connector and over here we can print length of the list which will give us the number of Records it fetched from the database so if I execute this code so it just gave me the number of rows that's it you have successfully used mysql.connector and pandas with SQL Alchemy packages to interact with a mySQL database I hope this video added another tool to your data analysis toolkit we will meet again in another exciting video very soon stay tuned bye bye and take care

Channel: Swachalan Kaar

Views: 2,614

Keywords: Power Query, Power BI, Automation, Data Analysis, Power Platform, Power Automate, Python, MySQL.Connector, MySQL, Pymysql, Pandas, Sqlalchemy

Id: 0k-vEn7KXpo

Length: 34min 38sec (2078 seconds)

Published: Mon Jun 26 2023