Python fetchall to json

Python converting mysql query result to json

You can use cursor description to extract row headers: row_headers=[x[0] for x in cursor.description] after the execute statement. Then you can zip it with the result of sql to produce json data. So your code will be something like:

from flask import Flask from flask.ext.mysqldb import MySQL import json app = Flask(__name__) app.config['MYSQL_HOST'] = '127.0.0.1' app.config['MYSQL_USER'] = 'root' app.config['MYSQL_PASSWORD'] = 'password' app.config['MYSQL_DB'] = 'hello_db' mysql = MySQL(app) @app.route('/hello') def index(): cur = mysql.connection.cursor() cur.execute('''SELECT * FROM Users WHERE row_headers=[x[0] for x in cur.description] #this will extract row headers rv = cur.fetchall() json_data=[] for result in rv: json_data.append(dict(zip(row_headers,result))) return json.dumps(json_data) if __name__ == '__main__': app.run(debug=True) 

In the return statement you can use jsonify instead of json.dumps as suggested by RickLan in the comments.

From your output it seems like you are getting a tuple back? In which case you should be able to just map it.

from flask import Flask, jsonify from flask.ext.mysqldb import MySQL app = Flask(__name__) app.config['MYSQL_HOST'] = '127.0.0.1' app.config['MYSQL_USER'] = 'root' app.config['MYSQL_PASSWORD'] = 'password' app.config['MYSQL_DB'] = 'hello_db' mysql = MySQL(app) @app.route('/hello') def index(): cur = mysql.connection.cursor() cur.execute('''SELECT * FROM Users WHERE rv = cur.fetchall() payload = [] content = <> for result in rv: content = payload.append(content) content = <> return jsonify(payload) if __name__ == '__main__': app.run(debug=True) 

There is, perhaps, a simpler way to do this: return a dictionary and convert it to JSON.

Just pass dictionary=True to the cursor constructor as mentioned in MySQL’s documents.

import json import mysql.connector db = mysql.connector.connect(host='127.0.0.1', user='admin', passwd='password', db='database', port=3306) # This line is that you need cursor = db.cursor(dictionary=True) name = "Bob" cursor.execute("SELECT fname, lname FROM table WHERE fname=%s;", (name)) result = cursor.fetchall() print(f"json: ") 

(Assuming those Bobs are in the table.)

Note that types are preserved this way, a good thing, BUT will need to be transformed, parsed, or serialized into a string; for instance, if there is a date, the SQL query may return a datetime object, which will need to be parsed or serialized depending on your next step. A great way to serialize is in this answer.

Источник

Return SQL Data in JSON Format in Python : Stepwise Solution

To Return SQL Data in JSON Format in Python is easily possible in just two steps establishing the connection and fetching the data with SQL Database and then converting the data into JSON Format. Now the fetching part is not that tricky but converting the data into SQL format is possible in multiple contexts which depends on the business problems which opt for. Let’s start.

Return SQL Data in JSON Format in Python ( Solution ) –

As I explained earlier the first step is common in all the approaches to fetching data and then the second is converting the data into JSON format.

Step 1: Retrieving the data from the SQL Server –

To achieve this there are some substeps that will be common across all the SQL Server & Databases but the implementation syntax may vary. To elaborate and explain we are referencing the syntax related to MySQL Databases :

1.1 Establishing the connection –

Here we need to provide the connection string/config as follows :

db_config = < 'host': 'localhost', 'user': 'root', 'password': 'password', 'database': 'my_db' >conn = mysql.connector.connect(**db_config)

These are default configs but the user / DBA can alter them as the application requires.

1.2 Query Execution and result fetching

In this step, we will create a cursor and execute the query on the SQL Server. Please refer to the below coding part.

cursor = conn.cursor() cursor.execute('SELECT * FROM TABLE') obj = cursor.fetchall()

Here the query will be changed as per the application requirement.

Step 2: Converting SQL Query Output into JSON Format

JSON is a key pair data structure that is equivalent to dict data structure format. Hence either we somehow convert the query output into key-value pair by assigning some random key and then convert the dict into JSON Format. OR there is a direct conversion possible where we will retrieve the output in tabular format and then direct convert the output into JSON object. Well, let’s walk through both ways.

Option 1: Converting the Query output to dict and then convert it into JSON –

Here we will take a reference key and then assign the output as a value for the key. This will create the query output into a python dict object. After it, we will use Python JSON Library to convert the same JSON Format. Here is the complete implementation.

import json dict_obj = < 'key': obj >json_obj = json.dumps(dict_obj)

Option 2: Fetching the Query Result as Pandas object and then convert it into JSON-

Here we will first fetch the data into pandas dataframe and then convert the dataframe to JSON Object To make it easier, Here is the complete code for reference.

import mysql.connector as sql import pandas as pd db_connection = sql.connect(host='hostname', database='db_name', user='username', password='password') db_cursor = db_connection.cursor() db_cursor.execute('SELECT * FROM TABLE') table_rows = db_cursor.fetchall() df = pd.DataFrame(table_rows) result = df.to_json(orient="records") parsed = json.loads(result) json.dumps(parsed, indent=4) 

This single code will Return SQL Data in JSON Format in Python. Another way to achieve the same is explicitly mentioned in the below article as well –

pandas read_sql() method implementation with Examples

Additional Notes –

The Reverse is also possible. For more information please refer to the below article.

pandas to_sql method: Insert datasets values to the Database

Thanks
Data Science Learner Team

Join our list

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.

We respect your privacy and take protecting it seriously

Thank you for signup. A Confirmation Email has been sent to your Email Address.

Источник

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 fetchall to json = 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 fetchall to json = 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():

Источник