Detailed Notes
Focus for this episode: We continue creating columns that are missing in the destination.
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 tutorial series here where we are building a python script to synchronize tables and columns and maybe even eventually data from one database to another at this point we have got our table sinking in so if we have a table that exists in our source but does not exist in our target then we can create that we will execute that sql and create that table and we did that to basically build a sort of like a sample database on another server now last episode we we dropped off where we were comparing columns so now we're seeing where there is for a table that exists in both cases in the source and the target then which we're going to find cases where there's a column that does not exist in both and if we were to run this script then that would make sense the way we're doing is we're going to first get all our tables figured out and then that means that we should be good so that we're going to have every table that exists in the source now exists in the target because we will have corrected that and now we're going to go in and we're going to fix those columns and actually what we would want to do here is move this guy up ah let's see so i'm going to get our missing tables first but we're not going to call that right now and then we're going to do call and i think we're fixing right there yeah create oops missing columns and we're just going to go ahead and create it there so missing columns is going to tell us what we're not there but we're we probably don't even need to do that but we'll do that for now we're going to do the create it as we go because otherwise it's just a pain to do so what we looked at last time we were printing out the missing column now based on that we want it to do this uh format basically so we started with this so it's going to be alter table and we're going to give it the table name and the uh column wait let's see alter table table and so we're going to do table dot oh that's i'm sorry that's database.table which is fine because we're going to be in the uh given table we don't really need that database prefix so alter table table which is going to be the table name and let's go ahead and just because we put those little tick marks just to protect the table name and then we do a space and then we're going to do add and then we're going to open up our parentheses because then we're going to add if we look here the name and then the type the name we already got for the column and the type is going to be here in this row uh there and then we need to close it out now we do need to do as we did here because this is a very simple one we need to do values and we probably need to do no so let's go ahead and get those two so here let's see so handle default values no that's what we really want this to be is so we're going to add this if it can be null then it's going to be no and if not it's going to be not null but since we're doing an ad i guess it always has to be a note so we don't even have to worry about that because you really can't add a not null column so let's throw that out for now we do want to allow for a default and so this is going to be it's not call anymore it is callrow conroe so we need to do that before we do the other piece oops sorry we didn't want to get rid of all of that from the other ones let's go back and see where we okay um let's do that let's get back to where we were there we go so first uh wait we're gonna do it here and we don't need the null but we do need uh says great column type plus default plus close it out oops and we don't need a semicolon there uh so now it's gonna say here's a row here's our thing we're going to let's go ahead and print our table sql oh actually we did it so we can just do we still print that we do so now we're going to add it we're going to give a default value if there is a one and all should be well so let's take a look at what that looks like because we should see our address uh so here we're going to do alter table address add zip and what we want to do probably is our little type thing so let's do this let's fix for our type which is going to be here as well uh so let's see call type equals column row one and then uh there and then we want the column type to be down here which is here so that's not going to be called row that's going to call type now let's look at it and now we see alter table address add zip int and i bet if we execute that let's go here and just execute it just for the fun of it works fine so we're going to see it here refresh and boom now we have our column in there so we've been able to come in and add columns now what we're not doing is doing any changes to the source so let's uh which we want to make sure so let's go ahead and just i don't know let's go back and alter that sorry let's alter this table and let's drop zip out again uh let's see oh there we go and let's see if we can take just to be safe let's take uh one that has a default whoops wrong one here we go so we have this type timestamp uh although it says it can't be noble what's going to be an issue well let's try to drop it anyways let's see what happens okay so now we're going to see two this isn't executing anything yet so we're gonna see two two cases where it doesn't come in uh let's see so type timestamp uh here we go here we go so and the default is current timestamp because it is coming in as a function so let's see let's go ahead and turn this on so here uh we don't need this we really don't need uh how do we do this is really so let's do this and let's do four uh let's do cmd in this in columns so reach one of these we're gonna get this little sql back that's what we wanna do in each of these is we're going to execute on db2 right yeah so we're going to execute in db2 which is our second database that's the target and then we're going to do let's do it this way uh here we go we don't really need all of that but let's just do that anyways so we get our cursor our query is equal well actually it's just our command so we have to worry about that execute command and we're good so let's see what happens and it gets us these and it gets us these executions and so now let's see if it runs it again oh it does okay so let's go look at oh did i not return columns return missing columns i did because it definitely shows up because we print it and then here let's print the command and let's not let's get rid of this part ah let's see oops oopsie oopsie oopsie oopsie um [Music] here we go my mistake so these are not in the target oops i did it for both of them we just want to see if they're additionals in the source which we don't want to do so this is a missing column for that which is good i'm glad we didn't do that and then we're going to write to there that makes sense and let's just get rid of that for now interesting no it doesn't want to connect to the database interesting interesting interesting so we just did something we don't want to do for some reason so oh it didn't want to connect to the database connecting to the source database it did not want to do so let's do this real quick i don't know if i can kill that or not uh here we go i can uh that guy oh three two one okay there we killed it okay so now if we try to connect to that test database let's go test there real quick uh here okay so that worked so let's go see what you do okay there we go so now he runs through it's just sort of a minor little issue there let's see what we got here uh let's see duplicate column name zip so it was already there uh so he must have already run through somewhere so if i go here oh there i got a local uh tutorial tables refresh the objects tree just to be sure oh he's not there so let's go look at this again so these columns are not on the target which is db2 he comes out and he prints that oh shoot that's a problem now if we look at it there we go let's run it again and now we're good okay sorry about that so now we've got our column syncing up next time around we are going to consider some options for data i don't know we're going to go all the way deep into it but it's something we can talk about at least that being said it's a good time 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 [Music] you
Transcript Segments
[Music]
well hello and welcome back we're
continuing our
tutorial series here where we are
building a
python script to synchronize tables and
columns and maybe even eventually data
from one database to another
at this point we have got our table
sinking in so if we have a table that
exists in our source but does not exist
in our target
then we can create that we will execute
that sql and create that table and we
did that to basically build a
sort of like a sample database on
another server
now
last episode we we dropped off where we
were comparing columns so now we're
seeing where there is
for a table that exists in both cases in
the source and the target
then which
we're going to find cases where there's
a column that does not exist in both
and if we were to run this script then
that would make sense the way we're
doing is we're going to first get all
our tables figured out
and then that means that we should be
good so that we're going to have every
table that exists in the source now
exists in the target because we will
have corrected that and now we're going
to go in and we're going to fix those
columns
and actually what we would want to do
here is move this guy up
ah let's see so i'm going to get our
missing tables first but we're not going
to call that right now
and then we're going to do
call
and i think we're fixing right there
yeah create oops
missing columns and we're just going to
go ahead and create it there so missing
columns
is going to tell us what we're not there
but we're we probably don't even need to
do that but we'll do that for now we're
going to do the
create it as we go because otherwise
it's just a pain to
do so what we looked at last time we
were printing out the missing column now
based on that we want it to do
this
uh
format basically so we started with this
so it's going to be
alter table
and we're going to give it the table
name
and the uh column
wait let's see
alter table table and so we're going to
do table dot
oh that's i'm sorry that's
database.table which is fine because
we're going to be in the
uh given table we don't really need that
database
prefix so alter table table which is
going to be the table name and let's go
ahead and just because
we put those little tick marks just to
protect the table name and then we do a
space
and then we're going to do add
and then we're going to open up our
parentheses
because then we're going to add
if we look here
the name
and then the type
the name we already got for the column
and the type is going to be
here in this row uh there and then we
need to
close it out
now we do need to do
as we did here because this is a very
simple one we need to do
values
and we probably need to do
no
so let's go ahead and get those two so
here let's see so handle default values
no
that's what we really want this to be is
so we're going to add this
if it can be null
then it's going to be
no
and if not it's going to be
not null but since we're doing an ad i
guess it always has to be a note so we
don't even have to worry about that
because you really can't add
a not null
column
so let's throw that out for now
we do want to allow for a
default
and so this is going to be it's not call
anymore it is callrow
conroe
so we need to do that before we do the
other piece
oops
sorry we didn't want to get rid of all
of that from the other ones let's go
back and see where we okay
um let's do that
let's get back to where we were
there we go
so first
uh wait we're gonna do it here
and we don't need the null but we do
need
uh says great column type
plus
default plus
close it out
oops and we don't need a semicolon there
uh so now it's gonna say here's a row
here's our thing we're going to let's go
ahead and print our table sql oh
actually we did it so we can just do we
still print that we do
so now we're going to add it we're going
to give a default value if there is a
one and all should be well so let's take
a look at what that looks like
because we should see our address
uh so here we're going to do
alter table address add zip
and what we want to do probably is our
little type thing
so let's do this
let's fix for our type which is going to
be here as well
uh so let's see call type equals column
row
one
and then
uh there and then we want the column
type to be down here which is here
so that's not going to be called row
that's going to call
type
now let's look at it
and now we see
alter table address add zip int and i
bet if we execute that
let's go here and just execute it just
for the fun of it
works fine
so
we're going to see it here
refresh
and boom now we have our column in there
so we've been able to come in and add
columns
now what we're not doing is doing any
changes to the source
so let's uh which we want to make sure
so let's go ahead and just i don't know
let's go back and alter that
sorry let's alter this table
and let's drop zip out again
uh
let's see oh there we go
and let's see if we can take just to be
safe let's take uh one that has
a default
whoops wrong one
here we go so we have this type
timestamp
uh although it says it can't be noble
what's going to be an issue
well let's try to drop it anyways
let's see what happens
okay
so now we're going to see two
this isn't executing anything yet so
we're gonna see two
two cases where it doesn't come in
uh let's see so type timestamp
uh here we go
here we go
so
and the default is current timestamp
because it is coming in as a
function so let's see let's go ahead and
turn this on so here
uh we don't need this
we really don't need
uh how do we do this is really so let's
do this
and let's do four
uh let's do cmd
in
this in columns
so reach one of these we're gonna get
this little sql back that's what we
wanna do in each of these
is we're going to execute on
db2
right
yeah so we're going to execute in db2
which is our second database that's the
target
and then we're going to do let's do it
this way
uh
here we go
we don't really need all of that but
let's just do that anyways
so we get our cursor
our query is equal well actually it's
just our command so we have to worry
about that
execute command
and we're good so let's see what happens
and
it gets us these and it gets us these
executions and so now let's see if it
runs it again
oh it does okay so
let's go look at
oh did i not return columns
return missing columns i did because it
definitely
shows up because we print it
and then here
let's print the command
and let's not let's get rid of this part
ah let's see
oops
oopsie oopsie oopsie oopsie
um
[Music]
here we go my mistake
so these are not in the target oops i
did it for both of them
we just want to see if they're
additionals in the source which we don't
want to do so this is a missing column
for that which is good i'm glad we
didn't do that
and then we're going to write to there
that makes sense
and
let's just get rid of that for now
interesting
no it doesn't want to connect to the
database
interesting interesting interesting
so we just did something we don't want
to do for some reason so
oh it didn't want to connect to the
database
connecting to the source database it did
not want to do
so let's do this real quick
i don't know if i can kill that or not
uh here we go i can
uh
that guy
oh
three
two
one
okay there we killed it
okay
so now if we try to connect to that test
database let's go test there real quick
uh
here
okay
so that worked so let's go see what you
do
okay there we go so now he runs through
it's just sort of a minor little issue
there let's see what we got here
uh let's see duplicate column name zip
so it was already there
uh
so he must have already run through
somewhere so if i go here oh
there
i got a local
uh tutorial
tables
refresh the objects tree just to be sure
oh he's not there
so let's go look at this again
so
these columns are not on the target
which is db2
he comes out and he prints that
oh shoot
that's a problem now if we look at it
there we go
let's run it again and now we're good
okay
sorry about that
so now we've got our column syncing up
next time around we are going to
consider
some options for data i don't know we're
going to go all the way deep into it but
it's something we can talk about at
least that being said it's a good time
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
[Music]
you