Join Bruce Van Horn for an in-depth discussion in this video Creating the database, part of Learning Python with PyCharm.
- [Instructor] In our project so far we've been using PyCharm and Python to create our very own PageSpider. Essentially it reads a list of URLS from a text file and then parses up all the words that are in those URLs and we're to that point in the project where we really want to save all of those words and the result of our analysis to a database. PyCharm Professional has a collection of database tools built into it. These are going to be similar if not identical to their companion tool called DataGrip which is an IDE geared specially for database programming.
Since working with databases is so integral to building professional grade software particularly software for the web most of the functionality you'll find in DataGrip is also in PyCharm Professional. For our videos we're going to be using a database called SQLite. SQLite is a file-based database meaning I don't have to install a server or set up any sort of network infrastructure in order to use it. It still uses SQL tables and all the usual relational structures but it's just much easier to work with.
So, let's go ahead and design our database. To get to the database tools select view, tool windows and then database. This will bring up the database window which is typically docked to the right side of the IDE. You can move this back and forth and you can also close it with this little collapse button. I'll bring it back by going again to view, tool windows, and then database. To create a new database connection I'll select this little green plus icon and I'll select data source and here I can see all of the possibilities that are open to me.
I can work with DB2, Derby, H2, HSQLDB and a list of very common and very popular database architectures. You might notice that the list has duplicate entries in it which you're really see are the drivers that you're going to be using in PyCharm. PyCharm is written in Java and so in order to use databases you're going to need to use a JDBC driver. The good news is that they've done a lot of this sort of work for you. You don't have to worry about finding and downloading the drivers as we'll see in just a moment.
We're going to be working with SQLite and so I'm going to select SQLite and it's going to bring up a window that's going to allow me to set all the connections to a SQLite database but before I do that I need to come down here and address this missing drivers files. You need to do this in PyCharm the first time you deal with any of the databases in the list. You need to select and download the JDBC drivers that you need to make this work. Fortunately this is very easy. Just click download and it will go out and download it.
You don't have to really do anything and that's pretty much all there is to it and it works the same regardless of which database you picked. Now in our case we're going to work with a file rather than a server, so instead of defining a URL and all of the usual connection parameters that you'd be used to if you're working with Oracle or MySQL or SQL Server we're actually going to point to a file. So, I'm going to show you a bit of a pro tip here. I'm going to go ahead and click cancel here and I actually want my database file to be in the same folder as everything else.
Right, so I really want it to be here up here with PageSpider. So, what I can do is I can right click on PageSpider and I can select copy path and that copies the path to my project to the clipboard. Now I can come over here and do this again, select a data source and this time it's at the top because it's the last one that I accessed, select SQLite, and then in the file section here I can paste in that path that I copied. And now I can go ahead and add words.db which is what I'm going to call my database file.
Earlier when I canceled out of it apparently that nullified my download, so let me do that again and while that looks odd I suspect that everything is fine. I can click test connection. And I should see successful here. You'll note that by default I have auto commit and auto sync already checked. Auto commit refers to the database and if you have it checked then any change you make via SQL will be committed as soon as you run the statement. Auto sync is part of the database window and what happens here is as changes are made to the database you're always going to see the latest version of that synchronized in the database window.
If you turn this off, then you'll have to click a button in order to synchronize changes to the view that you see in the IDE. I'll go ahead and click apply and I'll click OK. My database has been created and now I can start working on its structure.
- Installing the PyCharm, Git, and Pypy packages
- Adding functions
- Refactoring code
- Calling packages
- Debugging code
- Creating databases
- Working with in-line SQL
- Creating web projects with PyCharm