Connecting Python to a MySQL Database With PyMySQL

I use Python quite frequently to dump web scraped data to a MySQL database. In fact, I have a separate post going in depth on a script I wrote to automatically pull intraday data for every company in the S&P 500 and store it in a MySQL database.

To start, we need to install the PyMySQL module.  We can do this by using pip. Note I am on a Windows operating system. Pip is a package manager for Python. If you don’t have it installed or don’t know how to install it, YoutTube how to install Pip for whatever operating system you have. A simply pip install PyMySQL (case sensitive) in the command prompt will do the trick. Once we have PyMySQL installed, we can create a connection object. First, we need to import pymysql.cursors. the cursor will allow us to execute actions and move over a database. This will allow us to establish a socket with our MySQL server. In layman’s terms, a socket is merely an IP in conjunction with a port number. These can be represented either client side or server side. We need to define a few variables here in order to establish the socket. We need the username to log in as, the hostname, the port number, and password of our database at a minimum to connect. Once we have these defined, we can establish the connection object

Next, if you haven’t already, we can create a database in whatever IDE we are working with. We will interact with the database with our cursor object now. We can use an SQL syntax within the execute method referring to our cursor object. It is also proper to close the socket after we are done doing this. We can accomplish this with a finally statement which will run the line after everything else has previously been executed.  We will open more connections to accomplish more tasks.

We can now re-establish the connection object and insert our database name within as an argument. We can now use an SQL statement to create a table and start to define our basic schema. This table will theoretically hold open, high, low, close and volume data from a vendor.

Now that we have our table created, we can start to insert data into the table. We can refer to the same cursor and connection object. Keep in mind the SQL syntax must be formatted as a string, so we have to use string formatting here in order to successfully run our query. We will pass in the values in the execute method. Also, keep in mind the data types we used for our columns. We need to make sure we are passing in the right types in the execute statement, which is why the values are wrapped in float and int functions.

Finally, we can query data from our recently created table. We can do this by executing a SELECT statement. Keep in mind we have to use string formatting again here. A few key points here. I posted in the new connection object which defines a cursor class. Here, we use a DictCursor which returns the results of a query as a dictionary. This is useful for small queries. If you are running large scale queries, consider using the SSCursor. This is an unbuffered cursor which is useful for queries that will return a ton of data. Note also the cursor.fetchone() statement. This will obtain the next row of data. We could use cursor.fetchall() or cursor.fetchmany() also. These methods have limitations when querying large amounts of data, however.

About the author

programmingforfinance

Hi, I'm Frank. I have a passion for coding and extend it primarily within the realm of Finance.

View all posts

Leave a Reply

Your email address will not be published. Required fields are marked *