Detailed Notes
Focus for this episode: We step back and do some code cleanup in this episode. This will position us for a more user friendly approach moving forward.
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're continuing our look at Python and SQL and this time we're going to focus a little more on the python side I guess because we've made a pretty solid progress we have a few things left that we need to do some of these are non-trivial but I think at this point I want to pull this one here is clean this thing up a little bit because I think as we move forward it'll be a little easier to do so now what we're going to do is we're going to take uh we've got a couple things here so we're going to start with this main.py that we run and we're just going to duplicate it and we're going to somewhere in here I think I just do a copy paste there we go and we're going to call this uh dbsync .py and we don't have to worry about that right now so what we're going to do is we're going to have Main and it's going to use dbsync so what Maine's going to do um and actually let's clean this up a little bit so we go to DB sync what we're going to do is we're going to create a class called my SQL sync uh we'll do synchronize and then it's going to have a couple different things let's have a bunch of stuff in it so what we can do is let's see we're going to keep all our procedures so we're going to get rid of the code at the end here that's a cleanup here we go um and let's actually let's go back here because what we'll probably do is actually end up sort of keeping the connections uh do we have a oh we don't have compare tables do we so we're gonna have to create compare tables yeah we have missing tables but we need compare tables okay so let's do this first let's take that compare tables and when we do that here we go so the first thing we do is we're going to do this compare tables and we're going to build out a tables list actually we're going to do it twice I think because what we're going to do is we're going to do let's go look at these real quick and let's do it this way we're going to take connections and what we're going to have here is we're going to have a source DB and we're going to have destination DB and we'll just do there's none equals none I can do it this way and then let's see what we're gonna have here is we're going to have this is going to be self dot Source DB equals connection so we're going to change this stuff up quite a bit in a sense um and we're going to call this connect to destination this is going to be connect to source what we'll probably end up doing like that um oh itself whoops that's gonna be self and then this is going to be self dot destination DB equals so missing tables whoop okay so missing tables is simply going to take uh this is we're going to do two things we're going to have uh Source tables and we'll just start with that we'll have Target tables and so now here this is just going to be self and then for Row in self Dot uh is that Source tables oops Target tables and if the name is not in self.source tables then and we're going to do this we're going to go ahead and take missing tables so I have to pass those around and we're going to do self Dot missing tables uh let's do this uh this just needs to be we're going to make sure that we synchron that we set him to nothing and then we're going to come in here and do self-doubt missing tables so when we run missing tables we should be in good shape what's you complaining about oh um let's call him uh let's see this is going to be let's do set missing tables so now oops um let's do it like this let's be consistent and we're gonna do Source tables Target tables and missing tables so that gets us Target tables Source tables missing tables there we go and Row from Outer scope I don't know what that is okay so we should be good so here we're going to be there's going to be uh Define let's see compare tables and it's going to be a self there we go and then we're going to take this like that so we need a cursor which is going to be probably from let's see if we can go back here where we created our little cursor we got it is okay so we need these cursors um let's go ahead we're going to create those each time so we're going to come into our DB Sync here and we're going to do cursor equals and this will be um self.source uh what did we call it Source DB okay so this should be oh except for I call it yeah edit with an SRC my mistake okay and so that gives me a cursor and then this one uh cursor two is going to be whoops from the destination so we're going to come in here and we're going to go call him the destinationdb.cursor so I've got our cursors we're going to create these uh create a list of each of these we don't need missing at this point we do need self Dot Source tables and we're going to do self dot Target tables and then we're gonna have to do self dot Source tables and we're going to do self dot Target tables and so now we've built out here we've built out the things that we're going to need so there is going to be you know some some work to be done here uh we have to make sure that we call things in order but we're going to go ahead and do this so we compare tables missing procedures is going to be pretty straightforward if we can get all the way into that oh and we're gonna let's take our cursors here and do the same thing up here I'm going to make sure we close it so we have a cursor and a cursor too so we're gonna do a cursor close and a cursor 2 close I assume what is it complaining about there oh there's some sort of outer scope we don't need to worry about that too much we don't need import CIS I don't think well probably not okay uh let's see so this is going to be uh self Dot Source DB and this is going to be self.destination DB so we're good and we're gonna go through here let's see do we have oh but we do need for missing procedures we need self and we need a source name and a destination name which is okay so this yeah this is our database so we're going to go ahead and keep that and then we should be good through all of this um not seeing anything that should matter here oh yeah it is because here we've got to do okay so this is self dot Source DB whoops and then here is going to be self.source DB and by using destination it probably just says that's a spelling typo okay and then self-source uh close everything we can probably let's get rid of some of this output um now we'll keep that output for now so we're going to do the same thing so we've got that we're gonna come in here we're going to do the same thing we're going to do that we don't need our sourcing oops it's a comma um pretty much the same of everything let's go back up here where we create these two and those are probably the same names and we're going to take this whole thing to hear tab that up and let's see so again we have self.source DB and here self.sourcesdb and let's see do we use it again doesn't look like we do okay so we should be good same thing missing triggers we're going to come we don't need our source our databases we got those we get our source and our destination I have that yep there we go so we got those fixed uh let's see we'll do this here I don't need this these comments anymore so we should be good there I think that's all good it's gonna be yes in the self and notice we're not having to do all of this um I don't know what that table's list missing columns let's look at what do we use for missing columns when we call missing columns we're actually using it on Source tables so we should be good um vsync so here this should be that tables list is actually Source columns the set's missing columns and table in self Dot uh source oh it's gonna push this up one so I can make sure it picks it up right and uses its little autocomplete stuff properly I get that so now this is going to be self dot Source tables and then Source execute execute oh interesting I don't know that I need to do and missing columns oh that's for each table okay so we're good yeah so we're going to come in we're going to find our missing columns uh see destination names because we only need the tables that are in the source because we've already built them into the destination okay so we're good we could technically do only destination and see if they exist in Source but uh that could cause some problems if we have extra tables in our destination so we're good there so we're going through here we are gonna let's get rid of this print column row and we go through and we've got everything so we should be good there I went a little bit long there so this seems like a good point to sort of cut away and wrap up for today we will dive right right back in tomorrow uh next episode and we're going to continue right into this um it is a little bit tedious it is something that you may want to you know Skip ahead and just take a look at the how things have changed however there are several little comments and things that are noted as we go through and do this cleanup that may be useful to you particularly from the python side the SQL side not so much we've sort of you know this is really sort of a couple of python heavy focused episodes but then we will get back into it so we've got one more we're doing some cleanup and then we're going to dive into our next round of issues to tackle that being said let's get out there and get back to your day so go out there and have yourself a great day a great week and we will talk to you next time [Music]
Transcript Segments
thank you
[Music]
well hello and welcome back we're
continuing our look at Python and SQL
and this time we're going to focus a
little more on the python side I guess
because we've made a pretty solid
progress we have a few things left that
we need to do
some of these are non-trivial but I
think at this point I want to pull this
one here is clean this thing up a little
bit
because I think as we move forward it'll
be a little easier to do so
now what we're going to do is we're
going to take uh we've got a couple
things here so we're going to start with
this main.py that we run and we're just
going to duplicate it and we're going to
somewhere in here I think I just do a
copy paste
there we go and we're going to call this
uh dbsync .py
and we don't have to worry about that
right now so what we're going to do is
we're going to have Main
and it's going to use dbsync
so what Maine's going to do
um and actually let's clean this up a
little bit so we go to DB sync what
we're going to do is we're going to
create a class called my SQL sync uh
we'll do synchronize
and then it's going to have a couple
different things let's have a bunch of
stuff in it so what we can do is let's
see we're going to keep all our
procedures so we're going to get rid of
the code at the end
here that's a cleanup here we go
um and let's actually let's go back here
because what we'll probably do is
actually end up sort of keeping the
connections
uh
do we have a oh we don't have compare
tables
do we
so we're gonna have to create compare
tables
yeah we have missing tables
but we need compare tables okay so let's
do this first
let's take that compare tables
and
when we do that here we go
so the first thing we do is we're going
to do this compare tables and we're
going to build out
a tables list
actually we're going to do it twice I
think because what we're going to do
is we're going to do let's go look at
these real quick and let's do it this
way we're going to take
connections
and what we're going to have here is
we're going to have a source DB
and we're going to have destination DB
and we'll just do
there's none
equals none
I can do it this way
and then let's see what we're gonna have
here
is we're going to have this is going to
be self dot Source DB equals
connection
so we're going to change this stuff up
quite a bit in a sense
um and we're going to call this connect
to destination
this is going to be connect to source
what we'll probably end up doing
like that
um
oh itself
whoops
that's gonna be self
and then this is going to be
self dot destination DB equals
so missing tables
whoop okay so missing tables
is simply going to take
uh this is
we're going to do two things we're going
to have uh Source tables
and we'll just start with that we'll
have Target tables
and so now here
this is just going to be self
and then for Row in
self Dot
uh is that Source tables
oops Target tables
and if the name is not in
self.source tables
then
and we're going to do this we're going
to go ahead and take
missing tables
so I have to pass those around
and we're going to do self Dot
missing tables
uh let's do this uh this just needs to
be
we're going to make sure that we
synchron that we set him to nothing and
then we're going to come in here and do
self-doubt missing tables so when we run
missing tables we should be in good
shape what's you complaining about
oh
um
let's call him
uh let's see this is going to be
let's do
set
missing tables
so now
oops
um let's do it like this let's be
consistent
and we're gonna do Source tables Target
tables
and missing tables
so that gets us
Target tables
Source tables missing tables there we go
and Row from Outer scope
I don't know what that is okay so we
should be good so here we're going to be
there's going to be uh Define
let's see compare tables
and it's going to be a self there we go
and then we're going to take this
like that
so we need a cursor which is going to be
probably from let's see if we can go
back here where we created our little
cursor we got it
is
okay
so we need these cursors
um
let's go ahead
we're going to create those each time so
we're going to come into our DB Sync
here and we're going to do
cursor equals
and this will be
um
self.source
uh what did we call it
Source DB okay
so this should be
oh except for I call it yeah edit with
an SRC my mistake okay and so that gives
me a cursor
and then this one
uh cursor two
is going to be whoops
from the destination
so we're going to come in here and we're
going to go call him the
destinationdb.cursor
so I've got our cursors we're going to
create these uh create a list
of each of these we don't need missing
at this point we do need self Dot
Source tables
and we're going to do self dot Target
tables
and then we're gonna have to do self dot
Source tables
and we're going to do self dot Target
tables
and so now we've built out here we've
built out the things that we're going to
need so there is going to be you know
some
some work to be done here uh we have to
make sure that we call things in order
but
we're going to go ahead and do this so
we compare tables missing procedures
is going to be pretty straightforward if
we can get all the way into that
oh and we're gonna let's take our
cursors
here and do the same thing up here
I'm going to make sure we close it so we
have a cursor and a cursor too
so we're gonna do a cursor close and a
cursor 2 close
I assume what is it complaining about
there
oh there's some sort of outer scope we
don't need to worry about that too much
we don't need import CIS I don't think
well probably not
okay
uh let's see so this is going to be
uh self Dot
Source DB
and this is going to be
self.destination DB
so we're good
and we're gonna go through here let's
see do we have oh but we do need for
missing procedures
we need self
and we need a source name and a
destination name
which is
okay so this yeah this is our database
so we're going to go ahead and keep that
and then we should be good through all
of this
um not seeing anything that should
matter here
oh yeah it is because here we've got to
do okay so this is self dot Source DB
whoops
and then here is going to be self.source
DB
and
by using destination it probably just
says that's a spelling typo okay
and then self-source uh close everything
we can probably
let's get rid of some of this output
um now we'll keep that output for now
so we're going to do the same thing so
we've got that
we're gonna come in here we're going to
do the same thing we're going to do that
we don't need our sourcing oops it's a
comma
um pretty much the same of everything
let's go back up here where we create
these two
and those are probably the same names
and we're going to take this whole thing
to hear
tab that up
and let's see so again we have
self.source DB
and here self.sourcesdb
and let's see do we use it again doesn't
look like we do okay so we should be
good same thing missing triggers
we're going to come we don't need our
source our databases we got those we get
our source and our destination
I have that yep there we go
so we got those fixed
uh let's see we'll do this here
I don't need this these comments anymore
so we should be good there
I think that's all good
it's gonna be yes in the self and notice
we're not having to do all of this
um
I don't know what that table's list
missing columns
let's look at what do we use for missing
columns when we call missing columns
we're actually using it on Source tables
so we should be good
um
vsync so here this should be that tables
list is actually Source columns
the set's missing columns and table in
self Dot
uh source
oh it's gonna push this up one so I can
make sure it picks it up right and uses
its little autocomplete stuff properly
I get that so now this is going to be
self dot Source tables
and then Source execute execute
oh interesting I don't know that I need
to do
and missing columns
oh that's for each table okay so we're
good yeah so we're going to come in
we're going to find our missing columns
uh see destination names because we only
need
the tables that are in the source
because we've already built them into
the destination okay so we're good
we could technically do only destination
and see if they exist in Source but uh
that could cause some problems if we
have extra tables in our destination so
we're good there so we're going through
here we are gonna let's get rid of this
print column row
and we go through and we've got
everything so we should be good there
I went a little bit long there so this
seems like a good point to sort of cut
away and wrap up for today we will dive
right right back in tomorrow uh next
episode and we're going to continue
right into this
um
it is a little bit tedious it is
something that you may want to you know
Skip ahead and just take a look at the
how things have changed however there
are several little comments and things
that are noted as we go through and do
this cleanup that may be useful to you
particularly from the python side the
SQL side not so much we've sort of you
know this is really sort of a couple of
python heavy focused episodes but then
we will get back into it so we've got
one more we're doing some cleanup and
then we're going to dive into our next
round of issues to tackle
that being said let's get out there and
get back to your day so go out there and
have yourself a great day a great week
and we will talk to you
next time
[Music]