Detailed Notes
Focus for this episode: Create missing tables Part 1 Simple table creation script output and columns name,type,null
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 this uh basically it's a database sync walkthrough learning about sql doing a little bit of a program along the way so last time around we were working on um missing tables so comparing two databases and finding the tables that we're missing now what i want to do before we go a little further into this one is i'm going to actually clean this up a little bit so i've got my connect to database one and two i've got those little functions and what i'm gonna do is i'm gonna do let's see so we had three things we're doing we're gonna do let's see i don't need oh i do need that well let's take this let's get this whole thing and this is gonna be let's go ahead and do it like this is we're going to do these uh so we've got let's do source tables uh let's see missing source oops if i can type that right we do missing source tables and we're going to do missing target tables so we're going to clean this up a little bit this will be a little easier to see so missing source tables is going to be this one uh let's see and let's do it this way so we're gonna send it uh source and target and we're going to come in here and let's see so we're going to get let's see source tables and target tables and so that's just going to be here so we're going to do new tables equals missing source tables and then this is going to be source tables and target tables whoops i'm gonna do this mostly for readability so i'm gonna come in here and that's gonna be that guy and let's do it this way so i get that new tables okay which equals that and then here let's do it this way new tables equals that and then we're going to return new tables whoops let's say we're gonna call it missing tables uh gonna go here and then we're gonna come here uh missing two there we go so we're gonna do that we're gonna basically do the same thing and actually what we can do is where did we do that um we're going to take the same thing so we're going to say missing tables because we can and now because all we have to do is we have to just reverse the two so where do we do that let's see there's missing tables those are the tables that are not in the source and then these are the tables that are not in source tables there we go so we can clean that up a little bit shrink it down simplify a little bit now what we're going to do is we're going to do a we're going to do what's going to call create missing table and what we're going to do is we're going to give it a uh let's see oops this is going to be missing so we're going to always do it in the target let's do that well yeah let's think about that a second so let's go ahead and do it so we can do it either so we're going to do four source which is going to be uh dbc um in this case oh no it's going to be actually in db2 so he's going to be db2 and we're going to give it the tables and then we're going to come up here well actually let's do it here because we're going to do it not what we're gonna do and then we'll move it in a minute so we're gonna do create missing tables and we're going to give it a controller and a list uh we'll call it table list and so we have a dbc there we go so let's take this whole thing uh right there and let's oh we're gonna move this up and then we're gonna talk about this a little bit okay so what we're doing here so now what we're going to do is we're going to look at what uh okay let's go right up here so this is basically what a create looks like which we've seen this before it's not terribly formatted but essentially we want to be able to create to make this uh sql query based on what we get back if we look down here we're getting in our rows that we get back when we do our described table we get a column name a type whether it is nullable or not if it's got a whether it's primary key or not there is a default value and then there's uh whether it's auto increment or not so first thing we want to do when we create the first thing we're going to do is we're always going to have create table and then we're going to give it the table name so as we're spinning through our list we're going to uh let me do it this way let's sort of look at it as we're going through it so if we're going to come through here create a missing table print the table uh let's see why do i need describe query i need it oh because i'm going to do it for each column so let's do it here um let's take that query dot dot there okay so i'm going to do this for my table name and then the first thing i want to do is i'm going to create table whatever so let's just look at that right now and we'll see that we are creating that so if we run oops i've got a key equals oops um [Music] p k what is p key o is equal to that oh i forgot this let's see if that's gonna work there we go okay so these tables are not sourced these table are not in the target so let's see what did i get wrong here so source and target oh i didn't flip those two i bet uh here we go they're not in the source so that's missing equals that new tables equals that so he's not doing that one uh oh i need to send it wait is that right okay so i do source tables and i get target tip let's do look at that so i missed something here so there's my source tables here's my target tables and if i look so there's source and then target is empty so it should be source tables missing tables okay sources oh that's why let's do it this way source and target uh source and target there we go so now okay so what uh tutorial a doesn't exist okay so interesting i didn't want oh because this is my list let's try that uh let's see so there's address okay tutorial that address doesn't exist oh because i need to do it from the source so here i need uh so i need to get the source one which is dbc yeah that's a whoops so here let's see dbc2 dbc1 so what i would need to do here is i'm going to need two cursors probably let's do cursor let's do it this way cursor source and cursor destination and dbc1 is my source dbc2 is my destination and now what i need to do is so i'm going to go in and i'm going to describe so i so i come into each table i get my name and then i'm going to do describe so i can get all of the details about that um and so he's going to come in and he's going to do a describe on the source and so if i do let's do print let's do the results and now let's look at it let's see if we can get it there we go so what's going to come through as it comes through on each table and if we jump up here to address there's address audit there's address so it comes in and it's going to print out what is the um wait am i there yeah okay i am so if i go here so so we have table address and you can see here oops it's the one above here um i do that i do the results here okay right yeah so here's the address and we can see each row we don't see it super well formatted here but then we can see here's where i'm starting to create my sql so i can see create table address which now i've got this piece done so next thing i want to do is i want to come in and start using some of this information so now what i want to do is come in with each of these let's move my quit oh i didn't have my quick before so i'm going to come through and for each column oh and i do a print column in there for each column i printed the column information which you can see here let's do this um now for each one this time i'm going to come through and i have a couple things to do is first i'm going to create a column um [Music] oh i didn't actually grab the name out of that did i so i'm going to add a column right here so interesting i said oh here we go nope oh here it is okay so i come in and i want to see if there is uh and we can see here if it's primary key so if it is i'm going to say that this is this name i'm going to get the name for this primary key because it's going to be this column i'm going to use that to create my primary key and we'll look at that a little bit next time around but now if column two is yes this is whether or not is it can it be null or not so i'm going to have to either create this with my column name space [Music] column one which is the type and then null or i'm going to do it with not null so that's going to create this thing column name type and either null or not null and then i'm going to come in and i'm going to say if i have that auto increment because otherwise it's empty if it's auto increment then i'm going to make it an auto increment right now i'm not going to mess with my defaults so if i come through here oh and that's going to wrap it up sort of and it's going to say what's my primary key which gives me this right here i'm not going to worry about foreign keys right now so let's do that so if i print whoop let's print let's move that print down and then do a quit and we will see that now i have this create table which if i go look at something over here um i think i can do a format format sql there we go so now i'm creating i've come out of this and i can do create table address column type now the problem is i've got this auto increment coming up every time so i'm not resetting that so here um i want to do it here i want to move that pk in oh i'm sorry i need to do pk here oops uh interesting i want it to be if he's that then primary key equals that what am i doing let's see oh i did auto increment my mistake um oh here it is i did that twice i don't need it to be auto increment there and if i look uh let's see auto increment should be uh where did i put that there did i put a column a comma after i do not so if we look i probably don't have oh i do have comments so i had a comment somewhere in here come on come on come on come on oh that's what it is sophie's auto increment let's do this let's just do this so if he has automatic command otherwise he's just a comma and so now if i take that and look at it and do my little create table well let's do it this way because that's going to be pain so if i take that and then format it then we can see now i've got create table address i've got a column i've got an increment i've got either null or not null and i come all the way down and so if i wanted to create this in local database testing i believe oh and it's going to ask me to connect and so let's see if i run this real quick oh it says table address already exists um oh i think because tutorial it does not let's go look here okay so he does an end tutorial so now if i put it in tutorial instead i'm going to see that this table is going to exist integer display oh so i don't really need that but so it created the table let's do a refresh and now i can see here's my table here's my original table so now when i run this a second time through address will not be in there because it's not going to find it missing and so it moves on to address audit so the next thing we're going to do is we're going to want to actually execute this sql that sounds like a good time thing for us to do next time around so i'll let you get back to it 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 this uh basically it's a
database sync
walkthrough learning about sql doing a
little bit of a program along the way
so last time around
we were working on um
missing tables so comparing two
databases
and finding the tables that we're
missing
now what i want to do before we go a
little further into this one is i'm
going to actually
clean this up a little bit so i've got
my connect to database one and two
i've got those little functions
and what i'm gonna do is i'm gonna do
let's see so we had three things we're
doing
we're gonna do
let's see i don't need oh i do need that
well let's take this let's get
this whole thing
and this is gonna be
let's go ahead and do it like this is
we're going to do these uh so we've got
let's do source tables uh let's see
missing source
oops if i can type that right we do
missing source tables
and we're going to do
missing target tables
so we're going to clean this up a little
bit this will be a little easier to see
so missing source tables is going to be
this one
uh let's see
and let's do it this way so we're gonna
send it
uh source and target
and we're going to come in here and
let's see so we're going to get
let's see source tables and target
tables and so that's just going to be
here
so we're going to do new
tables
equals missing
source tables and then this is going to
be
source tables and target tables
whoops
i'm gonna do this mostly for readability
so i'm gonna come in here
and that's gonna be that guy
and let's do it this way
so i get that new tables
okay which equals that
and then here
let's do it this way new tables equals
that and then we're going to
return new tables
whoops
let's say
we're gonna call it missing tables
uh gonna go here
and then we're gonna come here
uh missing two there we go so we're
gonna do that
we're gonna basically do the same thing
and actually what we can do
is where did we do that um
we're going to take the same thing so
we're going to say missing tables
because we can
and now
because all we have to do is we have to
just reverse the two so
where do we do that let's see there's
missing tables
those are the tables that are not in the
source
and then these are the tables that are
not in
source tables there we go so we can
clean that up a little bit shrink it
down simplify a little bit
now
what we're going to do is we're going to
do a we're going to do what's going to
call create
missing table
and what we're going to do is we're
going to give it a
uh let's see
oops this is going to be missing
so we're going to always do it in the
target let's do that well
yeah
let's think about that a second so let's
go ahead and do it so we can do it
either so we're going to do four
source which is going to be
uh dbc
um
in this case
oh no it's going to be actually in db2
so he's going to be db2
and we're going to give it the tables
and then we're going to come up here
well actually let's do it here because
we're going to do it
not what we're gonna do and then we'll
move it in a minute so we're gonna do
create missing tables
and we're going to give it
a controller
and a list uh we'll call it table list
and
so we have a dbc there we go
so let's take this whole
thing uh
right there
and let's oh we're gonna move this up
and then we're gonna talk about this a
little bit
okay so what we're doing here so now
what we're going to do is we're going to
look at
what
uh
okay let's go right up here so this is
basically what a create looks like which
we've seen this before it's not terribly
formatted but
essentially we want to be able to create
to
make this
uh sql query based on what we get back
if we look down here we're getting in
our rows that we get back when we do our
described table
we get a column name
a type
whether it is nullable or not
if it's got a whether it's primary key
or not
there is a default value and then
there's uh whether it's auto increment
or not
so
first thing we want to do
when we create the first thing we're
going to do is we're always going to
have create table
and then we're going to give it the
table name so as we're spinning through
our list
we're going to
uh let me do it this way
let's sort of look at it as we're going
through it so if we're going to come
through here create a missing table
print the table
uh let's see
why do i need describe query i need it
oh because i'm going to do it for
each column
so let's do it here um
let's take that query
dot
dot
there
okay
so i'm going to do this for my table
name and then the first thing i want to
do is i'm going to create table whatever
so let's just look at that right now
and we'll see that we are creating that
so if we run
oops i've got a key equals oops
um
[Music]
p k what is p key o is equal to that
oh i forgot this
let's see if that's gonna work there we
go okay
so
these tables are not sourced these table
are not in the target so let's see what
did i get wrong here so
source and target
oh i didn't flip those two i bet
uh
here we go they're not in the source
so that's missing equals that new tables
equals that so he's not doing that one
uh oh
i need to send it
wait is that right okay so i do source
tables
and i get target tip let's do look at
that
so
i missed something here so there's my
source tables here's my target tables
and if i look
so there's source
and then target is empty so it should be
source tables
missing tables okay sources oh
that's why
let's do it this way source and target
uh source
and target
there we go so now
okay so what uh tutorial a doesn't exist
okay
so
interesting
i didn't want oh because this is my list
let's try that
uh
let's see so there's address
okay tutorial that address doesn't exist
oh because i need to do it from the
source so here i need
uh so i need to get the source one which
is
dbc
yeah
that's a whoops so here
let's see dbc2
dbc1
so what i would need to do here is i'm
going to need two cursors probably
let's do cursor
let's do it this way
cursor source
and cursor destination
and dbc1 is my source dbc2 is my
destination
and now what i need to do is so i'm
going to go in and i'm going to describe
so i so i come into each table i get my
name
and then i'm going to do describe so i
can get all of the details about that
um and so he's going to come in and he's
going to do a
describe on the source
and so if i do let's do print
let's do the results
and now let's look at it let's see
if we can get it there we go so what's
going to come through
as it comes through on each table
and if we jump up here to address
there's address audit there's address so
it comes in and it's going to print out
what is the
um
wait am i there yeah okay i am so if i
go here
so
so we have table address and you can see
here oops it's the one above here um
i do that i do the results
here okay
right yeah
so here's the address and we can see
each row we don't see it super well
formatted here but then we can see
here's where i'm starting to create my
sql so i can see create table address
which now i've got this piece
done
so next thing i want to do
is i want to come in and start using
some of this information so now what i
want to do is come in with each of these
let's move my quit
oh i didn't have my quick before so i'm
going to come through and for each
column
oh and i do a print column in there for
each column i printed the column
information which you can see here
let's do this um
now for each one this time i'm going to
come through
and i have a couple things to do is
first i'm going to create a column
um
[Music]
oh i didn't actually grab the name out
of that did i
so i'm going to add a column
right here so interesting i said oh here
we go
nope
oh here it is okay
so i come in and i want to see if there
is
uh and we can see here if it's primary
key so if it is i'm going to say that
this is
this name i'm going to get the name for
this
primary key
because it's going to be this column i'm
going to use that to create my primary
key
and we'll look at that a little bit next
time around but
now if column two is yes this is whether
or not is it can it be null or not
so i'm going to have to
either
create this with my column name space
[Music]
column one which is the type
and then null or i'm going to do it with
not null so that's going to create
this thing column name type and either
null or not null and then i'm going to
come in and i'm going to say if i have
that auto increment because otherwise
it's empty if it's auto increment then
i'm going to make it an auto increment
right now i'm not going to mess with my
defaults
so if i come through here oh and that's
going to wrap it up sort of and it's
going to say
what's my primary key which gives me
this right here i'm not going to worry
about foreign keys right now
so
let's do that so if i print whoop let's
print let's move that print down
and then do a quit
and we will see
that now
i have this
create table
which if i go look at
something over here
um i think i can do a format
format sql
there we go
so now
i'm creating i've come out of this and i
can do create table address
column type
now the problem is i've got this auto
increment coming up every time so i'm
not resetting that so here
um
i want to do it here i want to move that
pk in
oh i'm sorry
i need to do pk here
oops
uh
interesting i want it to be
if he's that then primary key equals
that what am i doing let's see
oh i did auto increment my mistake um
oh here it is i did that twice i don't
need it to be auto increment there
and if i look
uh let's see auto increment should be
uh
where did i put that there
did i put a column a comma after i do
not
so
if we look i probably don't have oh i do
have comments
so i had a comment somewhere in here
come on come on come on come on oh
that's what it is
sophie's auto increment
let's do this
let's just do this so if he has
automatic command otherwise he's just a
comma
and so now if i take that and look at it
and do my little create table
well
let's do it this way because that's
going to be pain
so if i take that
and then format it
then we can see now i've got create
table address i've got a column i've got
an increment i've got either null or not
null and i come all the way down and so
if i wanted to create this in local
database
testing i believe
oh
and it's going to ask me to connect and
so let's see if i run this real quick
oh it says table address already
exists um
oh i think because tutorial it does not
let's go look here
okay so he does an end tutorial so now
if i put it in tutorial instead
i'm going to see that this table is
going to exist
integer display oh so i don't really
need that but so it created the table
let's do a refresh
and now i can see here's my table
here's my original table
so now when i run this a second time
through
address will not be in there because
it's not going to find it missing
and so it moves on to
address audit so the next thing we're
going to do is we're going to want to
actually execute this sql
that sounds like a good time thing for
us to do next time around so
i'll let you get back to it
as always go out there and have yourself
a great day a great week and we will
talk to you
next time
you