Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Load citylots json
#!/bin/env python3
# Loads https://github.com/zemirco/sf-city-lots-json/blob/master/citylots.json into MySQL
# First wget the file (could have used requests.get...)
# This requires the MySQL 5.7 JSON support.
#
# Table layout:
# CREATE DATABASE json_test;
# USE json_test;
# CREATE TABLE `test_features` (
# `id` int(11) NOT NULL AUTO_INCREMENT,
# `feature` json NOT NULL,
# PRIMARY KEY (`id`)
# );
import json
import mysql.connector
with open('citylots.json') as fh:
citylots = json.load(fh)
con = mysql.connector.connect(host='127.0.0.1', port=5711, user='msandbox',
password='msandbox', database='json_test')
cur = con.cursor()
for feat in citylots['features']:
cur.execute('INSERT INTO test_features(feature) VALUES(%s)', (json.dumps(feat), ))
con.commit()
cur.close()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment