Detailed Notes
Focus for this episode: This episode continues our look at stored procedures and adding those.
This tutorial walks through the creation of a database synchronization tool in Python. It continues our Python and SQL Tutorials with a useful application that provides a deeper look at SQL structures and commands.
Repository For Code: git clone [email protected]:rbroadhead/dbsync.git
Transcript Text
[Music] well hello and welcome back we're continuing our database and our sql and python series of tutorials with these combos and doing our database synchronization script last time around we started looking at stored procedures and we got into some uh moderately useful queries let's go ahead and just like we'll walk back through some of these so what we did is one of the things we're looking at is we were getting our routine names based on our database name so our database name is tutorial and we're going to get our procedures that exist in there and so we see we've got a whole bunch and this was one that we created back in the sql tutorial so there's a whole bunch in there then you can see the uh some of the details related to those now one of the things we did is uh we went in to look at the source code so if we go look at the one called while loop then we get to see that it was a pretty straightforward you know definition there if we go back and look at the names one that we know had some parameters because that was the next thing is one look at parameters and actually the i guess we'll start with that so if we do and they live in the parameters table so if i just do in the information schema parameters we'll just look at this first it's just bringing back a little over 2 000 rows and you can see here we have a catalog and so we can actually which is the um oh actually no that's not what i want it's the we have a specific schema so we can see here uh let's see we can see well i've got a lot of tables in here let's nail down our schema and we're going to do where specific if i can spell it specific schema equals so let's narrow it down to our database which is what we're going to need to do anyways we get into this we're going to pull everything so now we've got everything our database and we can see here specific name so that's going to be our each of these that's going to be our store procedure so and we're going to do an specific whoop specific specific name equals uh in this case like let's do while loop uh well we do have one for wildlife but let's go look like multipliers got three so let's look at those so if we look at that we have a three of them we have three variables now this has this ordinal position so it's where does it exist in the procedure so we definitely want to do order by and that's fully spelled out so ordinal position just to make sure that they come through as one two three and we see here this is actually got a fair amount of information so we have um we have an identifier basically you know what is the uh what is the type which i think is what we're going to be able to work with you have a data type but you can do something here that's a little more specific so what i could do is uh if it's remember we're doing like our create procedure and we're going to call it like here the multiplier i think it's as and then we give it the parameters so the parameters are going to be uh these by parameter name so it's going to be like uh x y and z but remember we have to do whether they're in or out so this is a this is an n this is an out this is an in out and then we have our data type we're going to use our data identifier instead and then we get our source which if we do for let's see let's do here if we get our source it's pretty simple and so it looks something like that let's see what happens if we try to do that so if we come in here and we do delimiter there and then come back here i want to do that and do delimiter semicolon we'll get back to that let's see how this works we may have to tweak we have to jump back because this may not be exactly what i did so let's uh uh where am i going i'm gonna go to this database and i'm just gonna jump in and do it this way because i can let me do a mysql i'm gonna do use tutorial uh actually let's see let's see do i already have a temp okay use temp and now if i do this i have an error in my sql syntax so it's not oh it's not as so if i do go back to um let's go back to wherever that's at i think it's here um [Music] maybe i don't have it here oh so we got a cd sql demo uh let's see let's do grip what did i call that i called that multiplier let's go find that one there we go so if i do here uh let's see so if i do vid14 we're gonna see that he looks basically like this so let's get out of that whoop there we go and then if we do my sql use temp so let's go look at what this looks like actually let's go bring it over here so that's what we started with let's see how close were we to what actually should we do it so we should have create we can do or replace procedure we want to give it we do want to give it the database name because we won't necessarily be there oh we don't need an as and then we do our uh got everything there and then we're good so let's see if that works it may not like the size so we may have to oops i don't want to make a tutorial i want to make it oh good i did it wrong anyways spelled that wrong so if i come in here there we go so now i have created that so this is what we have to do and it's not too terribly difficult so let's go look at it because this is the this is what i want to execute and so let's jump back here where we were creating it oh and let's see it's missing procedure oh missing procedures so what we want to create whoops let's do this maybe he'll do it right there we go so that's what we want to create so what we've gotten from here is we have our source results we've already picked we've you know pitched those out so now what we want to do is we want to grab our let's do print and it's going to be variables and what we need here is it's going to be a little different because this one's going to be we'll call it the var query and we're going to go pick up let's see what we want from it so we want uh we really don't need the ordinal position because that's going to come in but we do need parameter mode we need parameter name and i think what we can do is we can get away what we want to do is we're going to have to find some that are vars so let's go look at that real quick um let's do dtd identifier ident fire okay so if we do those there we go and so now let's do this where we just grab all of them because let's see if we've got yeah so we can see here so we've got like you know whatever it is we need to do so we'll be able to build them just with these and obviously we need it where there is not a parameter name that is missing so um let's see and so we've got those things oh and let's do parameter name is not no i'm not sure why we would have that we'll probably have to look at that one at some point there we go okay so we're going to take that [Music] and we're going to have a little bit of so we're going to have to do some work on this guy because tutorial is actually source name what and the specific name is going to be the proc name let's see we'll get it to there and parameter name is not no okay and [Music] doing this one somewhere let's see so we come in we do that that oh and then we have to close it there we go so that's our var query uh and we'll do this because we're going to need a cursor and so we're going to do var cursor from the source database and then we're gonna execute with our var query and then we're gonna do uh four palm row in oops we need results and that's going to be invar cursor fetch all for that in that and for right now let's just print that row and see how it looks so uh let's see let's get out of there it's out of there out of there python three main and so now we're seeing all this stuff gets split out and we can see our variables and i think we'll wrap this one because the next thing we're doing is going to actually build that out so now what we're seeing is we're able to go through we're able to find uh missing uh store actually i don't even know where we're finding our missing store procedures we're grabbing the source we're grabbing the variables so now we're going to come back next time and we're going to for each of these we're going to create this make the call and see what happens that being said i think it's a good time to wrap it up so we will so go out there and have yourself a great day a great week and we will talk to you next time you
Transcript Segments
[Music]
well hello and welcome back we're
continuing our database and our sql and
python series of tutorials with these
combos
and doing our database synchronization
script
last time around we started looking at
stored procedures
and we got into some
uh moderately useful queries let's go
ahead and just like we'll walk back
through some of these
so what we did is one of the things
we're looking at is we were getting our
routine names based on
our database name so our database name
is tutorial and we're going to get our
procedures that exist in there and so we
see we've got a whole bunch and this was
one that we created back in the
sql tutorial so there's a whole bunch in
there
then you can see the
uh some of the details related to those
now one of the things we did is uh we
went in to look at the source code
so if we go look at
the one called while loop
then we get to see that it was a pretty
straightforward you know definition
there if we go back and look at the
names one that we know had some
parameters because that was the next
thing is one look at parameters and
actually the i guess we'll start with
that so if we do
and they live in the parameters table so
if i just do
in the information schema parameters
we'll just look at this first
it's just bringing back a little over 2
000 rows and you can see here we have a
catalog
and so we can actually which is the um
oh actually no that's not what i want
it's the we have a specific schema so we
can see here
uh let's see
we can see well i've got a lot of tables
in here let's nail down our schema and
we're going to do
where
specific
if i can spell it
specific schema equals so let's narrow
it down to our database
which is what we're going to need to do
anyways we get into this we're going to
pull everything so now we've got
everything our database
and we can see here specific name so
that's going to be our
each of these that's going to be our
store procedure
so and we're going to do an
specific
whoop
specific
specific
name equals uh in this case like let's
do while loop uh
well we do have one for wildlife but
let's go look like multipliers got three
so let's look at those
so if we look at that
we have a three of them we have three
variables now this has this ordinal
position so it's where does it exist in
the procedure so we definitely want to
do order by
and that's fully spelled out so ordinal
position
just to make sure that they come through
as one two three
and we see here this is actually got a
fair amount of information
so we have
um
we have an identifier basically you know
what is the
uh what is the type which i think is
what we're going to be able to work with
you have a data type but you can do
something here that's a little more
specific
so what i could do
is
uh if it's remember we're doing like our
create procedure
and we're going to call it
like here
the multiplier
i think it's as and then we give it the
parameters so the parameters are going
to be
uh these by parameter name
so it's going to be like uh x
y and z but remember we have to do
whether they're in or out so this is a
this is an n
this is an out
this is an in out
and then we have our data type we're
going to use our data identifier instead
and then we get our source which if we
do for
let's see let's do here if we get our
source
it's pretty simple
and so it looks something like that
let's see what happens if we try to do
that so if we come in here
and we do delimiter
there
and then come back here i want to do
that
and do delimiter
semicolon we'll get back to that let's
see how this works
we may have to tweak we have to jump
back because this may not be exactly
what i did so let's uh
uh where am i going i'm gonna go to this
database and i'm just gonna jump in and
do it this way
because i can
let me do a mysql i'm gonna do use
tutorial
uh actually let's see
let's see do i already have a temp
okay use temp
and now if i do this
i have an error in my sql syntax so it's
not
oh it's not as
so if i do go back to
um
let's go back to
wherever that's at
i think it's here
um
[Music]
maybe i don't have it here
oh so we got a cd sql demo
uh let's see let's do grip
what did i call that i called that
multiplier
let's go find that one there we go
so if i do here
uh let's see so if i do vid14
we're gonna see
that he looks basically like this
so let's get out of that
whoop
there we go
and then if we do my sql
use
temp so let's go look at what this looks
like actually let's go bring it over
here
so that's what we started with let's see
how close were we to what actually
should we do it so we should have create
we can do or replace
procedure
we want to give it we do want to give it
the database name because we won't
necessarily be there
oh we don't need an as
and then we do our
uh
got everything there and then we're good
so let's see if that works
it may not like the size
so we may have to
oops i don't want to make a tutorial i
want to make it oh good i did it wrong
anyways
spelled that wrong so if i come in here
there we go so now i have created that
so this is what we have to do and it's
not too terribly difficult so let's go
look at it
because this is the
this is what i want to execute
and so
let's jump back here
where we were creating it
oh
and let's see it's missing procedure oh
missing procedures
so what we want to create whoops
let's do this maybe he'll do it right
there we go so that's what we want to
create
so what we've gotten from here is we
have our
source results we've already picked
we've you know pitched those out so now
what we want to do is we want to grab
our
let's do print
and it's going to be variables
and what we need here
is it's going to be a little different
because this one's going to be
we'll call it the var query
and we're going to go pick up
let's see what we want from it
so we want
uh we really don't need the ordinal
position because that's going to come in
but we do need
parameter mode
we need
parameter name
and i think what we can do is we can get
away
what we want to do is we're going to
have to find some that are vars so let's
go look at that real quick um
let's do dtd identifier
ident
fire okay
so if we do those
there we go
and so now let's do this where
we just grab all of them because let's
see if we've got
yeah so we can see here so we've got
like you know whatever it is we need to
do so we'll be able to build them just
with these and obviously we need it
where there is not a
parameter name that is missing
so
um
let's see and
so we've got those things oh and
let's do parameter
name is not no
i'm not sure why we would have that
we'll probably have to look at that one
at some point
there we go okay
so we're going to take that
[Music]
and we're going to have a little bit of
so we're going to have to do some work
on this guy because
tutorial
is actually
source name
what
and the specific name is going to be the
proc name
let's see we'll get it to there
and parameter name is not no okay and
[Music]
doing this one somewhere let's see so we
come in we do that
that oh and then we have to close it
there we go
so that's our var query
uh and we'll do
this because we're going to need a
cursor
and so we're going to do var cursor from
the source database
and
then we're gonna execute with our
var query
and then we're gonna do uh four
palm row
in
oops we need results
and that's going to be invar cursor
fetch all
for that in that
and for right now let's just print that
row
and see how it looks
so uh
let's see let's get out of there
it's out of there out of there
python three
main
and so now we're seeing all this stuff
gets split out and we can see our
variables
and i think we'll wrap this one because
the next thing we're doing is going to
actually build that out so now what
we're seeing is we're able to go through
we're able to find
uh
missing uh store actually i don't even
know where we're finding our missing
store procedures we're grabbing the
source we're grabbing the variables so
now we're going to come back next time
and we're going to for each of these
we're going to create this make the call
and see what happens
that being said i think it's a good time
to wrap it up so we will so go out there
and have yourself a great day a great
week and we will talk to you
next time
you