Detailed Notes
Focus for this episode: We do some code cleanup and create an easier framework for running this script across a wide range of environments in this episode.
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 series of basically this combo tutorial SQL and python and we're getting deeper into this we've got you know a basic application working where we can can copy some uh tables and synchronize them and deal with the columns the data the triggers store procedures functions you know all that good stuff and now what I want to do is expand on this a little bit now we started um you know cleaning this up a little bit we got into this where we've got now this MySQL synchronized class and what I want to do now is I'm going to pull a couple of things out to make this a little bit more uh generic I guess so that we can put a little more into our main script and um not so much that specific in our uh you know our class itself one of the things we're going to add this time is we're going to have uh we can take the database name for the source and the destination this is something that will allow us at some point maybe even to do from one database to another so it doesn't have to have the same database name but more importantly right now we're going to do is we're going to come in here and this is our little script and what we're going to do here is we're going to create uh we're going to call it we'll call it databases and what we're going to do is we're going to have a list of databases that we're going to actually synchronize and let's do three of them so we're going to have tutorial that we have always used we're going to use uh let's see what else do we want to do let's use uh giganator database there and let's use I think I had another one oh let's use H goals it's a nice simple one those are just because these are smaller databases that's what we want to be able to do now is we're going to say 4 DB oops in databases and now we're going to do is we're going to go through each of the databases and synchronize each one of them now this may or may not be something that you need but if you're trying to do a server that's got either you know maybe you've split your data into a couple database data into a couple of different databases or you've got like a you know like a Dev server or something like that that you want to synchronize some data across you know beyond a single database this is going to allow you to do it and what we need to do here is we just actually we don't even need that so we're going to come in each time we're going to do a MySQL synchronize we're going to go ahead and set the database names and here both of them are equal to the same one so it's pretty straightforward and then we do have to come through here there's a couple places where we used like here before we sent the database name in and we're just going to do in both cases we're just going to send that in technically we probably don't even need that because now we could use the databases database names that we've stored as parameters so let's actually go look at that let me see make sure so let's look at missing triggers triggers so here we have the we've got a source name and a destination name what we can do is we can say Source name equals self dot Source database name and then destination name equals self Dot destination DB name and now we don't need those parameters at all so we come back to our main and that was for missing triggers we also have that for missing procedures and missing functions so let's do that for each of those so we've got less stuff we're having to pass around missing procedures missing functions the same procedures uh let's see whoops let's go back to where is that missing functions missing triggers here we go so we can actually clean these up pretty easy and I've come up here to our missing procedures and paste and we're good there now uh one of the things I need to do is I need to check to see if these databases even exist locally now what we could do is you know check in our source and say hey if we don't have a database that exists in the destination go ahead and create the database but we're gonna pass on that because it's sort of a we could do it but right now we're not going to bother with that too much so we're going to go in and manually create those if we need to so uh let's see if I can log into my database oops oops let me make sure I pass password is typed correctly there we go sure if I need to show databases okay so here I have tutorial I did not have giganator H goals so let's do that create database H goals and so we're just going to pull those directly across again later and let's go ahead and do our synchronized rows oh and here we use the DB again uh so let's go to syncrose [Music] so it is the destination database name which is uh let's see I think I used it here oh there we go I guess I could have just used it instead so that's going to be a destination DB name and let's see let's make sure I use that right oh because I just stepped on that name let's see let's see oh I may not need that in Sacramento so I wonder why I sent that may not need it at all so I'm going to drop the table Target tables okay so we're probably okay there so let's see so we go through each of these we're going to go ahead and just take all the rows so we're going to run this through all the way now um Let's see we come through we're going to connect to the source um and here's something else I'm going to do is let me go ahead and create a settings file and actually now instead of all the way up here before what we were doing is we were just creating the configuration as it existed and so we would come in here and we'd settle our values so what I'm going to do now is I'm just going to keep this pretty simple and from settings I'm going to import The Source config and the cart Target config so you go Source config and then Target config and import those in and then I'm going to send those into the Connect Now technically now I could do two different I could do one connect and then just pass back you know assign out the connector that comes back but I'll keep it as source and destination just for I don't know cleanliness understanding and now we've got that so we've got we've extracted our setups into our configuration settings into a settings file so we're going to come in we're going to pull that in what we could do is we could even pull the databases out bases and if I go to settings I'll just push that up here and so now you have everything actually extracted out into a settings file or at least a lot more of it now what we're also going to have here is let's go ahead and do Max rows and we'll pull that out as well so now there's just less we have to worry about so now in here you're starting to get something that's pretty darn clean uh and it's sort of a generic kind of script that we have got created out which is um one of the things we wanted to run into here uh here we go generalize extract command line parameters we actually are going to pull those out into a settings file now let's go ahead and run this and what we will see um uh actually let me not sync the data so let's do that first without I'm just going to get rid of synchros not going to use that right now now what we can do is we can actually pull little Flags in and put the settings to be a whole series of flags whether we do triggers procedures functions indexes and all that kind of good stuff right now we're not going to do it that way we're going to just create our database real quick let's see so this is Main and whoops [Music] what did I miss oh gosh I didn't even lose this I used to lose one of my imports so now there we go there we go and boom so now if we go look at uh locally refresh this all right let's just reconnect because we probably need to so now we can see that we have a giganator that doesn't have any tables oh interesting and we probably have an H goals that maybe doesn't have any tables nope it's not how many tables so let's go see what we got here oh failed to create index hmm so for some reason oh probably because it's already there so what we want to do in missing indexes oh well that says it should be already missing so we've got something there that didn't quite work but we did a try so it came in what did it do oh interesting so we come out we do missing indexes for DB in databases let's do this um current DB and let's see what it does here yeah so for some reason index oh we're missing something here uh oh oh oh oh oh oh oh oh oh oh oh oh oh it's because the database there we have to change the database for each of those uh so that's going to be so what we really want to do here is that the source config which I think we can do here so let's do uh Source config but first what we want to do is we want to do Source config um let's see if we can do database equals DB and then we're just going to quit because we're going to have to update our database there oh look at that yeah we can't assign that so it's not a function call try that there you go so let's see we need to set so we've got to do a dictionary key kind of thing here and let's do this uh see python set dictionary value oh that's right I think it's a get and a set assigning values so oh that's okay yeah that's okay because that is correct because that is the first uh so let's go here uh let's see Target config well don't need that don't need that now let's take a look so take a look at this again let's do it this way let's go to whoop settings and let's just go ahead and get rid of that as the first one okay so now if we come in and let's do this okay so let's do print DB foreign so let's see how this works see what we got here okay so it does that and then let's print Source config and we've got our database has changed so we should be good so if we look at let's go to missing tables [Music] tables whoop and we will look real quick so it says here it's going to be print for a table that's in missing tables for each missing table get details but we've got to get self dot missing tables which we do so we do set missing tables uh here we go so we do compare tables so let's do that we'll do set missing tables let's just do this real quick self dot Source tables and Target tables let's go look at that real quick and we'll see so there you go so we have oh we have source and Target flipped somehow we got source and Target flip so we go here is that right uh oh that is flipped so that's Target that Source it's amazing how that works out so let's try that again uh let's see so now if we go into Main and we run it wow it works amazingly better when you get your settings record properly so now we're going to see that this is going to update and we're starting to see all of our little tables show up so I think we'll wrap it up this time um I'll probably go back and edit a little bit shorten this out a little bit but we uh have straightened that out in our settings and now we're actually able to to send multiple databases across and do our synchronizing uh actually what I'll do is I will sync rows and um we'll kick this off and it'll probably be pretty close to next time before this is all done anyways and we'll get all that stuff taken care of and we're gonna come back and we're probably going to dive into one of the more complicated things here uh dealing with enumerated values for a column because that is something we've run into now a couple of times and I want to make sure we get that one fixed that being said 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 series of basically this
combo tutorial SQL and python and we're
getting deeper into this we've got you
know a basic application working where
we can can copy some uh tables and
synchronize them and deal with the
columns the data
the triggers store procedures functions
you know all that good stuff
and now what I want to do is expand on
this a little bit now we started
um you know cleaning this up a little
bit we got into this where we've got now
this MySQL synchronized class
and what I want to do now is I'm going
to pull a couple of things out to make
this a little bit more
uh generic I guess so that we can put
a little more into our main script and
um not so much that specific in our uh
you know our class itself one of the
things we're going to add this time is
we're going to have uh we can take the
database name for the source and the
destination this is something that will
allow us at some point maybe even to do
from one database to another so it
doesn't have to have the same database
name but more importantly right now
we're going to do
is we're going to come in here and this
is our little script
and what we're going to do here is we're
going to create
uh we're going to call it we'll call it
databases
and what we're going to do is we're
going to have a list of databases that
we're going to actually synchronize and
let's do three of them so we're going to
have tutorial that we have always used
we're going to use uh let's see what
else do we want to do let's use uh
giganator
database there and let's use I think I
had another one oh let's use H goals
it's a nice simple one
those are just because these are smaller
databases
that's what we want to be able to do now
is we're going to say
4 DB oops in
databases
and now we're going to do is we're going
to go through each of the databases and
synchronize each one of them now this
may or may not be something that you
need but if you're trying to do a server
that's got either you know maybe you've
split your data into a couple database
data into a couple of different
databases or you've got like a you know
like a Dev server or something like that
that you want to synchronize some data
across you know beyond a single database
this is going to allow you to do it and
what we need to do here is we just
actually we don't even need that
so we're going to come in each time
we're going to do a MySQL synchronize
we're going to go ahead and set the
database names and here both of them are
equal to the same one so it's pretty
straightforward
and then we do have to come through here
there's a couple places where we used
like here
before we sent the database name in
and we're just going to do in both cases
we're just going to send that in
technically we probably don't even need
that
because now we could use the
databases
database names that we've stored as
parameters so let's actually go look at
that let me see make sure so let's look
at missing triggers
triggers
so here we have the we've got a source
name and a destination name what we can
do
is we can say Source name equals self
dot Source database name
and then destination name equals
self Dot
destination DB name
and now we don't need those
parameters at all
so we come back to our main and that was
for missing triggers
we also have that for missing procedures
and missing functions so let's do that
for each of those
so we've got less stuff we're having to
pass around missing procedures missing
functions
the same procedures
uh let's see whoops
let's go back to
where is that missing functions
missing triggers here we go
so we can actually clean these up pretty
easy
and I've come up here to our missing
procedures
and paste and we're good there now uh
one of the things I need to do is I need
to check to see if these databases even
exist locally
now what we could do
is you know check in our source and say
hey if we don't have a database that
exists in the destination go ahead and
create the database but we're gonna pass
on that because it's sort of a we could
do it but right now we're not going to
bother with that too much so we're going
to go in and manually create those if we
need to so
uh let's see if I can log into my
database oops
oops let me make sure I pass password is
typed correctly there we go sure if I
need to show databases
okay so here I have tutorial I did not
have giganator H goals so let's do that
create database H goals
and so we're just going to pull those
directly across again later
and let's go ahead and do our
synchronized rows
oh and here we use the DB again
uh so let's go to syncrose
[Music]
so it is the destination database name
which is uh let's see I think I used it
here
oh there we go
I guess I could have just used it
instead
so that's going to be a destination
DB name
and let's see let's make sure I use that
right
oh because I just stepped on that name
let's see
let's see
oh I may not need that in Sacramento so
I wonder why I sent that
may not need it at all
so I'm going to drop the table
Target tables
okay so we're probably okay there
so let's see so we go through each of
these we're going to go ahead and just
take all the rows so we're going to run
this through all the way now
um
Let's see we come through we're going to
connect to the source
um
and here's something else I'm going to
do
is let me go ahead and create a settings
file
and actually now
instead of all the way up here before
what we were doing is we were just
creating the configuration as it existed
and so we would come in here and
we'd settle our values so what I'm going
to do now is I'm just going to keep this
pretty simple and from settings I'm
going to import The Source config and
the cart Target config so you go Source
config and then Target config
and import those in and then I'm going
to send those into the Connect Now
technically now I could do two different
I could do one connect and then just
pass back
you know assign out the connector that
comes back
but I'll keep it as source and
destination just for I don't know
cleanliness understanding
and now we've got that so we've got
we've extracted our setups into our
configuration settings into a settings
file
so we're going to come in we're going to
pull that in
what we could do is we could even
pull the databases out
bases
and if I go to settings I'll just push
that up here
and so now you have everything actually
extracted out into
a settings file or at least a lot more
of it now what we're also going to have
here is let's go ahead and do Max rows
and we'll pull that out as well so now
there's just less we have to worry about
so now in here you're starting to get
something that's pretty darn clean uh
and it's sort of a generic kind of
script that we have got created out
which is
um
one of the things we wanted to run into
here uh here we go generalize extract
command line parameters we actually are
going to pull those out into a settings
file
now let's go ahead and run this
and what we will see
um
uh actually let me not sync the data so
let's do that first without
I'm just going to get rid of synchros
not going to use that right now now what
we can do is we can actually pull little
Flags in and put the settings to be a
whole series of flags whether we do
triggers procedures functions indexes
and all that kind of good stuff right
now we're not going to do it that way
we're going to just create our database
real quick let's see so
this is Main
and
whoops
[Music]
what did I miss oh gosh I didn't even
lose this
I used to lose one of my imports so now
there we go there we go and boom so now
if we go look at uh locally refresh this
all right let's just reconnect because
we probably need to so now we can see
that we have a giganator that doesn't
have any tables oh interesting and we
probably have an H goals that maybe
doesn't have any tables nope it's not
how many tables so
let's go see what we got here
oh failed to create index
hmm
so for some reason
oh probably because it's already there
so what we want to do in missing indexes
oh well that says it should be
already missing so we've got something
there that didn't quite work
but we did a try so it came in what did
it do oh interesting
so we come out we do missing indexes for
DB in databases
let's do this
um
current DB
and let's see what it does here
yeah so for some reason
index
oh we're missing something here uh oh oh
oh oh oh oh oh oh oh oh oh oh oh it's
because the database there we have to
change the database for each of those
uh so that's going to be
so what we really want to do here
is that the source config
which I think we can do here so let's do
uh Source config
but first what we want to do is we want
to do Source config
um
let's see if we can do
database
equals DB
and then we're just going to quit
because we're going to have to update
our database there
oh
look at that
yeah we can't assign that so it's not a
function call
try that
there you go so let's see we need to set
so we've got to do a dictionary key kind
of thing here
and let's do this uh see
python set dictionary
value
oh that's right I think it's a get and a
set assigning values
so
oh that's okay yeah that's okay because
that is correct because that is the
first
uh so let's go here
uh let's see Target config
well don't need that don't need that now
let's take a look
so take a look at this again
let's do it this way let's go to whoop
settings
and let's just go ahead and get rid of
that as the first one
okay
so now if we come in
and let's do this okay so let's do print
DB
foreign
so let's see how this works
see what we got here okay so it does
that
and then let's print
Source config
and we've got our database has changed
so we should be good
so if we look at
let's go to missing tables
[Music]
tables whoop
and we will look real quick so it says
here it's going to be print for a table
that's in missing tables
for each missing table get details but
we've got to get self dot missing tables
which we do so we do set missing tables
uh here we go
so we do compare tables
so let's do that we'll do set missing
tables let's just do this real quick
self
dot Source tables
and Target tables
let's go look at that real quick
and we'll see
so there you go so we have oh we have
source and Target flipped
somehow we got source and Target flip so
we go here
is that right
uh oh
that is flipped so
that's Target
that Source it's amazing how that works
out so let's try that again
uh let's see so now if we go into Main
and we run it
wow it works amazingly better when you
get your settings record properly so now
we're going to see that this is going to
update
and we're starting to see all of our
little tables show up
so I think we'll wrap it up this time
um I'll probably go back and edit a
little bit shorten this out a little bit
but we uh have straightened that out in
our settings and now we're actually able
to to send multiple databases across and
do our synchronizing uh actually what
I'll do is I will sync rows
and
um we'll kick this off and it'll
probably be pretty close to next time
before this is all done anyways and
we'll get all that stuff taken care of
and we're gonna come back and we're
probably going to dive into one of the
more complicated things here uh dealing
with enumerated values for a column
because that is something we've run into
now a couple of times and I want to make
sure we get that one fixed
that being said go out there and have
yourself a great day a great week and we
will talk to you next time
[Music]