Detailed Notes
Focus for this episode: This episode starts to 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
thank you [Music] well hello and welcome back we are continuing where we're looking at our uh SQL and basically sort of our SQL tutorials and our PA or I'm sorry our python tutorials and we're building our database synchronization python script to focus on Pardon Me Oh kick out of that to focus on MySQL this episode I was going to continue looking at indexes indices depending on how you want to call it but I want to start into stored procedures and it's going to be there's a little bit of some there's a few challenges there as well but I want to get some of the basics so we're going to start with missing procs and [Music] we'll do and we'll do missing procs.pin we'll do it there we should probably we'll do that um that's a good question do I want to do this as fixing it or not um I think what I want to do oh because that was missing part uh Missing table because that's not what I want I don't want missing tables I want to do more like missing columns okay so let's take that because what we're going to end up doing is same sort of thing we did with columns is we're going to pardon me while I play around with this for a second and catch up so we're going to call this missing procs and whoops and what we're going to do here is we're going to go through each of these oh and this is tables list so oops it's not going to be like missing procedures it's going to be like missing indexes I should probably know what I'm doing first okay so let's do it like missing indexes there we go because what we're going to do is we're going to generate a list of one from one and we're going to generate a list from the others so if this could be missing procedures uh let's go ahead and call it we'll do the whole word uh we don't need a tables list so we don't need that um so what we're going to do whoops well I'm just going to start coding because I need to start coding instead I'm going to keep I'm going to spend all day on this so my procedure query prox query now this one uh as a reminder if you do show procedure status then that's going to get you all of your store procedures now what we can do is we can actually string this we can simplify this down and we're going to do a we're going to start playing around in the information schema and it's a little better than just seeing what procedures are out there because we're going to actually grab some detailed information so for these we're going to come into our missing procedures and our query it's going to look something like this and equals and we're going to do our two uh C I'm going to do this and so we're going to do is we're going to get for our table oop it's not our table this is for our database and so in this case what we're going to have to do is we're going to have to actually send it a database name so it's going to be a little bit different from what we've done in the past we could do it based off the database that's there and it's just going to be better how do we want to do it yeah we probably want to do let's see this will be Source name let's do Source name and uh destination name it'll probably be the same but that's okay so let's actually go do our missing procedures back down here we'll load this thing up and missing indexes there we go um yeah we probably want to wait till the uh we'll probably do that beforehand so let's do miss seeing procedures and we definitely don't want to sync rows on that so let's not worry about it so we're gonna do missing procedures and this one's going to be our source Center database and in both of these we're going to call it tutorial is the database because we're getting up with if we don't do that we're going to have every store procedure in the not like that database but the entire Surfer base of the database server so it's going to cross databases it's going to get into the the MySQL system type stuff and things that we don't really want so we're gonna we're gonna have to trim this one down and so it's going to be we're going to actually have two of these so I guess we'll call it because it could be different databases they usually aren't but that's okay um and we're going to do the destination prox query and this is going to be the source name and this is going to be the destination name and then we're going to execute those two uh let's see so we're gonna do uh it's gonna be Source Products query and this is going to be destination procs query and then we want to let's do this we're going to call this Source procedures destination procedures and we are going to have to now from here we're actually pulling the name so we just all we have to do is worry about names in this case we already have the name so we can actually do uh let's see and let's start with this let's just start with let's see what exists in one and does not exist in the other because that's going to be probably the better way to go so first let's see so let's go uh for proc let's do proc name and source procs so we're gonna go through each of those and then we're gonna see if the name is not in um then we want to see if the name is not in the destination procs oh so what we want to do yeah this is what we want to do is so we're going to say Source names and for proc Damon there because we want to do this quickly so we're going to do Source names dot append and then this is just going to be the proc name and then we're going to do uh uh oh okay so that's already good so then we're going to walk through the destinations and this will be we'll call it destination name and destination procs and oops we actually want to go the other way so we're going to find everything in destination oops so it's gonna be destination names destination names and then here we go so for Source name and source it's got our source procs okay Source name in there if the if Source name is not in destination names then we're just going to do it right now we're just going to say print whoops boy that is totally I don't know why that type of helped did not help very much so I need to create procedure and then it's gonna be Source name I was just going to do each of those walk through that and we don't need missing procedures because we're not going to use that right now we don't need that table at the end and let's do that let's go ahead and run through this um oops don't do that and I'm going to quit afterwards so let's work on that let's see where we're at so if we do Python 3 what does this mean oh we've got an endowed oh because we forgot to do a plus so let's go all the way back here I'm going to do plus wow okay oops that's oh here we go so here's what we want start missing procedures and I've got another typo whoops right there it had a little red that even told me that let's see what else we got uh that's destination names uh let's see is there another little running oh here we go return missing indexes uh he doesn't need to return anything and oh I still get a red up there oh that's because it doesn't recognize this but that should be okay and it is not okay so it's a tuple so it's uh missing procedures power went past it didn't I yes I did and uh so Source name um so let's do this let's print proc name let's see what that looks like and we're gonna see here um oh interesting uh destination procs that one destination procs query let's do let's print that because maybe we're not building that right okay so now we're going to take that and let's just go jump whoops and my SQL use tutorial let's see does it give us what we need it does okay so it's giving us our names so what is it not let me get back in over here let me run that again and we start that so we get our query we get our SQL which is here we do a fetch all oh it's not destined oh yeah that's right destination names is that destination procs is that but it's not so let's print destination products I don't think it's coming through right print destrox uh curse your destination he's gonna ask to he is going to do that uh it's right so let's see it's empty so oh that's destination okay which is right so yeah so destination should be empty so now let's do here I'm sorry of course because I haven't pulled any over yet so now if I do it all right so it's count rows which is going to be a procedure so for some reason um it is a tuple in each of those let's do it like this let's see if that's right yep so what we want to do I don't know why it's always giving us a tuple but we're going to do Source name zero that should be proc name zero and uh there we go and Source name zero all right let's do that there we go so we've gotten that far so now what we do is we have a list of procedures we need to create so what we're going to need is we need the source for each procedure and then we're going to need the signature because if you remember you have to do something like create procedure proc name as and then there's some sort of like you know there's probably some begin and an end and then you have to execute all of that so we need to get our source now let's take a look at that and with that we can get it from routine definition so if we go look for each of those and that'll probably get us through this one let's see so instead of this what we're going to need to do oh shoot it pretty printed it on me uh let's do it this way um there let's move that comment up here and what we're going to need to do for each of those is we're going to do uh let's do Source query equals and then for each of these we're going to do it where and this is going to be from the source so we're going to need this is again going to be a source name and whoops in our table is going to be this guy here which is going to be our source name actually it's Source name zero and then we need to do thank you uh we can't let's create a new cursor and it's going to be when you pull it from the source so we're going to do um Pro let's do this the proc cursor equals source and then we're going to do procursor dot execute and that's going to be based off of the source query we could have just simplified that a little bit we're going to go with that and then we're going to do a fetchall oh that's probably what it is it's a fetchup we could probably do fetch one but we'll do fetchall um so let's do Source results equals that and then we're going to just print Source results zero and let's see how that looks uh scanning uh we missed one oh we didn't close that out there we go let's try that again only concatenate a string oh it's not Source name this oh that is Source name oops um oh we change that so let's call it Source product name and then that's going to be Source proc name that Source name that Source proc name see that's why we have to watch out sometimes you get name collisions you don't even notice it until it's too late uh let's see that should be everybody's now let's try it and so now okay so now we're seeing stuff so we're going to see like create procedure while loop and then it has this string and so that is um I wonder if that's oh because that's row zero zero so let's try that and there we go so if we look at uh let's see here we go like procedure while loop this is a source for it if we look to procedure temp table then we get this thing now the thing we're going to run into is let's take a look if we look at these uh let's see simple execution simple continue let me see if I can get because I think what we're going to run into is we're going to have some that don't have uh variables that we're going to have to look at see if it's going to show me uh it's not going to show me the let's see can I reconnect and let's see um let's look at uh multiplier I think multiplier will give us one so if we go look at multiplier here what we're going to see is multiplier and this is one where we gave it parameters because there's an X of Y and a z parameter but if you look at the source we don't have that and so when we come back next time that is where we're going to start because we're going to go figure out what are our parameters because right now with multiplier and actually with like while loop this is what it would look like is that we could do uh if we come down here it would be something like create procedure procedure uh while loop and I think it's as actually I think I just do it this way let's see if that works I don't think that's quite it let's see let's go create a procedure real quick and see what it looks like so if I do create procedure um actually let's do it on a local and if I come into here store procedures I want to create a procedure called delete me execute very simple procedure ah it's not going to show it to me darn it so we're gonna have to go we'll go take a look at this stuff later let me delete delete me and we'll start taking a look at that next time around because we're going to have we're going to go in and create it and it actually is something along these lines but we're going to have a little bit of work we're going to do and like I said that'll be next time so I think I've given you enough to think about right now go ahead and go out there and have yourself a great day a great week and we will talk to you next time [Music] foreign
Transcript Segments
thank you
[Music]
well hello and welcome back we are
continuing where we're looking at our
uh SQL and basically sort of our SQL
tutorials and our PA or I'm sorry our
python tutorials and we're building our
database synchronization python script
to focus on
Pardon Me Oh kick out of that
to focus on MySQL this episode I was
going to continue looking at indexes
indices depending on how you want to
call it but I want to start into
stored procedures
and it's going to be there's a little
bit of some there's a few challenges
there as well but I want to get some of
the basics
so we're going to start with missing
procs
and
[Music]
we'll do and we'll do missing procs.pin
we'll do it there we should probably
we'll do that
um that's a good question do I want to
do this as
fixing it or not
um
I think what I want to do oh because
that was missing part uh Missing table
because that's not what I want I don't
want missing tables I want to do more
like missing columns okay
so let's take that because what we're
going to end up doing is
same sort of thing we did with columns
is we're going to pardon me while I play
around with this for a second and catch
up
so we're going to call this missing
procs and whoops
and what we're going to do here is we're
going to go through each of these oh and
this is tables list so oops it's not
going to be like missing procedures it's
going to be like missing
indexes
I should probably know what I'm doing
first okay so let's do it like missing
indexes
there we go
because what we're going to do is we're
going to generate a list of one from one
and we're going to generate a list from
the others so if this could be missing
procedures
uh let's go ahead and call it we'll do
the whole word
uh we don't need a tables list
so we don't need that
um
so what we're going to do whoops
well
I'm just going to start coding because I
need to start coding
instead I'm going to keep I'm going to
spend all day on this so my procedure
query prox query now this one
uh as a reminder if you do show
procedure status
then that's going to get you all of your
store procedures
now what we can do is we can actually
string this we can simplify this down
and we're going to do a we're going to
start playing around in the information
schema and it's a little better than
just seeing what procedures are out
there because we're going to actually
grab some detailed information so for
these we're going to come into our
missing procedures
and our
query
it's going to look something like this
and equals and we're going to do our two
uh C
I'm going to do this
and so we're going to do is we're going
to get for our table
oop it's not our table this is for our
database
and so in this case what we're going to
have to do is we're going to have to
actually send it a database name so it's
going to be a little bit different
from what we've done in the past we
could do it based off the database
that's there and
it's just going to be better how do we
want to do it yeah we probably want to
do
let's see this will be Source name
let's do Source name
and uh destination name
it'll probably be the same but that's
okay so let's actually
go do our missing procedures back down
here we'll load this thing up
and missing indexes there we go
um
yeah we probably want to wait till the
uh we'll probably do that beforehand so
let's do
miss seeing procedures
and we definitely don't want to sync
rows on that so let's not worry about it
so we're gonna do missing procedures and
this one's going to be
our source Center database and in both
of these we're going to call it tutorial
is the database
because we're getting up with if we
don't do that
we're going to have every store
procedure in the not like that database
but the entire Surfer base of the
database server so it's going to cross
databases it's going to get into the the
MySQL system type stuff and things that
we don't really want
so we're gonna we're gonna have to trim
this one down
and so it's going to be we're going to
actually have two of these so I guess
we'll call it
because it could be different databases
they usually aren't but that's okay
um
and we're going to do the destination
prox query
and this is going to be the source name
and this is going to be
the destination name
and then we're going to execute those
two
uh let's see so we're gonna do
uh it's gonna be Source Products query
and this is going to be destination
procs query
and then we want to
let's do this we're going to call this
Source procedures
destination procedures
and we are going to have to now from
here we're actually pulling the name so
we just all we have to do is worry about
names in this case we already have the
name so we can actually do
uh let's see
and let's start with this let's just
start with let's see what exists in one
and does not exist in the other because
that's going to be
probably the better way to go so first
let's see so let's go
uh for
proc let's do proc name
and source procs
so we're gonna go through each of those
and then we're gonna see
if the name is not in
um
then we want to see if the name is not
in the destination procs
oh so what we want to do yeah this is
what we want to do is so we're going to
say Source names
and for proc Damon there because we want
to do this quickly so we're going to do
Source names dot append
and then this is just going to be the
proc name
and then we're going to do uh
uh oh okay so that's already good so
then we're going to walk through the
destinations
and this will be we'll call it
destination name
and destination procs
and
oops we actually want to go the other
way
so we're going to find everything in
destination
oops
so it's gonna be destination names
destination names
and then here we go so for Source name
and source
it's got our source procs okay Source
name in there if
the if Source name
is not in
destination names
then we're just going to do it right now
we're just going to say print
whoops boy that is totally I don't know
why that type of helped did not help
very much so I need to create procedure
and then it's gonna be Source name
I was just going to do each of those
walk through that
and we don't need missing procedures
because we're not going to use that
right now
we don't need that table at the end and
let's do that let's go ahead and run
through this
um oops don't do that and I'm going to
quit afterwards
so let's work on that let's see where
we're at so if we do Python 3 what does
this mean
oh we've got an endowed oh because we
forgot to do a plus so let's go all the
way back here
I'm going to do plus
wow okay oops that's oh here we go so
here's what we want start missing
procedures and I've got another typo
whoops right there it had a little red
that even told me that let's see what
else we got
uh that's destination names
uh let's see
is there another little running oh here
we go return missing indexes uh he
doesn't need to return anything
and oh I still get a red up there oh
that's because it doesn't recognize this
but that should be okay and it is not
okay so it's a tuple
so it's uh
missing procedures
power went past it didn't I
yes I did
and uh
so Source name
um
so let's do this let's print proc name
let's see what that looks like
and we're gonna see here
um
oh interesting
uh destination procs
that one destination procs query
let's do
let's print that because maybe we're not
building that right
okay so now we're going to take that
and let's just go jump whoops
and my SQL use tutorial
let's see
does it give us what we need it does
okay so it's giving us our names
so
what is it not let me get back in over
here let me run that again
and we start that so we get our query we
get our SQL which is here
we do a fetch all
oh it's not destined oh yeah that's
right
destination names is that destination
procs is that
but it's not
so let's print destination products
I don't think it's coming through right
print destrox
uh curse your destination he's gonna ask
to he is going to do that
uh it's right so let's see
it's empty so
oh that's destination okay which is
right so yeah so destination should be
empty
so now let's do here I'm sorry
of course because I haven't pulled any
over yet
so now if I do it all right so it's
count rows which is going to be a
procedure so for some reason
um
it is a tuple in each of those
let's do it like this let's see if
that's right
yep so what we want to do I don't know
why it's always giving us a tuple but
we're going to do Source name
zero that should be proc name zero
and uh
there we go and Source name zero
all right let's do that
there we go
so we've gotten that far
so now what we do is we have a list of
procedures we need to create
so what we're going to need is we need
the source for each procedure
and then we're going to need the
signature because if you remember you
have to do something like create
procedure proc name
as and then there's some sort of like
you know there's probably some begin and
an end and then you have to execute all
of that
so we need to get our source
now let's take a look at that and with
that we can get it from routine
definition
so if we go look for each of those
and that'll probably get us through this
one let's see so instead of this
what we're going to need to do oh shoot
it pretty printed it on me
uh let's do it this way
um there
let's move that comment up here
and what we're going to need to do for
each of those is we're going to do uh
let's do Source query
equals
and then for each of these we're going
to do it where
and this is going to be from the source
so we're going to need
this is again going to be a source name
and whoops in our table
is going to be
this guy here which is going to be our
source name
actually it's Source name zero
and then
we need to do
thank you uh
we can't let's create a new cursor
and it's going to be when you pull it
from the source
so we're going to do
um
Pro let's do this the proc cursor
equals source and then we're going to do
procursor
dot execute
and that's going to be based off of the
source query we could have just
simplified that a little bit we're going
to go with that
and then we're going to do a fetchall
oh that's probably what it is it's a
fetchup we could probably do fetch one
but we'll do fetchall
um so let's do Source results
equals that and then we're going to just
Source results zero
and let's see how that looks
uh scanning uh we missed one oh
we didn't close that out
there we go let's try that again
only concatenate a string
oh it's not Source name this oh that is
Source name oops
um
oh we change that so let's call it
Source product name
and then that's going to be Source proc
name
that Source name that Source proc name
see that's why we have to watch out
sometimes you get name collisions you
don't even notice it until it's too late
uh let's see that should be everybody's
now let's try it
and so now okay so now we're seeing
stuff so we're going to see like create
procedure while loop and then it has
this string and so that is
um
I wonder if that's oh because that's row
zero zero
so let's try that
and there we go
so if we look at uh
let's see here we go like procedure
while loop this is a source for it
if we look to procedure
temp table
then we get this thing
now the thing we're going to run into
is let's take a look if we look at these
uh let's see simple execution
simple continue let me see if I can get
because I think what we're going to run
into is we're going to have some that
don't have uh variables that we're going
to have to look at see if it's going to
show me uh it's not going to show me the
let's see can I reconnect
and let's see
um
let's look at uh
multiplier I think multiplier will give
us one so if we go look at multiplier
here
what we're going to see is multiplier
and this is one where we gave it
parameters because there's an X of Y and
a z parameter but
if you look at the source we don't have
that and so when we come back next time
that is where we're going to start
because we're going to go figure out
what are our parameters because right
now with multiplier and actually with
like while loop this is what it would
look like is that we could do
uh if we come down here it would be
something like create procedure
procedure
uh while loop
and I think it's as actually
I think I just do it this way
let's see if that works
I don't think that's quite it let's see
let's go create a procedure real quick
and see what it looks like so if I do
create procedure
um
actually let's do it on a local
and if I come into here
store procedures I want to create a
procedure called delete me
execute
very simple procedure
ah it's not going to show it to me darn
it so we're gonna have to go we'll go
take a look at this stuff later let me
delete delete me
and we'll start taking a look at that
next time around because we're going to
have we're going to go in and create it
and it actually is something along these
lines but we're going to have a little
bit of work we're going to do and like I
said that'll be next time so
I think I've given you enough to think
about right now go ahead and go out
there and have yourself a great day a
great week and we will talk to you
next time
[Music]
foreign