Alright everybody this is Mr. Fugu with Mr. Fugu Data Science. I would
like to say that all this information will be provided on my Github. Which is
right here Mr. Fugu Data Science. it'll also be in
the description for a link in the video. We're doing a continuation of the last
video where we generated dummy or fake data using R, in the Jupyter notebook.
Here is a brief piece of the last few entries of that file. Mind you
this is dummy data. It's not real, there's no security or privacy concerns. Okay, we
have credit card, an email, first, last name, and primary phone number. If you
don't remember how we did this. There will be a link to the other video as
well. Okay, I need you to open up your terminal and enter "mysql". I'm using a
Mac with an installation via "homebrew" so for me I open up by "mysql" dot
Server and then start, (mysql.server start). and then before we get started if you've never created an
alternate user profile. You're gonna need to do these other steps so you first log
in to your "mysql root" , from there you will need to create a new database
with whatever database name you have and always end with a semicolon. Now, you're
creating a user with whatever username inside of single quotations at "@' single
quotation localhost (username@''localhost'). and then you're identified by some password you create
now you're doing a separate call so you say you're granting all privileges on
whatever database you just created dot star to your username at localhost and
you need to 'flush all privileges'. Now, feel free to pause the video.
and spend a minute or two logging in and setting this up if necessary and I can
wait. Alright, so I'm assuming that you've logged in
you either created or have an existing user other than 'root'. Before, we get
started I need to note that if you get an error while loading data from R to
'mysql' you may have this it'll say a: ' loading local data error'. you're like
what the heck is this junk? So you say all right you go on stackoverflow and
you find something and it'll say: all right login as your root account. after
you log in you say 'show global variables like' and then you're looking for the
'local in file' and you'll get a printout in the terminal it looks like this and
it'll be 'off'. So then now you have to 'turn it on' and you say all right' set
global local end file equal to on' and that'll fix it .Now, I'm not sure if this
is going to fix it only temporarily while you're making the connection call
or if it's permanent. And if it's not permanent you are gonna have to change I
think the 'config file' there is a link for a stack overflow that gives like a
semi a brief explanation. Now, I may need to look into that. So for the table that
we're creating. I decided to call this 'user info' for this first example we're
going to also have another example. This is loading what we are creating in the
'first example is a data frame' we're loading data directly to my sequel the
'second one is a CSV file' that we dumped from R that we will load into mysql.
That's the distinction here. So what we're gonna do is go to my terminal
and I already have this set up. But, let's do it as if you're doing it. So looking
at all the tables I have. We're gonna, I need to drop this for mine.
So we could be on the same page. No table. Alright now after we drop that table.
We're gonna say all right let's create this table. Because, this is
a step where you basically should be since you don't have a table like this.
So now we got it, now to see what's inside of it. Alright here's what we have
inside. We have a primary key initiated by an ID. that auto-increments we have
a primary phone number which are 'ints' first and last names 'varchar' or 'varchar' for
last name if you had like hyphens and stuff like that first name and then
email and credit card. Okay, so you're going to need to setup a driver let
looks for MySQL. We're gonna set up a connection where we're doing a DB
connect and then your 'mysql' you're setting up whatever your database is
that you created. You're setting up your localhost and then your user information
for your login and your password here.
Okay, then we could look at the info and say okay this is my user I have this
is the database I'm in, just a version of mysql. Whatever this stuff is. Okay,
when you're writing your table from R to mysql you can do an 'overwrite
equals true,' where append will be false and this is creating a database no
matter. What whereas if you're doing the converse: it would be 'overwriting as
false' and you're just appending only. Okay, so what we're doing as you're
saying your connection your 'conn' which is equal to the connection you set up above.
We're going to 'override equals true append equals false'
and our data frame is called 'users' that we're using and we're going to send it
to our info. Good, now we have a valid connection and this is how many
connections we have to mysql. This starts incrementing, if I'm correct. So
then basically there's a few things you could do a: 'send query' which basically
gets the query. But, it doesn't procure any information it just does a pure
query call. You can do a 'fetch' which actually takes the data from mysql
imports it into R. You could store it and actually manipulate and use that
data. But, at the end of that call you need to do a 'DB clear result' otherwise
you end up getting an error. You also have a 'get query' which by default calls
your send query followed by a fetch. Allowing you to be free from the call of
the DB clear. I provided a link to the description through the are from in R
document. We're gonna do let's just look at some simple queries one proof of
concept. Two, see what we could pick up so I did a 'select all from the user',
'user info' this one worked because I already have an established connection
to that as well. We have the first three people with the first name of Samuel.
Okay that's great.
So it shows that we can actually do some
queries which is perfect. This lists what fields there are so we have credit card,
email, first name, last name, primary phone and then your your row numbers. Let's see
using the 'DB fetch' what happens. We get a true, oh okay let's find our data. So
basically, what I was doing I was those using the above query sending it to
fetch data so we could actually store it here. You also have options such as
'extracting chunks' and 'extracting all' the remaining data. and then we can do a
disconnect when we're done with everything. But, I'm gonna leave it open
real quick so we can now set up a dot CSV let's inside of R that we created
and dump it to MySQL. So what I had to do to figure this out since I kind of find
very much documentation. I basically hard-coded and figured out what the path
was there would be an easier way using like an OS import from Python, but you
can do From a package. But, for simplicity we'll just paste this and dump the file
,use the connection ,override true , row names false, name of where we're going to
send it and then append equals false.
Perfect. Now, let's do a query I say okay perfect
this looks like it's working we have proof of concept we have query. Let's also
add now an append to this file which is the table of 'user O ' that we just created
and lets add a primary key.
Alright ,so that's see. Then now we print it out and now we have an ID which is the
primary key. So then we go over here and we say if we notice we didn't have that
new table. So that's look at show tables.
So here's a new table that we just added. Let's do 'describe' that table. Now ,it
shows everything that we just added from inside of R and shows that we just
provided an update of a primary key. Now, everybody I want to say "Thank You" for
watching this video. The next video I will do is "Postgres from R" and in
subsequent videos I will go from "Python to MySQL" and possibly "Mongo or Postgres"
But, I appreciate you watching this video: :Please LIKE: and
"SUBscribe" and as always have a good one Thank You