Python Database Connection | How to Connect Python with MySQL Database | Edureka

hello everybody this is her shot from in Eureka and in this tutorial I'd be talking about Python database connection along with its practical implementation so let's see what are the offerings of today's session well I'm going to start off today's session by giving you a small introduction about Python and my sequel further which I'm going to talk a bit about integrated connection then I'm going to talk about the internal working logic behind when you actually establish a connection between Python and my sequel DB and last but not the least I am going to end my tutorial by performing third operation switch Python and my sequel so I hope you guys appear with the agenda candy drop in a quick confirmation in the chat box so that I can proceed further alright guys so without wasting further time let me just dive into my tutorial and talk about my first topic that is introduction to Python and my sequel well as we already know Python is one of the most widely used programming language and in demand among developers but you can't merely develop any application without having to know where to store the data so this is where database management system comes into picture and one of the most widely used DBMS server that is also used in the industry practices is my sequel DB server let me move ahead and talk a bit about integrated connection alright guys this is just a glimpse of how the bigger picture will look like what I am trying to do is I'm basically trying to integrate my Python with my back-end database server and then do the further manipulations now let me move ahead and explain you the internal working logic behind when you establish a connection between Python and my sequel DB server or I can say that I'm basically going to explain you the internal working logic that how does it actually connect or work so as you can see there are numerous pictures given in this slide on my extreme right hand side there's a picture of a database and on the extreme left hand side there's a picture of a Python application and in between lies the my sequel connector Python API so my sequel connector python api is nothing but acts as a bridge between my front-end python application and my back-end database server now when I say this word Python application think of any Python application let's say suppose think of a website which a user has developed which lies in the front end now my front-end Python application will basically send a connection request to the my sequel connector python api then the api will forward the same request to the database further which the database will accept the request and send the connected message and then my python application will send a courser connection request so over your think of connection as a method and cursor as an object which basically lets you communicate through your entire my sequel DB server and also lets you create your own database finally my Python application will execute a certain sequel statement or a certain sequel query and for that my back-end database server will fetch the result data so that this was it with the internal working logic that basically behind when the Python and my sequel TV server connection is taking place now let me move ahead and talk a bit about operations which I am going to perform with Python and my sequel so guys I'm basically going to perform read operations but simply means create read update and delete with my Python and my sequel so let me move ahead and show you the implementation of crud operations from coding point of view so let me quickly open my PyCharm all right there's before diving deep into the coding perspective my first and foremost step is to basically import the my sequel connector package and how do I do that I'll click on file click on settings click on project interpreter on the extreme right hand side there's a picture of a plus sign and then type here my sequel - connector all right click on my sequel - connector click on install package it will basically take a minute or two to get installed essentially our first step is to install the my sequel - connector package which forms the stepping stone for our further operations which I'm going to do again it is it will take a minute or two to get installed all right so as you can see our package my sequel - connector has been installed successfully now one of the other approach to install the same thing is suppose this is for those set of users who don't use any Python IDE suppose you are using a Python command line so for them with reopen command prompt once your command prompt is opened quickly go there and type pip install my sequel - connector which is the package and press Enter well it is going to show me or give me a message that is requirement already satisfied since I have already imported the package on my PyCharm ID but this demonstration I am showing for those set of users who simply use Python command line so for them you can go to your command prompt type pip install - equal - connector now my sequin - connector package will be installed and then you can import your same package on your Python command line and then you can move on with the further operations so now it's saying me a warning this is none of our concern guys let me just quickly close it and as I have already created a file under the name of python DB creation i'll move on with my coding demonstration so let me into the presentation mode and start off by writing my first line of code so the first line of code which i am going to write over here is I'm simply going to import the package which I installed just now that is my sequel - connect up I'm sorry it will be my sequel dot connector now I'm going to create a local TV instance that is my DB under which I'm going to write my sequel dot connector dot connect under which I am going to pass in the three parameters that are post which is equal to your localhost then comes the username that is equals to root and then comes the password which is equals to password one two three so root is the default username which I had given guys and password one two three can also be customized so this is the password which I gave now I will merely print this local DB instance that is my DB quickly go ahead and run this code here all right yeah so as you can see the connection has taken place but how do I verify whether the connection has been established successfully or not so in order to do that I am just going to write two more lines of code that is if my DV then print that is if it falls or if the local instance that is my DB then I'm going to print connection successful else I'm simply gonna terminate this or I'm gonna write print connection that is if there is a local DB instance that is my DB is coming or under my DV it is coming or the parameters are there then my connection will be successful else my connection will be unsuccessful let me move ahead and run this code give me a minute guys well as you can see our connection is successful now I'll just change one parameter and see whether it's verifying that the connection will be established successfully or not now I'm going to change a parameter suppose I'm changing the root which is the default name of user - ROM and let's see if the connection is being established successfully or not or it is being verified or not but as you can see guys it shares access denied' for user ROM as because our default username was root which I had given so it will not be getting connected to a my sequel DB server or let me quickly write back root over here and move on by creating a database so in order to do that I'll quickly open file I'll create a new Python file for the name as Python DB creation let me just name it Python DB one creation now I'm gonna write my first line of code that is importing my sequel dot connector which is the package then I'm going to write my DB is equals to my sequel dot connector dot connect under which I'm going to pass in the three parameters that are host which is equals to your localhost then comes your username that is equals to root and then comes my password that is equals to password 1 2 3 finally I'll move on and I'm going to show you what I'm going to do over here so I'm going to create an object the name as my cursor under which I'm going to pass my DV dot cursor which is pointing towards the location and finally I'm mainly going to print this object that is my cursor dot execute under which I'm gonna write create database and let me say my database name is Hodge TV all right and then I'll quickly move ahead and run this code let's see what is the output all right so it says that the database has been created that is Hodge VB has been created now I'm going to show you whether this database is existing in our my sequel workbench or not so in order to do that I will just change few parameters and add a for loop I will explain you about the for loop part later so let me just omit this part HDB and instead of create I'll simply write show over here and then over here I'm gonna write show databases now I'm gonna write for DB in my cursor I will simply print the database all right move on our run Python TV one creation file well guys as you can see from the output the reason why I used a for loop over there is because once my code enters the for loop it is basically printing all the databases which are existing in our my sequel workbench along with the database Hodge DB that we have just created which is there in our output so it's very clear now I'll quickly open my my sequel workbench and I'll write the same sequel query over there so that it's crystal clear to you all right let me quickly maximize it one second now let me simply write show databases and click on execute one minute days well as you can see our database Hodge DB has been created along with all the other databases which are existing and are my sequel workbench now let me just refresh the schemas all right so let me just minimize this and I'll move ahead by creating a table under the database so I think you guys are pretty clear with the concepts which I explained just now I basically started off by creating or by establishing a connection between our Python and my sequel DB server so that I moved on and created a database and now I'm moving on and creating a table under the database harsh DB now how do I do that well let me create a new file first one second I'll name it as create TB and Pythian all right all right guys before moving ahead I'm going to tell you one thing the first three or four lines of code are going to be same everywhere I don't find it useful by repeating the same first three lines of code everywhere again and again I don't want to bore you guys with the same thing so let me just copy pasted into the presentation mode yeah it will be now I'm gonna write my cursor dot execute it's gonna be create table suppose I give my tables name as employee under which let me pass two parameters they'll be named varchar' 200 salary and pedro 20 you just close it move on execute this code well I have done a very silly mistake guys and the error is also very self-explanatory it says no database selected I'm gonna pass a parameter where it's going to be database is equals to harsh DB and I can quickly move ahead and run this code one second yeah all right so when I run this code our table has been created now I'm going to show you the table which I have just created in our database Hodge TV so I'm going to write the same code what I wrote while I was showing you the database so let me just omit these things and add an S over here and instead of create let me just add a show and let me type a for loop so it will be for TV and my cursor print your table all right now let me move ahead and run this code so as you can see we have created only one table under the database Hodge DV so it is showing that table which is employed now I'll show you the table and our my sequel workbench so one minute guys so instead of databases let me just write tables let me just select the Hodge TV thing over here instead of databases let me just write tables select Hodge TV and then execute this yeah so once I execute this well as you can see it is fetching the table and our table employee has been created on the extreme left hand side under the schemas column now I will quickly minimize this and I'll move ahead by doing the read operation so before I dive deep into the read operation guys let me give you a small introduction about this so a read operation will basically happen in two stages and my first stage I'm going to populate my people with some values and in my second stage which will be the main stage where I'm going to do the read operation consisting of to fetch functions so I'm going to talk about the fetch functions data first let me just in order to populate my table I'm going to make use of insert command from the sequel query so let me just name it as insert of table and move ahead yeah ctrl-v before there's one more parameter which I left it's going to be database is equals to her dB now moving on let me create a new variable with the name as sequel form and under sequel form I'm going to write the sequel query insert into employee 1 secondary's pass in the two parameters which I declared which were name and salary values and make use of or I'm gonna write % s comma percent as well guys this person tests are nothing but placeholders you can replace them any time you want with any other value now I'm going to create a temple now the reason why I'm creating a double is because again and my second stage where I'm doing the read operations I'm going to make use of to fetch functions so there is one fetch function called fetch all so when I'm making use of fetch all I need not populate my table with a single value because that will not look good that's the reason why I'm creating a double over here so that to give you more clarity into what actually happens now let me just name it as hardship let me give my salary as twenty thousand OMA permit saria's thirty thousand comma Ankita and her salary as 40,000 come on space yep I think I need to put this back yeah now when I move on I'm gonna simply rate my cursor dot execute many because if I use execute it is just gonna execute a single value but since I am making use of a table where I need to write my cursor or execute many and call in sequel form comma employees and finally I'm going to write my D beat or commit which is going to save my changes for my last executed sequel statement now let me quickly move ahead and run the score so you can see our insert operation has taken place and our towel has been inserted into the table employee under the database Hodge DB now I will quickly exit the presentation mode and show you the same in our my sequel workbench it's gonna be the same thing I'm gonna write select star from employee and click on employee you can execute one second days yeah I'll click on the result grid over you well guys as you can see this select star from Hodge DV dot employee valid changes nothing our concern is to basically view the tupple which we have created and that tupple is being viewed in the result grid that is under name and salary the values which we pass that a Harsha Tommy thunk either with the salary has twenty thirty and forty thousand let me quickly minimize this and moving ahead I'll be creating a new file and let me just name this file as read off table all right now into the presentation mode once again copy the first three lines of code over your what I'm going to do is yeah let me pass the parameter database which is equals to DV and then I'm going to start off my read operation by the first function that is such one so let me write my code and then side by side I'm going to explain you all so why am I writing that part so I'm going to simply write my cursor dot execute let's say I'm going to write a sequel query that is select name column from employee ok these are very simple sequel query and let me create a new variable and let me name it as my result under my result I'm going to write my cursor dot fetch one now guys this is one of the most important part of my code because this is the first function of read operation what fetch one is going to do is it is going to fetch me one value from the column name under the employee table all right now when I move on let me write the for loop that is for row in my result friend the row when I run this code as you can see it has fetched the value hardship from the roll name under the table employee which is again under the database harsh TB as you can see this code is pretty self-explanatory when I make use of the fetch one function it basically fetches a single value from the column name under the table employee now let me just quickly move on with my next function of fetch in order to do that let me just make few changes in this code so instead of name suppose I am going to fetch the whole tupple it's going to be select star from employee and instead of fetch one let me write here fetch all and let me run the for loop so it will be for Rove in my result friend row all right now I'm gonna run this code I'll explain you why am i doing it or what the sketch all do well as you can see from the output the whole topper has been fetched now when I make use of the fetch all function guides it basically fetches the whole tupple for us as because we are writing the sequel query select star from employee so all the values which are there under employee table which was in the form of a double when we inserted it a while ago while we were writing the insert code that whole topple gets fetched now I'll accept the presentation mode I'll go to the my sequel workbench and rerun this code once again but as you can see there are no differences the name is still there with the values horseshoe tomato Nikita and salary 20,000 30,000 and 40,000 suppose I write this thing select name from employee one second you I'm going to show you the changes of the fetchin functions in order to do that let me just write name again over your name and instead of fetch all let me write h1 all right let me quickly go ahead and run this poor so as you can see it is fetching here now I'll go to the my sequel workbench and click on same thing well guys these changes are not taking place away oh because we will show you the changes in the pie charm and since we applied a for loop over there that's the reason why it is doing it but since when we are using the backend database server we need not do that because we are not using any loop of you're not doing anything we are simply fetching the name old column from the table employee now I will move on and do the update operation so in order to do that let me just create a new file a new Python file and name this ass update of table yeah let me close this just click on this into presentation mode yeah control V let me pass the parameter which was database that is equals to purge DB moving on let me create a new variable with the name as sequel under which I'm gonna write AB date employee set one second salary let me set the salary to 70,000 making use of the where clause where name is equals to let's say I'm updating on guitar salary to 70,000 and finally I'm going to write my cursor dot one second execute all in the sequel thing and then write my DV dot comment you can go ahead and run this code our update operation has taken place and in order to see the changes let me quickly go back to the read operation I will show you the changes on both places in the PyCharm and my sequel workbench 2 so the last goal which I wrote let me remove this and right here select star from employee this was fetch all ok into the presentation mode once again yeah run this code as you can see from the output the changes have taken place and unka the salary is updated to 70,000 which was 40,000 before I'll quickly move on and show the same changes in the my sequel workbench which is quite easy let me just feed on this code this should be starred over you yep and then execute it as you can see uncle the salary over your is again updated to 70,000 now I will move on and do the last operation that is delete operation it should also conclude my tutorial it's going to be delete of let me name it as table all right and to the presentation mode once again copy the first three lines of code I'll pass here the database parameter theta phase is equal school purge DV I'll move on and let me make use of the same variable if you used our time say suppose delete from employee we're name is equal to suppose I am deleting my record guys which is her and then my cursor not execute or in sequel and then write my DB dot commit which is going to save the changes for my last executed sequel statement let me quickly go ahead and run this code well the database spelling which I used is wrong I guess well as you can see from the error is quite justified it says unsupported argument data BAC so the tier IV spelling is wrong with me quickly rectify that one second guys yeah I'm gonna write a over your I missed it my bad I run the school once again so as you can see our delete operation has taken place and I'm going to show you the changes exit this will go to read off I'll enter the presentation mode once again run this code but as you can see the required hardship has been deleted from here and the same thing would be or the same changes would have taken place and the my sequel workbench let me quickly open it and run this code once again well as you can see the required her chute is deleted from here too so this marks the end of Python database connection tutorial I hope you guys learned something new today I'll be coming up with more videos on Python until then bye bye take care and happy learning you

Channel: edureka!

Views: 281,335

Keywords: yt:cc=on, python database connection, how to connect python with mysql db, connect mysql with python, how to connect mysql database with python, database connection in python, sql crud operations, how to connect mysql database in python, python database connection mysql, db_connect module in python, mysql database in python, connecting to mysql in python, mysql in python, mysql connection in python, python mysql, python edureka, edureka, python sql connection

Id: g60QghtJmjY

Length: 29min 22sec (1762 seconds)

Published: Mon Jul 29 2019