Effective PyCharm Transcripts
Chapter: Databases
Lecture: A coding delight
Login or
purchase this course
to watch this video and the rest of the course contents.
0:01
It's pretty awesome that we can be over in this console here for the database and we can get intellisense with the SQL query language
0:09
and with our database tables and even with our things, our schema inside there. We want to see all the albums that have a preview,
0:24
I think there's just one, yes, that one. That's pretty excellent and while I think it's really nice,
0:31
a lot of tools don't have that, it's not entirely blowing my mind, because inside the thing that works with the database,
0:39
right here is the schema, like good job JetBrains but not entirely blowing my mind,
0:45
but let me show you something that did blow my mind when I first saw it, and I think it will blow yours if you haven't seen this.
0:51
Let's suppose we just have some random Python file, super important, random Python file, it has nothing to do with SQLAlchemy,
1:02
the only thing that makes what I am about to show you happen is the fact that we have this data source registered okay? So we'll have def, some method,
1:13
and suppose we're not using SQLAlchemy, right, SQLAlchemy has its own style with its own classes and it has Python level autocomplete
1:22
because you're just working with the type, but sometimes you want to work directly with SQLAlchemy, or any SQL related database,
1:30
so you're going to write some SQL embedded in a string in Python, so you might say this query="" and in this string
1:38
you'll say basically select * from album, okay, so let's write this, just watch the words here so select— nothing, *— nothing
1:52
from—oh, wait at a minute, look at that select * from album or track, that is full on autocomplete inside an embedded string,
2:07
inside a Python back into our data model. That is super awesome, let's do this one and soon as I have a really simple query statement
2:18
or any kind of reasonably structured SQL statement it turns into full intellisense, so I could say now I get
2:26
where, join, all of the stuff that you would expect, including— check that out, exactly the same where has_preview is true
2:37
and what else— control space brings up the rest of the list, we could do a join over an album, so we could say the year is greater than 20,
2:50
I don't know, whatever, 2001, something like this. Now it even says the SQL dialect is not configured so why do we care about a SQL dialect anyway?
3:00
One of the beautiful things about working with SQLAlchemy is it adjusts automatically to the dialect that you're working with,
3:07
different databases have annoyingly slightly different query syntaxes and ways of working with parameters, so in SQL server you would say add name,
3:20
if you had a parametertized query where you passed thing called name, I think in Oracle you have question mark
3:29
and it's based on the order in which you pass them in, but they're not the same, which is annoying and so we can come over here and we can go
3:37
if you don't know where it is, you can just type SQL dialect but it's under language and framework SQL dialects,
3:42
we can say for this project I am working with SQLite or I am working with SQL server or whatever,
3:49
so we hit ok, I come back, and it's down with one more warning but that has nothing to do with the embedded SQL,
3:59
that just has to do with the fact that we need to format that code. Sweet, huh? When I first saw that that just blew my mind,
4:06
I'm like wow, I get full on schema based intellisense or autocomplete in embedded SQL strings— I love it, I still hate writing raw SQL,
4:16
but this sure makes it more patable for as far as I'm concerned, but given a choice, let's stick to SQLAlchemy or something like that.