A python script that exports CSV or Excel file directly into a relational database management system.
- Getting Started
- Concept behind SQLake
- Target audience
- About me
- Contributing
- License
pandas==1.0.0
psycopg2==2.8.4
mysql_connector_repackaged==0.3.1
PySimpleGUI==4.19.0Prior to this installation, we assume that you have MySQL or PostgreSQL or SQLite3 installed on your local system. If you have not done so, please proceed to the following links to get them installed:
If you experience any issues, do feel free to contact me.
After which, do the following:
- You can clone or download my package.
- Using terminal, move to the directory.
- Example for Mac OS users:
$ cd Downloads/SQLake - Install the required packages using:
pip install -r requirements.txt
- Now, execute the main.py file by:
$ python main.py
- You will see a GUI popping up.
- Pick a file format (CSV or Excel)
- Click on the browse button to choose the file
- Choose your database type
- Key in your database name. This database has to be created prior to the running of this script.
- Key in the name of the table. My script will create this table for you.
- Key in the Username and password.
- Default for MySQL is root and postgres for PostgreSQL.
- SQLite3 has no username and password requirement so leave those 2 fields blank.
For start, SQLake utilises Pandas to identify data type of each column in the provided dataset. With the information of the data type, I was able to match the data type to the data type reflected on MySQL, PostgreSQL or SQLite3. I then use the for loops to loop through each row of the dataset and append them into a bunch of lists. At the end, I concatenate all the required list into one variable and execute this variable into the SQL connection.
I am aware that analysts are required to clean up data files and export those files into an SQL database. Imagine spending hours sitting in front of the computer doing that for the next 5 to 10 years of your career. Won't that scare you? Indeed, there are tons of websites out there that can take in your files and generate an SQL query for you but do you trust them with your client's details? How confident are you that they won't exploit those data for personal use?
That is why I have decided to create this script. This script can be run on your local machine offline and every process is transparent to the end user. I have also added in a graphic user interface to cater to users with non-technical backgrounds. All they have to do is choose the file path, key in the names of the database, table, their usernames and password and bam! your work is done!
I graduated from the University of New South Wales in 2018 with a Bachelor of Biotechnology (1st Class Hons). My honours project exposed me to the field of bioinformatics and I have fallen in love with it ever since. I realised that we can generate tons of insights just from data alone and this inspired me to venture into this field. I started the process of being a self-taught programmer in 2019 and I am glad I have made the right choice!
Feel free to reach out to me!
I am open to any form of contribution but do take a look at the Contribution.md document before contributing.
This project is licensed under the MIT License - see the LICENSE file for details