SQLAlchemy & Copilot Pt 1 - Defining Models

I'm Pamela a python advocate for azir when I'm writing a python app and I want to communicate with a database I often use the very popular orm and SQL Alchemy this RM allows me to describe my tables using python classes and then use methods and functions for inserting data and querying data instead of needing to write raw SQL SQL Alchemy recently released version 2.0 and it's got a lot of changes the good news is that these changes make SQL alchemies work better with python type Checkers like my pie but there is a bit of a learning curve to the changes so in this series of videos I'm going to create SQL Alchemy 2.0 models insert data and query the data I'll be doing it all inside vs code which means I get to use helpful extensions like GitHub copilot and SQL tools here I am inside BS code but how do I get started well since we are using a new version of SQL Alchemy I do recommend looking at the documentation for SQL Alchemy it's all updated for version 2.0 so you can look at the reference docs you can look at the change log whatever it is that helps you see how 2.0 works you could also look at blog posts like this one from Miguel Greenberg which talks about what's new in SQL Alchemy 2.0 it's a really nice high level overview to see a diff especially for those of you who have already worked with older versions of SQL Alchemy so what I'm going to do is actually just start from the SQL Alchemy orm quick start code and the reason I'm going to do this is because I am trying to use copilot here and copilot has seen all different versions of SQL Alchemy so if I just start typing now it's probably going to use an older version because that's what it's seen a lot of so I want to give it enough information so it knows what version that I wanted to use let me paste this in here and we see that it's got a bunch of imports it's got this class base which inherits from declarative base and this Base Class becomes the parent class of all our models and that's an important part of SQL Alchemy 2.0 and then this has a user class and address class I'm going to delete most of this here because I don't really feel like I need it and I think this is going to be enough so that copilot has a good idea of what we're working with here I'm going to be using SQL Alchemy to model customers and orders and products for a store so I'm going to change user to customer and also change the table name to customer and here I've got a name and a full name and these are using the new SQL Alchemy interface which for each of the column attributes you see there's a colon and then mapped bracket you know some data type so this one's mapped bracket string mapped bracket int and then this is mapped bracket optional string so this way of doing it actually makes it so that SQL Alchemy models are now compatible with python type Checkers and that can be really important to lots of people who have gotten used to using tight pins in Python code and getting nice feedback from the editor about whether they're using something correctly if we don't need any additional constraints on something we can just say you know full name is a string but then if we do want additional constraints like here we said name is then we say mapped column and then we use the SQL Alchemy column class like string or integer foreign key and we give the con you know the additional constraints the customizations so this one can know cannot be any longer than 30 characters that's a bit constraining so I'm going to change it to 100. let's go ahead and add more things to this customer class I want customers to have an email address and that will also be a string I actually don't know how long email addresses can go so I'm not going to limit that I do want customers to have an address so I like that suggestion and I also want them to have a country code and this one I do want to constrain and just as copilot suggests I want to contribute it to two characters because this should be a two character country code that looks good I want to add a repra first just so we have easier debugging and a nice way of viewing our customers in the console so that looks good there actually I want to add a credit card class next because I want customers to have a one-to-one relationship with credit card so each customer can have a single credit card so we need to inherit from base and then for our credit card we need a table name so table name equals credit card you know every model we have does need an ID that's a primary key and then here you see it wants to relate it to the customer ID using a foreign key that looks good and then we have the actual number and this one set it to 16 which is good if you're doing just us credit cards if you're going International I think 19 is the limit so this is the customer ID one thing we can do for inconvenience is also add customer just as copilot suggests here and so that's gives us a nice way of referring to the customer and having a customer instance that we can use so here you can see mapped customer mapped column and it has a relationship of customer and we're getting a squiggle here and that's because copilot has actually slightly bungled this so what we could do is you know look at these docs here look for a relationship and we see that it's just relationship we don't need to put that mapped column in front of it so we're going to remove that mapped column no more squigglies when you are using copilot do have the documentation open for what it is you're working with so that you have a nice check especially if you're getting some reports of Errors from your linter or your tests as we see relationship and we can also specify this back populates which populates a field on the related class so maybe we should add that too and this one wants to do background ref and we'll make it credit card I wonder if that is an older way of doing things so let's go and change it back to back populates and we'll add a wrapper for this one as well oh and you see it wants us to add something up here and let's say it will add a one-to-one relationship to credit card so now it says credit card mapped credit card equals relationship credit card and then back properly it's customer make the line shorter and one thing that's interesting to point out here is that credit card here is in quotes whereas customer here is not in quotes and the reason for that is because at this point when python has read this code when it's reading this code it hasn't yet seen the credit card class so if we had it without quotes we would actually get an error that it's not defined because you know we're reading from top to bottom it hasn't seen it yet so what you can do is just put your class name in quotes and the type Checker will understand that all right so now we're going to add a product with a name price description and category so let's see if copilot can just do that for me table name is product got an ID it's got a name it's got a price it's got a description and it has a category very good and next we're going to add a repra to the product just like we've done for the other ones for easier debugging finally it wants us to add an order and it says order should have a customer ID product ID and quantity that sounds pretty good class order and here it's from base a table name equals order got an ID like everything a customer ID a product ID quantity a repra and I also want to add in the nice convenience attributes here so you'll see this one customer relates customer back problem leads order so that means that we should go back up to customer and add a one-to-many relationship to order so actually this is probably back populate orders that would make more sense from a naming perspective and here you can see that it is a list of orders and order is once again in quotes and the relationship is order and it back populates customer and this is customer here so that that seems right so we have product and it relates to product and let's give that a back pop leads as well and product is also going to have a one-to-many relationship to order so this should look very similar here pretty much the same line we've written these four classes with a lot of help from copilot trying to use the new SQL Alchemy 2 interface were we successful one thing we could do is actually run the file and see if it runs without any errors ready finger crossed and it did run no errors that's a great sign but I still haven't connected our code to a database in the next video I'm going to connect to a sqlite database populate the tables with data and use the SQL tools extension to inspect those tables see you there foreign

Channel: Visual Studio Code

Views: 7,579

Keywords: vscode, visual studio code, vs code, githubcopilot, SQL, SQLAlchemy, copilot, code

Id: qS7ueUcQfjI

Length: 9min 43sec (583 seconds)

Published: Mon Aug 07 2023