About pandas if you use already used pandas and data frames there's much easier ways to load sql server data into them than the instructions contained in this tutorial.
So this is for people who aren't using pandas so firstly we'll learn how to install your example database of 10 films then i'll show you how you can connect a.
Sql server in python using the pi odbc module we'll look at how you can fetch rows three different ways to do that we'll look at how you can run action.
Queries to insert update or delete data and finally we'll look at how you can run stored procedures passing parameters parameters to them if necessary at the top right of your screen about.
Now you should see a link appearing as always and you can click on that to get any of the files referred to or used during this tutorial and any exercises if there are any and if that link isn't.
And hand control to sven will guide you through the rest of the tutorial as ever so let's get started so before you devote a chunk of your life to watching this excellent tutorial.
On linking sql server and python just a quick warning on whether you're using pandas and data frames what i'm going to do is show you how to.
Loop down a set of rows so we'll create a cursor to go to the first row in a table and then it will go to the second row and the third row and it will loop down the rows doing something to each in.
Turn now that's great but if you're working with pandas data frames you'll want to create something like this and you can do it with a single command.
In pandas by transferring the information from a sql server table into a pandas data frame so i think my advice would be if you've absolutely no idea what i'm talking.
About and think of pandi is a black and
White bear living in china then ignore what i've just said and carry on and enjoy the rest of this tutorial but if you're already working with.Pandas i wouldn't watch the rest of this tutorial and i would wait for the next uh dedicated tutorial on pandas coming up in this series.
So before we can link to a database we need a database to link to and what i'm going to do now is show you how to set up the example we're going to use in the rest of this tutorial obviously you can.
Easily use your own database instead in which case you could skip this part so i'm in sql server management studio what i can do is open up a file in fact i've got mine in my recent files list.
But otherwise i could have chosen open and then chosen file so i'm going to choose to open the file called creating movies database dot sql which is a an accompaniment to this.
Tutorial and what can i can now do is to execute this by pressing f5 or clicking on the execute button and what it will do is generate a database and a table and show.
The results of it so you can see if i right click and refresh my list of databases there's a movies database and if i look at the list of tables within that there's one.
Called tbl film and it contains those columns so that's generating my database what i can now do is show how to link to it in python so what i want to do is to create a.
Program which will link to my table of films and print them all out so the first thing i need to check is whether i have the module uh installed to do this.
So i'm going to go to my open up a new
Copy of a terminal window and in this i'll run pip space list to see what modules i've got installed and unfortunately i haven't got pi odbc.Which is the one i need so i'll run pip install and then pyod bc odbc stands for open database connectivity if you want to show off in a pub quiz.
And that will install the necessary module or at least it would if i could put the right number of ls in install so now that i've got that available to.
Me i can import it it's ready for use and the rest of this is really just a question of getting the commands working once and then whenever you want to use.
Them again you can just copy and paste there are many many different ways to run these commands in different orders but they all do exactly the same thing in just different permanent permutations.
So the very first thing i need to do is i need to create a connection so i'll call it cn and to the to do that i'll type pyodbc.
Dot connect and then in brackets specify the connection string now i'm going to cheat and the reason i'm going to cheat is because i don't.
Want to spend the rest of this tutorial discussing connection strings it's a huge topic i'm sure you'll all have your own connection strings already set up and i.
Really don't want to get into it here i'm using a trusted connection so i'm using uh rather than using sql server authentication i'm using windows authentication.
And i'm connecting to a database called movies in an instance called sql 2019 on my current computer so hopefully that will all work what i'm next going to do is set up my.
Query do that i'll create a variable it's called a qry and keep my variable name short for now and although it's not code sensitive i'm going to type it in in.
Upper and lower case combined so i'll select all the records from the table of films and just to show you don't have to do a plain vanilla select i'll do some.
Ordering by i'll order the order them by the film title and it just commented let's add a comment here saying create a.
Connection to sql server so the next thing i need to do is to create something called a cursor and the cursor is what allows you to go line by line or row by row through the records.
Do this i create let's call this csr and what i can do is use the connection i've created called cn and apply the cursor method to it now that i've got a cursor.
I can execute it against the query to basically get a load of records back i'm typing the comment comment execute a cursor i'm not entirely sure i know exactly what it does but i know it works.
And that's all i care about so to do this i can type csr.execute and in brackets i can specify my sql query and then the only other thing i need to.
Do after this is to fetch the records now i'll talk more about the various permutations for fetching records in the next part of this tutorial but at the moment just let's just fetch them all.
Fetch all and i can do that by creating a variable i'll call it films and i'll put csr dot fetch all.
And that's a method so i need to put an open and close brackets after it i'll test if this works to do that i'll just print out the films it won't look very pretty but it will at.
Least prove i've actually talked to the database and the final thing i need to do is very important i need to close the connection because i've left it open and it's a.
Very bad idea to leave connections to databases open and the way you do that is to type cn.close which won't surprise anybody i think that's going to work so let's.
Give it a go i'll try running my program and you can see it gives me uh it looks to me like i've got a list of rows and each row is a tuple if i could just highlight that one.
That's one row as a tuple and i've got a list of different rows so that's the basics of connecting to sql server you've now you've got this.
Code set up and running you can copy it and use it anywhere and it should always link to the sql query that you wanted to i've created another file called fetchingrose.py.
And copied all of the code from the previous one up to the point at which i was fetching all the records and because i'm a bit scared i'm going to forget to close the connection let's.
Just add that in at the end between this i can fetch one record at a time or fetch a lot of records a bucket or fetch all of them.
The different ways of fetching uh rows are shown in this picture called fetch methods dot png the three ways you can either fetch a single row at a time.
And the advantage of this is once you've reached the row you want you can stop you can fetch a chunk of rows or you can fetch every single row provided you haven't got too many rows.