Creating a connection

- [Narrator] The first step in using the Python DB-API is to connect to the database. This is db-connect.py from chapter one of the exercise files. Notice the import line at the top here, where I import the sqlite3 module. That module, of course, comes with Python, you don't need to add it to your installation. And then down here in line 9, I call connect on that sqlite3 module to connect to the database. Now in parentheses here, normally there is a file name because SQLite stores all of its database information in a single file. And so you'll usually get a file name there. In this case I'm using the word memory enclosed in colons to get the special SQLite in memory database. And it operates just the same as if I had specified a database file, except that there's no file involved, it's just in memory. This returns an instance of the database interface. And in this case, we're connecting to an in-memory database. And this instance, I tend to call it db. Some people call it c-o-n for con because it's a connection, whatever you choose to call it, just be consistent. I call it db. Once I'm connected, I can create a cursor object. And I do this with the cursor method off the db object. We'll use this cursor object to query the database. And so down here, I have an execute method off the cursor object, and that submits a query to the database and returns a row of results as a tuple. And so, in this case, I'm passing it this bit of SQL here, which just says, it's just a select statement that selects a value with the special function that's specific to SQLite, sqlite_version. And this just returns the version number of the SQLite interface as an element in a row. And it's really just a one column row. And so we call fetchone to get one row from our query. And you notice that I subscript it here with the zero to get the first element of that tuple. And then I print that out with the print statement. So let's go ahead and run this. And this is shift-control-R on a Mac or shift-control-F10 on a PC, and you notice it runs it and it returns that value 3.34.0, which is the version of sqlite3 that came with Python that I installed on this machine. So this looks pretty simple, right? And what's cool about this is it works almost exactly the same for MySQL or for any database. So I'm just going to change this import and I'm going to mysql.connector, and we'll say as mysql so I don't have to type all of that out every time. And instead of sqlite3.connect, I'm going to say mysql.connect. And instead of memory here, I need to give it enough information to actually connect to a MySQL database. So I have my MySQL database on a different host. So I'm going to to say host="pluto.local", which is where my SQL database is. If your MySQL database is on your local machine, you just write localhost here. But be sure to spell it right. So it'll look like that. In my case, it's on pluto.local, and then you need to give it your credentials. I have a special user set up for this purpose called appuser. And it has a very simple password, don't ever do this in real life. Spartacus. And I have now connected to my database. And I clearly have a syntax error here. That should clear all that up. Now, obviously this select statement is specific to SQLite and it won't work with MySQL. Instead in MySQL, you can get exactly the same thing by just calling the version function with your SQL. And obviously here instead of SQLite here, I want to say MySQL. And now I can just save this and run it. And it works exactly the same. So all I changed was the connection. Well, I imported the right module and I changed the connection string because that's different for every database system. And I changed the SQL to get the version. In this case, it gets the version of the server and not the version of the interface. And it prints out that 8.0.23, which is the version of the server that I'm running on Pluto. So as you can see, Python's DB-API makes it easy to connect to a variety of different relational database systems with virtually the same code.