In a previous tutorial, we saw how a database and table can be created in mySQL. Here is how you can run this on a local machine with the mySQL Installer and mySQL Workbench.
PostgreSQL is a commonly used database language for creating and managing large amounts of data effectively.
Here, you will see how to:
- create a PostgreSQL database using the Linux terminal
- connect the PostgreSQL database to R using the “RpostgreSQL” library, and to Python using the “psycopg2” library
In a previous tutorial, we set up a financial database using a range of mySQL queries, and used such queries to create separate tables and discriminate among data in those tables. However, there are many occasions when a user needs to connect to a mySQL database through an external program. This is particularly the case with Python, which integrates quite well with mySQL through the MySQLdb library. If you are not familiar with the workings of mySQL, then I strongly recommend reading the previous tutorial, which provides a guide for the commands being used here.
The following is a hypothetical dataset of 20 securities with various financial variables for each. As a database language, mySQL allows us to select specific data as specified by the user, as well as conduct certain calculations on the data already available. In this regard, we use mySQL queries below to illustrate the use of the same in manipulating the database and conducting various calculations (note that the securities in this database are hypothetical, and any resemblance to a real-life security or company is merely coincidental).