• Exporting Results of a MySQL Query as INSERTS

    Another technical post, mostly for my own future reference.

    tl;dr If you only have SELECT privileges, you may be out of luck. Otherwise create a new table with the results of your query and export that.

    Here we have another case of my wanting to do things of a “data science” flavor when much of SQL and the advice about using SQL is more of a “run a production application” flavor.

    We wanted to share some data with another team that has their own database. This other team does not – and should not – have full access to our database. Additionally, some of the fields in our database are filled with all sorts of escape characters and punctuation that would not fare well as a CSV export. So we wanted to export the results of our query as a bunch of INSERT statements so that the other team could import this information into their database.

    You would thing that the GUI that we use for writing and debugging our queries would have a button to click for exporting the results of a query as SQL. It does, but only in the case that all the data comes from one table. That is, I can export the results of SELECT foo FROM bar WHERE baz > 0 as a bunch of INSERT statements. But I can not do it when my SELECT statement involves data from more than one table in my database.

    But what I can do is CREATE TABLE with the results of my query and then export that.

    The MySQL documentation (5.5, 8.0) gives the following example:

    CREATE TABLE artists_and_works
      SELECT artist.name, COUNT(work.artist_id) AS number_of_works
      FROM artist LEFT JOIN work ON artist.id = work.artist_id
      GROUP BY artist.id;

    You can then export this as a bunch of INSERT statements and send it to your friends!

    To be perfectly honest, I didn’t realize that you could do it in one step, and I created a view from the query and then created a table from the view. That definitely worked.

    I was fortunate that I have my own test instance of the database so that I could create a new table without bothering anyone (and can drop it at my convenience).

  • Thursday Omens

    1. The avocado that I cut into this morning had two pits. One was in the middle, like normal, and the other was kind of stuck to the inside of the skin. This is much less exciting than the time roughly 20 years ago when I cracked open an egg and it had two yolks.

    2. Brought my camera with me to the doctor’s office because the parking garage is a good place to watch airplanes. Unfortunately, there wasn’t enough light to take good pictures with the long lens.

    3. Normally when I go to the doctor’s office first thing in the morning, I can be out of there before the parking attendant shows up for work. He was there this morning as I departed. But he let me park for free.

    4. My car has been warning me TIRE PRESSURE LOW for almost two weeks now. Even though I put more air in the tires. More than once. Today the message was gone.

  • Confirming Email Addresses

    The medical office mentioned in the previous post called me back, and they are working on figuring out why I was getting email meant for a patient with the same name as me.

    My best guess is that her email address is very, very similar to mine and that there was a typo or a transcription error somewhere. For example, maybe her email address is at mail.com instead of a similarly spelled and more well-known email provider? Or perhaps she normally includes a middle initial in her email address? It’s even possible that her last name has some sort of misspelling that was introduced when her ancestors moved to the US, and a staffer familiar with Polish names corrected it without thinking.

    In any event, email ended up in my inbox instead of hers.

    So I was thinking: What could a medical office do to verify patient email addresses without violating anyone’s privacy? Medical offices don’t build their own IT systems from scratch. Whether it’s a large organization that uses something like Epic or a smaller office that relies on a simpler tool, there is a third party involved. So really it is these third parties that need to implement the solution to this problem.

    Here are my ideas.

    1. This case assumes that most patients who want to sign up for the email reminder system also carry smart phones around with them. The medical office would collect the patient’s email address on the intake forms (as well an whatever opt-in approval is necessary). While the patient is waiting for the appointment and/or seeing the provider, the staff would enter the patient’s email address into the system. The system would send an email to the patient that says something along the lines of, “Your medical provider uses [name of this service] to send appointment reminders and other messages to patients. To confirm that you wish to receive these messages at this address, please give the front desk staff the code [SOME-CODE-GOES-HERE].” The email wouldn’t say which medical office it was, so an incorrect recipient wouldn’t learn anything about the patient other than that person has gone to some medical office somewhere. The staff could then ask patients on the way out if they received the code and then enter that into the system to confirm the email address. If something goes wrong, they can work together to fix it before the patient leaves. If the patient doesn’t have a phone with them, they could call the office later with the code; an incorrect recipient wouldn’t know which office to call.

    2. The other situation that I thought of is a little bit trickier because the medical office also needs to make sure that random people are not just signing up pretending to be patients and trying to get unauthorized access to patients’ information. The office would give the patient a card with a URL (something easy to type or else explain where the link is on the practice’s web page) and a code. The patient would enter their email address and the code from the medical office; this confirms that the person using the system really is a patient. The system would email the patient (again, with a generic email that’s roughly similar to the one that I described above) a second code, which needs to be entered onto the page; this confirms that the email address belongs to that patient. This is annoying and convoluted—and it’s likely to reduce the number of people signing up for the system—but it will prevent mix-ups.

  • Recent Updates

    1. Cats keep sitting on me, preventing me from getting anything done or participating in any of my hobbies.

    2. Today in Tales from the BugMaster, a student reported the following error in the homework system: “This is against what you teached in the book u nub.”

    3. Today in HIPAA violations, I received an email from a medical practice where I am not a patient wishing me a happy birthday. Someone with my name who lives in the Chicago area is a patient of a particular medical practice, and her birthday is today. I called their office to let them know about this situation, but no one returned my call. Yet.

    4. Fun fact about calling businesses in Chicago. (Sorry if you already read this on Twitter.) Their phone tree starts out like usual for a medical practice: “To make an appointment, press one. If you are calling from a doctor’s office, press two. Para español oprima tres.” I didn’t catch all the words in the next part, but I definitely heard polskiego and cztery. Good thing that there weren’t more options before we reached this point, as I can recognize the words for the numbers from one to five and then some number that is either nine or ten. I will never be able to say the word that means “three.”

    5. The seemingly boring button clicking thing that I posted the other day is growing into a larger project. But I need to redo it because it has become a nest of CSS classes and jquery, and I probably should change it to use layers. Or maybe not?

    6. If you are looking for me this coming weekend, I will be in Schenectady.

    7. I have a new next door neighbor. A long time ago my neighborhood was made up of single-family homes. My building (seven apartments) sits where a house used to be. However, next door is a house with a large backyard. My neighbor seems to have a large number of vintage Volkswagens in the yard at all times. There are always at least two microbuses, but I definitely remember seeing three at one point. There might have been more? Yesterday there were a lot of the old style Beetles, but they seem to be gone today.

    8. The ridiculous GIFs continue. Originally I had a link to one of them here together with the commands I used to make it in ImageMagick. BUT THEN IT STOPPED WORKING. It moves back and forth on my phone and on Facebook, but it gets stuck here. So I took it away.

  • Yesterday I Made a Ridiculous GIF

    I was out taking pictures of airplanes and stopped to take a picture of cars.

    (I also took a picture of a train, so we have the trifecta of planes, trains, and automobiles.)

    freeway traffic

  • A Thing I Made By Accident

    I was making a thing at work that was supposed to do something, but instead it did something else.

    I’ve distilled the essence of it below (in JavaScript, so if you’re reading via a feedreader, you won’t see it). Pick two of the four boxes. Click the buttons until only your two boxes are visible. What’s the smallest number of clicks that will get you there from the original arrangement?


  • Migraine Update

    You can probably guess that I’m doing better since there hasn’t been a migraine update in a while.

    To bring everyone up to speed, I’ve been seeing visual auras every day since late June. They got pretty bad in July (seriously disruptive), so I started taking the Scary Rash Medicine. They got better for a while, and by late August they were almost gone – just a faint shimmer or distortion or a quick flash one a few times a day. And then they started to get worse again. The doctor upped my dose of the Scary Rash Medicine, and I started to get better.

    All that to say, it’s pretty rare for me to see the auras just out of the blue, but there are two things that will consistently bring on an aura. One is staring at the same spot for more than 5 seconds in a row (happens when trying to fix mistakes in craft projects). The other is leaving my home during the daytime.

    Specifically, it is the security screen that triggers the migraine aura. It’s the perfect storm of edge-detection, light vs. dark, figure vs. ground, and a busy pattern to set off some irritated neuron in my visual cortex.

    Here’s a picture of the view out my front door as well as a closeup.

    screen door

    screen door closeup

subscribe via RSS