Detailed Notes
Focus for this episode: We start looking at bringing across data.
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 uh working with the data sync as far as a tutorial for both mysql and really for python since this is a python script but we're going to be we're doing a lot of playing around with the sql now since last time one of the things that i wanted to mention is and you'll see these in the update in github i was not closing out cursors before and that can cause some issues you can and so you always want to everywhere you have a uh an open like you do here where you're grabbing a cursor you want to be able to close that back as well just to make sure that you're you know reclaiming your resources so everywhere that i had that create missing tables create missing columns i would just come in after the fact and clean up those resources now this episode i want to get into uh i'm going to do it this way i'm going to get into the data side of this what we had before was we're able now to go from source to destination and we can create tables and then we can add in any missing columns and now we get to deal with data this one becomes a little tricky because there's a lot of different things being run into and the biggest thing being probably size of data this is something you're probably not going to want to run if you're dealing with millions of records because it's just going to be time consuming but let's sort of dive into this first and see what we can do so let's start with so we're going to call now this sync rose and we're going to send it a list of the tables in the source database because now they should all be synced up and then the two connectors and so where do we go back here uh up here sync rows so we'll go ahead and we'll just open up the cursors for both of them really don't need that missing columns and our query is going to be select star from and it's whatever the table is and so that's going to be and this is going to be our first part here so let's just do it we're just going to go from scratch sort of on this method function uh so we're gonna come in here we're going to do our column query actually we'll call this our source query now uh let's call it our data query and for now uh let's see and then we wanted to do the uh fetch all so let's see source rows equals uh what do we call that cursor source dot if i spell it right fetch all there we go and let's just do this four row in source rows we're just going to print row print row and this thing is going to blow us up because we get a bunch of data but let's go ahead and run this real quick uh let's see that should work so we're seeing a huge amount of data come by here because it's flipping through in this case it's one of those tables that has a huge amount of data here we've got one and we can see in each one it's sort of flying by but you can see here where you're getting some uh here we go you're getting each of the columns and then you're getting some sort of a converted value so the nice thing is what i can do is i can for each row i can basically do i'm going to do a uh insert actually insert query and it's not really query we'll do instead statement equals insert into a table and so there's our table then we're gonna have to give it the columns and so our columns are going to be we're gonna have to come from a describe which we've done before so we gotta describe query we'll just steal that here there we go uh from that describe results okay and actually let's do this uh insert statement equals that and then we're going to do four item in uh what do we call it um [Music] description results or describe results insert statement equals insert statement oops statement plus uh so each one we're going to do a let's do our little tick mark and plus the name which is item which i believe is zero gives us the column name and then uh tick mark and then a comma oh how do we want to do this okay i'm gonna do this a little differently so uh for whoops first equals true if first then i just do that and first equals false else i'm going to do the same thing so for this i'm going to start it with a comma uh let me do a comma and then let's see so i get all the way through it and then i'm going to come up here and i'm going to say insert statement equals and start statement plus i got to close that out and then i'm gonna do values whoops i'll use oop if i can type right and i'm gonna give my values and then i'm going to end up ending it and i'll end it with a close it with a semicolon so then what i need to do is um am i in the oh i want to do this within a row okay so that gives me the start so now what i'm going to do is i'm going to come in here and i'm going to do present print oh uh for each row okay and this is going to be a print search statement and what i want to do is not build that over and over again so this is going to be uh insert base because i need this to be um what i'm going to do is this is going to be what i'm going to start one for with for each one and we'll see this in a second there we go just insert space okay so insert base insert base center surface insert base and i get that so here insert statement uh for each one equals insert base plus uh equals insert bay uh let's do it this way so for each row uh let's do this because i know i'm gonna have to close that out so equals that and then he equals insert statement to close it out and then for within the row i'm going to want to go through each uh let's do four value in row and that's going to look like the oh let's do this so here first equals true i do basically the same thing except for here it's going to be insert statement insert statement and this should be value i'm going to see how this looks except for i don't need those i need regular tick marks because these are going to be values and not uh names or anything like that so let's see insert statement i need that here and then we're going to see in a second what do these look like oh i'm sorry it's not value it's called row oh no that's right it is value okay so there's our value okay so let's take a look what does it blew up uh oh that needs to be a string i'm not sure because we're adding this into our little sql string that we're building and so now we're going to see a ton of insert statements coming in i'll take a second here while it's generating all of these and hopefully it'll finish here in a second i probably could get rid of those two tables and that would have helped it immensely okay here we go so now we can look into like stats insert into stats he's got stat id code value value 2 updated and then the values are going to be right there so let's just take those because we should be able to take those so let's do uh some values and stat id we're going to take those we're just going to pop over here and oh i did not want to do that crap okay so if i take those oh no data basics selected so it needs to be tutorial and then they all just ran so now i won't see those show up now let me say uh owners what were the ones that had a lot of table uh owners and horses i think uh let's do because i had a couple of these let's get rid of breeders horses and owners so what i'm going to do here is sync rows let's go back to my tables list here real quick [Music] so okay if row not equal to horses let's do this horses owners and breeders so let's do this and and raiders whoops that's the owners so now this should fly through a lot faster uh oops if we come through here so we're not going to see at the end we're not going to see stats or some values oops oh that's why let's do this it's row zero my mistake let's do that so it'll be a little more readable okay oh but we do see some values and stats oh because we're not checking for differences we're just pulling all of the rows over so that's our first thing is we need to see does that exist or do we want to just you know empty the table so the dumb thing to do you know the brute force thing would be to empty the table and do our inserts so let's do first let's go to the most simple way to do that which would be so if we go into here so before we do them the first thing we're going to do is we're going to do this and we're going to go to the db destination and we're going to empty it so we're going to do truncate which i think we can do i'll make sure i've got that right okay stats okay so i can truncate the table uh this is going to be the trunk cable query and trunk query except we don't want to do it on the source we want to do it on the destination so now we're going to empty the table and then we're going to do is we're going to come in here and we're going to execute here except for it's going to be our insert statement and let's see how this works for us so we now should see a whole bunch of the not a whole bunch we should see these tables start to fill up so let's see what happens uh let's see incorrect integer four okay so let's see what our statement looks like uh print insert statement and so we may have an issue that we've got to look into so here uh oh it's a none so we need to change that so what we do need to do is say uh let's see let's do this um [Music] let's do curve value equals string value and this is going to be curve value except let's do it this way we're going to actually play with this one a little bit so we're going to say that plus that plus that and this it becomes core value or value so it's going to have that but what we're going to do is we're going to say if curve value equals none value equals no so now we should see that run through and we're still going to incorrect integer none in here so we have to look into that but we are going to be able to see here if we look at like address we're now seeing that our numbers have come across so the next episode we're going to come in we're going to chase this little thing down because we're having an issue with that oh i know what it is it's going to be we have to do it like that well quick fix um yeah so now we should see this works and oh no it's not defined it doesn't like the null so oh so it can't be no it's going to be we're going to keep it blank and now let's see what happens and we're still getting some errors so we're going to dig into this next time around because i don't want to go too long on this one so we will come back as always 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
uh working with
the data sync
as far as a tutorial for both mysql and
really for python since this is a python
script but we're going to be we're doing
a lot of playing around with the sql
now
since last time one of the things that i
wanted to mention
is and you'll see these in the update in
github
i was not closing out cursors
before and that can cause some issues
you can and so you always want to
everywhere you have a
uh an open
like you do here
where you're grabbing a cursor you want
to be able to close that back as well
just to make sure that you're you know
reclaiming your resources
so everywhere that i had that
create missing tables create missing
columns
i would just come in after the fact
and clean up those resources
now this episode i want to get into
uh
i'm going to do it this way i'm going to
get into the data side of this
what we had before
was we're able now to go from source to
destination and we can create tables and
then we can add in any missing columns
and now
we get to deal with
data
this one becomes
a little tricky
because
there's a lot of different things being
run into
and the biggest thing being probably
size of data this is something you're
probably not going to want to run if
you're dealing with millions of records
because it's just going to be
time consuming but
let's sort of dive into this first
and see what we can do so let's start
with
so we're going to call now this sync
rose
and we're going to send it a list of the
tables in the source database
because now they should all be synced up
and then
the two connectors and so
where do we go back here uh
up here sync rows
so we'll go ahead and we'll just open up
the cursors for both of them
really don't need that missing columns
and our query is going to be
select star
from and it's whatever the table is
and so that's going to be and this is
going to be our first part here so let's
just
do it we're just going to go from
scratch sort of on this method function
uh so we're gonna come in here we're
going to do our column query actually
we'll call this our source query now
uh let's call it our data query
and for now uh let's see
and then we wanted to do the uh fetch
all so let's see source rows
equals
uh what do we call that cursor
source
dot
if i spell it right fetch all there we
go
and let's just do this four row
in
source rows
we're just going to print row
row and this thing is going to blow us
up because we get a bunch of data
but let's go ahead and run this real
quick uh let's see
that should work
so we're seeing a huge amount of data
come by here
because it's flipping through
in this case it's one of those
tables that has a huge amount of data
here we've got one and we can see
in each one it's sort of flying by
but you can see here where you're
getting some
uh here we go
you're getting each of the columns and
then you're getting some sort of a
converted
value
so
the nice thing is
what i can do
is i can for each row
i can basically do i'm going to do a uh
insert
actually
insert query
and it's not really query we'll do
instead statement
equals
insert into
a table
and
so there's our table
then we're gonna have to give it the
columns
and so our columns are going to be
we're gonna have to come from a describe
which we've done before so we gotta
describe query
we'll just steal that here
there we go
uh from that describe results okay
and actually let's do this
uh insert statement equals that
and then we're going to do four
item
in
uh
what do we call it um
[Music]
description results or describe results
insert
statement
equals insert statement
oops statement
plus
uh so each one we're going to do a let's
do our little tick mark
and
plus the name which is
item
which i believe is zero gives us the
column name
and then
uh tick mark
and then a comma
oh
how do we want to do this
okay i'm gonna do this a little
differently so
uh
for whoops
first equals true
if
first
then i just do that
and
first equals false
else i'm going to do the same thing so
for this i'm going to start it with a
comma
uh let me do a comma
and then let's see so i get all the way
through it and then i'm going to come up
here and i'm going to say
insert statement equals and start
statement
plus i got to close that out
and then i'm gonna do values
whoops
i'll use
oop if i can type right
and i'm gonna give my values
and then i'm going to end up ending it
and i'll end it with a close it with a
semicolon
so then what i need to do
is
um am i in the oh i want to do this
within a row okay so that gives me
the start
so now what i'm going to do is i'm going
to come in here
and i'm going to do present print
oh
uh for each row okay
and
this is going to be a
search statement
and what i want to do is not build that
over and over again so this is going to
be uh insert base
because i need this to be
um what i'm going to do is this is going
to be what i'm going to start one for
with for each one and we'll see this in
a second
there we go just insert space
okay so insert base insert base center
surface insert base
and i get that so here insert statement
uh for each one equals
insert
base
plus
uh equals insert bay uh let's do it this
way so for each row
uh let's do this because i know i'm
gonna have to close that out
so
equals that and then he equals insert
statement
to close it out and then for within the
row
i'm going to want to go through
each
uh let's do
four
value in
row
and that's going to look like the oh
let's do this
so here first equals true
i do basically the same thing
except for here
it's going to be
insert statement
insert statement
and this should be value
i'm going to see how this looks
except for i don't need
those i need regular tick marks
because these are going to be values
and not uh names or anything like that
so let's see insert statement i need
that here
and then we're going to see in a second
what do these look like
oh i'm sorry it's not value it's called
row
oh no that's right it is value okay so
there's our value
okay so let's take a look what does
it blew up
uh oh
that needs to be a string
i'm not sure because we're adding this
into our little sql string that we're
building
and so now we're going to see
a ton of insert statements coming in
i'll take a second here
while it's generating all of these
and hopefully it'll finish here in a
second
i probably could get rid of those two
tables and that would have helped it
immensely
okay here we go
so now we can look into like stats
insert into stats he's got
stat id code value value 2 updated and
then the values are going to be
right there
so let's just take those because we
should be able to take those
so let's do
uh
some values and stat id we're going to
take those we're just going to pop over
here
and
oh i did not want to do that crap okay
so if i take those oh no data basics
selected so it needs to be tutorial
and then they all just ran so now
i won't see those show up now let me say
uh owners what were the ones that had a
lot of table uh owners and horses i
think
uh let's do
because i had a couple of these let's
get rid of breeders
horses and
owners
so what i'm going to do here is sync
rows
let's go back to my tables list here
real quick
[Music]
so okay if
row
not equal to
horses
let's do this
horses owners and breeders so let's do
this
and
and
raiders
whoops
that's the owners
so now this should fly through a lot
faster
uh
oops
if we come through here
so we're not going to see at the end
we're not going to see stats or some
values
oops
oh that's why
let's do this it's row zero my mistake
let's do that so it'll be a little more
readable
okay
oh but we do see
some values and stats
oh because we're not checking for
differences we're just pulling all of
the rows over
so that's our first thing is we need to
see does that exist or
do we want to just you know empty the
table
so the dumb thing to do
you know the brute force thing
would be to empty the table and do our
inserts so let's do
first
let's go to
the most simple way to do that
which would be so if we go into here
so before we do them the first thing
we're going to do is we're going to do
this
and we're going to go to the db
destination
and we're going to empty it so we're
going to do
truncate
which i think we can do
i'll make sure i've got that right okay
stats
okay
so i can truncate the table
uh this is going to be the trunk cable
query
and
trunk query
except we don't want to do it on the
source we want to do it on the
destination
so now we're going to empty the table
and then we're going to do is we're
going to come in here
and we're going to execute
here
except for it's going to be our insert
statement
and let's see how this works for us
so we now should see a whole bunch of
the not a whole bunch we should see
these tables start to fill up
so let's see what happens
uh let's see incorrect integer
four
okay so let's see what our statement
looks like
uh print
insert statement
and so we may have an issue that we've
got to look into so here
uh
oh it's a none so we need to change that
so what we do need to do is say uh let's
see
let's do this um
[Music]
let's do curve value equals string value
and
this is going to be curve value
except
let's do it this way
we're going to actually play with this
one a little bit so we're going to say
that plus that
plus that
and this it becomes core value
or
value
so it's going to have that but what
we're going to do is we're going to say
if
curve value equals
none
value equals no
so now
we should see that run through
and
we're still going to incorrect integer
none in here
so we have to look into that but we are
going to be able to see here if we look
at like address
we're now seeing that our numbers have
come across
so the next episode we're going to come
in we're going to chase this little
thing down
because we're having an issue
with that
oh i know what it is
it's going to be we have to do it like
that well
quick fix
um
yeah
so now
we should see this works
and
oh no it's not defined it doesn't like
the null
so oh
so it can't be no it's going to be
we're going to keep it blank
and now let's see what happens
and we're still getting some errors so
we're going to dig into this next time
around because i
don't want to go too long on this one
so we will come back
as always go out there and have yourself
a great day a great week and we will
talk to you
next time
you