SQLAlchemy insert values into reflected table results in NULL entries all across

user_12349857 0
Viewed 1267

The following code results in None () across the row in every attempt. The query.values() code below is just a shortened line so as to keep things less complicated. Additionally I have problems inserting a dict as JSON in the address fields but that's another question.

CREATE TABLE public.customers (
    id SERIAL,
    email character varying(255)  NULL,
    name character varying(255)  NULL,
    phone character varying(16)  NULL,
    address jsonb  NULL,
    shipping jsonb  NULL,
    currency character varying(3)  NULL,
    metadata jsonb[]  NULL,
    created bigint  NULL,
    uuid uuid DEFAULT uuid_generate_v4() NOT NULL,
    PRIMARY KEY (uuid)
);

from sqlalchemy import *
from sqlalchemy.orm import Session

# Create engine, metadata, & session
engine = create_engine('postgresql://postgres:password@database/db', future=True)
metadata = MetaData(bind=engine)
session = Session(engine)

# Create Table
customers = Table('customers', metadata, autoload_with=engine)

query = customers.insert()
query.values(email="test@test.com", \
             name="testy testarosa", \
             phone="+12125551212", \
             address='{"city": "Cities", "street": "123 Main St", \
                        "state": "CA", "zip": "10001"}')


session.execute(query)
session.commit()
session.close()

# Now to see results
stmt = text("SELECT * FROM customers")
response = session.execute(stmt)

for result in response:
    print(result)

# Results in None in the fields I explicitly attempted 
(1, None, None, None, None, None, None, None, 1, None, None, None, None, UUID('9112a420-aa36-4498-bb56-d4129682681c'))
sqlalchemy postgresql sql python
2 Answers

Calling query.values() returns a new insert instance, rather than modifying the existing instance in-place. This return value must be assigned to a variable otherwise it will have no effect.

You could build the insert iteratively

query = customers.insert()
query = query.values(...)
session.execute(query)

or chain the calls as Karolus K. suggests in their answer.

query = customers.insert().values(...)

Regarding the address column, you are inserting a dict already serialised as JSON. This value gets serialised again during insertion, so the value in the database ends up looking like this:

test# select address from customers;
                                                       address                                                        
══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
 "{\"city\": \"Cities\", \"street\": \"123 Main St\",                         \"state\": \"CA\", \"zip\": \"10001\"}"
(1 row)

and is not amenable to being queried as a JSON object (because it's a JSONified string)

test# select address->'state' AS state from customers;
 state 
═══════
 ¤
(1 row)

You might find it better to pass the raw dict instead, resulting in this value being stored in the database:

test# select address from customers;
                                  address                                   
════════════════════════════════════════════════════════════════════════════
 {"zip": "10001", "city": "Cities", "state": "CA", "street": "123 Main St"}
(1 row)

which is amenable to being queried as a JSON object:

test# select address->'state' AS state from customers;
 state 
═══════
 "CA"
(1 row)
user_53209060

I am not sure what do you mean with

The query.values() code below is just a shortened line so as to keep things less complicated.

So maybe I am not understanding the issue properly. At any case the problem here is that you execute the insert() and the values() separately, while it is meant to be "chained".

Doing something like:

query = customers.insert().values(email="test@test.com", name="testy testarosa", phone="+12125551212", address='{"city": "Cities", "street": "123 Main St", "state": "CA", "zip": "10001"}')

will work.

Documentation: https://docs.sqlalchemy.org/en/14/core/selectable.html#sqlalchemy.sql.expression.TableClause.insert

PS: I did not faced any issues with the JSON field as well. Perhaps something with PG version?

user_56772980
Related