Author Archives: Josh

Use Python to load CSV into sqlite3 database

The Internet seems to have trouble telling you how to load an arbitrary comma separated values datafile into a database. Assuming the CSV has a header row, and given sqlite3 doesn’t know or care about data types, this ought to be a doddle.

import csv
import sqlite

def import_csv_to_db(table_name: str):
  """Load arbitarty CSV file into database"""
  filename = f"{table_name}.csv"
  columns = None
  count = None
  with open(filename) as f:
    rows = []
    reader = csv.reader(f)
    for row in reader:
      if columns:
        rows.append(row)
        if len(row)!=count:
          print(f"{table_name} column count mismatch; {len(row)} columns in this row, {count} in header")
          print(row)
          print("Halting")
          exit()
      else:
        columns = row
        count = len(columns)
        col_list = ",".join(columns)
        create_sql = f"CREATE TABLE IF NOT EXISTS {table_name} ({col_list})"
        insert_sql = f"INSERT INTO {table_name} ({col_list}) VALUES ({','.join('?' * len(columns))})"
  return create_sql, insert_sql, rows
  
conn = sqlite3.connect(self.db_path)
c = conn.cursor()

create, insert, rows = import_csv_to_db('import_filename')
c.execute(create_sql)
c.executemany(insert_sql, rows)

You could do what I did, and add a column-name:datatype lookup, but rather than throw that in here I’ve left it as an exercise for the reader. Equally, halting the program when there’s missing columns is kinda extreme, but in my particular use case it’s reasonable. Fix it if you don’t like it.

Pitivi timeline end is in the wrong place. How to move the end of the timeline?

Once again the Internet has failed me. I was using the Pitivi non-linear video editor, and discovered my three minute video had the end of the timeline (where the video ends) at over ten minutes. This would triple the render time: unacceptable, time is money.

You can reach the start/end of the timeline by pressing the Go To Start and Go To End buttons on the video player thing. I spent quite some time trying to find how to alter the timeline end.

Turns out there’s no way to move or adjust the end of the timeline. However if you close the project and reopen it, Pitivi correctly detects where the last clip ends and makes that the end of the video. Render time reduced.

There is no way to block, reject or prevent payments being made to your phone number via Paypal

How can I block PayPal payments to my mobile phone number?

None of these

How do I confirm my mobile phone number with PayPal?

I found some articles in our Help Center that might help you. Check them out! If you need more help, please rephrase your question or type “need more help”.

How do I confirm my mobile phone number with PayPal?

How do I add, edit or remove a phone number on my PayPal account?

How do I edit my customer service email, phone number or website URL on my PayPal account?

– PayPal Assistant

need more help

Yes

Hi Josh. I am not sure what you mean by block payments? Are you referring to notifications?

– Beth

No, I don’t want PayPal payments available to my mobile phone number. If someone tries to make Payal payment to my mobile phone number, I’d like it to fail immediately.

I see that right now your phone number is not confirmed and would not be able to receive payments at this time.

– Beth

But I get a text telling me I’ve got a payment, as far as the payer is concerned they’ve successfully made a payment. I don’t want this. I want payments to my mobile to fail, immediately.

In the sender’s account it would say that the payment is pending since the phone number is not confirmed. Unfortunately there is not a way to auto deny payments sent to your phone number. I apologize that this is not something that PayPal is able to do.

– Matthew

There’s also no way to reject payments sent to a phone number, other than contact the payer and ask them to cancel it from their end. Unless I want to refund payments to my mobile, which I do not.

The payments sent to your phone number will be reversed automatically after 30 days have passed, with no action required of you.

– Lindsay

uBank management and developers are a bunch of dummies

uBank, who I have chewed out before, has drawn my ire yet again. After several blissful years of not using any of their software, I found it necessary to do so again because they’ve done something to themselves, like a merger/rebrand/reverse takeover thing. As such, I get an un-requested NFC debit card. They then proceed to migrate all the customers off their existing IT systems – including me, who has no desire to migrate from, or even use said systems.

This causes me to need to log into their system. It won’t let me in, because I have a password that complied with their old requirements — that wouldn’t let you have a special character (*#@% etc) — but that password doesn’t comply with their new requirements — must have a special character (*#@% etc). Presumably, every damn customer has this problem.

No worries says the computer, let’s reset your password. Please confirm your identity by answering your secret question – “What are the last four digits of your private health insurance?” I dutifully type this in, but surprise surprise, this value has changed in the last ten years. At this point the computer suggests maybe I tell it the last four digits, and I scream.

A seventy-plus minute wait on hold (scratchy line, annoying announcements, bad hold music), I speak to the guy. The guy says, “so, what’s the last four digits of your private health insurance?” and I explain that number has changed in the last decade, and perhaps we could use some other technique to identify me. We do all the usual things like name, birthday, address and then my password is reset to a six digit number and SMSed to me. Couldn’t let me into the system with my old, 620-bit strong password that I knew. Now we have an almost 20-bit password that I had to wait 82 minutes for. Couldn’t just SMS me a reset six digits when I say, “dunno, forgot”. No, I had to wait over an hour to sort out this mess of a process. And great, now I have to generate and save a new password.

So I suggest perhaps we should change the secret question now, and get told that “oh, that’s not a thing anymore” and I just facepalm. New system uses 2FA, old system uses it for password resets, old system wouldn’t use for password resets. Morons, the lot of them.

And then I find out that their fabulous new system allows you to see transactions all the way back in the past… all the way back to 364 days ago. Because disk is expensive.

They don’t have a million customers, and they don’t each do a thousand transactions a year. That billion transactions a year they don’t process would require hundreds of gigabytes to store, and last I checked you could pick up a 240Gb SSD for less than $50.

And pagination! Show more than 10 things on a page! This isn’t 1992!

I believe the new system is meant to be an improvement on the old one. Money well spent.

Programatic submission of Australia Post’s CN23 customs form

A number of major international destinations of packages now will only accept packages with electronic CN23 customs declaration. Normally, you’d do this by rocking up to the Post Office with your pre-addressed parcel, filling in a CN23 paper form, and have that transcribed into Australia Post’s computer system by the postal worker behind the counter. You can elect to receive SMS notifications of change of status (landed, delivered, etc) for 50c.

Australia Post also allows you to fill in the appropriate details on their website; if you do this, then you get a QR code sent to you via SMS (free) and email (free) which the postal worker scans in and all the details (your name and address, destination name and address, contents, etc) are attached to your package’s details without any error-prone re-keying. The downside of going down this path is the dismal website Aussie Post provides, a JavaScript heavy, painfully slow dog of a site that doesn’t cache your own address.

Once the QR code is scanned, and the postal worker checks everything with you, they’ll print out the CN23, get you to sign it , and then it gets attached to your parcel. Because the To and From addresses are on the CN23 form (and those details are in electronic form, associated with the barcode for the package), it’s perfectly acceptable to present an unaddressed package to the post office (make sure you can tell which package is which, if you go down this route).

One thing you need to be aware of: Australia Post hasn’t heard of Unicode. You absolutely can’t use any characters not in the ASCII character set, and even then a very limited range of them. Certain fields allow some characters, which in turn aren’t allowed in other fields.

One of the fields you can supply is the HS tariff code, which is an international standard group of codes to describe “stuff” – the Harmonised System Tariff code. The sourcecode below uses the code for “Toy, plastic construction” – you should use the code for what you’re actually sending. You can specify multiple HS codes. Dollar values are in decimal dollars, weights are in decimal kilograms.

After calling the Australia Post website with your customs declaration, it returns to you a base-64 encoded PNG of the QR code to present at the counter, and a base-64 encoded PDF of the CN23 form – there’s no point printing this out, because it’s not paid for yet; let the Post Office print it out with the postage on it. You’ll also get the PNG via email and SMS (free).

Here’s some Python to make this submission:


    AP_session = requests.Session()
    jsonFormData =  {"customDeclaration":{
      "label":{"source":"AEM","postagePaidIndicator":False,"eadIndicator":False},
      "parcelCharacteristics":{
        "productClassification":11,
        "dangerousGoodsIndicator":False,
        "returnInstructions":"Return By Most Economical Route",
        "confirmationMobileNumber":"0411111111",
        "content":[{
          "content":"HS traffic code name for your stuff",
          "contentQuantity":1,
          "contentUnitValue":subtotal,
          "totalContentValue":subtotal,
          "contentWeight":int(order["total_weight"])/1000,
          "hsTariff":"95030039",
          "contentCountryOfOrigin":"DK"
          }],
        "totalConsignmentValue":subtotal},
      "senderAddress":{"firstName":"Josh","lastName":"FromGeekrant.org",
        "addressLine":["11 Example St"],"suburb":"YourSuburbName","state":"VIC",
        "postcode":"3000","email":"addr@example.com",
        "phone":"0411111111","smsConfirmation":False,"countryCode":"AU"},
      "receiverAddress":{"firstName":CustomsString(order["label_address_name_first"]),
        "lastName":CustomsString(order["label_address_name_last"]),
        "countryCode":order["label_address_two_char_country_code"],
        "addressLine":CustomsAddress(order),
        "suburb":CustomsString(order["label_address_city"]),
        "state":CustomsString(order["label_address_state"]),
        "postcode":CustomsString(order["label_address_postal_code"]),
        "email":order["buyer_email"]}
    }}

    stopact = {"jsonFormData":json.dumps(jsonFormData) }
    result = AP_session.post(url='https://auspost.com.au/bin/form/stopact', 
      data=stopact, timeout=2)
    response = json.loads(result.text)
    result.raise_for_status()
    filename = "{}-customsQRcode.png".format(orderid)
    with open(filename, "wb") as fh:
      fh.write(base64.b64decode(response['qrCode']))
    filename = "{}-CN23.pdf".format(orderid)
    with open(filename, "wb") as fh:
      fh.write(base64.b64decode(response['label']))

LEGO Pick-a-brick container sizes, dimensions and capacity

There are three Pick A Brick containers – The 950ml tumbler, 475ml tumbler and 30ml lid. I have measured the volume of these containers using a 0.1g scale and water; I have high confidence in the measurements. Other measurements have been taken with callipers and rulers; I have lower levels of confidence in those numbers.

The lid’s stud (where you can store LEGO if you’re particularly cunning) is 48mm diameter and approximately 16mm deep – six studs in diameter by two studs deep. This means you can’t fit something six studs wide (48mm) into it, because LEGO bricks aren’t 0mm tall or long. You might be able to store one 1×6 plate if you jammed it in, as plastic objects are plastic (bendy).

The 475ml tumbler has a profile matching that of the 950ml tumber, cut off at the bottom. They share an opening of over 100mm. They both have an indentation that matches that in the lid, allowing stacking. The displacement of the indentation of the base is 55mm wide and has three strengthening piers projecting into the interior of the base.

The length of the interior wall top-to-bottom depth of the 475ml tumbler is 76mm; it can hold 1675 1×1 round plates. The top-to-bottom depth of the 950ml tumbler is 170mm.

Dear ABC: use English better

It’s been several years, but now it’s time to complain about the use of certain phrases by ABC News journalists:
– “Quote Unquote” is meant to surround what you’re quoting, not preface it. If you’re just going to preface it and use a different tone of voice, just use “Quote.” If you want to clearly signal the end of a quote, say “Unquote” at that part.
– All crimes happened in the past, and are thus historic. There is no such thing as historic rape. It’s rape. Find another way to communicate “very old”. Vintage rape? Much classier.
– If a court order or law forbids naming someone, then you “mustn’t” name them, not “can’t”. You can, but you’re just unwilling to go to jail for contempt. “can’t be named for legal reasons” is also wrong, but less wrong than “can’t”. I hope you’re not trying to avoid sounding like characters in Harry Potter and “He who mustn’t be named”
– Vehicle crashes are not best described as accidents. As reporters you don’t generally know at the time of reporting the intent of drivers, so it could well not be an accident. Try crash, collision, or even the bland “incident”.

And on another matter, could your staff stop editorializing misfortune? “The driver reversed and tragically didn’t see the three year old, who sadly died as a result” ought to be reported as “The driver reversed and didn’t see the three year old, who died as a result”

BrickLink API PushNotificationMethod Get Notifications callback semantics

The documentation for the BrickLink API PushNotificationMethod suggests that the data sent to the URL you registered on the BrickLink API Consumer Registration Page is sent to this URL (via a POST verb, by the way) and as such you don’t need to call Get Notifications. Given the body of the POST is empty, this is not right – what you instead need to do is use any POST to your registered URL as a prompt to call Get-Notifications. It’s probably best to periodically call it too, given “it does not guarantee delivery of all events” and doesn’t either based on my experience.

A notification to be created when:

  • Order
    • You received a new order.
    • Buyer updates an order status.
    • Items of an order are updated (added or deleted).
  • Message
    • You received a new message.
  • Feedback
    • You received a new feedback or reply

Also note: NULL fields are not included in the returned JSON. Some fields names don’t match the documentation (eg: drive_thru_sent instead of the documented sent_drive_thru).

Setting up a public facing webserver behind a Netcomm NF18ACV

Note: this will move your Broadband Router’s web-configuration to an unexpected port :8080, instead of the :80 your browser expects.

Navigate to Management | Access Control | Services then disable the WAN side HTTP service (why would you even expose this?), change the port for the LAN side to the Alternate HTTP port of 8080, and hit Apply/Save.

Navigate to Advanced Setup | NAT | Virtual Servers and hit Add. Select the correct interface, fill in the other details including the Web Server’s LAN address, ensure you’ve got Status: Enabled for the port forwarding, and hit Apply/Save.

Run up a trial HTTP server using something like
sudo python -m SimpleHTTPServer 80
and check for access from outside. Kill the server, because that isn’t safe for production use.