Step-by-Step Python and Postgres Tutorial with psycopg2

what's up y'all this is Hussein NASA from my geometry where we discuss software engineering by example in this video we will talk about how we can write Python script to talk and to interact with Postgres database all right so you would obviously to bring your Python scripts to life to build real applications you will need to store some surface state right you will need to talk to a database you need to store some information user data anything right and database is the perfect thing to do these sort of things all right almost any application out there he uses that back-end as a data base bhaskar's is a great open source database and it we love it alright so in this video we will go through what do you need to do exactly to basically write a Python application from scratch right and then talk to a pair to a Postgres instance right so we're gonna describe how to read and how to write if any basic commands right I don't want to make the video very long or M bore you guys okay so yeah so I in this laptop here I'm using a Mac and I have docker and installed and I spin up a Posterous instance and PG admin just to maintain this poster in his desk we talked all about that how to do all that from scratch in this video I'm gonna I'm gonna reference it and the cards here somewhere you can take a look at that see how we can actually spin up both Chris and instance on your machine without actually installing the database or I'm warning about that you can spin up a container just do your stuff test your stuff develop write applications and then destroy the container right it's very simple stuff III always recommend that to my students alright so let's just jump into it here I have a positive instance I have two databases okay one day I was called Hussain DB and I have table called employees and there are a bunch of records in that employees and we're gonna just learn how to display those in Python alright so we can just look for example display the name okay very simple stuff right so let's go where from scratch I'll be using Visual Studio code because it's my favorite editor I use it for every single scripting language out there I use a first-time screen just for JavaScript I use it for Python right I just like to use one thing for everything it makes things very very very easy right I have Python installed I'm not gonna go through that process because it's simple you stop Python 3 all right I might actually make a video on how to install Python and do all very simple just install and there you're done okay videos to record yeah just the moment you create a Python file says hey by the way let me install for you the extension and do intellisense and all that stuff so I like Visual Studio code because of that so I'm gonna go ahead and create a folder here our project is called Postgres demo right whatever we're gonna call this so this is how we talk to Postgres all right so we're gonna create is a python file and this file file is I don't know what we're gonna call a DB don't pi okay and this is what I'm talking about right so the more do you create that it says hey what's your Python interpreter give me which version you want to Muse 3.7 2.7 I'm gonna use 3.7 okay once you do that the library that we can use for Python and this is my favorite library I dabbled a lot with a lot of other library and this I this is why far is a more standard easy to understand and it's like it's like based on the DB DB standard for a DP API so it is called important so I'm gonna butcher the name okay this is how I memorize libraries okay P for Python cycle without an edge right and Postgres too because the one sucked right this is the second version to P psycho PG - all right let me zoom so you can see it is that enough guys can you actually see it now okay okay so the first thing we need to do is actually connect right we establish a connection to the database which happens to be called Hussain DB but it is sitting on my laptop which is called whose Mack right that's the just a hostname okay and so that's great a connection how about that connection equal is how you do it peace cycle PG 2 did very hard connect duh right and to do that we need to put a bunch of parameters here okay you need obviously the host what's the host name that you can execute this is your could be Amazon could be whatever right it's like this in this case is just my local machine so Hussein Mac okay the same kak yeah sure alright so second thing is what what what do we need the database right database Hussain DB that's the database name remember guys it's called Hossein DB I'm gonna remember I want to query this table that's one way to do okay that's the second thing yeah this is by the way quick keyword arguments so that's the cool thing about Python I love it a lot alright so database Hussain DB ok and what else what else user what are you connecting ass ok I'm gonna connect as post Chris that default user here but you can create a user and use that as well and the password is very secure okay please do not look guys okay what what else what else are we missing so there's an optional protocol port okay if you if you're running your postgis instance on a different port not the default ones and you can specify that the default one is four three two five four three two right but I'm not gonna it's our optional thing that's gonna we use but if your instance is running on a different product you want it specify that all right Kulish kulish alright now I have a connection okay I like to like if you establish a connection right just like connect to the DB this is where your session will start right so you'll start issuing queries and then once you're done you need to really close this thing okay release that stuff don't leak anything guys just make it a habit okay close that comic Sean all right we close the connection okay so to interact with the database right this library P psycho BG - I know I'm pushing the now you guys are laughing okay I don't know how to say otherwise okay this I copied YouTube okay is you need cursors when cursors are the commands right are the the vessel in which you communicate to the database with okay and how do we create a cursor a cursor is created off your connection so you will ask me to create a cursor okay and that will give me a cursor okay so create a cursor and there are two table cursors there are client-side cursors and servers casas site cursors and we can take a whole episode just to talk a lot of those too but in general if you like a query you think about like queering a huge table okay and your application right selecting let's say everything alright in that table okay there are two types of cursor you there are client-side cursors and there are server-side cursor client-side cursors are when you when you query you essentially the client will basically allocate memory for the entire damn thing that you're querying okay okay so you you essentially pull everything from the server to that client which is our little application here and then start processing the stuff in the client okay the other way the other type of cursors is a server-side cursor says you know what it created that cursor but leave it there don't give me anything until I ask you to so you can call cursor dot give me one give me one give me one give me another one just like you can you can loop through those cursors like by one by one okay I say I start explaining this like I said and I'm not gonna explain but it's like so there are advantages disadvantage for both like performance there's like you can lose one and you get you get a lot of network traffic but you get more memory like less memory footprint in your application and the otherwise we will give you like another like network bandwidth anyway so our topic sorry guys okay let's go back to our two coding here okay cursor cursor let's zoom in here what did we do I'm gonna query cursor don't execute I'm gonna execute a cursor that says select ID and name they remember that guys that's the two columns there from employee yes right usually that's basically a bad query but for the sake of example just let's say you don't want to do an unbounded a query like that you always want to do a limit or just like give me the first one or second one right but yeah that's that's bad practice guys but we're learning okay it's like as long as we're saying it that's we're good right execute that query okay and then we want to return the rows this will not return you the rows this will execute the cursor it will start do they do its things in the cursor right so what I'm gonna do and the cursor will do Fitch there are many features here but okay there are many pitches the the the famous fetches fetch all give me everything gave me everything tonight what year is this this is no 2019 this is 2000 apparently all right I'm sorry guys all right plus this guy beats Rihanna so would you shouldn't sing his songs all right so you loop through the rows very simple all right and this will actually be tuples of like zero whatever like the the ID name okay it will be an array of tuples like that okay that's what we're gonna get back okay and let's print our fancy print let's use fancy print okay I don't know if this is really cold fancy prints I just made it up guys okay so our chef zero and then we're gonna do the ID equal to Co sub 0 and then we can adieu wait that's JavaScript dammit okay okay yeah javis could uses text like that so I always get confused over this view all right let's see if this works all right obviously after you're done with the cursor you also have to close the cursor it's a completely different thing okay guys don't forget close the cursor close the cursor close the cursor don't leak things leaking is the worst leaking is the worse okay are we running are we running this thing I think I forgot to mention what a very important thing obviously this library doesn't exist by default right so if I run I'm expecting an error yep it says I can't find that I found out I cannot find the cycle so how to do that you go to terminal the same folder or anywhere really just use pip pip in style peace I call remember guys P for post guys no for Python psycho the movie Postgres and the purge the version one sucked so the version two is better okay that's how we do it then you debug and run again and then just like that we print ID name cool all right guys we just printed everything let's say if we don't lie there it looks like the names Jim a is your name let's do much so you can see them that's the printing that's that's what happened right we did ID and then this and then that okay cool cool let's do one more thing let's do our answer how about that let's do an insert let's do an insert okay so before we create the culture to actually query let's do the same cursor that's the cool thing about it you can use the same cursor but this time we're gonna insert insert in cue employees right I'm assuming guys you know just like basic sequel right ask me if you have any questions obviously right I'm down reference also the the documentation this is beautiful they those guys have beautiful beautiful beautiful documentation beautiful all right it's a beautiful documentation all right insert into employees and then I'm gonna say all day and name these values okay so to avoid sequel injections you can you can obviously do this right whatever give me a name Bob all right is that Bob there is a Bob damn it Bob okay let's just choose another name John damn it John okay that's okay let's just an Arabic name Ali okay okay and okay so you can do this right that's fine okay you can obviously put variables here but that's actually bad practice it because of sequel injection people can write stuff right if you asking users for input and then they take your input and they play with like they do like a slur and then do a comment and sequel and they'd a drop table employees they can actually drop your table writes they call sequel injection or bad so this beautiful PG cycle the cycle think here is actually pretty cool with this it actually can gives you like this execute can be as a parameter so what you can do is just use our s okay and do a tuple here and then put your variables here and they will be safely distributed in this and they will be sanitized they will take care of all that stuff for you so you don't have to worry about it again that's Pomona all right so we're gonna insert here and then we're gonna see let's run run run run mmm and we can see the name right we can see now who say is that it okay let's go to the database and let's refresh do we see Hussein here where am I guys where are my I don't see myself here do you and can you guess what happened here what happened is since I am in a transaction in a session you did not tell PG cycle to actually commit your edit you said hey I'm gonna make another and then you exerted you didn't say anything you didn't say roll by you didn't say comment okay so you actually have to commit your stuff after you're done okay so what do you do is basically Duke own action don't commit you commit your connection okay so commit your changes we didn't have to do that with select because we're just squaring but if you're doing insert you want to commit few changes do that all right obviously so you have to be careful with your atomicity and that consistency here I made a lot of videos about databases reference it here hopefully you can see it in the cards I'm going to put in the description while we talk a lot about software engineering by example in this channel if you're new here just just subscribe guys like hit like this video we're gonna teach you a lot of cool ish stuff okay commit the transaction let's commit the transaction my friend and let's let's see what will happen this time okay wait run it again I don't see any difference but if I yes I see hüseyin I see who's saying now what happened if I run again who can guess what will happen if I run again boom just like that we got an error and that's a beautiful we like this error we want to see this error this error keeps us consistent this error keeps us alive this keeps us safe guys okay okay we have to stay safe okay this error keeps us safe it's like if it is this is a bird box this is essentially your blindfold okay right okay so don't ya use it okay this is the essentially the indexing okay it's primary key right that's what happened here we just try to insert double thing all right guys I think that's it that's it for this very brief really video just to tell you about their basic stuff how to use P psycho to connect to the database from Python alright hope you enjoyed this video subscribe to this channel to learn more about programming soft engineering topics we don't know not only do coding we do a lot of kind of stuff like we explain very technical software engineering topics to become a software better software engineer consider subscribing to our geometry alright and this is your host Hussein Nasser signing out stay awesome guys

Channel: Hussein Nasser

Views: 128,265

Rating: 4.5785122 out of 5

Keywords: Software, python scripting tutorial, python postgresql tutorial, postgres python tutorial, postgres python, psycopg2 tutorial python, psycopg2 insert, psycopg2 tutorial, sql injection python

Id: 2PDkXviEMD0

Length: 18min 10sec (1090 seconds)

Published: Sun Jan 06 2019