🔴 Connecting to SQL databases from VS Code

[Music] m [Music] hello hello everyone happy live stream Thursday welcome back to the VSS Cod Channel every week we have these live streams always a fun time rotating guest um but always amazing guest who have something great to show um so we're gonna do just a couple of intros and little uh tick throwback Tik Tok Thursday before we bring the host on um but in the meantime go ahead and let me know where you're watching from in the chat say hi very excited to have you all here we have a great guest today um if you subscribe to the vs code Channel you're definitely familiar with her um you've seen her in co-pilot videos you've seen her at vs code day on python pulse um she's amazing so we'll definitely get to her introduction in just a little bit hello Mike thanks for coming ish hello oh Pamela's in the chat hello um all right so we have a great show today like I said um but before we kick things off we do like to do just a quick throwback Tik Tok Thursday so you can see a little taste of what we post on our Tik Tok channel so why can we implicitly convert some types in C and not others turns out it's all about data loss so here I have a bite and when I try to implicitly convert it into an integer that works just fine when you try and go in the opposite direction you're going to get an error and you won't won't be able to do that let's see why that's happening so here when you have a bite obviously that takes up a bite of memory however an integer takes up four bytes of memory so when we copy a bite into an integer the runtime will prefix that number with a bunch of zeros to make sure there's no data loss however in the opposite direction there might be data loss as these three bytes might need to be truncated since the compiler does not want that to happen you're not allowed to implicitly convert those types in that case you need to actually explicitly tell the compiler that you're aware that there's some potential data loss but you're okay with it you can do this by Casting and to cast all you have to do is set the target type that you'd like in parentheses in front of the variable and that will actually allow you to convert the type here if I open up my variable Explorer you'll see we now have our variable X and variable B and they're both an integer and a bite I love that one because on our Tik Tok and then also on our YouTube shorts um you know we post a lot of really cool tips and tricks like that so if you were watching that Tik tocking kind of like how is she going from C to mermaid back to C that was polyglot notebooks um so definitely check out the polyot notebooks extension if you're curious about that but you can use C mermaid JavaScript markdown SQL F I think a couple other as well um all within one notebook which is really cool because you can also share the variables and really just have a whole interactive experience just in one notebook with all the different languages um okay I see we have a lot of other people saying hello where they're from we got Houston Honduras UK a novice from UK we love developers of all levels welcome um let's see some more hi hi from the UK we're the maintainers of the SQL tools extension Pamela will be using looking forward to the session awesome thank you so much for being here it's gonna be a fun one um and a couple more hello so with that let's go ahead and bring on Pamela oh hey hello everyone hello welcome how are you doing today um pretty good pretty good I'm excited me too oh my gosh I'm so happy anytime we can have you on um you know I think it's been a little while since I've hosted anything with you but back on BS code day it was so fun um so yeah always a great time and people are already pumped in the comments um so yeah do you want to start off with just a little bit about yourself and then what you will be showing today yeah so uh I'm Pamela I use she her pronouns and I am on the python Advocate team at Microsoft so that means trying to help python developers be successful with aure and with you know Microsoft products generally which is why I get to use F code a lot and I do say viz code even though I'm supposed to say vs code and I'm trying to train myself out of it but I just love I love saying that's I mean honestly I love it it's nice it's kind of like almost like ASMR exactly right for your ASMR Tik toks Ser exactly yeah uh so yeah so I do quite a lot of uh you know getting flask and fast API and jgo apps working on as your lately of course I work a lot on open AI samples and I do a lot with databases as well so uh so yeah so that's what I'll be doing today is showing how we can use a a postgress database inside VSS code cool all right I'm excited because this is not really something I work with a lot so I'm always happy anytime I can be on these and also learn alongside everyone else sweet all right so shall I dig in then yeah let's bring up your screen and chat as we're uh Pamela's going through let me know any questions you have I'll be monitoring the chat um and Pamela is more than happy to answer what you have yeah so that's my vs code I'm going to be working off of this uh this repo here so if any of you want to do what I'm doing follow along or you know try this later you can get you know get this repo I think it's also linked in the YouTube description uh and it's a pretty it's you know pretty uh basic repo but it's got you know it's got what you need to get started um so let's take a look at that repo here and uh the first thing I want to do is show how I can work with a postgress database locally in vs code so when I'm developing with postgress locally uh I always use a de container I do actually have the postgress app for my Mac you can tell because there's an elephant up here and postgress is always represented by an elephant the other actually yesterday I clicked the elephant accidentally but I was looking for the whale because it's so confusing that like these two massive animals yeah two massive mammals on my far and I just get confused I'm like oh no no I wanted the other no I need the land one not the water one yeah but you know like elephants like whales kind of started off in the lane you know um so yeah so anyway I do have the postgress app but I actually find it pretty hard to set up uh that app and I I don't like having conflicts between you know between my different apps because I for many of my apps the table and the database is called the same because we use like kind of the same sample app and I don't want to accidentally reuse that same uh that same database so I like to have really isolated environments and so that's why I use Dev containers and that's why you'll find this Dev container folder and all of my repos uh so with this Dev container I able to have postgress database entirely inside this developer environment and not have it leak into any other environments which I just think is just so cool and I love it so much it does it makes it so much easier when like you said you're working on multiple projects that kind of use the same thing you can have that segmented like okay I know what I'm doing in this environment is all that I'm doing for the sample app right here I'm not like accidentally touching something else I know I have everything we need it just gives you so much more peace of mind yeah exactly it's beautiful and the way this happens is it you know it starts off with Dev container. Json and this one references a Docker compose and that's pretty much what you have to use if you do want to have a postgress database in your Dev container and in this compose you can see that I have you know my app which is the code I'm actually working on and you know that's just based off of the code in there but then I have a database service and the database service is based off the official postrest image and you know it's configured with some just some local uh you know local authentication um and then I have a volume for that postgress database that it uses so this is the basics of how to have a Dev container that has postgress inside it and you can see in the ports tab that uh I you know I've I've told it that the postest port is 5432 uh so that's exposed at least for the app to use so that the app can access 5432 which is the default postgress Port uh inside of it okay [Music] okay so we have that postgress set up so how do we prove that it's working well uh we can do we can do a couple things so uh since we have the uh SQL tools folks here let's start off with SQL tools so SQL tools is this really nice extension for uh for browsing SQL databases in vs code so I always have it uh you know in any of my repos that use databases and you can see here in the extensions list in my Dev container. Json it says SQL tools and then also I have the postgress driver for SQL tools here so it you know depending what kind of database you're using because SQL tools works with tons of different databases so uh postgress uh SQL you know uh post SQL probably my SQL Etc and it just has different drivers for each of them okay so bring those in and then I even have um I even set up the default connections like for local so you can even do that either in your settings. Json or your Dev container. Json cool I didn't know you could do that in your Dev container to Json that's awesome yeah so the most I can you know whatever I can set up in here I do um I even try to set it up for the azir database but I'm GNA have to fill some of those details in later so I got as far as I could um so yeah so that's set up so then you notice that it's got uh you know a database username password and these all match what was in that Docker composed. yaml so it's okay have these because this is a local database right there we wouldn't want to have this for production database checking our password bad idea please do not check in your password um but it's okay for this local database because you can't access my Local Host I can't access your Local Host there's there are a couple of quick comments um people are commenting about your light theme obviously too it's all personal preference you can do I actually usually use solarized we'll change it to solarized oh I like the solarized um and then some someone actually also asked on your um sidebar on the left what are the two last extensions what are those icons for uh yeah we've got the Azure extension which I'm going to be using and then we also have the co-pilot chat extension and you can see that I was trying to use that for debugging an issue earlier so um we might we could try using that at some point today too so yeah so those are the right because we have these are all default Vis code and then we've got SQL tools azir and co-pilot chat awesome thanks sweet all right so SQL tools we got that connection set up uh and you can see here that it's already connected uh if I wanted to disconnect I could uh press this little icon here um and then you know once it's connected I can browse and see what the database looks like and what you see is that there are no tables right now not very exciting let's make some tables let's do it let's see okay so I'm going to be using pyth Pyon as I mentioned I am a python Advocate um so I've got some packages already loaded into this environment uh so I've got psychop pg2 which is the weirdest package name and sounds psychotic to me but that's the official like pack or like Yeah official but it's a standard package for working with postgress uh I've got seal Alchemy and then I've got you knowm for loading in a EMV file um and then some other ones that we might use later so one option for connecting to a SQL database in Python is to directly use psychop pg2 and if we wanted to do that we'd first need to load in our environment variables uh I always uh well for local I always do aemv file so I've got like sample. EMV files here and then uh and then I can load them into this EMV file so right now mymv file is set up with everything I need for that local connection and once again this matches what I put in that Docker composed. so all these things things need to match and and just you know a reminder you mentioned that this repo is available for everyone so if people just pull down this repo they'll have all this configuration basically started and then they can essentially just start up their Dev container and be on their way yeah the only thing you're gonna have to do is I never check EMV files into source source control because sometimes people do put secrets in there so you'll notice my got my get hasv in it um so the first thing you'll have to do is just copy this one into aemv cool okay awesome I should put I should make a read me okay read me to do okay thank you great all right uh great point so yeah so we load in that. d. EMV and we get you know the pass host user name uh for local we are not going to use SSL so generally when you're developing locally you don't use an SSL connection to the server but when you're in production you hopefully do uh so this is how we could connect in psychop PG uh and this is how we can execute SQL statements so here I'm just executing straight raw SQL statements uh so we can do if we want to you know do this you know just do direct SQL statements you could do it just like this so let's try running this okay no errors that's a good sign good now we go to SQL tools and we go here and it's actually already refreshed if you didn't see it you could click on this little refresh icon here so sometimes I do like manually refresh but we do see it's already here so that's really cool and then we can click on that and we expand it and we can see the schema so we see it's got an ID which is the primary key and it also has a name which is a barart with a Max of 255 uh now we can click on this little magnifying glass that says show table records and we see a single record not a very exciting one but uh it has inserted this single record there cool and you can just inspect that all in vs code that's awesome yeah there's a couple questions just before we go so um danillo sorry if I didn't pronounce that correct from Brazil um asks how um stable is the connection to the database uh i' I've had a lot of Success With It uh in in terms of it just staying connected uh luckily the SQL tools extension makers are in the chat so they could comment if they've seen any uh connection stability issues uh but I have not had issues with it in my experience cool yeah and just um for those of you in the chat that's coming from George James software is commenting um about the SQL tools extension um I think we had another question saying how did you get SQL Tools in vs code the answer is the SQL tools extension yeah so uh I have it in my Dev container already but if you uh don't have it yet just search for SQL tools so first download that and then get the driver for whatever uh you know whatever database you're going to be using so you can see they've got postgress driver SQL driver uh snowflake driver um all of that um uh so you and you as mentioned you can just uh start if you're going to be using postgress you can start from my postgress playground because it's all set up in the dev container I also have a SQL light playground so those of you like SQL light uh you can find that I have a SQL light playground as well that has a little bit of a different setup because of the driver requiring um requires node I think yeah SQL tools use node um so those are the two two playgrounds I have because those are the two that I use the most often uh but you should generally be able to download SQL tools and then the driver for whatever you need cool um okay just a couple more questions and then uh we'll let you back into your demo um okay some uh brills asked do you also use Nex kex to interact with your database I know nothing about NEX I don't know if that's how you pronounce it uh wait I'm trying to see where K next I actually also know nothing about kex okay great question I think aliv and I will both be researching Googling that after yeah yeah yeah all right good question we like when we get those um and then water B bonkle also mentions um you can make the docker compos the M all pull and variables from the dot end so you don't need to match them manually so yeah that's a good point and I think I did experiment with it and I don't remember why I am not doing that anymore so uh pool requests are welcome you um if you want to send a PR for this repo that'd be super cool to find at the end of this stream uh because I yeah I oh I think what I was trying to do was actually reference m files in my Dev container. Json and I think I had some issues with that um but that's a good point about your do Docker compos that it probably should be able to reference. M so that would be a nice way to avoid to have less uh redundancy cool love it all right thanks for the questions y'all keep them coming um but yeah Pamela let's see what's next okay all right so this is cool if you are into raw SQL and some people are I guess um no I to be fair I do use raw SQL if I'm doing like data analysis on like a readon database uh but if when I'm doing like web apps I always use an omm so that I can model uh you know model my tables using uh you know object-oriented programming using classes so to the most popular python orm is SQL Alchemy so that's what I'm using here and I'm using SQL Alchemy 2.0 which has a new style of defining columns which works nicely with python type annotations uh so I do encourage everyone to upgrade to that style uh if you're you know if you're using SQL Alchemy and I have three videos on the visco channel about about using 2.0 so you can check those videos awesome but here we go yeah so we you know imported stuff from SQL Alchemy uh we Define the base for all our models and then we can Define our individual model and we give it the table name and you can see now I've got three columns here so the ID the name and I've also added an address and then I set up the connection and this is really similar to before uh it just looks slightly different um but you know once again I use SSL on production but not locally there was a question about whether SSL is really required in production if you're you know on the same server it may not be required I've gotten use to always requiring it at least for Azure um so I think different servers you know treat something differently but if you are deploying to asure uh I recommend always requiring it uh otherwise you'll get this particular error uh that indicates that it needs it um so yeah it's a good point it may not always be required but I've gotten used to always requiring it when deploying to a Zer cool then we okay first I drop all the tables now don't just willy-nilly drop tabl can do this because I'm in a demo but you should be care proceed with caution yes U and then uh so this actually drops the whole schema which means like dropping the table named restaurant really drop yeah it's a real real drop uh then I do create all so that looks at all this the you know the class is declared and then creates tables for each of them uh and then I uh create a new session with that uses this you know Engine with the connection and I add a bunch of restaurants and then I commit which will actually really do the ad then I do a query and get some results uh so let's just print those results and let's try writing this all right so no errors and you can actually see uh because um how did I configure this so you can actually see uh in the logs here you can see the SE that was executed which is really nice so we can confirm like okay yeah this is what we wanted right cool so now let's go ahead and look at SQL tools again and this time when I click on here I can see all of those things that got added um so you know we can keep on you know checking SQL tools to to make sure that everything's working uh how we expect I would love to have 10 cheese shops so I was doing it because I was I wanted um I wanted copilot to like be more creative but uh there's other ways so what you can do is be like um create a list of uh 10 cheese shops with funny cheese names cheesy names oh okay let's see how we do oh is it gonna do it like this no I'm gonna start off with okay thanks this really just every [Laughter] location well let's see if it even does it count correctly one two three four five six seven eight nine 10 well it counted we got 10 yeah I'm not sure if I call this I mean they're cheesy in the sense that cheese is in the name very good um Okay so we've got those great cheese shops now um so you can use copilot generate data what I do more often is I use Faker uh so we do like f equals um Faker let me import Faker up here from Faker import Faker and Ruff wants to fix my imports okay and we go back down okay we have f is a faker instance and then I'm going to say use um Faker to create a fake address thank you and then I'm go to address equals f. address so this is going to use um Faker address generation which is really nice and this is something I talk about in the the YouTube series it's very nice because it it it really looks like a real a real address uh when you ask co-pilot to generate address says it's always one two 3 Main Street four five6 main street 78 N Main Street not much variation yeah good job co-pilot um uh so let's see oh it says it got unexpected uh name address oh oh oh my parentheses are off Heavens H okay you spotted that so quickly though I yeah I would been staring at that for like five minutes and be like why what yeah I did teach a python class 2000 students used to see but uh School cool now let's take a look once again at our awesome data and and we can see that the cheese shop of De Moes is located at the USS Neil sub Okay cool so we have some data now this is all locally the thing that I really wanted to show today uh is actually making a air database and being able to interact with that from BS code so uh are there is there anything uh on this that we should talk about before we go into making it as your database um I'm scrolling through the chat I don't see any new questions um just Rolo says copal is not trained for humor yet I don't know it cracks me up though all right so um it's it's very helpful as well um all right so let's make an Azure database so I'm going to be using the azir visz code extension and uh you can see I've got that in my Dev container it's this one the azir pack so air pack brings in a bunch of a bunch of different functionality uh you can also see um if you you know want to find it maybe we just search for as your pack yeah as so I think it's called as your tool yeah here we go and it's from Microsoft as you might imagine and we've already you can see it's already enabled in this Dev container awesome so we go to the Azure icon and to uh we can see here all the resources under this subscription and you can see I already do have quite a few postc servers as you as I said I I do a lot of testing of um databases and web apps but but let's go ahead and make a new one why not so we click the little plus so I did that real quick do it again so we click the plus okay and then say create database server and we give a bunch of options I'm going to do flexible server do you also see this single server option that's kind of like on the way out flexible server is the new cool thing to do if you want to be cool do the flexible exactly uh now I'm going to give it a name it's code t three and then I'm going to pick the skew so I'll just go with Basics so I don't spend um more money than necessary basic B1 Ms is around like $112 uh a month you can't get lower than that right now uh with the the postgress servers but uh you know $12 isn't bad depending on what you're doing right and then of course we've got you know much many other SKS uh as well um but if you are just doing this as a trial then you don't want to you know have to pay but there is um there's also I think it's part of the free tier if you've never made a postgress server before I think you get one postgress server free oh cool okay yeah and that's good to know I think a lot of times people think because a lot of times when you sign up for Azure they'll see oh I put in my credit card and they immediately think they're about to get charged a ton there's a lot of free tiers and free options like even though you might have to do that initial sign in you can still never actually get charged for anything if you kind of stay within those free tiers yeah yeah so definitely look into that um because I'm pretty sure postgress has one free uh database but double check anything uh you know before you embark on a cost Journey uh so now I need to put my admin username so I'm G to go with admin 123 awesome and then password so the way I'm going to do the password is that I've got one pre-stored in here that I'm going to use because the hardest this stream was figuring out how to not show you my password so I'm going to use it from my one password app uh when I'm doing this for production apps I typically store this the password either in um like in the service itself like app service and container apps have their own kind of Secrets ability uh or I store it in as your key Vault so that's what I do when I'm you know when I'm making an a server a database server that I'm going to use with the production app is that I use some sort of secret functionality in order to store that password cool it's always a good practice Yeah Yeah there I see sometimes people store their um store their password in you know kind of the environment variables like the application settings for app service um but then it's just it's too easy to accidentally see it so you always want to use some sort of uh you know something specifically designed for secrets that will make it difficult to see it um and not you know accidentally expose it right makes sense uh so you can see down here that is creating the server it says it should be ready in several minutes um I of course have one that's already made and ready to use so that we don't have to wait for it because it can take it can take some time uh any questions before we start using it um someone asked again uh about what theme you switched to I think you said it was solarized this is solarized light uh so for me it's it's a it's a nice level of light and contrast yeah I definitely like that too um someone else just said they've been loving having um vs code with co-pilot gethub co-pilot in chat um helps them write queries and Views so just some oh nice yeah yeah I use co- pilot chat for writing my bash scripts that's like yeah I don't want to ever really learn bash yeah I feel like that's like you got to find like the things with C pilot that like just really speed up your your development workflow for some people that's going to be one thing for others it's going to be something just kind of depending on your background yeah if you want to know bash or not um it's really great in certain certain instances yeah so uh yeah so let's use one of my pre-baked uh pre-made servers so you can see in the list here we've got Vis Code test one and um I've actually already connected to it so what you can do is you can you know click on um you know the database and say connect to database and it'll actually connect to it and even show you some details about the database so this is almost has a functionality of SQL tools but actually not that much it will it does show you some things so right now it only has these default tables which is job and job run so these are ones that uh is your made uh so I could um uh connect to this this database all right so let's get connected to this one so it's called visz Code test one so what I'm going to do is um copy this into mymv I'm going to delete the password because uh I'm not going to reveal the password to you and I've got it already in the environment and I'm going to set the host to this so this is the name that I came up with and then this is always the rest of the URL the user was admin 123 and this was the database name and then let's go to our uh file here and uh so we should now grab everything from that EMV file the DB pass I already set that in my environment to what I hopefully used when I set it up so hopefully that should work if not I have a backup as option but uh yeah so let's try it out cool and I think someone just joined in I said um is this postgressql um so yeah this if you just want to say real quick that this you made this in Azure it's flexible server yeah this is the postgress flexible server from azer and now let's take a look and refresh and here you see restaurants so we can see that on our actual production database uh we have now made a restaurants table dang that was so easy just to move from local to that like I actually thought that was going to be a lot harder like in all sudden we just were there yeah we really just have to you know set up the these credentials now part of this is that okay okay yeah to be fair one thing you have to do is that in your um uh in your postgress server you do have to allow your current client IP I already allowed it for this one so there is a step before um let me see if I can do it with this one let's try uh connecting and it might what it might do is prompt oh did I already allow that one too okay um I'll show I'll show it in the portal let me show it in the portal um so that you can see um so I think that the the the vs code extension actually uh tries to do it for you but just in case the vs code extension doesn't do it for you uh I'm going to show uh what it what it looks like I think it's still making test three right oh maybe it exists now let's see okay so I think that exists now which is cool now we can go to networking and then uh in order to be able to connect to this from my current computer I need my current computer's IP address to be allowed so what I do is add current client IP address and so that makes a firewall rule which allows just this address and not other IP addresses um there's also ones where you can allow any as your service so the best practice is actually to always put your server inside a v-net a virtual net with you know with like your app service your container app um but you know it all depends on what you're doing so you know just keep Security in mind um but you do have the option to say okay this particular IP is is allowed to access it which is great especially just for like quick demo things like this we're just kind of playing around just nice little click the button we're added we're good yeah yeah so that is the the step you have to do but I do think that the extension tries to do it for you um yeah actually successfully set up server so let me see if we can because I think I did really similar I think I did tried to use the same stuff so let's see if we can now run the same code on that second server and we'll expand this and while that's loading um someone asked if they're gonna have issues with you know adding their current IP um if they're on a dynamic IP well if you're on a dynamic IP I think your your current like your IP at this time is fixed right so you should be able to temporarily do it um but uh but yeah you might have an issue when your when your IP changes uh there was actually a similar issue in uh code spaces because I was originally going to do this in codes spaces and you know you can like so you could allow the current code spaces IP address but then it could change over time so here's the um discussion uh about that because I thought people might find that interesting so code spaces on their road map they have a plan to uh make it possible to to actually access a postgress server that's inside of vnet okay cool because I also see questions so can this work through an an SSL turn tunnel to an Azure compute uh so I haven't I haven't tried it um it you know so if uh you know if this doesn't work so I know like my colleague is always doing stuff in his your Cloud shell um so you know if you're not able to use vs code there's also as your Cloud shell which you know usually has better access um you could also try using uh you know um ma manage identity and default as your credential um but uh yes so I haven't tried every permutation of how to access a database cool yeah so karma kodo um try it out let us know how it goes yeah um oh yeah so that one actually didn't work so it might be that I don't um I don't have that one uh set up yet so we'll go back to to this one so do make sure that you you know check your um you know check that the networking is allowing the particular IP address cool and that's good to know though for people who might be trying this at home it looks like you'll get kind of just a connection error if you maybe have to still do that one last step yeah yeah so if you get some connection area like what you got to do is you got to you know go back to your portal and check your connect workking check your um you can go uh check this uh connect connect will show you your connection string not going to show you in case it shows the password uh you can check the server name you can check the login name um so just check that all those details are matching and then the networking is set up appropriately and uh and that your SSL is uh set to require when you're connecting to an as your server cool good to know uh there's a question about which version of procress So currently I am connecting to uh it looks like the default right now is 12.15 but actually they they have quite a few other versions available um so you can I think go all the way up to 14 right now possibly even 15 when you uh create a postrest server um so uh let me see as your postgressql flexible server versions of postgress so I think we can definitely go to 14 possibly even more I don't know if it's going to say here uh but yeah keep that in mind because here's the thing is that it's actually hard to change your version after the fact so if you know you want a particular version of postgress you should start off uh specifying that version because if you need to change the version you actually you have to you have to spin up a whole new server and then do like a PG dump like dump your data out and then bring it back into the new one um because it you know the server are actually they're pretty significant in terms of big major versions like 12 to 13 to 14 they are you know significantly different um so uh you can't change that after the fact there's actually a bunch of things you can't change after the fact for postgress servers so you can't change the admin username you can't change the version um yeah so keep some things you have to be certain about before you make that server okay that's good to know yeah so you don't find yourself in a little pickle after and having to basically re put the new one yeah um okay oh so now let's show using SQL tools so let's go back to SQL tools um so for SQL tools uh what I can do is edit the well you can even make a brand new connection so um we could try making a brand new connection to see show what it's like so we are doing postgress and our elephant yeah with our elephant um con gr we don't need that okay so the server address is going to be this so we will go and put that in there the database is going to be postgress the username is this and then use password I'm going to say askon connect and then I'm going to say SSL enabled okay so I think I've got it all set up correctly save connection connect now and now it wants that password so I'm going to go to my one password U let me do it again uh there we go enter the password word connecting and it connected nice uh so and it starts off it always gives you this blank blank file for SQL uh Fe feedback to the extension creators I never use this file and then when I exit out of it it always says do you want to save changes but I didn't make any changes I I know I should open a p pull request um so uh yeah so we are connected so now we can take a look and go through here and we can see the restaurants and now we can click that magnifying glass and see all the records in it nice so so seamless yeah yeah and you can so in SQL tools you can do lots like you can uh you can run queries uh you can do like filters in here oh didn't spell cheese right okay there we go they all have match cheese as it turns out um but uh let's see how we do okay so generate insert query yeah you can insert and it it's got um an insert statement already made uh so there's a lot you can do uh in in SQL tools I would caution that when you're on a production database you maybe shouldn't be doing that much but I don't know it depends like what you're using your database for like maybe you're doing like data analysis um if I'm connecting to a database locally like this I'm probably just doing a lot of selects on you know like existing data um and not actually you know trying to insert more data but you know you do you that's awesome um let's see so um someone asks is this a core vs code function or just an extension feature so this is the SQL tools extension that's being shown right now yeah uh yep so you just search for SQL tools and you find the one from and you also Find the drivers and then you'll be all set up cool awesome I think that's all the questions right now where Rodolfo says what a cheese database datab cool yeah and so let's get some more data in this database um so uh you know I thought it'd be nice to have a bit more data than just this um so I found a um a good uh you know list of like a hundred restaurants okay so it's like 130 restaurants and I just found this on kaggle uh it was from a from a machine learning study or from a user study I think so this has lots of data in it so how could I load this in there's lots of ways you could load stuff into a postrest database but I found a cool technique that uses pandis uh so I thought we could uh we could try that so load geop places 2. CSV using pandis uh so we're going to import pandis and uh then we're going to read the CSV here we go um don't know how it was encoded so I'll just leave that off uh so it's going to read the CSV uh and now uh it has a bunch of it has a bunch of you know columns in it that I don't have in this data so um I'm going to well maybe I'll add some of these right so some of these look like maybe interesting so uh maybe I'll add the these ones let's see let's see how quickly we can add these to the table so add columns for a going to do latitude and that should be just a float going to do longitude uh I don't even know what the geometer is so I'm not gonna do that H name is a string address we already have so we can delete that uh city state country fact I don't think we need people still using fax yeah so let's let's keep the okay let's do that let's delete that uh let's see we already have name and address um we don't need facts because I don't know who uses that okay so we have all those and then I'm going to tell pandas to um keep only the coms we need for the restaurants table and so then uh we can do DF and then we tell it which which columns we want so it's going to be these ones ni yeah I I this is what I do like copil for is that it's actually really good at referencing your your previous data here so that creates a data frame with only those and then here's the really cool thing I discovered last night was this two SQL function where you can um you can tell it to you know reuse a SQL Alchemy engine so pandas actually knows about SQ Alchemy which blew my mind um so we're going to tell the table the engine um you know tell it whether it should append or replace and then um and then actually I'm going to tell it to use uh index of ID that's our primary key I think that's right dang that's so cool I did not know you could do that yeah uh yeah I only discovered this very recently so let me uh just try that out let me see I'll I'll I guess just delete these bye all right so let's see is it gonna work okay so it created the new schema it inserted lots of things let's go ahead and check our SQL tools and click on this we can see the new schema and then we can look at this and see that there is lots of data nice we can use the pagination now because got multipli pated oh right yeah okay so that's cool that's awesome um there are a couple questions coming through um someone asked um will my connections be stored in the project folder yeah good question so you'll actually notice if I go back to my um file explorer you'll see that it has made a DOT bz code folder with a settings. Json uh I assume that the extension did this I've actually never developed an extension so I don't know the mechanics of how these things work but you'll see that in here it does actually um have uh these things saved uh luckily it doesn't have my database because I told it it's going to ask for password true so what I normally do is that I I usually just delete this because I like to use what's in the dev container but you know if I was reusing this connection all the time then you could just save it in your settings. Json um and you know it's up to you whether you check it in or or not you might not want to you may not want to check it in um you know given that it has a a server URL in here and you may not want people trying to brute force their way into it yeah um but yeah so SQL tools uh you know the settings do automatically get put in the settings. Json for you awesome um and then Rolo also asked um he said I assume that this approach can also be used for jupter notebook so using the pandas and the to Sequel and everything yeah the notebook here we go I think I could just like copy paste this and put in a Cell yeah works right oh I have to choose a kernel I'm so bad is how do we python yeah I wonder if C I'm using the I'm in a python 3.11 devb container okay so I think I would find that yeah I'm not sure I'm not sure why it can't find the the Kel it's probably something about how my Dev container is set up uh so I'll have to ask my my Jupiter colleague Sarah uh for ERS but uh but yeah I mean this should all run fine in a notebook once you got the kernel set up awesome um and then water bunkle just also kind of did a mention of the rainbow CSV extension for anytime you're working with CSV it kind of colorizes it so that it's a little easier to read let's try it maybe I should add that to my Dev container so if you if you're working on a Dev container you can just click on this thing and you say like add to Dev container. Json and then it it puts it in your Dev container configuration automatically so now you can see it's in my Dev container H so let's what did that let's see what it looks like now wow look that that's a little pizzazz we love rainbows that's really nice um yeah Okay cool so we got we got all that data in um what else I think that was a lot of what I wanted to show actually um you oh yeah we can do some more queries right so let's do some more queries um so let's now that we've got like query you know query the database so I presume that if you're if you're connecting to a um you know to uh uh let me see I'm going to do with I'm going to use the context manager approach because it'll automatically um save uh treat this session correctly okay so we're going to query so with session engine is engine and then uh we'll just get all restaurants in Mexico and here we've got a select state so yeah we can start off with a select statement and then we execute that statement and get back the results and here we go print those out and here we can see nice all the names here now we can get a little fancier so get all restaurants inside a bounding box uh around around Mexico City oh I don't know is it gonna know the L to long if people interesting [Music] oh so this is getting a little fancier um is fancy geography really in there I don't actually know if this is the Bounty box for Mexico City no idea um and instead of actually doing this you know print all restaurant thing I'm just going to say print results and then what I'm going to do is go up uh here and um I have a few options I can add a repa myself so repa in Python is how things are represented at the like at the command line so so we could do a reer like this and um and that'll be nice so let's let's try that again print results um and I'm going to not drop so I'm just going to just do the queries this time because we don't need to keep on dropping our dropping our schema so this should work let's see okay uh so we do see that it found a these are the results from the first time so there you can see see the repa um we didn't find any results for the L long search I think that's because legitimately it doesn't have anything in the data for that so I'll change it to something so I think it does have it I've already looked at the lat long this so I think that this this search will work uh and yeah now you can see these results are inside that bounding box so we can do we can do more complex queries I want to show one other way um besides repa so repa is nice to you know have a nice display of a python object on the command line however SQL Alchemy 2.0 also has integration with python data classes and the way you do that is you add on mapped as data class as an additional subass for your base class and then we can remove the repa because it has an automatic repa oh and then we might have to I think we have to do init equals false here let's try this out and oh we might need to re oh no it did work okay so what you see is that um now it has an automatically created repa so every time we see a restaurant it shows every you know every single detail of it uh so definitely recommend checking out maa's data class which is in SQL Alchemy 2.0 uh because you'll get you know some nice things like that like the automatic repa um but also just some other things will just work work better that's so cool um all right we're coming up on the last few minutes so there breett LP says what is the sorcery with the lud logitude and then water bunkle must have actually looked up the latitude and longitude because they that's actually right so I did okay I did find a website um bbox finder which is a cool a cool uh a cool website and H oh I just have to that's not what I want here we go uh so this is the what we actually ended up searching but yeah Mexico City is presumably isn't it like gosh I've been there I should know oh yeah so mexic cities lower down here yeah okay yeah that's awesome knows it oh um this is so cool and I feel like so many just like really quick tips too of like how to make it even more efficient and how to like use what's already built in to your advantage um and just kind of knowing those tips and tricks because I feel like I mean I know like I'm definitely nowhere anywhere close to your level of python Pamela so I feel like I knew like the very like Basics and then like you would just bring up oh yeah but when also we can do this and you can automatically have these repr oh my gosh this is insane so it's always really cool to see that yeah yeah so as a reminder if you want to you know do what I'm doing I've you know got a good starting repo here um where uh it's got a lot of this stuff already set up awesome yeah and that's going to be in um our video description um it's also on the um screen right now um so you can go ahead and check out all those links after this um and then also the SQL tools extension I believe we also have Linked In the description yeah and the actual author just posted in the chat that is so cool that's a I love that thank you so much for all your work this is I love anytime we can I mean this is what it's all about right as the community and like getting to see these amazing tools that people build um and seeing them in action that's awesome I'm a big fan awesome all right well thank you so much Pamela before we I mean is there anything else you want to show actually I guess in the last couple minutes answer all good that's that's all all right well then let's go ahead and close out with just a really quick lightning round um of just a few questions just kind of the first thing that comes to mind um so this is always polarizing GIF or GIF GIF GIF okay and I will say that I have a long long history in gifs in animated gifs specifically because I was on the web back when animated gifs were first getting started and I I even accidentally shared animated gifs of of dogs uh going to the bathroom with Carl San in a chat room way back when it's crazy story but uh anyway I love that's awesome I was not expecting that little tidbit for that I love it I do love animated gifts okay um similar question I think you said this a couple times this stream so Jason or Jon oh Json I always have to think about it too and I'm like wait which one do I say I say Json too yeah yeah Jon um okay more personal one what is your biggest pet peeve uh dark mode no you just everyone in the chat just fire um no I like like in terms of programming I get really disturbed by by like Whit space like missing Whit space like when I did lots of JavaScript what I hated would be um let's go in the JavaScript console um if when you have a uh like if you know blah and then the curly bracket just right next to it oh yeah okay I hate it looks like the curly bracket is stabbing the parentheses it is so violent it's so violent so I need I need a space there otherwise I just freak out all right good to know I will never have you review a code where there's not that space there yeah I know was reviewing code yesterday which had a a like in Python it had a hash and then like the immediate no no no we need space that's awesome our viewers like that they're all saying LOL um okay and then last question what is one vs code extension that you cannot live without um uh I mean at this point like co- pilot probably is the one because I have like a one-year-old baby and I basically like she's old always napping on me and so I only ever have one hand and that one hand can only type so much so I'm always like oh go t t it's like my hands falling asleep just uh so yeah so right now it it's been particularly helpful this year since I only have like you know like half a hand that I get to use um besides that probably like um probably sequel tools is actually yeah and somebody actually asked about sequel versus SQL I actually have an entire video about that that I made for KH Academy way back when because I created an SQL course for KH Academy so you can watch my video about SQL versus SQL oh okay I'm looking that up right after this all right that concludes our lightning round um thank you so much Pamela you it's always such a joy to have you on here um I feel like I always learned so much from you um the viewers loved it thank you everyone for tuning in um like I mentioned make sure to subscribe to our YouTube channel so that way you can see the recorded up upload of this um and then it will have all those great links in the description um and then while you're at it you can go ahead and subscribe to us on Tik Tok as well if you want just to kind of see some of the shorts that we post um all right well with that P I'm sure we will have you on again at some point in the future too thank you so much for being here and thank you everyone for watching have a great day bye [Music] everyone

Channel: Visual Studio Code

Views: 11,359

Keywords: code, python, postgresql, SQL, codespaces

Id: _8nSXEIMMpA

Length: 59min 57sec (3597 seconds)

Published: Thu Aug 24 2023