Data Science Jumpstart with 10 Projects Transcripts
Chapter: Project 9: SQL / Database Integration
Lecture: Query a SQLite table from Pandas using read_sql
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
In this video, I'm going to show you how to query a database. We have our table created here. Now I'm going to connect to it. I've imported SQLAlchemy,
0:10
I've created my engine, and my query is select star from Alta. I'm going to use pandas read SQL to do that. Read SQL, if you want to,
0:19
you can pull up the documentation for that. You can see that we pass in a SQL string and we can pass in
0:24
this connection which is a SQLAlchemy connection. Once I've done that, the result is a pandas data frame. I'm pulling out my SQL query as pandas.
0:37
Let's look at the types of that. This is using NumPy types. Let's try and do that again with PyArrow types.
0:44
We're going to say dtype back into this PyArrow, and we'll look at the types. You can see that the date did not work.
0:52
It said that this is a PyArrow string. If you come up here, this says that this is an object.
0:58
The date conversion did not quite do what we expected it to. Let's use SQLite to inspect what's going on inside of SQL.
1:06
This is a query that will give us the schema. You can see that date says it's a timestamp. Sadly, pandas is not converting the timestamp there.
1:16
If you want to get a timestamp working, you'd have to manually convert that.