Detailed Notes
Focus for this episode: We debug a few column synchronization issues.
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 series on sql in python where we are building a database synchronizing python script application and last time around we were looking at synchronizing columns we got so we have the ability to add tables to add columns to add rows and now we sort of flip back up a level to deal with columns now one of the things i want to do is we ran through this and we're basically spinning through doing our describe of columns and one of the things we want to do is where there are differences then we come in and we do this little uh update and we print our update well what i want to do is i'm going to do something where i do a print that the difference is found and i'm going to pull out some of these other prints that'll work fine i think that'll do it if i get that and then that probably looks pretty good okay and then i'll print the tables so um yeah that's if update so now let's try this again and i'm going to go through let's make sure i'm the right one let's see what we're getting because some of these are getting changes so let's look at some of these because i think we're not reading these are where we don't read stuff exactly right so first one here [Music] we come up here oh let's see okay so we have here there's a yes and a no uh let's get what the let's say difference found um let's do this let's go ahead let's actually make this a little easier so we're going to print see do i want oh because i got rid of those two that's what i don't want so here we go um oh well i can do i'm going to go and print the index yeah idx okay oh let's do this while length of source column so let's do this let's just print um source column wait that's not what i want because i need to go up here okay so that's where the name matches and then what i want to do is uh yeah i can probably do source column and destination column let's go look at that let's see if this will give it to me all right so here for example so i've got a current time stamp but it's the default generated so in this case that's going to be good what i want to do is i want to do the modify update so i'm going to go look at that default generated key because i think that's what that is so that's one of the things we're going to fix so let's look here we're just going to do fix default generated oh and that's going to be there so that's actually part of the those are two different ways to so we need to do is we need to fix that in our time so if we go up here to where's our time uh see did we do oh here we go so if he is that oh wait that's not where i want the default to be ah default default default false michael none and i done and that is okay so that's just gonna say that's those two so we're gonna have to fix that one okay so let's go see if we can find something else as well so here we've got a mole so this is a multiple prime uh key so i think we're gonna find these so what we're probably going to do is we're going to be running into key issues here uh let's see multiple uh unique so what we're going to have to do is we're going to actually dig up those uh those keys those indexes in those cases uh let's see timestamp no that's whether it's in default or whether it's nullable or not so this one should be okay so the destination is the second one and so it's saying that there it is uh can be defaulted but it's saying no so i need to check uh let's see oh i need the null did i not get the null piece oh i didn't get the null piece so let's go look here to um is that what i want uh default let's see no i need not know here we go so i'm going to need this one basically so that was in column two so i need to say here [Music] let's see sync my rows sync my columns this is going to be sort of a problem so i'm going to say here um this is going to be uh this is going to be lf idx equals is that one no two two so far i gotta make that that guy so i get my bites here i go there there there so here um if um so this is going to be source call right uh this is cur source whoops dot upper equals yes then i need to do i'm going to do like a null string right default so i'm going to need here and he's going to need to do another one so i'm going to have to do a plus default i'm also going to have to do a i'm going to call that the null string and the null string is up here in two i'm going to sit here so i'm going to say null string equals uh so i'm going to say if it's no uh if it's nullable yes so it's going to be not null and here if he's yes then null string equals no okay so now let's go take a look at those whoops doesn't like that data truncated oh that's because of the columns sync rows i don't need to do sync rows let's get rid of that real quick that's a different issue oh maybe not um let's go so app user doesn't like that let's see if i do this uh max rose equals zero let's go do this let's do my sequel what is my little thing up here tutor okay tutor and password and i'm going to use tutorial actually i'm going to do drop database tutorial to tool oops if i can spell it right drop database tutorial create database tutorial and now let's see what happens if i run it nope i'm still getting an issue here so i've got a data current truncated so let's see what if i get right before that alter table modify t1id oh so i want to ignore zero one two three so let's ignore the keys for now and so now uh let's see i want to come in sync columns because i'm not going to i'm going to deal with the uh oh let's see i am going to deal with make sure i got everything here oh no i don't and i'll make sure i don't deal with the um the indexes and such that zero one two three so if an index not equal to three now let's try here we go so what we're doing here is let me put that in there ignore index compare here so now i'm going to come through it's going to do all the data so i'm going to i've blown this away we're going to bring all our data across and then we'll get some alters here can i give it a minute while it's doing this yes i am just vamping a little bit while this thing is cranking through okay we finally got through all of that so let's go back in here and what we want to do is uh we've got now the columns are all coming across same so what i want to do is go over here somewhere if it's going to allow that i need to reconnect and what i'm going to do now is i'm just going to go into a simple like i'm going to go here and i'm going to change that so i'm going to go alter the table and we're here it has address names 100 and city is 40. i'm going to change it to 50 and 20. and now what i should get is i should get those two to update as this thing comes through and then what's going to happen is uh we'll see these will kick back to what was at 140. so if we run through and do this i've dropped the zeros the max rows to zero and i just got rid of syncros so just go quick okay it took a few seconds to get everything back in line but what we're going to see here is that we can see our differences and here an address we've got one that was a varchar50 one is a 100 one and we can see the difference found was there and then it's going to modify it and it's going to make address name equal to 50 which means we're grabbing the wrong value uh and here it goes from 2040 so we're doing the wrong one so we are let's see when we create the sink of rows oh no i want some columns then what we want is we want it to be it should be the source so let's see if we're getting these right so like here we're talking at the size which is probably here if they're not the same then we have this difference and so oh difference is found and that's going to be in the name equals cursor if it's one if it's four which is zero one so we need one uh so for one let's see call type equals cur source which should be right oh we've got the two flipped let's see so source is the source columns which is okay so you have source and destination source which is dbc yes dbc is our source db2 is our yep destination so we should have here when we do sync columns oh here it says the source column so the source column is not getting the right thing so source call uh let's see so i think what we want here set source column that's destination column which we want that to be fixed here if we do the update then we take the column type which should be where's call type call type is nothing interesting so i have that somewhere in here so let's see call type there we go so the call type should be the current source but the source is 50 which is not right because what we had back here was 50 was the address name we're looking at the local and so if we look here let me make sure i got these right so source is source destination is dist i'm going to update cursor which is the destination cursor uh source equals that query destination equals that source columns is the source destination columns is destination source calling that destination call that if the same they come in here name equals source column current source current destination got that got that got that got that got that got that something is awry here say columns sync columns that's sorry the sync rows sync columns source destination uh let's see and then an update do i do an update yeah do an update which is on the destination source does a query destination does a query so let's go look here something's not quite right there so that's probably going to run through if i run it and then i'm going to see the same thing up now they're the same so let's see maybe i've just printed those wrong let's go back here refresh the objects tree no local 50 and city is 20. if i go to my demo did we get those flipped uh if we go out here to our tutorial did i just make a big mistake uh i don't think i did but we're gonna find out in a second yep 15 20. so let's go look at that again oops oh he doesn't like that so he's got an issue okay so i'm going to have to fix that but i'm not sure why so if i come through here let's go look again db2 is my local host oh which i'm probably working everything except here so let's go look here did i switch those two let's see so this is dbc and db2 which do not get changed anywhere so source and destination so if i come in here source destination i do the description on the source description on the destination so it should be at 5900 updates oops sorry that's synchros is destination as well and so i do uh source fetch all i do destination fest hall i'm really concerned about this because that source everything here is coming out of the source except that's okay so that's a column type so let's start here if we go all the way up here let me see address name is there city oh because it's comparing both of the same so we have to change it out this time so we'll have to come back and we will tackle that next time around we've got a bug but i think we're going to go ahead and call this and then we will come back next time and track that bug down as always go out there and have yourself a great day a great week and we'll talk to you next time [Music] you
Transcript Segments
[Music]
well hello and welcome back we're
continuing our series on sql in python
where we are building a database
synchronizing python script
application
and last time around we were
looking at synchronizing columns we got
so we have the ability to add tables to
add columns to add rows and now we sort
of flip back up a level to deal with
columns
now one of the things i want to do
is we ran through this and we're
basically spinning through doing our
describe of columns
and one of the things we want to do is
where there are differences then we come
in and we do this little uh update and
we print our update well what i want to
do is i'm going to do something where i
do a print that the difference is found
and i'm going to pull out
some of these other prints
that'll work fine
i think that'll do it
if i get that and then
that probably looks pretty good okay and
then i'll print the tables so
um yeah that's if update
so now let's try this again
and i'm going to go through let's make
sure i'm the right one let's see what
we're getting because some of these are
getting changes
so
let's look at some of these because i
think we're not reading these are where
we don't read stuff exactly right so
first one here
[Music]
we come up here
oh let's see
okay so
we have here there's a yes and a no uh
let's get what the
let's say difference found
um
let's do
this let's go ahead let's actually make
this a little easier so we're going to
see do i want
oh because i got rid of those two that's
what i don't want so here we go um
oh well i can do i'm going to go and
print the
index
yeah idx
okay
oh let's do this while length of source
column
so let's do this let's just print
um
source column
wait that's not what i want because i
need to go up here
okay so that's where the name matches
and then what i want to do is uh
yeah i can probably do source column
and destination column
let's go look at that let's see if this
will give it to me
all right so here for example
so i've got a current time stamp
but it's the default generated
so in this case that's going to be good
what i want to do is i want to do the
modify
update so i'm going to go look at that
default generated key because i think
that's what that is
so that's one of the things we're going
to fix so let's look here we're just
going to do
fix
default
generated
oh and that's going to be there so
that's actually part of the
those are two different ways to so we
need to do is we need to fix that in our
time so if we go up here to where's our
time
uh see did we do oh here we go
so if he is
that
oh wait that's not where i want the
default to be
ah
default default default
false michael none and i done and that
is
okay so that's just gonna say that's
those two so we're gonna have to fix
that one okay so let's go see if we can
find something else as well so here
we've got
a mole so this is a multiple prime uh
key
so i think we're gonna find these so
what we're probably going to do is we're
going to be running into key issues here
uh
let's see
multiple
uh unique
so what we're going to have to do is
we're going to actually dig up those
uh those keys those indexes in those
cases
uh let's see timestamp no that's whether
it's in default or whether it's nullable
or not
so this one should be
okay so the destination is the second
one
and so it's saying that there it is
uh can be defaulted but it's saying no
so i need to check
uh let's see
oh i need the null did i not get the
null piece oh i didn't get the null
piece
so let's go look here to
um
is that what i want
uh default let's see
no
i need not know here we go
so i'm going to need this one basically
so that was in column two
so i need to say here
[Music]
let's see
sync my rows sync my columns
this is going to be sort of a problem so
i'm going to say here
um
this is going to be
uh this is going to be lf idx
equals
is that one
no two
two
so far i gotta make that that guy
so i get my bites here i go there there
there
so here
um
if
um so this is going to be source call
right uh this is cur source
whoops
dot upper
equals yes
then
i need to do
i'm going to do like a null string right
default
so i'm going to need here
and he's going to need to do another one
so i'm going to have to do a plus
default i'm also going to have to do a
i'm going to call that the null string
and the null string is up here in two
i'm going to sit here so
i'm going to say
null string equals
uh so i'm going to say if it's no uh if
it's nullable yes so it's going to be
not null
and here
if he's yes
then null string
equals no
okay so now let's go take a look at
those
whoops doesn't like that
data truncated
oh that's because of the columns sync
rows i don't need to do sync rows let's
get rid of that real quick
that's a different issue oh maybe not
um let's go
so app user doesn't like that
let's see
if i do this
uh max rose equals
zero
let's go do this let's do my sequel
what is my little thing up here
tutor okay
tutor
and password
and i'm going to use tutorial actually
i'm going to do drop database tutorial
to tool oops if i can spell it right
drop database tutorial
create database tutorial
and now let's see what happens if i run
it
nope i'm still getting an issue here so
i've got a data current truncated so
let's see what if i get right before
that
alter table modify t1id
oh so i want to ignore zero one two
three so let's ignore the keys for now
and so now
uh let's see
i want to come in sync
columns
because i'm not going to i'm going to
deal with the uh
oh let's see i am going to deal with
make sure i got everything here oh no i
don't
and
i'll make sure i don't deal with the um
the indexes and such that zero one two
three so if an index
not equal to three
now let's try
here we go
so what we're doing here is let me put
that in there
ignore index
compare
here
so now i'm going to come through
it's going to do all the data so i'm
going to i've blown this away we're
going to bring all our data across
and then we'll get some alters here
can i give it a minute while it's doing
this yes i am just vamping a little bit
while this thing is cranking through
okay we finally got through all of that
so let's go back in here and what we
want to do
is uh we've got now the columns are all
coming across same so what i want to do
is go over here somewhere
if it's going to allow that
i need to reconnect
and what i'm going to do now is i'm just
going to go into a simple like i'm going
to go here
and i'm going to change that so i'm
going to go alter the table
and we're here it has address names 100
and city is 40. i'm going to change it
to 50 and 20.
and now what i should get is i should
get those two
to update as this thing comes through
and then what's going to happen is uh
we'll see these will kick back to what
was at 140.
so if we run through and do this i've
dropped the zeros the max rows to zero
and i just got rid of syncros so just go
quick
okay it took a few seconds to get
everything back in line but what we're
going to see here is that we can see our
differences
and here an address we've got one that
was a varchar50 one is a 100
one and we can see the difference found
was there and then it's going to modify
it
and it's going to make address name
equal to 50 which means we're grabbing
the wrong value uh and here it goes from
2040 so we're doing the wrong one so we
are
let's see when we create
the sink of rows
oh no i want some columns
then what we want
is we want it to be
it should be the source so let's see if
we're getting these right so like here
we're talking at the size
which is probably here if they're not
the same then we have this difference
and so
oh
difference is found
and that's going to be in the
name equals cursor if it's one if it's
four
which is zero one so we need one uh so
for one
let's see call type equals cur source
which should be right
oh we've got the two flipped
let's see
so
source is the source columns
which is
okay so you have source and destination
source which is dbc
yes
dbc is our source db2 is our
yep
destination
so we should have
here when we do sync columns
oh here it says the source column so the
source column is not getting the right
thing so source call
uh let's see
so i think what we want here
set source column that's destination
column
which we
want that to be
fixed here if we do the update
then we take the column type
which should be
where's call type
call type is nothing
interesting
so
i have that somewhere in here so let's
see
call type
there we go
so the call type should be the current
source
but the source is 50
which is not right because what we had
back here
was 50 was the address name
we're looking at the local
and so if we look here
let me make sure i got these right so
source is source
destination is dist
i'm going to update cursor which is the
destination cursor
uh source equals that query destination
equals that source columns is the source
destination columns is destination
source calling that destination call
that
if
the same they come in here name equals
source column
current source current destination got
that got that got that
got that got that got that
something is awry here
say columns
sync columns that's sorry the sync rows
sync columns
source
destination uh let's see and then an
update do i do an update yeah do an
update which is on the destination
source does a query destination does a
query
so let's go look here something's not
quite right there so that's probably
going to run through if i run it
and then i'm going to see the same thing
up now they're the same so let's see
maybe i've just printed those wrong
let's go back here
refresh the objects tree
no
local 50
and city is 20. if i go to
my demo did we get those flipped
uh if we go out here to our tutorial
did i just make a big mistake uh i don't
think i did but we're gonna find out in
a second
yep 15
20.
so let's go look at that again oops oh
he doesn't like that so he's got an
issue okay so i'm going to have to fix
that but i'm not sure why so if i come
through here let's go look again
db2
is my local host
oh which i'm probably working everything
except here so let's go look here did i
switch those two
let's see so this is dbc
and db2
which do not get changed anywhere
so source and destination so if i come
in here
source destination
i do the description on the source
description on the destination so it
should be at 5900
updates oops sorry that's synchros
is destination as well
and so i do uh source fetch all i do
destination fest hall
i'm really concerned about this because
that source
everything here is coming out of the
source except
that's okay so that's a column type
so let's start here
if we go all the way up here
let me see address name is there
city oh because it's comparing both of
the same so we have to change it out
this time
so we'll have to come back and we will
tackle that next time around we've got a
bug but i think we're going to go ahead
and call this and then we will come back
next time and track that bug down
as always go out there and have yourself
a great day a great week and we'll talk
to you
next time
[Music]
you