Python json from sql

Sql2json: sql2json is a tool to query a sql database and write result in JSON or CSV format in standard output or external file

sql2json help you to automate repetitive tasks. For example i need a cronjob to extract yesterday sales and sent it to geckoboard.

This tool is focused to use to automate command line apps or cron jobs to extract data from sql databases

Why sql2json if you can write to csv and excel file

Good question. That was true until version 0.1.9. After some time i need to support csv files too, but i don’t want to change the library name(sql2what or sqltowhat). Or create some new like sql2csv and sql2excel.

How install sql2json

Default output format

The default output format is json.

Limitations

sql2json config file

sql2json by default use a config file located at USER_HOME/.sql2json/config.json

< "conections": < "default": "sqlite:///test.db", "postgress": "postgresql://scott:tiger@localhost:5432/mydatabase", "mysql": "mysql://scott:tiger@localhost/foo" >, "queries": < "default": "SELECT 1 AS a, 2 AS b", "sales_month_since": "SELECT inv.month, SUM(inv.amount) AS sales FROM invoices inv WHERE inv.date >= :date_from ", "total_sales_since": "SELECT SUM(inv.amount) AS sales FROM invoices inv WHERE inv.date >= :date_from ", "long_query": "@FULL_PATH_TO_SQL_FILE", "json": "SELECT JSON_OBJECT('id', 87, 'name', 'carrot') AS json", "jsonarray": "SELECT JSON_ARRAY(1, 'abc', NULL, TRUE) AS jsonarray, JSON_OBJECT('id', 87, 'name', 'carrot') AS jsonobject", "operation_parameters": "@/Users/myusername/myproject/my-super-query.sql" > > 

Use a config.json in a different path

You can use sql2json —config PATH_TO_YOUR_CONFIG_FILE

Available variables to do your life easy:

• START_CURRENT_MONTH: Date the first day of current month
• CURRENT_DATE: Current Date
• END_CURRENT_MONTH: Date the last day of current month
• START_CURRENT_YEAR: First day of current year
• END_CURRENT_YEAR: First day of current year

Operations in variables

• You can use + or — operator in your querys with variables CURRENT_DATE, START_CURRENT_MONTH, END_CURRENT_MONTH
• +1, -1 in CURRENT_DATE mean +1 day, -1 day
• +1, -1 in START_CURRENT_MONTH, END_CURRENT_MONTH mean +1 month, -1 month
• +1, -1 in START_CURRENT_YEAR, END_CURRENT_YEAR mean +1 year, -1 year

Date formats to CURRENT_DATE, START_CURRENT_MONTH, END_CURRENT_MONTH, START_CURRENT_YEAR, END_CURRENT_YEAR

You can use date format supported by python datetime.strftime function, default is %Y-%m-%d

How to run queries using sql2json:

Run query sales_month in database conection mysql:

python3 -m sql2json —name mysql —query sales_month_since —date_from «START_CURRENT_MONTH-1»

I don’t wat an array, i want an object with an atribute with the results, useful to generate in format to post to geckoboard

python3 -m sql2json —name mysql —query sales_month_since —date_from «START_CURRENT_MONTH-1» —wrapper

Run query sales_month in database conection mysql, use month as key, sales as value:

python3 -m sql2json —name mysql —query sales_month_since —date_from «START_CURRENT_MONTH-1» —key month —value sales

Run query sales_month in database conection mysql, get the unique row and only sales amount:

python3 -m sql2json —name mysql —query total_sales_since —date_from «CURRENT_DATE-10» —first —key sales

Output: 500 or the amount of money you sold since 10 days ago

When i use sql2json with result of JSON functions i get escaped strings as value

sql2json as a flag to allow you specify your JSON columns

python3 -m sql2json —name mysql —query json —jsonkeys «json, jsonarray»

[ < "json": < "id": 87, "name", "carrot" >"jsonarray": [1, "abc", null, true], > ] 

This is only a row i want first row only, no array.

python3 -m sql2json —name mysql —query json —jsonkeys «json, jsonarray» —first

  < "json": < "id": 87, "name", "carrot" >"jsonarray": [1, "abc", null, true], > 

Run query in external sql file:

query «operation_parameters» Path «Users/myusername/myproject/my-super-query.sql»

Content of my-super-query.sql:

SELECT p.name, p.age FROM persons p WHERE p.age > :min_age AND p.creation_date > :min_date ORDER BY p.age DESC LIMIT 10 

min_age: 18 min_date: Today YYYY-MM-DD 00:00:00

python3 -m sql2json —name mysql —query operation_parameters —min_age 18 —min_date CURRENT_DATE|%Y-%m-%d 00:00:00

min_age: 18 min_date: First day, current year YYYY-01-01 00:00:00

python3 -m sql2json —name mysql —query operation_parameters —min_age 18 —min_date START_CURRENT_YEAR|%Y-%m-%d 00:00:00

Run external SQL query not defined in config file

python3 -m sql2json —name mysql —query «@/Users/myusername/myproject/my-super-query.sql» —min_age 18 —min_date START_CURRENT_YEAR|%Y-%m-%d 00:00:00

Run custom query inline

You do’t need to have all your queries in config file

python3 -m sql2json —name mysql —query «SELECT NOW() AS date» —first —key date

Write data to a csv file

python3 -m sql2json —name mysql —query sales_month_since —date_from «START_CURRENT_MONTH-1» —format=csv —output Sales

Write data to an excel file

python3 -m sql2json —name mysql —query sales_month_since —date_from «START_CURRENT_MONTH-1» —format=excel —output Sales

Write data to a json file

python3 -m sql2json —name mysql —query sales_month_since —date_from «START_CURRENT_MONTH-1» —format=json —output Sales

Write data to a file with custom filename using formula

python3 -m sql2json —name mysql —query sales_month_since —date_from «START_CURRENT_MONTH» —format=csv —output Sales__

If Current Date is "2020-05-24" Sales_2020-05-01_2020-05-24.csv 

python3 -m sql2json —name mysql —query sales_month_since —date_from «CURRENT_DATE» —format=csv —output sales/Sales_

If Current Date is "2020-05-24" File Sales_2020-05-24.csv inside folder sales. sales/Sales_2020-05-24.csv 

IMPORTANT: The sales folder is not created. You need to create it in your own.

Источник

How to convert MySQL query result to JSON in Python

In this article, you will learn a simple process to convert MySQL query results to JSON using the Python programming.

JSON (JavaScript Object Notation) is a lightweight, open standard file format. It is easy for humans to read and write. It is easy to read and write for humans. It is used primarily to transmit data between a web application and a server. JSON is popular among developers for data serialization. It is so popular that every modern programming language has methods to generate and parse JSON formatted data.

Install MySQL Connector

A connector is a bridge when we have to connect a database with a programming language. It provides access to a database driver to the required language. Python MySQL installer available for different operating systems. You can download the MSI version from here-

The installer requires ‘python.exe‘ in your system PATH; otherwise, it will fail to install. So make sure to add Python to your system environment.

You can also install MySQL Connector using the pip command.

pip install mysql-connector

Python connecting to MySQL

First, we need to import the MySQL connector into the Python file to connect to the database. Then, use the connect() constructor to create a connection to the MySQL server. Make sure to replace the ‘user’, ‘password’, ‘host’, and ‘database’ with your database credentials.

import mysql.connector conn = mysql.connector.connect(user='root', password='', host='localhost',database='company') if conn: print ("Connected Successfully") else: print ("Connection Not Established")

Python MySQL Select Table

Here is the MySQL select statement to fetch data from the ‘employee‘ table.

mydict = create_dict() select_employee = """SELECT * FROM employee""" cursor = conn.cursor() cursor.execute(select_employee) result = cursor.fetchall() for row in result: mydict.add(row[0],())

In the above code, first we have instantiated the create_dict class and called the add() method to store the fetched data in a key-value pair. The class is defined as follows-

class create_dict(dict): # __init__ function def __init__(self): self = dict() # Function to add key:value def add(self, key, value): selfPython json from sql = value 

Import JSON Module

The JSON module provides the dumps() method for writing data to files. This method takes a data object to be serialized, and the indent attribute specifies the indentation size for nested structures. The sort_keys attribute is a boolean value. If it is TRUE, then the output of dictionaries will be sorted by key.

stud_json = json.dumps(mydict, indent=2, sort_keys=True) print(stud_json) 

Complete Code: Convert MySQL query result to JSON

Here, we have merged the above code that we explained in chunks to convert the MySQL query results to JSON using the Python programming language.

import mysql.connector import json conn = mysql.connector.connect(user='root', password='', host='localhost',database='company') if conn: print ("Connected Successfully") else: print ("Connection Not Established") class create_dict(dict): # __init__ function def __init__(self): self = dict() # Function to add key:value def add(self, key, value): selfPython json from sql = value mydict = create_dict() select_employee = """SELECT * FROM employee""" cursor = conn.cursor() cursor.execute(select_employee) result = cursor.fetchall() for row in result: mydict.add(row[0],()) stud_json = json.dumps(mydict, indent=2, sort_keys=True) print(stud_json) 

The above code returns output in JSON format as follows-

Источник

Python psycopg2 как получить данные и передать в json

Admin 07.06.2020 Python

На примере ниже разберем как получить данные из БД, отсортировать по дате и вернуть в json формате.

Пример кода

import json
import psycopg2
from psycopg2 import sql
from psycopg2. extras import NamedTupleCursor

from datetime import datetime

def get_all_data ( self ) :
«»» Returning key-value pair «»»
with self . connect . cursor ( cursor_factory = psycopg2. extras . RealDictCursor ) as cursor:
self . connect . autocommit = True

query = ‘SELECT * FROM stocks ORDER BY date DESC’
cursor. execute ( query )

# Create new List and Dicts inside it
dic = { }
stocks = [ ]
for stock in fetch:
dic [ ‘currency’ ] = stock [ ‘currency’ ] ,
dic [ ‘price’ ] = stock [ ‘price’ ] ,
dic [ ‘quantity’ ] = stock [ ‘quantity’ ] ,
dic [ ‘date’ ] = stock [ ‘date’ ] . strftime ( «%Y-%m-%d %H:%M:%S» )
stocks. append ( dic. copy ( ) )

# Sort by date where last row would be last date (just in case)
stocks = sorted (
stocks ,
key = lambda x: datetime . strptime ( x [ ‘date’ ] , ‘%Y-%m-%d %H:%M:%S’ ) , reverse = False
)

return json. dumps ( stocks , separators = ( ‘,’ , ‘:’ ) )

Разбор кода

connect.cursor

Нам нужно получить объект в виде словаря для его дальнейшей обработки:

Так мы получим данные в таком формате:

[RealDictRow([(‘name’, ‘Macerich’), (‘price’, 11.3), (‘quantity’, 200), (‘date’, datetime.datetime(2020, 6, 5, 23, 0, 29, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)))]),
RealDictRow([(‘name’, ‘Macerich’), (‘price’, 11.3), (‘quantity’, 200), (‘date’, datetime.datetime(2020, 6, 5, 23, 0, 29, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)))])]

Если обработка не нужна, можно забрать данные в виде кортежа (но что-то изменить внутри потом будет нельзя):

[Record([(‘name’, ‘Macerich’), (‘price’, 11.3), (‘quantity’, 200), (‘date’, datetime.datetime(2020, 6, 5, 23, 0, 29, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)))]),
Record([(‘name’, ‘Macerich’), (‘price’, 11.3), (‘quantity’, 200), (‘date’, datetime.datetime(2020, 6, 5, 23, 0, 29, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)))])]

Если ключи не нужны можно без содержимого в скобках:

[(148, ‘Macerich’, 11.3, 200, 2260.0, -0.57, ‘Sell’, datetime.datetime(2020, 6, 5, 23, 0, 29, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None))),…

Преобразование даты

Здесь мы преобразовываем дату:

(‘date’, datetime.datetime(2020, 6, 5, 23, 0, 29, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)))

Как добавить в лист внутрь цикла словарь

Для этого мы используем метод copy():

Источник