An example of inserting the .csv data into an SQL database using Python

Learning by doing … and copy-pasting

When you just learn to program, you may often use existing code snippets kindly shared by more experienced colleagues on StackOverflow or their personal blogs.

The more advanced code you need to write the more customization you have to apply to the snippets that you find on the Internet. It is always quite a pity to have done a lot of editing and then realizing that your new code won’t work. And that you neither saved the original snippet nor bookmarked the blog you have copied it from.

In one of my previous blog posts, I gave a few recommendations for those working with Jupyter Notebooks. The main advice was to keep all cells (= code chunks), even the failed ones, and always copy, paste, and only then edit a cell. The reason I advocated it is that you sometimes may be wrong in identifying what part of your code had actually led to the failure. You may have edited the part that was working and, therefore, lost even more of the code you had. 

I must confess that backuping every code snippet manually sometimes resulted in a huge mess inside the Jupyter Notebook. When I needed to run a kernel, I could not sort out the failed cells, and it all ended with numerous error messages popping out of all corners. 

I can imagine that if you use a code editor or especially an IDE it becomes even more messier since you cannot break your code into chunks. 

R Studio has a history section where you can find all chunks that you have run in the console. But it is barely possible to pick something out of it quickly: all chunks are kept as a plain text stacked over each other with only a few first lines visible. 

A practical example

Imagine that you need to insert some data into an SQL database using Python. The data originally comes in a .csv file. 

You type in Google:

how to insert csv into database python

You’ve got quite a few (millions) results. Why are there so many? Well, there is no universal way to do it. A lot will depend on your initial setup and what kind of data you have. Besides, some blogs will offer a complete tutorial about creating a database and inserting data into it, whereas such sources like StackOverflow will focus on a particular part of the task. Last but not least, the coders publish their solutions to their problem, not to yours.

Recently, I tried out a clipboard manager known as Paste which gave the name to an awesome publication on Medium. Whereas your computer saves each copied item and replaces the one previously saved, Paste creates a clipboard history and stores all items. Or at least as many as you have allowed it to save.

Let’s say, you went through the first ten results and realized that you have a lot of choice among the available solutions:

  • a custom function if you use the sqlalchemy package (click)
  • creating a dictionary out of the .csv data with the csv package and inserting it into the database with sqlite3 (click)
  • creating a data frame with pandas read_csv() and iterating over its rows and using a connector from the pyodbc package (click)
  • the same but using itertuples() instead of iterrows() (click)
  • creating a data frame with reader() function from the csv package and inserting it with a connector from the MySQLdb package (click)

Although the choice of the right connector may be spared if you have a specific database, choosing between the methods of transporting the data from the .csv file into the final destination may get tricky. They all seem worth a try!

How to end the chaos and retrieve the lost code quickly

OK, you have copied the first chunk and then another one and want to combine them. For instance, replace the connector. But your customization did not work. Maybe, it is some kind of a general incompatibility issue. Maybe, the logic of the single packages are not in your favor. Whatever. You go on and copy another chunk. Repeat your trial-and-error. 

Since you are still learning, trial-and-error remains your main working method for a while. 

After having tried a few code snippets, you start to understand which parts can work for you and how you should assemble them together. The entire picture in your head become more sharp. You should urgently use the moment to implement it. It is very important to have all the code snippets in one place and ready to be retrieved. 

That’s where Paste comes into play. 

First, you can search through the clipboard history to select only the code items by using a keyword that only those are likely to include. You can narrow your search by adding the format definition. For instance, let’s look for the text items that contain “sql”:

Screen Shot 2020-12-29 at 17.42.04.png
Searching for the code snippets containing “sql”

You’ve got exactly the five snippets you need!

Then, holding Shift and navigating with the arrow, select them all and add them to a new pinboard “CSV to SQL” that gets a nice yellow tag.

Selecting the items for a new pinboard

Now, if you want to have a break and grab a coffee or you want to finish your work tomorrow, you can click on the pinboard and directly access the original snippets after you return. 

But before you close your laptop, spend one minute and rename the single items. Believe me: you won’t recollect which snippet is which on the next day. Extract the most important information and use it as a title. 

Screen Shot 2020-12-29 at 17.52.53.png
Renaming your clipboard history items

Of course, you can open a Quick Look to preview the items but it does not hurt to give them catchy names, especially, if you have a lot of code chunks in the clipboard history. 

You do not have to create any files on your computer with such confusing names as “Code snippets for later.” Trust me one more time, you forget that they exist in your file manager the very next morning! 

A short afterward

You are set to assemble your perfect code. Use the editing feature of Paste to pick up single lines from each snippet and put them together in a code editor. 

Screen Shot 2020-12-29 at 18.33.44.png
Paste plain text editor

Happy coding!

Leave a Reply

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