Python json to database

Python read, validate and import CSV/JSON file to MySQL

In this post how to read, parse and load CSV/JSON file to MySQL table:

• Python with Pandas and MySQL — read CSV file(pandas), connect to MySQL(sqlalchemy), create a table with CSV data
• Read, parse and load JSON file into MySQL table- Read and parse JSON, validate data, connect and insert to MySQL(PyMySQL)

Read CSV file with Pandas and MySQL

The easiest and simplest way to read CSV file in Python and to import its date into MySQL table is by using pandas. Several useful method will automate the important steps while giving you freedom for customization:

import pandas as pd from sqlalchemy import create_engine # read CSV file column_names = ['person','year', 'company'] df = pd.read_csv('/home/user/data/test.csv', header = None, names = column_names) print(df) df = pd.read_csv('/home/user/data/test.csv', header = 0) print(df) engine = create_engine('mysql://root:@localhost/test') with engine.connect() as conn, conn.begin(): df.to_sql('csv', conn, if_exists='append', index=False) 

Open CSV file with pandas

First step is to read the CSV file data. This is done by:

df = pd.read_csv('/home/user/data/test.csv', header = None, names = column_names) print(df) 

df = pd.read_csv('/home/user/data/test.csv', header = 0) print(df) 

the difference is about headers — in first code the csv files is without headers and we provide column names. The second example the file has headers and we show that line — 0 — should be used as header.

Note also that method pd.read_csv will add index or line number to your rows and the result would be:

 person year company 0 Person Year Company 1 John 2018 Google 

Connect to MySQL DB with sqlalchemy

next step is to connect to MySQL DB ( or any other — you will need to change your module)

engine = create_engine('mysql://root:@localhost/test') 

username — is the username which will connect to DB
password — the password of the user. In case of no password than you can leave it empty. So for root without password you can use: root:
localhost — the MySQL server address
dbname — the database that will be used for your connection.

create new MySQL table from the CSV data

Final step is creating new table from the CSV data:

with engine.connect() as conn, conn.begin(): df.to_sql('csv', conn, if_exists='append', index=False) 

df.to_sql(‘csv’, conn, if_exists=’append’, index=False)

• csv — is the table name which will be created in database test.
• conn — we are creating new connection with the parameters from the previous step
• if_exists — this parameter has two options
  • append — if you want to append data to existing table
  • replace — recreate the table and insert the data into newly created table
  • False — insert the CSV data as it is
  • True — this will add column ‘index’ in the field list of your CSV. So instead of having:

  INSERT INTO csv (`Person`, `Year`, `Company`) VALUES (%s, %s, %s)'] [parameters: (('John', 2018, 'Google') 

  INSERT INTO csv (`index`, `Person`, `Year`, `Company`) VALUES (%s, %s, %s, %s)'] [parameters: ((0, 'John', 2018, 'Google') 

  Import JSON file into MySQL

  If you want to read json file and parse it’s data you have several ways to do it with Python. In this tutorial we will see how to do it by using modules: pymysql, os, json. We will validate JSON data before loading it to MySQL.

  import pymysql, os, json # read JSON file which is in the next parent folder file = os.path.abspath('../../..') + "/test.json" json_data=open(file).read() json_obj = json.loads(json_data) # do validation and checks before insert def validate_string(val): if val != None: if type(val) is int: #for x in val: # print(x) return str(val).encode('utf-8') else: return val # connect to MySQL con = pymysql.connect(host = 'localhost',user = 'root',passwd = '',db = 'test') cursor = con.cursor() # parse json data to SQL insert for i, item in enumerate(json_obj): person = validate_string(item.get("person", None)) year = validate_string(item.get("year", None)) company = validate_string(item.get("company", None)) cursor.execute("INSERT INTO testp (person, year, company) VALUES (%s, %s, %s)", (person, year, company)) con.commit() con.close() 

  Read and parse JSON with JSON

  • the first step is to read and parse the JSON file into python dict. You can open the file by absolute or relative path:

  file = '/home/user/data/test.json' 

  for i, item in enumerate(json_obj): person = validate_string(item.get("person", None)) 

  The enumerate is used in order to get index — i — just in case if you want to insert a unique key to your data. At this step is invoked method validate_string to verify JSON data. You are free to add or change validation to match your needs. Useful data integrity checks are:

  * check for duplicated data * check for incomplete data - partially required fields (fields depending on other fields) * check for required fields * check for wrong types * check for bad formats - incomplete date * method validate_string - validates if the values are type string. You can write check that meet your requirments. It's better to be used is_instance rather than direct comparisson of types. 

  Connect and insert to MySQL with pymysql

  • then we are connecting to the DB. The parameters are similar to the CSV example.
  • last step is to prepare insert method and write the data into MySQL

  cursor.execute("INSERT INTO test (person, year, company) VALUES (%s, %s, %s)", (person, year, company)) 

  For JSON you can also use pandas in order to load JSON data into DB. Both ways has their advantages and disadvantages. What you are going to use depends on you.

  This is a simple example using JSON and pandas.io:

  from pandas.io.json import json_normalize data = [< "Person": "John", "Year": 2018, "Company": "Google" >, < "Person": "Sam", "Year": 2017, "Company": "IBM" >, < "Person": "Jeff", "Year": 2014, "Company": "Yahoo" >] json_normalize(data) 

  CSV file example:

  This is the example CSV file used in this example.

  Person,Year,Company
  John,2018,Google
  Sam,2017,IBM
  Jeff,2014,Yahoo
  Allen,2015,Facebook

  By using SoftHints — Python, Linux, Pandas , you agree to our Cookie Policy.

  Источник

  How to Convert JSON to SQLite Database in Python

  To Convert Json data into SQLite database use the JSON module and sqlite3 module. JSON and sqlite3 modules are built-in python modules that help us work JSON data and SQLite databases.

  JSON Module in Python

  Json module in Python is used to read and write JSON files. To insert JSON data into SQLite database we will first need to read the data from the JSON file and then use the sqlite3 python module to insert it into the SQLite database table.

  Sqlite3 Module in Python

  The sqlite3 module is a python built-in module used to read from and write data into databases using python. sqlite3 module in python provides a utility function to deal with Sqlite databases in Python.

  Convert Json to Sqlit Database in Python

  To Convert Json to Sqlite database, first, read the JSON data using json.load() function and then use the sqlite3 module to insert it into sqlite database.Steps to insert JSON data into SQLite database

  1. Import JSON and sqlite3 modulesJSON module is used to read the JSON file and the sqlite3 module in python is used to insert data into SQLite database
  2. use the json.load() to read the JSON datajson.load() function is used to read the content in a JSON file. It returns a JSON object. which is an iterable and we can get the data by iterating on it.
  3. Create SQLite table with The required fieldsThe SQL CREATE query will help us create a table in the SQLite database. The field should be specified at the time of creating the table. The fields of the table represent columns of the table.
  4. use for loop to get each row of the JSON and convert it into a tuple or listyou can either change the row to a list or a tuple. In order to use the insert statement in SQL. converting each row into a tuple will be a Good Idea
  5. Using the SQL insert Statement to insert each row to the SQLite Databaseyou can create a formated string to build the insert query. The insert query will help us enter the data into SQLite database table. We have to specify the table name and the value that we are entering. In this case, we have provided a tuple.
  6. JSON data is successfully inserted into SQLite database tableCommit the changes and close the connection, as we have successfully entered the JSON data into SQLite database.

   import json import sqlite3 connection = sqlite3.connect('db.sqlite') cursor = connection.cursor() cursor.execute('Create Table if not exists Student (name Text, course Text, roll Integer)') traffic = json.load(open('json_file.json')) columns = ['name','course','roll'] for row in traffic: keys= tuple(row[c] for c in columns) cursor.execute('insert into Student values(. )',keys) print(f' data inserted Succefully') connection.commit() connection.close() 

  Why we use the sqlite3.connect() function

  To make a connection to the database and make it active we use the sqlite3.connect() function. It takes one parameter. the parameter it takes is the path to the SQLite database. The path should be provided as a parameter to this function to make a connection. If the database file is not available in that path, It will automatically create a new database file.Example of sqlite3.connect() function.

  import sqlite3 connection = sqlite3.connect('database.db') 

  After the execution of this line, a file with the name of database.db is created in the current working directory.

  What is the difference between json.load() and json.loads() functions

  The only diffence between the json.load() and json.loads() funciton is that json.load() funtion convert the json file data into a string and json.loads() funciotn convert the json file data into python dictionay object.

  I am a software Engineer having 4+ Years of Experience in Building full-stack applications.

  Источник

  How to Convert JSON to SQL format In Python

  In this tutorial we will see how to convert JSON – Javascript Object Notation to SQL data format such as sqlite or db.

  We will be using Pandas for this.

  Installation

  pip install pandas sqlalchemy

  Method 1 : Using Sqlite3

  import pandas as pd import json import sqlite # Open JSON data with open("datasets.json") as f: data = json.load(f) # Create A DataFrame From the JSON Data df = pd.DataFrame(data)

  Now we need to create a connection to our sql database. We will be using sqlite for that.

  import sqlite3conn = sqlite3.connect("data.db")c = conn.cursor()

  We can now convert our JSON data from dataframe to sqlite format such as db or sqlite.

  Note: The first argument is the table name you will be storing your data in.

  Method 2 : Using SQLalchemy

  We can also use the sqlalchemy instead of sqlite3.

  from sqlalchemy import create_engine engine = create_engine("sqlite:///my_data.db") df.to_sql("table_name",conn=engine)

  In case you want to automatically get the columns and their datatype in sqlite for creating a class model schema, you can use the .schema option

  sqlite3 my_data.db >>> .table >>> .schema book

  You can also check the video tutorial here

  Thanks For Your Time

  Jesus Saves

  By Jesse E.Agbe(JCharis)

  2 thoughts on “How to Convert JSON to SQL format In Python”

  Your tutorials are the best for what I need Jesse. I have purchased your courses on Udemy as they are so beneficial. Thanks! God bless,
  Tom

  Источник

  Store JSON Data into MySQL Using Python: A Simple Guide – Python Tutorial

  Json is a good format to share data in different applications, it is also widely used in python. In this tutorial, we will introduce python beginners on how to save json data into a mysql database.

  

  Prepare json data

  You can serialize a python object to json data, you also can create a json string data manually.

  As to serialize a python object to json data, you can read this tutorial.

  Here we create a json string by manually.

  Create a MySQLUtil instance to insert json into mysql

  Here we use a python MySQLUtil class to operate mysql. The class is in:

  We can connect mysql using MySQLUtil instance first.

  mysql = MySQLUtil() mysql.connectDB(host ='localhost', user = 'root', psw = '', db_name = 'test')

  We will save json data into test database.

  Insert json data into mysql

  We can use mysql.execSql() to insert json data. Here is an example.

  json_data = pymysql.escape_string(json_data) sql = "insert into all_tag ( index_name) values ('" + json_data + "') " mysql.execSql(sql)

  After inserting json, we should close mysql connection.

  Run this code, you will see the result:

  

  We have found that the json data has been saved into mysql.

  Moreover, if you plan to protect your json data, you can encrypt json before inserting and decrypt it after selecting.

  We can encrypt json with base64 algorithm before inserting it into mysql.

  To encrypt json with base64 algorithm, you can refer these tutorials.

  json_data = "" json_data = urlsafe_b64encode(json_data) sql = "insert into all_tag ( index_name) values ('" + json_data + "') " mysql.execSql(sql)

  Источник