Detailed Notes
Focus for this episode: Deeper details for creating columns and execute our table creation needs.
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] okay i had a bit of a challenge with the latest recording so we're going to run back through this a little bit and apologies as we build this out so last time we were last episode we were looking at building out some create table sql and we had a couple of issues or a couple of values we were looking at which were basically the column name uh let me do this let me run this real quick um and let's do this so what we were doing was we were going through and uh actually i'm just going to quit after that so we can see one uh whoops let's run it somewhere in here there we go i mean okay um we're looking at the description for tables here and we can see with each row we've got a column name a type whether it is no or not and then we've got uh this was [Music] let's say no or not and then we had whether or not i believe it's a primary key or not and then default values and then some additional stuff now what we were looking at last time as we were looking at the um the function which was uh which we used in some of these which would be the auto increment so if we looked here i think address was one if we look here uh yeah we did the auto increment and so we'd see that but now one of the other things we'll see because of what that is is we can see that for example uh let's see if i can find a column that does it oh address audit will have it sometimes we have a default and sometimes i'm going to have some extra which extra would be in this case extras the auto increment so what we want to do is be able to take care of default values and we also want to be able to deal with the extras at this point so for default for the extras instead of so this is our primary key let's do this i'm sorry this is let's say allow us null let's do that just for comment this a little bit this one is going to be our extras and it's just going to be whatever it is however let's see this is going to be primary key if it exists which it may not so if we have a primary key if one of those columns is primary key then great which is uh up here if it's a primary then it's going to go ahead and create that otherwise we're just going to close out our command this is our sql command and here we go here's our defaults let's do and the little default values if they exist now this one's a little tricky because we have two different types of default values we could have a default value that is either auto increment or as it ends here whoops uh address i think's got it hello um it's the current current timestamp which if you look at those here it's actually a function you can see that it's actually a function being called but if you were to look at one of the other ones so let's just not quit but let's go through and show all of those uh let's see don't have any extra prints nope that should work so if i do this then we're gonna see some for example i believe uh where did that go [Music] we had one that was a default value non-assigned let me go look for that real quick did i have that over here i did not let's go to grab this let's do it like that and let's just do this real quick um i think it was this there we go so here this one username has a default value of not assigned now if we just bring them over then in this case it's not going to it's going to give us something that's not correct so i'm going to show let me go ahead and do the create sql real quick and let me get rid of some of those pieces so it's a little easier to read and so we run into what was that end users let's see dates dates app user there we go so let's actually look at both of these so if we look at all dates and let's just go ahead and format these now if we look at all dates 2 let's go ahead then here uh oh we didn't have anything super special here uh oh yeah we do i'm sorry is we're to come in we can do create not null default is going to be this current timestamp so here we want it to be the function we don't want it to be a string however down here on um app user the default is a string we don't want to call it so we're going to say if it's a function which we've seen a little bit uh if it's a function then which basically we're just going to say hey if we've got a parentheses in there it's a function and so in that case we're just going to take the value out of the column otherwise if you can see here we're going to wrap it in single quotes now if we do all of that then we're gonna get all of our little uh executes our sql statements let's go ahead and see what we've got here um so we're gonna print it out and then we're gonna execute it so this time we're gonna try to actually create tables and we're going to end up having an error which is on all dates and what you're going to see here is another case we're going to have to deal with um let's pretty print it and if you see here we come in and we do create dates and let's clean this up a little bit so it's easier to read now if we come through here let's see what it's doing it does type date uh date time not all default okay so let's see what he does let's see what he complains about uh oh all dates already exists oh so we already have that probably so if we go to uh tutorial tables uh let's go ahead and delete all of those tables and now let's run it okay so we are going to have an issue with address and here what we're going to see is this little integer display width is deprecated will be moved in a future release and that problem is that we have that integer there so let's try to fix that one okay first let's get rid of our tables uh here and so it was just that address but see now it came back and it actually gave us an error so now what we could do is we could come back up here and do the raison warnings and say false and let it just go ahead and create it but we don't want to right now we're going to go ahead and bail out so into doing we can drop this table and what we're going to do here is we're going to come into the types where is our types here we go which is going to be column 1. ah that's the allows no let's see and it's gonna be a fix for data types int and we're going to do we're going to call type equals column one so now we're going to change this over because we're going to use kyle types instead because we can't directly check it it's the way it tends to work so okay so we sign it out and now we're going to do we're going to say if if call type dot starts with i think that's how it works uh if it starts with int then call type equals call type dot split and we're going to split it on that first parenthesis and we're just going to take the part to the left of the parentheses so now let's see what we've got here and we're going to see uh so he's still not doing that he's not picking that up right so let's go look at uh here oh and let's drop that table now let's go look at this so uh that is column type so let's go look here let's quit after this just to be safe and we're going to print call type and let's see what that looks like so if we look here call type is ant oh it's gonna be lowercase let's do this let's do i believe it is to upper we're gonna find out in a second okay so it's not too upper let's try upper there we go okay so now if we look here let's just print it again make sure it works there we go so now we've got our ant so let's get rid of those and let's see if it runs and we run into one now with create table all dates so year four is running into the same thing so we want to take uh here let's do that we're just gonna do an ore so if it's that or it starts with year whoops all upper starts with year uh it doesn't like an ore so i think it's this ore there we go that's a that's it my mistake so let's drop all those tables again [Music] just because and we're going to drop these tables and now if we come through looks like it all ran so now we're gonna see uh let's see what am i printing i'm printing out some extra crap i don't need so creating the table oh and i did my sql beforehand so which i'm going to leave that because it makes sense to do so i'm going to cut a little line out here and i don't need this stuff so i don't really need to worry about these tables anymore um i'm going to comment them out though and so now if i run it again it'll probably do nothing there we go connect to source connect to the target and now if i look i'm going to see in testing tables i'm going to see all my tables that are also in my tutorial well i've got to redo that that are here so i've got one two three let's see so i've got my 12 tables there and i should have here i should have 12 tables here so we have our tables now that we can create tables from one database to another and i think that's a good stopping point because now we're going to take it to the next level we're going to start playing around with the columns and that'll be where we pick up next time 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]
okay i had a bit of a
challenge with the latest recording so
we're going to run back through this a
little bit
and apologies as we build this out so
last time we were last episode we were
looking at
building out some create table sql
and we had a couple of issues or a
couple of values we were looking at
which were basically
the column name uh let me
do this let me run this real quick
um
and
let's do
this
so what we were doing was we were going
through and uh actually i'm just going
to quit after that
so we can see one
uh whoops
let's run it somewhere in here there we
go i mean okay
um we're looking at the description for
tables
here and we can see with each row we've
got a
column name a type
whether it is no or not
and then we've got
uh
this was
[Music]
let's say no or not
and then we had
whether or not i believe it's a primary
key or not
and then
default values and then some additional
stuff now what we were looking at last
time as we were looking at the
um
the function
which was
uh which we used in some of these which
would be the auto increment so if we
looked here
i think address was one
if we look here
uh yeah we did the auto increment
and so we'd see that but now one of the
other things we'll see because of what
that is is we can see that for example
uh
let's see if i can find a column that
does it oh address audit will have it
sometimes we have a default and
sometimes i'm going to have some extra
which extra would be in this case
extras the auto increment
so what we want to do is be able to take
care of default values and we also want
to be able to deal with the extras at
this point
so for default
for the extras instead of
so this is our primary key
let's do this
i'm sorry this is
let's say
allow us null
let's do that just for comment this a
little bit
this one is going to be our extras
and it's just going to be whatever it is
however
let's see this is going to be primary
key
if it exists
which it may not so if we have a primary
key if one of those columns is primary
key
then great which is uh up here if it's a
primary then it's going to go ahead and
create that
otherwise we're just going to close out
our
command this is our sql command
and
here we go here's our defaults
let's do
and the little default values
if they exist now this one's a little
tricky
because we have two different types of
default values
we could have a default value that is
either auto increment or
as it ends here whoops
uh address i think's got it
hello
um
it's the current current timestamp which
if you look at those
here
it's actually a function you can see
that it's actually a function being
called but if you were to look at one of
the other ones so let's just not quit
but let's go through and show all of
those
uh let's see don't have any extra prints
nope that should work so if i do this
then we're gonna see some for example
i believe
uh
where did that go
[Music]
we had one
that was a default value non-assigned
let me go look for that real quick
did i have that over here
i did not
let's go to grab this
let's do it like that
and let's just do this real quick um
i think it was this there we go so here
this one username has a default value of
not assigned
now if we just bring them over
then in this case it's not going to it's
going to give us
something that's not correct so i'm
going to show let me go ahead and do the
create sql real quick
and let me get rid
of some of those pieces so it's a little
easier to read
and so we run into
what was that end users
let's see dates dates app user there we
go
so let's actually look at both of these
so if we look at all dates
and let's just go ahead and format these
now if we look at all dates 2
let's go ahead
then here
uh
oh we didn't have anything super special
here uh oh yeah we do i'm sorry is we're
to come in we can do create not null
default is going to be this current
timestamp so here we want it to be
the function we don't want it to be a
string however down here on
um app user
the default is a string
we don't want to call it so we're going
to say if it's a function which we've
seen a little bit
uh if it's a function
then which basically we're just going to
say hey if we've got a parentheses in
there it's a function and so in that
case we're just going to take the value
out of the column otherwise if you can
see here we're going to wrap it in
single quotes
now if we do all of that
then we're gonna get all of our little
uh
executes
our sql statements let's go ahead and
see what we've got here
um so we're gonna print it out and then
we're gonna execute it so this time
we're gonna try to actually create
tables
and we're going to end up having an
error
which is on all dates
and what you're going to see here
is another case we're going to have to
deal with um
let's
pretty print it
and if you see here we come in and we do
create dates
and
let's clean this up a little bit so it's
easier to read
now if we come through here let's see
what it's doing it does type date uh
date time
not all default okay so let's see what
he does
let's see what he complains about
uh oh all dates already exists
oh so we already have that probably so
if we go to
uh tutorial
tables
uh let's go ahead and delete all of
those tables
and now let's run it
okay so we are going to have an issue
with address
and here what we're going to see is this
little integer display width is
deprecated will be moved in a future
release and that problem is that we have
that integer there so
let's try to fix that one okay first
let's get rid of our tables
uh
here
and so it was just that address but see
now it came back and it actually gave us
an error so now what we could do
is we could come back up here
and do the raison warnings and say false
and let it just go ahead and create it
but we don't want to right now we're
going to go ahead and bail out so
into doing
we can drop this table
and what we're going to do here is we're
going to come into the types
where is our types
here we go
which is going to be column 1.
ah that's the allows no
let's see
and it's gonna be a fix
for
data
types int
and we're going to do
we're going to call type
equals
column one
so now we're going to change this over
because we're going to use kyle types
instead because we can't directly check
it
it's the way it tends to work so okay so
we sign it out and now we're going to do
we're going to say if
if
call type dot starts with
i think that's how it works uh if it
starts with int
then
call type equals
call type
dot split and we're going to split it
on that first parenthesis
and we're just going to take the part to
the left of the parentheses
so now let's see what we've got here
and we're going to see
uh so he's still not doing that he's not
picking that up right
so
let's go look at
uh
here oh
and let's drop that table
now let's go look at this so
uh that is column type
so let's go look here let's quit
after this just to be safe
and we're going to print call type
and let's see what that looks like
so if we look here call type is ant oh
it's gonna be lowercase
let's do this let's do
i believe it is to
upper
we're gonna find out in a second
okay so it's not too upper let's try
upper
there we go
okay so
now if we look here let's just print it
again make sure it works there we go so
now we've got our ant
so let's get rid of those
and let's see if it runs
and we run into one now with create
table all dates
so year four
is running into the same thing
so we want to take
uh here
let's do that we're just gonna do an ore
so if it's that
or
it starts with
year whoops all upper starts with year
uh it doesn't like an ore
so i think it's this ore
there we go
that's a that's it my mistake
so let's drop all those tables again
[Music]
just because
and we're going to drop these tables
and now
if we come through
looks like it all ran so now
we're gonna see
uh let's see what am i printing i'm
printing out some extra crap i don't
need so creating the table oh and i did
my sql beforehand
so
which i'm going to leave that because
it makes sense to do so i'm going to cut
a little line out here
and i don't need this stuff
so i don't really need to worry about
these tables anymore um i'm going to
comment them out though
and so now if i run it again
it'll probably do nothing there we go
connect to source connect to the target
and now if i look
i'm going to see
in testing tables i'm going to see all
my tables that are also in my tutorial
well i've got to redo that
that are here
so i've got
one two three
let's see
so i've got my 12 tables there and i
should have
here
i should have 12 tables here
so we have our tables now that we can
create tables from one database to
another
and i think that's a good stopping point
because now we're going to take it to
the next level we're going to start
playing around with the columns
and that'll be where we pick up next
time
so go out there and have yourself a
great day a great week and we will talk
to you
next time
[Music]
you