Detailed Notes
Focus for this episode: More details about columns and adding columns that are missing in the destination DB.
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 are continuing our series where we're looking at uh sort of combining our python and our sql tutorials and we're building a database synchronization script based on uh it's written in python now since the last time i do want to catch up a little bit a couple things that i'm a couple of changes i made just so you know so one of them is we were looking at default functions you know you can either default as a value or a function one of your defaults uh and we basically said if it's a if it's a function then if we have a parenthesis open parenthesis we're gonna assume there's a function in there however there is the key phrase current underscore time stamp which actually is a function um and there may be some others but this one i know i ran into so go ahead and count that as a function another thing was uh just for safety's sake i went ahead and decided to wrap the column names in uh these little tick marks in case it is some sort of reserved word or something like that i came across that in one of the one things i was playing around with so i figured it was probably best to do that that being said now so what we've got is we've been able to come in and deal with our tables the next thing we want to do is we're going to go with the table we're going to see if the columns match up so let's let's see create missing tables let's just pause that for right now so we'll come back to that later and for now what we want to do is we're going to go for in each let's see where we do find here we go missing tables and now we're going to create one that's called missing columns and basically it's going to work the way as missing tables so there's going to be a source and a target and let's see we had somewhere down here we did missing tables uh here and we'll just print them for now in case there are any i don't think there will be because we should have everything going and now we're going to have missing columns and uh these table dot columns are not in target so this one's gonna be a little more complicated so for missing columns what we're gonna do is we're gonna go through so for each table and we're going to assume that they're not missing so we actually don't need well i guess we do need it in both of them that's a good question let's say well we do still need source and target and did we call those oh no so we need actually this source so we do missing columns here we go we actually need uh dbc which is our source and db2 is our target beca and let's actually do tables which is going to be let's just say source tables so we're going to assume that they're all there at this point and we'll see how that works out so back up here so we have uh let's do this db source db destination and tables tables list and so missing columns so now what we're going to do is for row and tables list and so now each one this is gonna be a table name and so we're gonna have to do a query so we'll just take that one for row and there uh we'll return missing columns and let's see so this is going to be our column query this will be our source column query and let's see in each one yep well actually that can be our column query and so we are going to we need two cursors oops so let's do that let's go and create these cursors real quick so that's going to be cursor source is db source the destination is going to be tv dust and so we're going to do with each case we're going to describe the table so we're going to get two set specs we're going to do source and destination and so we want source and we want destination so now we're going to have source columns equals we're going to have uh we'll call it destination columns equals and [Music] oops um wait that's how i want i'm sorry we just want to execute them first execute the two and then we're going to be source columns equals cursor source stuff actually there we go destination columns equals destination fetch all and now what we're going to do here is do something along these lines so what we're going to do so so for column row in target destination columns then for each one let's see so name is going to be equal to row 0 because that is where our names are for our columns and if name is not in oh so what we want to do okay so first we go to generate names so let's call it uh source names equals this and then for conroe and source columns then for each one of those source names dot a pin uh see row zero so we're gonna go into each of these first we're gonna do is we're gonna build a list of the column names and the source and then we're gonna go through each one of these uh which one of the destinations and we're gonna say if the name is not in source names this is call row so if the column if the name of the destination is not in the source then we're going to do missing columns and we're going to append the name except for we're going to do a table which is let's do this for table there we go it's gonna be table plus and we're just going to do dot name so this is going to give us all of the missing columns and then we're going to run through and see how that works now so right now let's see if it even runs whoops that's not what i want to do so we didn't get any mix but let's go ahead and break one here so let's go to our source uh let's see so testing was our source right uh let's see tutorial is our source okay so local uh see uh is that right okay so then local tutorial is our database so let's go look here okay so if we go here let's get rid of a column somewhere like it really doesn't matter so let's just do with our address let's start with that let's get rid of our zip so let's go in here alter table and we're going to delete this column so we're going to drop columns up and now what we should see is that should show up and it does not so let's go in here oh let's see these columns are not in the target okay so we are so now what we want to do is we're going to go missing columns oh did we do oh no that's right so uh oh we did this wrong we want to do we want to do the destination columns and then we want to say anything that's in source so this one says if the name is not in the source names so if the source data is not in the let's do this let's change this a little bit just to make it less annoying so here's the thing so in this case we're going from a to b and so we're going to say let's look at all the columns in b and then look at all the columns and then for each of those we're going to see which is here and then for each of those we're going to say for everything that's in a we're going to say hey does that already exist if it doesn't then it's missing and that should give us uh here we go missing calls is not defined oh it's columns and so i bet now we'll see there we go so address.zip is not there so we have gone through and we're now doing this and so we can find this is uh as we did before uh this is these columns are not in target we can take the same thing and say these columns are not in source just in case there's some extras and all we do here is swap these two so that's dvc that's db2 so what we'll get is we should get through here address that's it but it's all fine the other way and so that brings us to now what do we do so if we do this uh where did that go here so we need to do in this case is say that we have a missing column which is good because what we can do is um we can actually do this um how do we want to do this this will be good let's just look at it this way uh this won't give us a name but what we can do is we could just say call row and now we're going to see what the definition of that is and here we see that okay so this is missing and so all we need is a table name so we probably want to do this a little differently we're going to probably want to build our sql right here and then go back to this and then create out our sql which will probably include the table and the name so it would be something along bike let's do table sequel equals alter table plus name plus and column uh oops that's not name that's table plus name plus we'll do this plus a type which is a call row which we found before is the type is uh one so let's look at this for now uh and then we'll print this let's print uh call row there we go and so now we're gonna see oops we didn't print that didn't we oh yes we did um oh my mistake we want this to be a table underscore sequel bam so here we go so now we're starting to see the start of our sequel and uh we can see here uh basically you know for this column this sort of puts us a run to the end of this one so the next episode we're going to come back we're starting building out that column sql and probably like we've seen before where we built the table we're going to have to you know essentially um steal some of the same information so we may do some sort of uh you know abstract this out to a build column sql something along those lines but i don't want to you know spoil it too much so for now 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 are
continuing our series where we're
looking at uh sort of combining our
python and our sql tutorials
and we're building a database
synchronization script based on
uh it's written in python
now since the last time i do want to
catch up a little bit a couple things
that i'm a couple of changes i made
just so you know so one of them is
we were looking at default
functions you know you can either
default as a value or a function one of
your defaults uh and we basically said
if it's a if it's a function
then
if we have a parenthesis open
parenthesis
we're gonna assume there's a function in
there however
there is the
key phrase current underscore time stamp
which actually is a function
um and there may be some others but this
one i know i ran into so go ahead and
count that as a function another thing
was uh just for safety's sake
i went ahead and decided to wrap the
column names in uh these little tick
marks
in case it is some sort of reserved word
or something like that
i came across that in one of the one
things i was playing around with so i
figured it was probably best to do that
that being said
now so what we've got is we've been able
to come in and deal with our tables the
next thing we want to do
is we're going to go with the table
we're going to see if the columns match
up
so let's
let's see create missing tables let's
just pause that for right now so we'll
come back to that later
and for now
what we want to do is we're going to go
for in each
let's see where we do find here we go
missing tables
and now we're going to create one that's
called missing columns and basically
it's going to work the way as
missing tables
so there's going to be a source and a
target
and let's see we had somewhere down here
we did missing tables
uh here
and we'll just print them for now
in case there are any i don't think
there will be because we should have
everything going
and now we're going to have missing
columns
and uh
these table dot columns
are not in target
so this one's gonna be a little more
complicated
so for missing columns what we're gonna
do
is we're gonna go through
so for each table
and we're going to assume that they're
not missing so we actually don't need
well i guess we do need it in both of
them
that's a good question let's say
well we do still need source and target
and did we call those
oh no so we need actually this source so
we do missing columns
here we go
we actually need
uh dbc which is our source
and db2 is our target
beca and let's actually do tables which
is going to be let's just say source
tables
so we're going to assume that they're
all there at this point
and we'll see how that works out
so
back up here
so we have
uh let's do this db source
db destination
and tables
tables list
and so missing columns so now what we're
going to do is
for row and tables list and so now each
one
this is gonna be a table name
and so we're gonna have to do a query
so
we'll just take that one for row and
there
uh we'll return missing columns
and let's see so this is going to be our
column query
this will be our
source column query
and let's see in each one yep well
actually that can be our column query
and so we are going to we need two
cursors oops
so let's do that
let's go and create these cursors real
quick so that's going to be cursor
source is
db source
the destination is going to be
tv
dust
and so we're going to do with each case
we're going to describe the table so
we're going to get two set specs we're
going to do
source
and destination
and so we want source and we want
destination
so now we're going to have source
columns
equals
we're going to have uh we'll call it
destination columns
equals
and
[Music]
oops um
wait that's how i want i'm sorry
we just want to execute them first
execute the two and then we're going to
be
source columns
equals cursor source stuff actually
there we go
destination columns
equals destination fetch all
and now what we're going to do here
is do something along these lines so
what we're going to do
so
so for
column row
in
target
destination columns
then for each one
let's see so name is going to be equal
to row 0
because that is where our names are for
our
columns and if name is not in
oh so what we want to do
okay so first we go to generate names so
let's call it
uh source names
equals this
and then for
conroe
and source columns
then for each one of those source names
dot a pin
uh see
row
zero so we're gonna go into each of
these first we're gonna do is we're
gonna build a list of the column names
and the source
and then we're gonna go through each one
of these uh which one of the
destinations and we're gonna say if
the name is not in source
names this is call row
so if the column if the name of the
destination is not in the source then
we're going to do
missing columns
and we're going to append
the name except for we're going to do
a table
which is
let's do this for table
there we go
it's gonna be table plus and we're just
going to do dot name so this is going to
give us all of the missing columns
and then we're going to run through
and see how that works now
so right now let's see if it even runs
whoops that's not what i want to do
so we didn't get any mix but let's go
ahead and break one here so let's go to
our source
uh
let's see so testing
was our source right
uh let's see tutorial
is our source okay
so
local
uh
see
uh is that right okay so then local
tutorial is our database
so let's go look here okay so if we go
here
let's get rid of a column somewhere like
it really doesn't matter so let's just
do with our address let's start with
that
let's get rid of our zip
so let's go in here
alter table
and we're going to delete this column
so we're going to drop columns up
and now what we should see is that
should show up
and
it does not
so let's
go in here
oh let's see these columns are not in
the target okay so we are
so now what we want to do is we're going
to go missing columns oh
did we do oh no that's right
so uh oh we did this wrong
we want to do
we want to do the destination columns
and
then we want to say
anything that's in source so this one
says
if the name is not in the source names
so if the source data is not in the
let's do this let's change this a little
bit
just to make it less
annoying
so here's the thing
so in this case we're going from a to b
and so we're going to say let's look at
all the columns in b
and then look at all the columns and
then for each of those we're going to
see which is here
and then for each of those
we're going to say
for everything that's in a we're going
to say hey does that already exist if it
doesn't then it's missing
and that should give us
uh here we go missing calls is not
defined
oh
it's columns
and so i bet now we'll see there we go
so address.zip is not there
so we have gone through and we're now
doing this and so we can find this is uh
as we did before uh this is
these columns are not in target we can
take the same thing
and say these columns are not in source
just in case there's some extras
and all we do here
is swap these two
so that's dvc
that's db2 so what we'll get is we
should get through here address that's
it but it's all fine the other way
and so that brings us to now what do we
do
so if we do this
uh
where did that go here
so we need to do in this case
is say that we have a missing column
which is good
because what we can do
is
um
we can actually do this
um
how do we want to do this this will be
good
let's just look at it this way
uh this won't give us a name but
what we can do is we could just say call
row and now we're going to see what the
definition of that is
and here we see that okay so this is
missing
and so all we need
is a table name so we probably want to
do this a little differently we're going
to probably want to build our sql right
here
and then go back to this
and then create out our sql
which will probably include the table
and the name
so it would be something along bike
let's do table sequel
equals
alter table
plus
name
plus
and column
uh
oops that's not name that's table
plus name
plus we'll do this plus a type which is
a call row
which we found before is the type is
uh one
so let's look at this for now uh and
then we'll print this
let's print uh call row
there we go
and so now we're gonna see oops we
didn't print that didn't we oh yes we
did
um
oh my mistake we want this to be
a table underscore sequel
bam
so
here we go so now we're starting to see
the start of our sequel
and uh we can see here
uh basically you know for this column
this sort of puts us a run to the end of
this one so the next episode we're going
to come back we're starting building out
that column sql and probably like we've
seen before where we built the table
we're going to have to you know
essentially
um
steal some of the same information so we
may do some sort of uh you know abstract
this out to a
build column sql something along those
lines
but i don't want to you know spoil it
too much
so for now go out there and have
yourself a great day a great week and we
will talk to you next time
you