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