How can I import a JSON file into PostgreSQL?
You can feed the JSON into a SQL statement that extracts the information and inserts that into the table. If the JSON attributes have exactly the name as the table columns you can do something like this:
<span class="kwd">with</span><span class="pln"> customer_json </span><span class="pun">(</span><span class="pln">doc</span><span class="pun">)</span> <span class="kwd">as</span> <span class="pun">(</span> <span class="kwd">values</span> <span class="pun">(</span><span class="str">'[ { "id": 23635, "name": "Jerry Green", "comment": "Imported from facebook." }, { "id": 23636, "name": "John Wayne", "comment": "Imported from facebook." } ]'</span><span class="pun">::</span><span class="pln">json</span><span class="pun">)</span> <span class="pun">)</span> <span class="kwd">insert</span> <span class="kwd">into</span><span class="pln"> customer </span><span class="pun">(</span><span class="pln">id</span><span class="pun">,</span><span class="pln"> name</span><span class="pun">,</span><span class="pln"> comment</span><span class="pun">)</span> <span class="kwd">select</span><span class="pln"> p</span><span class="pun">.*</span> <span class="kwd">from</span><span class="pln"> customer_json l </span><span class="kwd">cross</span> <span class="kwd">join</span><span class="pln"> lateral json_populate_recordset</span><span class="pun">(</span><span class="kwd">null</span><span class="pun">::</span><span class="pln">customer</span><span class="pun">,</span><span class="pln"> doc</span><span class="pun">)</span> <span class="kwd">as</span><span class="pln"> p </span><span class="kwd">on</span><span class="pln"> conflict </span><span class="pun">(</span><span class="pln">id</span><span class="pun">)</span><span class="pln"> do </span><span class="kwd">update</span> <span class="kwd">set</span><span class="pln"> name </span><span class="pun">=</span><span class="pln"> excluded</span><span class="pun">.</span><span class="pln">name</span><span class="pun">,</span><span class="pln"> comment </span><span class="pun">=</span><span class="pln"> excluded</span><span class="pun">.</span><span class="pln">comment</span><span class="pun">;</span>
New customers will be inserted, existing ones will be updated. The “magic” part is the
<span style="color: #242729;">json_populate_recordset(null::customer, doc)</span>
which generates a relational representation of the JSON objects.
Can’t connect to remote MySQL server with error 61
Modify your configuration:
<span class="pln">nano </span><span class="pun">/</span><span class="pln">etc</span><span class="pun">/</span><span class="pln">mysql</span><span class="pun">/</span><span class="pln">my</span><span class="pun">.</span><span class="pln">cnf </span><span class="pun">#</span><span class="pln"> Edit</span><span class="pun">:</span><span class="pln"> bind-address </span><span class="pun">=</span> <span class="lit">0.0.0.0</span>
Enter mysql and give privileges:
<span class="pln">mysql </span><span class="pun">-</span><span class="pln">umyuser </span><span class="pun">-</span><span class="pln">pmypassword </span><span class="pun">#</span><span class="pln"> Run</span><span class="pun">:</span> <span class="kwd">GRANT</span> <span class="kwd">ALL</span><span class="pln"> PRIVILEGES </span><span class="kwd">ON</span> <span class="pun">*.*</span> <span class="kwd">TO</span> <span class="str">'myuser'</span><span class="pun">@</span><span class="str">'%'</span><span class="pln">IDENTIFIED </span><span class="kwd">BY</span> <span class="str">'mypassword'</span> <span class="kwd">WITH</span> <span class="kwd">GRANT</span> <span class="kwd">OPTION</span><span class="pun">;</span><span class="pln"> FLUSH PRIVILEGES</span><span class="pun">;</span> <span class="kwd">exit</span>
pytest: helps you write better programs
The
<span class="pre">pytest</span>
framework makes it easy to write small tests, yet scales to support complex functional testing for applications and libraries.An example of a simple test:
# content of test_sample.py def inc(x): return x + 1 def test_answer(): assert inc(3) == 5