1. 35
  1. 16

    One of the neat things about SQLite is that it can actually import CSV data directly. No need for the Go script.

    Start sqlite, then:

    .mode csv .import some_data_file.csv your_table_name

    It’ll automatically generate a schema for you, too. To see what it did:

    .schema your_table_name

    You can import as many more files as you like so long as the schema is compatible.

    You can also use the “export” command in csv mode to dump your SQLite table out to a CSV file. It’s a real Swiss Army knife for this kind of small-to-medium data processing.

    1. 5

      Yeah I used the Go script because there are 4 separate CSV files, and I wanted to combine them into one table. I’m sure that could be done with sqlite cli too, but this method was faster for me. Plus I needed to dust off my Go chops, haven’t built anything for a while.

      1. 3

        what I always found unfortunate is that these features are only part of the sqlite-cli, not the sqlite library. It would be nice to do these things via a database driver (in python or java or whatever)

        1. 2

          You can easily implement yourself a function to do that from within SQLite via UDFs in the language of your choice… here’s a quick & dirty implementation in Python (probably don’t use this as-is in a “real” setting):

          import csv
          import sqlite3
          DATABASE_PATH = "data.db"
          def write_table(tablename, write_path):
              connection = sqlite3.connect(DATABASE_PATH)
              select = f"select * from {tablename}"
              with open(write_path, "w") as f:
                  writer = csv.writer(f)
                  for item in connection.execute(select):
              return 1
          if __name__ == "__main__":
              connection = sqlite3.connect(DATABASE_PATH)
              connection.execute("CREATE TABLE IF NOT EXISTS test(field1, field2)")
              connection.execute("INSERT INTO test VALUES (1, 2), (3, 4), ('testing', 'onetwothree');")
              connection.create_function("write_table", 2, write_table)
              connection.execute("select write_table('test', 'test.csv');")

          SQLite is pretty magical


          There’s probably a more refined way to do this using C-extensions so that it can support arbitrary queries rather than just tables and views, but I’ll leave that to the reader?

          1. 1

            That’s cool! I will remember that the next time I try to do that from python.

      2. 14

        In metric units 170F is about 75C, which is literal Sauna temperature. Wow.

        1. 1

          A sauna is much more dangerous. Just as the air in a 150 celcius oven is pretty hot, but a 100 degree steam coming out of a pot is going to hurt way more. Temperature isn’t the only relevant factor when it comes to cooking…

          1. 4

            You make it sound like a sauna is dangerous. It’s not, because you get out when you get too hot.

            In Finland there were less than 2 deaths in sauna per 100,000 inhabitants per year in the 1990s. That was a time when on average, Finns spent 9 minutes in a sauna twice a week. That’s one death per 780,000 hours spent in the sauna. And half of that is because people binge drink and go to the sauna.

            I don’t have statistics for deaths per hours a child spends in a hot car, but it cannot be very high considering reasonable people don’t leave children in a hot car at all yet there are dozens of deaths every year.

            1. 2

              In these comparisons I think the deciding factor is the ability/inability to leave the hostile environment…

            2. 1

              Saunas are typically dry air (although you can sometimes pour water onto hot stones). There are 100degC saunas which you can sit in for several minutes because heat transfer is so low. But a 100degC steam room would instantly burn you (and so doesn’t exist).

              1. 1

                Yeah humidity plays an important role as well. Sadly the post doesn’t show the record high with humidity info, but maybe @JeremyMorgan can enlighten us :)

                1. 2

                  It looks like the min [1] humidity was 7.8% from one of the pictures.

                  [1]: Humidity and temperature should be inversely related, so as the temperature rises, the humidity should decrease, as more water vapor can be “stored” in the air. Similarly, when the temperature drops, humidity increases, leading to dew in the early morning or fog.

                  1. 1


                    Humidity levels:


                    • Min: 13.6
                    • Max: 89.3

                    In the car

                    • Min: 6.8
                    • Max: 55.3

                    Seems like some wild fluctuations. It was an unusual weather event for sure.

              2. 9

                It’s amazing just how much those graphs resemble an RC charge/discharge cycle. It’s like the car is a capacitor for heat.

                1. 7

                  They are both governed by exponential dissipation vs constant charging/heating so the shapes are the same. Well the heating is not constant but close enough engineer shrugs.

                  1. 2

                    I hadn’t noticed this before, but now I can’t unsee it.

                    1. 1

                      That is pretty amazing

                    2. 7

                      The car temperature graph has an odd feature, where it increases smoothly for a while and then almost instantaneously jumps by 10°F. The first three days all show that behavior. Do you have any idea why it was happening? I would be very surprised if it weren’t some kind of measurement error. (Maybe you were alluding to this when you said “Very curious about how it ramped up”.)

                      It was interesting (and sobering) looking at your data. Thanks for putting it together!

                      1. 7

                        Maybe the car is in the shade in the morning and that part is when the sun starts hitting it.

                        1. 6

                          I’m not really sure. It looks like an error to me too. It’s either a sensor error or something to with the sun shining directly into the windshield at the right moment.

                        2. 10

                          Really nice article, @JeremyMorgan! There’s just one thing that’s bugging me, and that is the use of the Fahrenheit-scale, which is only used in the US, in Liberia and the Cayman Islands, while the rest of the world uses Celsius.

                          This article is your project and I’m not in the position to order you to do anything, but if you intended this article to be for an international audience, this is a critical error. If you only intended this article for US-American readers (and those few from the Cayman Islands and Liberia), I can understand the choice, but please keep this in mind in the future in case it is different.

                          1. 17

                            While I agree that Celsius is the superior unit, the article is really about building the device and measurement system, rather than takeaway about how hot the car is. It really isn’t the author’s job to translate the units from something they’re not comfortable with – it’s not a scientific paper.

                            Calling use of Fahrenheit a “critical error” on a hobbyist blog post (even one viewed internationally) seems overly dramatic, don’t you think? It’s not as if it’s written in Klingon (though, he’d be welcome to that too on a blog, albeit with a vastly smaller audience.)

                            1. 1

                              Arguably since the audience is primarily those in the east and west coasts of the United States, Fahrenheit is the superior scale.

                              1. 1

                                I did convert it to Fahrenheit during storage, but I can convert it all to Celcius in another database! Thank you for the idea to enhance the project!

                                1. 1

                                  I hope I didn’t come across too negatively, which wasn’t my intention. Thanks for your feedback!

                                  1. 1

                                    Not at all thanks. Over the weekend I did another stream and added celsius columns!


                                2. 1

                                  Celsius is inferior for this purpose. The degrees are lower precision, and the scale doesn’t connect to human health, which is the focus of the article. 100F is approximately the temperature of the human body, and consequently, the point at which air temperatures become dangerous to people. Saying a car is at 130F clearly conveys that it is an extremely dangerous environment for humans.

                                  For cooking and chemistry, Celsius is fine, but for weather, it’s gotta be Fahrenheit.

                                3. 4

                                  I made a similar setup in an old house when I was selling it and it was unoccupied for about 2 months in the middle of winter. Believe it or not, someone touring the house actually turned the heat off when it was far below freezing, so it was extremely valuable!