Detailed Notes
Focus for this episode: This episode, we wrap up our look stored procedures and adding those to our target.
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 series of basically the combo series we're looking at python and sql we're putting a little bit more into uh an application type of you know point of view for this tutorial and what we're doing is building a database synchronization app in python focused on my sequel although it also as we've mentioned in the past can work for mariadb and honestly probably some others we haven't gotten into if you get into like a sql server if we get into oracle or db2 those are a little bit different but this would give you at least a foundation to do so this episode instead of going down that digressing there we're going to talk about actually creating stored procedures now last couple episodes we've gone out and we've been able to figure out where are the procedures that exist in our source basically we're using our information schema routines and it's based off of a database name and then we come in we get the the source code for it which actually happens to be um stored in a different place from the parameters and now we've got the parameters if we see here uh last time around that's what we were doing so we're looking at the parameters so what we're going to do now is we're going to work on building out our sql so to create it so uh let's go ahead and leave that one there and we'll leave all the debug stuff in there for now so what we want to do though is let's start here so this will be the um we're going to call this the proc sql and we're going to start with a creator replace procedure and then we're going to add in the schema name which is going to be source name uh where'd they go and then we're gonna do a dot and then we're gonna come in here and we're gonna need to add our procedure name and that goes right here and so that gives us this first line we're going to add in we'll go ahead and open our parentheses and we will do that for now and so what we're going to do is we're going to uh so for each of these parameters what we need to do is we're going to do products equal equals proc sql plus so now we're going to add our parameters and it needs to be uh the direction the variable name and the type which happens to be zero because we can see it here it lines up pretty well so it'll just be zero one and two but we do need a uh space and then we're gonna do uh palm road let's just do it this way so we can probably do it like this and then we're gonna do it like that then we're gonna go one and two and we're going to need to do we're going to say first equals true uh let's see so uh if first let's just do yeah if first equals false then first we gotta take care of our comma so that's going to be that and we'll create our comma and will be nice we'll put a space after our comma so there if it's uh first equals false then we're gonna first put a uh we're gonna put a comma in there to separate these out and we'll come through here and then we're going to say first equals false and instead of palm row we're going to do um we're going to actually pull the whole sql out and this will be uh proc sql and so that gets us to here so when we're done we need to close off our parameters so we close off our parameters and then we're going to take our code which is just a straight up uh and let's do this we're going to do we're going to actually do this a little bit we're going to do a delimiter [Music] that uh we'll wait on this let's do let's see how this works first i think we have to do a delimiter but i think we have to call that beforehand so uh let's see uh we may not we may be okay with this uh so we're gonna print the proxy oh we need to do we just need to add now we've got our we've got our parameters set up we're all set up to do that plus we've got a space so she should be good to do the source which is here and actually let's just do this let's do uh proc source equals because what we can do is we'll just sort of save that off that we don't have to worry about cursor issues and then uh we do want to do oh we're gonna have to close some of this stuff off so what we want to do here is let's see we're gonna do fetch all blah blah blah we get that so now we can do uh which one is that that's gonna be uh proc cursor so we can go ahead and close it so now we've got the procedure cursor and then when we're done here we're going to want to do the same thing let's see since this is going to be plus proc source and we want to close uh this cursor of our cursor make sure that gets closed off and do we have any others we've got proc cursor uh oh we have var cursor and we have that's it okay so we've got our two we've closed our uh everything off and we're gonna print this out so we're not actually gonna call it at this point so let's go ahead and take a look at that and we're going to see here uh i need to create procedure while loop so here's our while loop we're going to create that create a replace procedure blah blah blah now let's see if we can go ahead and we'll do did we create an update cursor i think we did uh cursor so we'll just use cursor destination at this point so destination procs equals that so we could actually close and close him afterwards because we don't need him anymore that means we're also going to need to do cursor source close so we're sort of cleaning up our properties here or our uh resource a little bit here which is always a good thing one two let's do that whoop and we need to uh let's see so we're gonna do uh let's not ah do we need to we probably don't need to close it yet so let's do this let's do a uh cursor destination xq so let's do this we'll just keep using that we're going to come in here and we need destination source we're going to close both of those up and we don't really need the cursor anymore so we can come into here [Music] and for each of these we can execute proc sql and so now let's go jump over somewhere to here we're going to jump to our database and if we do routine name from here these are our sources but now if we flip to our local uh which i don't know where that is there we go if we go to local same database select routine we have none so now what we're gonna do is we're gonna go ahead and run this and see what it does let's see if we get errors or not uh sort of running so let's see did any of them run yes some of them did so now we have to look at this specific one is we've got a case here where let's see ah we've got an issue here so this one tutorial greeting for some reason has got a procedure to it i mean a parameter to it somehow and let's go back here let's go back and look at this [Music] and we want it to be greetings so if we do let's go from here this is going to be out in our demo is that it yeah i think it's demo [Music] tutorial and our schema is going to be tutorial and our procedure name is going to be whatever that procedure name is greeting so if you look at that we have name and flag coming through so is that correct it should be we print it and then we try to execute it for some reason the variables are not coming out right so if we do tutorial greeting interesting that it's not those for some reason are not coming through so we do of our results cursor var cursor var cursor var cursor interesting so for some reason we're not getting somewhere along the way we're not getting this to work and it's because why this is going to be a nice little an irrational thing because here we're getting those names right when we go into var results which is the var query which is here let's print that that may not work at this point because we may have to let's see what we got here okay good so it did die again is it the same one it's not dying on the same oh yeah it is on the same one okay excellent so we can take that and if we look here it does get stuff back so the parameter name that comes back is name and flag and when we do okay so it does variables and we're not printing so let's go ahead and print the uh results let's do print of our results whoops and let's see if that shows us something useful uh we come up here of our results in name flag okay for some reason it's not coming through here so for palm row in there okay let's do if not first maybe it's that's what it doesn't like so it should come through for each of those and oh we managed to do this that's our problem so let's go in here and let's do because we probably just screwed something up so let's go to our local and we're going to do well we'll do a credit replace so we'll be okay okay so we can do that that's right so it's going to try to replace it each time anyway well let's do this prop procedure and then we need these four okay let's do this and we're going to drop these procedures and we'll put a little semicolon here okay so we dropped our procedures now i'll try it again because what we're doing is we weren't building our sql correctly so if we went back we were probably we would see stuff without parameters so we've saved it let's take a look we run through cool and it looks like we have everything it's like if we look at let's look at multiplier let's make sure we've got those local okay we've got those and i believe it would be so so if we do call multiplier uh no that one probably isn't one that i want to use because i have to set something up let's do i think while loop uh here we go uh we do so let's just call simple exception because all it has is a value that comes in so let's do call how did we rent that that was so we do a simple exception test value there we go so we get our message back so we're we're getting our parameters in if we look here we see our 17 procedures if we go back to our original we're going to see uh let's go rv demo there we go same thing whoops we see 17 and here we see 17. so we have now been able to bring across store procedures with their source and their parameters and pull that whole thing in and give ourselves a a more complete database now there are a few things left if you look at what's out there we also can take a look at triggers and user functions and those will be some of the things we go to next before we swing back to a couple of more complicated tricky kind of things that we're going to run into when we start looking at triggers and maybe some workarounds we're going to do there we're also going to make this a little more i'll say generic and be able to set it up we're going to work on cleaning this up a little bit with some parameters and such like that so that we can actually point it at at a server and bring multiple databases across and how that will look so still got plenty of topics to cover but we're not going to cover them today we're just going to wrap this one up 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 series of
basically the combo series we're looking
at python and sql we're putting a little
bit more into uh an application type of
you know point of view for this tutorial
and what we're doing is building a
database synchronization
app in python
focused on my sequel although it also as
we've mentioned in the past can work for
mariadb and
honestly probably some others we haven't
gotten into
if you get into like a sql server if we
get into oracle or db2 those are a
little bit different
but
this would give you at least a
foundation to do so this episode instead
of going down that digressing there
we're going to talk about actually
creating stored procedures
now last couple episodes we've gone out
and we've been able to figure out where
are the procedures that exist in our
source
basically we're using our information
schema routines and it's based off of a
database name
and
then we come in we get the the source
code for it
which actually happens to be um stored
in a different place from the
parameters
and now we've got the parameters if we
see here
uh last time around that's what we were
doing so we're looking at the parameters
so
what we're going to do now is we're
going to work on building out our sql
so to create it so
uh let's go ahead and leave that one
there
and we'll leave all the debug stuff in
there for now so what we want to do
though
is let's start here so this will be the
um
we're going to call this the proc sql
and we're going to start
with a creator replace procedure
and then we're going to add in the
schema name which is going to be source
name
uh where'd they go
and then we're gonna do a
dot
and then we're gonna come in here
and we're gonna need to add our
procedure name
and that goes right here
and so that gives us this first line
we're going to add in we'll go ahead and
open our parentheses
and we will do that for now
and so what we're going to do is we're
going to uh
so for each of these parameters
what we need to do is we're going to do
products equal
equals proc sql plus
so now we're going to add our parameters
and it needs to be
uh the direction the variable name and
the type
which happens to
be zero
because we can see it here
it lines up pretty well so it'll just be
zero one and two
but we do need a uh space
and then we're gonna do uh palm road
let's just do it this way
so we can probably do it like this
and then we're gonna do it like that
then we're gonna go one
and two
and
we're going to need to do we're going to
say first equals true
uh let's see
so uh if
first
let's just do yeah
if first equals false
then
first we gotta take care of our comma so
that's going to be
that
and we'll create our comma
and will be nice we'll put a space after
our comma so there if it's uh first
equals false
then we're gonna
first put a uh we're gonna put a comma
in there to separate these out and we'll
come through here and then we're going
to say first
equals false
and instead of palm row we're going to
do
um
we're going to actually pull the whole
sql out and this will be
uh proc sql
and so that gets us to here so when
we're done
we need to close off our parameters
so we close off our parameters
and then
we're going to take our
code
which is just a straight up
uh and let's do this we're going to do
we're going to actually do this a little
bit we're going to do a delimiter
[Music]
that
uh we'll wait on this let's do let's see
how this works first i think we have to
do a delimiter but i think we have to
call that beforehand so
uh let's see uh we may not we may be
okay with this
uh so we're gonna print the proxy oh we
need to do
we just need to add now we've got our
we've got our parameters set up we're
all set up to do
that plus
we've got a space so she should be good
to do the source which is here
and actually let's just do this let's do
uh
proc source equals
because what we can do is we'll just
sort of save that off that we don't have
to worry about cursor issues
and then uh we do want to do
oh we're gonna have to close some of
this stuff off
so what we want to do here is let's see
we're gonna do fetch all blah blah blah
we get that
so now we can do
uh which one is that
that's gonna be
uh proc cursor
so we can go ahead and close it
so now we've got the procedure cursor
and then when we're done here
we're going to want to do the same thing
let's see since this is going to be plus
proc source
and we want to close
uh this cursor of our cursor
make sure that gets closed off
and
do we have any others we've got proc
cursor
uh oh we have var cursor
and we have that's it okay so we've got
our two we've closed our
uh everything off
and we're gonna print this out so we're
not actually gonna call it at this point
so let's go ahead
and take a look at that
and we're going to see here
uh
i need to create procedure while loop so
here's our while loop we're going to
create that create a replace procedure
blah blah blah now
let's see
if we can go ahead and we'll do did we
create an update cursor i think we did
uh cursor so we'll just use cursor
destination at this point
so destination procs equals that so we
could actually
close
and close him afterwards because we
don't need him anymore
that means we're also going to need to
do cursor source close so we're sort of
cleaning up our properties here or our
uh resource a little bit here which is
always a good thing
one two let's do that whoop
and we need to
uh let's see so we're gonna do
uh let's not
ah do we need to we probably don't need
to close it yet so let's do this let's
do a uh cursor destination xq so let's
do this we'll just keep using that
we're going to come in here
and we need destination source we're
going to close both of those up
and we don't really need the cursor
anymore so we can come into here
[Music]
and for each of these
we can execute
proc sql
and so now let's go jump over somewhere
to here we're going to jump to our
database
and if we do
routine name
from here
these are our sources
but now
if we flip to our local
uh which i don't know where that is
there we go if we go to local
same database
select routine
we have none
so now what we're gonna do is we're
gonna go ahead and run this and see what
it does let's see if we
get errors or not
uh sort of running so let's see did any
of them run
yes some of them did so now we have to
look at this specific one is we've got a
case here
where
let's see
ah
we've got an issue here so this one
tutorial greeting
for some reason has got a procedure to
it i mean a parameter to it somehow
and let's go back here
let's go back and look at this
[Music]
and we want it to be greetings so if we
do
let's go from here this is going to be
out in our demo
is that it yeah i think it's demo
[Music]
tutorial
and our schema is going to be tutorial
and our procedure name is going to be
whatever that procedure name is
greeting
so if you look at that
we have name and flag
coming through so is that correct
it should be
we print it and then we try to execute
it
for some reason
the variables are not coming out right
so if we do tutorial
greeting
interesting that it's not
those for some reason are not coming
through
so we do of our results
cursor var cursor
var cursor
var cursor
interesting so for some reason we're not
getting somewhere along the way we're
not getting this to work
and it's because
why this is going to be a nice little an
irrational thing because here
we're getting those names right
when we go into var results
which is the var query
which is here let's print that that may
not work at this point because we may
have to
let's see what we got here
okay good so it did die again is it the
same one it's not dying on the same oh
yeah it is on the same one okay
excellent
so we can take that
and if we look
here
it does get stuff back so the parameter
name that comes back is name and flag
and
when we do
okay so it does variables
and we're not printing so let's go ahead
and print
the uh results
let's do print of our results
whoops
and let's see if that shows us something
useful
uh we come up here of our results in
name flag okay
for some reason it's not coming through
here so
for palm row in there
okay let's do if not first maybe it's
that's what it doesn't like
so it should come through for each of
those and
oh
we managed to do this that's our problem
so let's go in here
and let's do
because we probably just screwed
something up so let's go to our local
and we're going to do well we'll do a
credit replace so we'll be okay okay so
we can do that
that's right so it's going to try to
replace it each time anyway well let's
do this
prop procedure
and then we need these four
okay let's do this
and we're going to drop these procedures
and we'll put a little semicolon here
okay so we dropped our procedures now
i'll try it again because what we're
doing is we weren't building our
sql correctly
so if we went back we were probably we
would see stuff without parameters
so we've saved it let's take a look
we run through
cool
and it looks like we have everything
it's like if we look at
let's look at multiplier
let's make sure we've got those local
okay we've got those and i believe it
would be so so if we do call
multiplier
uh no that one probably isn't one that i
want to use because i have to set
something up let's do i think while loop
uh here we go
uh we do so let's just call simple
exception because all it has is a value
that comes in
so let's do call
how did we rent that that was
so we do a simple exception
test
value
there we go
so we get our message back so we're
we're getting our parameters in if we
look here
we see our 17 procedures if we go back
to our original
we're going to see
uh
let's go rv demo there we go
same thing whoops
we see 17 and here we see
17. so we have now been able to bring
across store procedures with their
source and their parameters
and pull that whole thing in
and give ourselves a
a more complete database now there are a
few things left if you look at what's
out there we also
can take a look at triggers and user
functions
and those will be some of the things we
go to next before we swing back to a
couple of more complicated
tricky kind of things that we're going
to run into when we start looking at
triggers
and maybe some workarounds we're going
to do there
we're also going to make this a little
more
i'll say generic
and be able to set it up we're going to
work on cleaning this up a little bit
with some parameters and such like that
so that we can actually point it at
at a server and bring multiple databases
across and how that will look so still
got plenty of topics to cover but we're
not going to cover them today we're just
going to wrap this one up so go out
there and have yourself a great day a
great week and we will talk to you
next time
you