So i have a composite field that i want to use in my django models using postgres as the DB.
CREATE TYPE address_verbose AS (
contact_person_name string_not_null,
mobile string_no_spaces,
mobile_cc string_no_spaces,
email email_with_check,
address text,
city text,
state text,
country text,
pincode string_no_spaces,
location geography
);
The string_not_null, string_no_spaces and email_with_check are just domains i have created for validation.
Everything works fantastically if i use SQL, but the thing is want to use this field in a django models and i want to use Django ORM for at least creating, updating and deleting objects containing this field.
So after some google search i was able to come up with a custom model field.
from typing import Optional
from django.contrib.gis.geos import Point
from django.db import connection
from django.db import models
from psycopg2.extensions import register_adapter, adapt, AsIs
from psycopg2.extras import register_composite
address_verbose = register_composite(
'address_verbose',
connection.cursor().cursor,
globally=True
).type
def address_verbose_adapter(value):
return AsIs("(%s, %s, %s, %s, %s, %s, %s, %s, %s, ST_GeomFromText('POINT(%s %s)', 4326))::address_verbose" % (
adapt(value.contact_person_name).getquoted().decode('utf-8'),
adapt(value.mobile).getquoted().decode('utf-8'),
adapt(value.mobile_cc).getquoted().decode('utf-8'),
adapt(value.email).getquoted().decode('utf-8'),
adapt(value.address).getquoted().decode('utf-8'),
adapt(value.city).getquoted().decode('utf-8'),
adapt(value.state).getquoted().decode('utf-8'),
adapt(value.country).getquoted().decode('utf-8'),
adapt(value.pincode).getquoted().decode('utf-8'),
adapt(value.location).getquoted().decode('utf-8'),
))
register_adapter(address_verbose, address_verbose_adapter)
class AddressVerbose:
def __init__(self, contact_person_name: str, mobile: str, mobile_cc: str, email: str, address: str, city: str,
state: str, country: str, pincode: str, location: Optional[Point] = None):
self.contact_person_name = contact_person_name
self.mobile = mobile
self.mobile_cc = mobile_cc
self.email = email
self.address = address
self.city = city
self.state = state
self.country = country
self.pincode = pincode
self.location = location
class AddressVerboseField(models.Field):
def from_db_value(self, value, expression, connection):
if value is None:
return value
return AddressVerbose(
value.contact_person_name,
value.mobile,
value.mobile_cc,
value.email,
value.address,
value.city,
value.state,
value.country,
value.pincode,
value.location,
)
def to_python(self, value):
if isinstance(value, AddressVerbose):
return value
if value is None:
return value
return AddressVerbose(
value.contact_person_name,
value.mobile,
value.mobile_cc,
value.email,
value.address,
value.city,
value.state,
value.country,
value.pincode,
value.location,
)
def get_prep_value(self, value):
if not isinstance(value, AddressVerbose):
return None
return (
value.contact_person_name,
value.mobile,
value.mobile_cc,
value.email,
value.address,
value.city,
value.state,
value.country,
value.pincode,
# Attempt 1
value.location,
# Attempt 2
# "ST_GeomFromText('POINT(%s %s)', 4326)" % (value.location[0], value.location[1])
)
def db_type(self, connection):
return 'address_verbose'
And a minimal model that uses above field is
class Shipment(models.Model):
pickup_detail = AddressVerboseField()
drop_detail = AddressVerboseField()
Everything works completely fine in creating, updating or fetching a shipment object
if i was to remove the location field. But things start breaking when you include the location field in AddressVerboseField.
From django doc, i got the idea to fiddle with get_prep_value, and i tried the first approach by directly sending the location as is in the tuple. But it raised the
following error.
Seems the adapt function from psycopg2 was unable to parse the Point field, so thought of directly sending the SQL text for a point field in attempt 2. This got me close but the presence of a additional quote in the sql generated broke.
I have searched quite a lot in google about adapting the composite field of postgres to django, but all results dont really include the fields present in postgis part of the postgres. This is my first time writing a django custom field, so i might be missing or incorrect on some logic there. It would be really hepfull if someone could help me out on this or maybe point me out in the right direction. Thanks