Connect to PostgreSQL Using SQLAlchemy & Python

have you ever wondered how to connect to a postgres database using sq alchemy well if you have you're in the minority but you're in good company today we're going to do exactly that we're going to use sql command to connect the psql so that way we can create our future equities and crypto price database let's dig into some code again let's dive jump let's create some code we'll start by opening jupyter notebook now obviously we don't want to have to use jupyter every single time you want to establish a connection to our database instead we kind of want this stuff in the future to happen behind the scenes but for now we'll use jupiter to understand the code that we're going to create and and what's happening under the hood how to create you know what what is a database engine what is a session you know what is a you know database driver and after we discuss all that have a firm understanding i'll upload this jupyter notebook to github and also upload a separate file that contains just the stuff that we need so that way in the next video when we're creating the database model and tables we'll just import this stuff get a connection and away we go so hopefully that makes sense so with that being said let's go ahead and get understanding this sql how can you step okay so see uh sql alchemy is essentially a database wrapper right an orm wrapper or object relationship mapper we use sql alchemy and we can use any database with it in our case we used postgresql so sql alchemy needs you know a database driver which in our case is side cop g2 and that connects to postgres itself you know we could you know have other drivers connect to other database we don't want the underlying database architecture to matter we just want to be able to use sql alchemy code in order to manipulate our database and don't care about the backend so that's the benefit of using sq alchemy now whenever we're creating a connection using sq alchemy we have really two things that we have to worry about we need to create an engine and an engine is something where we pass our database credentials to and then once we have an engine we create a session from that engine and we get multiple sessions per engine if that makes sense let's grab those imports do chrom sq alchemy import create engine from executor alchemy dot orm import especially maker and then we're going to also import some utilities so from sq alchemy utils import database exists and create database essentially if we you know don't have a database we want to check to see if the database exists if it doesn't exist we want to create it and then finally we want to import our you know connection settings so i've simply created a localsettings.pi file and your password should be different than password and if you don't know how to create a esql database we talked about that in the previous video i'll show you how to do that and create permissions to that database but essentially we're going to use that python file to store our credentials and then we don't include that in our github or we use dot you know our dot git ignore file to ignore that from pushing it to a repository we'll do from local settings import let's create sql add settings and then hit enter so that looks good so first things first we'll create our engine and then after we have our engine we'll create our session so let's create a function get our engine so def get engine and pass it to user password host port and db pass it the url string we'll do postgresql that's what we're using user password the host port and the database and then we'll say if not database exists or essentially the database doesn't exist read it and then let's return the engine engine equals create engine url full size and echo false because we don't want to echo every command and then return turn ending okay that looks good now let's create our first engine engine equals get engine settings eg user okay that looks like it worked and we'll receive the engine will look at the engine url and we can see that it's connected to database alpha username alpha here's our password and we're connected to localhost using postgresql awesome now before we create a session you know i don't have to pass this username and password every single time to get engine let's create another function here to do that for us right so we'll do def get engine from settings keys equal pd user eg password pg which is essentially all of the keys that we need right in order to create a connection to our database now we'll do if not all e in key so we're saying for every key in here make sure it exists in the settings.key so for e in settings and then if they if it doesn't exist we raise an acceptance section say bad impact file and now let's just return our engine we don't need an else because it will never get that far right i mean we don't need the else because and if it's false it'll just raise the exception okay so we'll do get engine in fact i don't need to do that i'll do this copy this now return that engine and enter perfect now what we can do is we'll create that session so sort of along the same lines we'll do define a new function get session engine equals get engine from settings and then session equals section maker we bind an engine and then that would return you know a session maker and we just run that to get an individual session return session okay perfect and now let's get an actual session discussion equals get session and we'll just double check to see if it exists perfect okay so that shows us that we have a sql alchemy rm session and that's it now you are able to connect to postgres using python and sql we're going to use this connection in the next video where we create a database to store crypto tickers with minute bar pricing and after that in other words there's a lot we've got to cover i hope you enjoyed this video i hope you're excited about some of the things that i just mentioned if you found this valuable please like and subscribe and i'll see you in the next one thanks

Channel: Analyzing Alpha

Views: 2,258

Keywords: trading, investing, data science

Id: neW9Y9xh4jc

Length: 8min 37sec (517 seconds)

Published: Mon Oct 04 2021