Python row to dict

Convert Pandas dataframe to dictionary

Pandas Dataframe to Dictionary by Rows

Let’s change the orient of this dictionary and set it to index

Now the Dictionary key is the index of the dataframe and values are each row

The first index of dataframe is 0 which is the first key of dictionary and has a dictionary of a row as value and each value inside this dictionary has column header as Key

Dataframe to Dictionary with values as list

Now change the orient to list and see what type of dictionary we get as an output

It returns Column Headers as Key and all the row as list of values

Pandas Dataframe to List of Dictionary

Let’s change the orient to records and check the result

it returns the list of dictionary and each dictionary contains the individual rows

Dataframe to Dictionary with one Column as Key

So we are setting the index of dataframe as Name first and then Transpose the Dataframe and convert it into a dictionary with values as list

df.set_index('Name').T.to_dict('list') 

It returns Name as Key and the other values Age and City as list

Converting Timestamp data to dictionary

Let’s see how to_dict function works with timestamp data

Let’s create a simple dataframe with date and time values in it

tsmp = Timestamp("20200101") data = DataFrame() data 

It returns list of dictionary and each row values is a dictionary having colum label as key and timestamp object as their values

Let’s take another example of dataframe with datetime object and timezone parameter info

data = [ (datetime(2017, 11, 18, 21, 53, 0, 219225, tzinfo=pytz.utc),), (datetime(2017, 11, 18, 22, 6, 30, 61810, tzinfo=pytz.utc),), ] df = DataFrame(list(data), columns=["d"]) result = df.to_dict(orient="records") result 

It returns the list of dictionary with timezone info

Dataframe to OrderedDict and defaultdict

to_dict() Into parameter:

You can specify the type from the collections.abc.Mapping subclass used for all Mappings in the return value

For example: the into values can be dict, collections.defaultdict, collections.OrderedDict and collections.Counter.

Let’s take a look at these two examples here for OrderedDict and defaultdict

from collections import OrderedDict, defaultdict test_data = , "B": > 

DataFrame(test_data).to_dict(into=OrderedDict) 

OrderedDict([('A', OrderedDict([(0, Timestamp('2013-01-01 00:00:00')), (1, Timestamp('2013-01-01 00:00:00'))])), ('B', OrderedDict([(0, Timestamp('2013-01-01 00:00:00')), (1, Timestamp('2013-01-01 00:00:00'))]))]) 

DataFrame(test_data).to_dict(into=defaultdict(list)) 

defaultdict(list, ), 'B': defaultdict(list, )>) 

DataFrame(test_data).to_dict(into=dict) 

Convert a Pandas Groupby to Dictionary

You can also group the values in a column and create the dictionary. Let’s understand this with the help of this simple example

Serial_No	Segment	Area
0	0	A	23
1	1	B	45
2	1	C	64
3	1	C	23
4	2	B	64
5	2	A	65
6	3	B	23
7	3	A	45
8	4	D	23
9	5	A	64
10	5	A	23
11	5	C	64

We will group the above dataframe by column Serial_No and all the values in Area column of that group will be displayed as list

df.groupby(['Serial_No'])['Area'].apply(list).to_dict() 

Convert Dataframe to Nested Dictionary

This is a very interesting example where we will create a nested dictionary from a dataframe

Let’s create a dataframe with four columns Name, Semester, Subject and Grade

Name	Sem	Subject	Grade
0	John	Sem1	Mathematics	A
1	Sara	Sem1	Biology	B
2	John	Sem2	Biology	A+
3	Sara	Sem2	Mathematics	B++

Now we are interested to build a dictionary out of this dataframe where the key will be Name and the two Semesters (Sem 1 and Sem 2) will be nested dictionary keys and for each Semester we want to display the Grade for each Subject.

For example: John data should be shown as below

As you can see in the following code we are using a Dictionary comprehension along with groupby to achieve this.

We have set the index to Name and Sem which are the Keys of each dictionary and then grouping this data by Name

And iterating this groupy object inside the dictionary comprehension to get the desired dictionary format

ppdict = print (json.dumps(ppdict, indent=2)) 

Conclusion

So just to summarize our key learning in this post, here are some of the main points that we touched upon:

• How to convert a dataframe into a dictionary using to_dict() function
• Using the oriented parameter to customize the result of our dictionary
• into parameter can be used to specify the return type as defaultdict, Ordereddict and Counter
• How a data with timestamp and datetime values can be converted into a dictionary
• Using groupby to group values in one column and converting the values of another column as list and finally converting it into a dictionary
• Finally how to create a nested dictionary from your dataframe using groupby and dictionary comprehension

Updated: March 24, 2020

Источник

Convert sqlalchemy row object to python dict

You may access the internal __dict__ of a SQLAlchemy object, like the following. note that this is the correct answer for modern versions of SQLAlchemy, assuming «row» is a core row object, not an ORM-mapped instance.,The expression you are iterating through evaluates to list of model objects, not rows. So the following is correct usage of them:,Assuming the following functions will be added to the class User the following will return all key-value pairs of all columns:

You may access the internal __dict__ of a SQLAlchemy object, like the following::

for u in session.query(User).all(): print u.__dict__ 

Answer by Kailey Case

When using the ORM to retrieve objects, this is not available by default. The SQLAlchemy inspection system should be used.,If you’re querying columns individually, the row is a KeyedTuple which has an _asdict method. The method name starts with a single underscore, to match the namedtuple API (it’s not private!).,Here, we created a function to do the conversion, but one option would be to add a method to the base class., Converting a query result to dict

First the setup for the example:

import datetime as dt from sqlalchemy import Column, Date, Integer, Text, create_engine, inspect from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() Session = sessionmaker() class User(Base): __tablename__ = 'users' primary_key=True) name = Column(Text, nullable=False) birthday = Column(Date) engine = create_engine('sqlite://') Base.metadata.create_all(bind=engine) Session.configure(bind=engine) session = Session() session.add(User(name='Alice', birthday=dt.date(1990, 1, 1))) session.commit() 

If you’re querying columns individually, the row is a KeyedTuple which has an _asdict method. The method name starts with a single underscore, to match the namedtuple API (it’s not private!).

query = session.query(User.name, User.birthday) for row in query: print(row._asdict()) 

When using the ORM to retrieve objects, this is not available by default. The SQLAlchemy inspection system should be used.

def object_as_dict(obj): return query = session.query(User) for user in query: print(object_as_dict(user)) 

Instead of using declarative_base as above, you can create it from your own class:

from sqlalchemy.ext.declarative import as_declarative @as_declarative() class Base: def _asdict(self): return

Answer by Arianna Duffy

Is there a simple way to iterate over column name and value pairs?,Here is the sample code where I tried using dict(row), but it throws exception , TypeError: ‘User’ object is not iterable,My version of sqlalchemy is 0.5.6,It simply returns a list of (key, value) tuples. So one can convert a row to dict using the following:

Here is the sample code where I tried using dict(row), but it throws exception , TypeError: ‘User’ object is not iterable

import sqlalchemy from sqlalchemy import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker print "sqlalchemy version:",sqlalchemy.__version__ engine = create_engine('sqlite:///:memory:', echo=False) metadata = MetaData() users_table = Table('users', metadata, Column('id', Integer, primary_key=True), Column('name', String), ) metadata.create_all(engine) class User(declarative_base()): __tablename__ = 'users' primary_key=True) name = Column(String) def __init__(self, name): self.name = name Session = sessionmaker(bind=engine) session = Session() user1 = User("anurag") session.add(user1) session.commit() # uncommenting next line throws exception 'TypeError: 'User' object is not iterable' #print dict(user1) # this one also throws 'TypeError: 'User' object is not iterable' for u in session.query(User).all(): print dict(u) 

Running this code on my system outputs:

sqlalchemy version: 0.5.6 Traceback (most recent call last): File "untitled-1.py", line 37, in print dict(u) TypeError: 'User' object is not iterable 

Answer by Kenna Nunez

The Row object actually behaves like a Python named tuple, so we may also access these attributes from the row itself using attribute access:,bindparam() constructs of the same name can also be used multiple times, where only a single named value is needed in the execute parameters:,This pattern is now deprecated and will be removed in SQLAlchemy 2.0, so that the Row object may now behave fully like a Python named tuple.,When executing multiple sets of parameters, each dictionary must have the same set of keys; i.e. you cant have fewer keys in some dictionaries than others. This is because the Insert statement is compiled against the first dictionary in the list, and it’s assumed that all subsequent argument dictionaries are compatible with that statement.

>>> import sqlalchemy >>> sqlalchemy.__version__ 1.4.0

Answer by Jolene Jensen

This is the approach I generally use to convert an sqlalchemy object collection to a python dict.,If you know about any alternative or better approach, please share.,http://stackoverflow.com/questions/1958219/convert-sqlalchemy-row-object-to-python-dict,Check out Marshmallow. It’s a data (de)serialization library. It’s ORM and Framework independent. I highly recommend it because it’s robust and extensible.

for u in session.query(User).all(): print u.__dict__ 

Answer by Sean Burgess

Snowflake SQLAlchemy uses the following connection string syntax to connect to Snowflake and initiate a session:,Snowflake SQLAlchemy runs on the top of the Snowflake Connector for Python as a dialect to bridge a Snowflake database and SQLAlchemy applications.,Using the Snowflake SQLAlchemy Toolkit with the Python Connector,However, Snowflake SQLAlchemy also provides Snowflake-specific parameters and behavior, which are described in the following sections.

pip install --upgrade snowflake-sqlalchemy 

Answer by Kaliyah Greene

Attempts to convert values of non-string, non-numeric objects (like decimal.Decimal) to floating point. Useful for SQL result sets.,Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported.,SQL query to be executed.,Read SQL query or database table into a DataFrame.

Dict of where format string is strftime compatible in case of parsing string times, or is one of (D, s, ns, ms, us) in case of parsing integer timestamps.

Источник