SQLAlchemy: Object Relational Modelling (ORM) for Python, quick-start

I'd like to briefly look at SQL alchemy which is an object relational modeling library for Python and ORM in general lets us take particular database models in whatever our DBMS we like to use particularly good or M libraries will provide more options more dialects and allows taxes like SQL alchemy what we're going to be looking at allows us to access Microsoft SQL sequel server or sequel Lite Firebird or Kol my sequel poster is sequel essentially it's allowing us to take the OOP constructs like classes methods constructors so on and relate them to those models so that when we're working with say a front-end framework to deliver a web application it's easy for us to reason about those objects as being kind of first-class in Python rather than worrying about blocks of SQL and ensuring that all of our robes and sessions are closed and handled and dispatched correctly so what we're going to be doing here is creating a small test project create a virtual environment so that we're not mucking up our global Python dependencies we are going to assume that virtual environment running the activate script we're not going to install as cool okay all right so let's write a trivial example create engine takes this kind of Uri like syntax which is going to allow us to specify which engines and options to pass in order to connect to a particular our DBMS in this case we're looking to do a little trivial hello world type example so what we're going to do is create an in-memory sequel like database we can do that with the following syntax if we're to specify file name here instead relative it would create a file name there and sorry excuse me objects that were created would persist between sessions sequel Lite is pretty performant it handled a lot of data and I've been using it for several years without issue across many languages the various bindings and libraries if we provide an echo equals true keyword option to create engine then we're going to get a dump out of all the sequel that's executed here now what's particularly nice that we get this base class creating function called declarative base and when we take this we can create a base class that's going to be shared by all of our models and let's say we're creating a person model for some kind of business logic so we subclass the base the declarative base we made here and now we're going to bring in a few more imports from sequel cloaca me call them integer string foreign key say good for now we've got to set a saturated table name I recommend lowercase singular dynamic convention and now we can use these column classes to create our various fields or columns on the table so we'll say we're going to create a primary key which is going to be an integer specify it as follows we'll create a name ribbon you know what that's a little bit more API like say that it's the user name we're dealing with and then it's going to have a uniqueness constraint we'll just create no other properties something that simple so now having subclass that base object we are going to be able to use sequel kameez kind of introspection facilities to create those tables to fill it all out and we'll go and bind it to the engine that we've made and if we run just this example we should see the table being created oh okay it's checking to see that table exists here in the first place in our case and then here it's exactly what we'd expect unique username primary key ID and that's this turn so now we're going to take this a little bit further session maker allows us to create a session Factory which is bound to the engine that we set up so what happens now is let's say for example if you wanted to create a session and work with it should be any issues there the session is kind of like a transaction we'll be able to add objects remove them make changes in this context and then when we're ready we commit them so if we wanted to now that we've setup the schema and we've created the session maker and we've created two tables if we wanted to use this declarative syntax with sequel alchemy to create a user for example what we would do is let's go lower case names and say the session will add that and we're going to close and we should see as a result this transaction we're yep the user that we created and specified in our iPhone X and tags here creating that object of setting its values is automatically created converted into a relevant sequel query and executed uncommitted now if we wanted to go and for example find users that were in the database so let's say for example that again we were to remove this from memory and we for our first pass here we'll save a user's database and we'll call it users DB so we're going to create that but now we're going to immediately remove the code to add that new user instead we want to see who's in the database what users do we have here since if we do this now it's not going to have any issues and you also note that when it does this pragma in order to determine that person table already exists it does not create it again does it need to knows that here what we're going to do is oops we've created a query that's all encompassing this has simply give us every single user and when we get those results which we've placed in this locally declared variable called users we iterate over it and we print it here it's going to be an object that again as our base derived user column object and it's going to have methods that make sense in context like here to say that's going to give us naturally I've expected here and so this is kind of our hello world example that's how we can use something like SQL sequel community in order to I've always had a problem with mixing up SQL and sequel anyways this has been kind of the hello world example to creating a model and storing in a database using sequel alchemy thank you

Channel: James Skarzinskas

Views: 121,769

Keywords: sqlalchemy, python, orm, sqlite

Id: OT5qJBINiJY

Length: 10min 7sec (607 seconds)

Published: Sun Sep 24 2017