Welcome everybody, you're watching mr fugu data science.
We're doing some pattern matching today with postgres and python featuring
psycopg2. I figured this video would be very useful for somebody starting out or
just trying to get some basics or remember something you may have forgot.
Because, pattern matching is very important as well as trying to figure
out how to manipulate functions within psycopg2 that emulate
command line utility; which is not straightforward.
You need to throw in pandas, psycopg2 from psycopg2 import sql as well as
if you have your user credential file, if you don't
feel free to just try this out right here. Also
thank you to the recent viewers and subscribers i greatly appreciate it. And
i apologize for the delay in the video
recently i've been very busy. It's been very hectic. Now, i'm using my
config file that's storing my function with my key
value pairs. That i'm using to establish my connection and further i create a
cursor allowing me the ability to send and
retrieve data. As well as read and write files. Please, understand
that i'm using auto commit equals true. So i could have all this happen
immediately. But, do understand the downfalls of this and read the
official documentation to get an idea. From our last video we had two specific
files allowing us to work with some car
dealer data. That we split so we could basically run
a primary key so we could join the two tables. But
we're not doing that today. We're gonna feature these so we could run some
queries, do some pattern matching and try to establish
changing some data types. Because, that's something that will come up i guarantee
you. For a future endeavor i decided
something because, i'm really tired of hand plucking these
tables that you create that are hard coded. i'm going to show you what's going
on. Let me show you something real quick.
This is what i'm going to work on in the future. This kind of dragged me for a
little bit of a loop. But, here's the id, or the index the vin
number ,and some of these columns from our data frame. And i converted them into
these data types that are usable for postgresql.
But, i'm trying to figure out a way that i could make a dynamic
table. Where we can throw these in all at once
instead of writing this annoying, stupid thing. Which is useful for writing a
couple individual columns but it doesn't work
if i have a hundred, two hundred or thousand columns. It's useless. Think
about that in the future if somebody has some great ideas or some links
send those my way. Now i created these two tables so we could check our data
and i just subset these real small so we could just get an idea of what's going
on. And i've used this in previous videos.
But, if this is the first time watching this: what i'm doing is i'm dropping this
if it already exists. iIm creating an unlogged table.
Here's the three columns with their data types, for their schema. Then i'm
sending that off. And just think of this with statement; like a with open if
you're throwing open a file. After it's done you don't have to worry
about allocating resources or closing this
out unless you're closing your connection. Now i did the same thing for
the second table which is fine and dandy. It's just a little bit
different in data. And what i wanted to look at first is, if you were looking at
postgresql. All you have to do if you're looking at posters this is all you have
to do to describe a table, alright. And we can see what the
data types are. But, you just can't do that
when you're using psycopg2. You don't have the same utility, that you do in a
terminal. So you have to do a little funky manipulation. For instance
you have to select table name, the column name and then it's
calling in your data types from the schema dot column. Then right here what's
going on with these brackets, is i'm using
the sql portion from psycopg2. I don't know if i imported that in
earlier but you'd have to do something like this: from
and you'd have to just call in sql. All right, that's what's going on here
and this is allowing me to do some specific formatting where i'm able to
call these in as parameters. I'm doing a formatting down here. We
went over this in the previous video. But if you didn't see that, you had
two distinctions one is a literal form the other is an identifier. Identifiers
deal with columns and tables. Literals do which direct string values.
So what i did was, i found this on stack overflow I think it was. But you're just
calling in your table and it's basically printing. out from this template form
right here which is non-specific information. But it's allowing you to get
a printout of your specific table and each column
and their specific data type. Which is very useful if you need to do any kind
of manipulations. For instance if you had to change
one of these data types. This would be something you like to use instead of
having to go back and forth between the terminal. But, that's just an option all
right. Now, i decided to subset our data since
we're going to be sending it off and calling it as dot value. Since we're
going to take our data frame and we're going to send
this off to postgres. I did this in my first video. But, what's
going on here? Making sure at least you have something for your data frame.
And this data frame will be as a list. You have the three parameters or
arguments here that will be your data frame,
the table you want to send it to, and then the cursor that's going to send
this off all right. And you're just turning everything into a string join
from your list that you just created above. And we're doing some formatting
where we're taking our values as our input right here through our
little curly braces. And using our placeholder, all right.
This is something you should be aware of and
leery of for security purposes. Because things like this would be more
suitable where you have whatever your name is and then you throw in
the s. That's one designation or you can just do
like this. Because, these parameters are known natively through
postgres. Whereas you can have some issues if these were input statements or
something like that on a website all right. This is fine if we're just
using our code. But, if this is some kind of utility for a website or for business
applications just be leery if it's from an outside
person having access. Now, we see we threw this into our input statement with
our three little parameters. Truncate is used so you don't have
issues with duplicating your data. We're using our connection and we're
sending this as a batch. We're sending everything at once all
right. Now let's scoot over. There. I'm using this select statement
real quick so we can see what's going on. Notice that
we have these three goofy looking things. This is what my vin number is, this is
the index value, and this is the corresponding model list
for this particular vehicle. You can manipulate this later if you wanted to.
Since it's an array. The other file which is our second part of
the car dealership that we subset will look like this we're taking just the
date of purchase, the vin number, what kind of vehicle it's, make and it's year.
And let's consider changing the data type
for our vin number which is a varchar and let's converted into
an integer. You need to do the alter table call in my table name
and then the column that i'm altering. And then i'm changing the type to an
integer and then i'm kind of forcing that
you know using this notation with the double colons, all right.
and you can say well why am i not using a cast? Well casting i was trying that
but i was having some issues with the column actually
changing. It wasn't changing the column that's why i didn't show that in
this example. But, casting is another option that you have for changing the
type. Now we can see that it became an integer
whereas before it was a var char. If you ran this again
the same little formatting that we had above. So we could basically see our
schema. And something that's really interesting
and pretty useful is what's called mogrify.
If you needed to do some tracing back a few queries like sometimes you
get these errors and you're like what the heck is this error mean? i don't know
what's going on. I need to trace back how postgresql or sql
actually interpreted my query. And so basically
i'm calling in my cursor and i'm calling mogrify; and then whatever sql statement
that i had. Since i ran multi-lines on my sql statements,
it prints out what would be read. I'm selecting some table with some column,
the data type, and then from the schema with my columns.
And then i'm calling in the where clause and throwing it into my
table of interest. And i did the same thing for the one we just
did above. But, this could be pretty useful for instance, if you were trying
to do some troubleshooting and throw this into your code for deployment.
for instance I run my query that i set up and then i set up my
mogrify to read that in. And then i could throw that in my execute. And
now you actually have one: you're trying to verify that the query looks correct.
And two you are throwing it directly in the execute to
have it do whatever you're trying to run. But, now you have like a traceback method
here to help you for debugging. Something
that's pretty useful is fuzzy matching of strings or
things like that. The first thing we're going to look at is an extension
called trigram. And that's allowing you to do
some string matching and it's working on i think it's three consecutive
characters, next to each other. Let's say that we're looking for a
person but, we're not really sure but we think that their name is
like something like Aldo or something like that right. You're like i don't
remember man, i'm busy somebody called for some business or
something i need to look up. Here we go. We create this extension real
quick we could do this outside of it first.
And then call in our select statement. But, i'm running it all together. But if
you run this again, it'll say that this is already established. So you
need to drop it and run this again or take this outside of it when you're
creating it. But anyway you create this extension allowing you to run your
trigrams where i'm looking for the similarity
of this person with the first name of Aldo in descending
order. And it's just limiting five entries, all right. So we have someone
that's Alfredo, Ali, Alec and Alex. Those are the
closest that we have for Aldo here. Now, what about if we had
parameters that we wanted to throw in doing the same exact thing but, letting
you see what it looks like if you're actually inputting your table name, your
column name, and then the name of interest that you
had. We could run this the same with our sql.sql and throw in our formatting and
do our column which is the identifier i mean our
table and our column get identifier and then the actual string name
is just a literal, all right. We can do this
little like confidence level basically of saying all right zero to one:
how likely is it you know these names that we're going to get
are close enough to b o r for the last name? And the default value is 0.03.
The lower the number, the less specific it gets.
All right so you're going to have things that are not very close to this
all right. So the higher i go we have nothing.
But what happens if i do the default value? Oh no we get a bunch of people. But
look at this box, box not very close right. So here's
here's what we got for that. What if we have an alternate
way or wanted to do an alternate way? You can do it like this with the parameters
that we just had. Throwing in our table our column in the
name. So it's not very hard. We could chuck this into a function if we're
interested in using a function as well. But i'm trying to give you as many
options and ideas as possible, to help you out later.
You could also do phonetic spelling. For instance if you had an idea how their
name sounded phonetically, but you didn't have a clue of
how it was actually spelled. You know, you can look that up too.
For instance, this is interesting. This fuzzy string match extension is
only working off of a conversion that it takes of your string
and makes it into four letter codes. And then what it's doing is it's just
matching those four letter codes. For instance flour let you bake with and
flowers let you smell. Those end up having the same code.
Because, phonetically they sound similar. So we were looking for somebody
with donnie a. We didn't know what was going on with
this name we just heard someone pronounce it weird, maybe they're from a
place that they don't enunciate i don't know. And we look for this
name using the fuzzy string match and we're using the where clause where we're
calling in this little library or function i don't know
what it exactly is. But it's called sound ex.
And i'm taking my first name with whatever phonetic identifier
i'm using and chucking that in and seeing what i get thrown out. And these
are the names that closely match whatever i'm looking for phonetically.
I didn't get too crazy here. But you could start spelling all funny if you
wanted to or you weren't sure. But i do need to mention something if
you're using names that aren't uh english language names; you're going to
have issues with matching these up. For instance if you have like special
characters or something like that over any of your letters
that's gonna throw it off and there's an example of this i left in the
citations for the fuzzy string matching that you could look into for
further examples. So scroll down, you also got a tool which is called the
Levenshtein distance. And that's just working on
edit distancing. You're basically measuring
how many deletes inserts and updates you have.
And for instance if we're converting long to length: you'd have to change the o
to an e which is one, you'd have to also
add a t and an h and i think this would add up to
four is what's going on. But you could do comparisons with this to figure it out.
Now i'm calling in my Levenshtein for someone named donnie w again. We're
just messing with that. We're just throwing in the
first five of those in ascending order and we see that we end up getting
Tony, Ronnie, Andre and Don. Okay fine, we can do some string
converting and throw everything into a lower case
and that's shown here. I just doubled it up so you can see the before and the
after. We can do pattern matching using regular
expressions. For instance if i was looking for
somebody that had either the first name of j or g in the
beginning of their name: that's what's going on
here. I'm looking for at least one occurrence of that. So now we have
Justin and Justin and Juan and Joshua and i'm only printing the first five.
Okay we can look at using the like: i shouldn't say
regex here. Because it's using this but you could use like or i like which is
case insensitive. For instance, i'm trying to find the
vin numbers that start with 102 here and i'm just converting it
into a text which was originally a integer after we formatted it above. And
we can do if we scroll oh; and we got two things
when you're using the like or i like. You gotta think about the operators the
percent sign is allowing you to match you zero or one occurrences
and then the underscore is letting you match like single letters. We're going to
see both. If we wanted someone with the last name
the had Bega and you're like huh, okay. i'm not exactly
sure who i'm looking for. But the name is something like this.
Let's see what we get for some results. Hopefully you don't have 10 billion of
these people. And so it prints out anything
that's very similar. That has at least what you're looking for
with the Bega. You could throw in an underscore and just look for ega for the
last thing, which is almost the same thing right. And what's going on here
is just taking those characters that are in front of it. There's something
interesting here that's going on i didn't mention this. So we're first
finding anybody with ega and any letters that are in front of it.
Then what i wanted to do is a useful thing. When you're always
printing off these stupid queries right look what's going on.
You don't even know what the columns are. That's why i did this. Because now
you have the ability to select your table of interest
at least something to pop up and you're going to throw in your description
of what you're looking for your columns and that's
how i'm getting these column names from this list comprehension.
Because it's basically iterating through this this description right here. Which
is very useful and something you should consider.
But as always i'd like to say please like share and subscribe
and if you subscribe turn on that notification bell. I hope this brought
utility to somebody. See in the next video. Bye