Pushing data back to database
1. Pushing data back to database
We've used commands in csvkit to do a variety of tasks related to the database. We've pulled data. We've manipulated data. Now let's learn how to push data back to the database.2. csvsql: documentation
Our friend csvsql makes a re-appearance here, since it pulls double duty for manipulating local CSV files with SQL statements as well as uploading and pushing CSV data back to the database. In our previous lesson, we focused on the dash-dash-query portion of csvsql. Here, we will focus on some more option arguments, dash-dash-insert, dash-dash-d-b, dash-dash-no-inference, and dash-dash-no-constraints.3. csvsql: pushing data back to database
Here's a sample script that uses csvsql to upload a local CSV file Spotify underscore music attributes dot-csv to an existing SQLite database called Spotify-database. The syntax for this task starts with csvsql and the dash-dash-d-b flag followed by the full database name, to establish location of the database. Remember to pass this in as a string, inside quotation marks. Please note, a quick built-in line break is used here to keep our code clean and readable. It's worth noting that we used three forward slashes after naming the database type, which is normal. However, because this is a sqlite database, we need to end the database with the file extension dot-d-b.4. csvsql: pushing data back to database
Still the same command, but now let's focus on the second option, dash-dash-insert. This is new to us and is very interesting because the insert option does many things all in one step. Traditionally, uploading a new file as a table in a database has multiple steps. Depending on your SQL client or user interface, you might need to create an empty table, specify the table schema, key the table and then write in the data row by row. Here, dash-dash-insert will take care of all of these steps by itself, by making its best-guess at what the schema of the table should be. Pretty neat, right? The insert option can only be used in conjunction with the db option, which should makes sense, because if we don't specify where the database is, we cannot insert data into the database.5. csvsql: pushing data back to database
Lastly and unfortunately, csvsql is not always going to be 100% failsafe when it comes to schema inference. If you know that your data have a numbers column that should rather be treated as a text column instead, then you can preemptively account for this by using the dash-dash-no-inference option flag with csvsql. This way, csvsql will treat every column as text, whether it looks like numbers, dates, or string. Likewise, dash-dash no-constraints allows the table to be created without any character length limits, which is helpful for particularly large data tables and considerably speeds up the insertion process. It will allow you to create columns containing all null data, without throwing any errors.6. Let's practice!
That's it! We now have a full vocabulary on how to do database processing on the command line! Let's get some practice in!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.