I'm trying to look for a method with the following:
IN: <SOME_PYTHON_VALUE>
OUT: val::type_cast
and...
IN: [<SOME_PYTHON_VAL>,<SOME_PYTHON_VAL>]
OUT: '{val,val}'::type_cast[]
Add a comment
|
1 Answer
'''
>>> py_to_pg([1,2,3])
"'{1,2,3}'::int[]"
>>> py_to_pg('Jack Johnson')
"'Jack Johnson'::text"
>>> py_to_pg([decimal.Decimal(0.3),decimal.Decimal(0.3),decimal.Decimal(0.4)])
"'{0.3,0.3,0.4}'::numeric(5,4)[];"
>>> py_to_pg(datetime.datetime(2013,7,7,13,0,0))
"'2013-07-07T13:00:00'::timestamp"
>>> py_to_pg(float(0.5))
"0.5::numeric"
>>> py_to_pg(5)
"5::int"
>>> py_to_pg(['a','b','c'])
"'{a,b,c}'::text[]"
>>> py_to_pg(None)
"'NULL'"
>>> py_to_pg(33.00)
"'33.0000'::numeric(5,4)"
>>> py_to_pg(True)
"'T'"
'''
def py_to_pg(pyval):
# set precision to 4 decimals out
getcontext().prec = 4
t2t = {int: "'%s'::int",
str: "'%s'::text",
Decimal: '%s::numeric(5,4)',
float: '%s::numeric(5,4)'}
if type(pyval) in t2t:
return t2t[type(pyval)] % (str(pyval),)
elif type(pyval) == datetime.datetime:
return "'%s'::timestamp" % (pyval.isoformat(),)
elif type(pyval) == list:
if len(pyval) > 0 and type(pyval[0]) in t2t:
# grab appropriate type
beginning = "'{"
for a in pyval:
beginning += str(a) + ','
return beginning[0:-1] + "}'::" + re.sub(r'.+::(.+)',r'\1[]',t2t[type(pyval[0])])
elif type(pyval[0]) == datetime.datetime:
converted = [a.isoformat() for a in pyval]
return re.sub(r'\[([^\]]+)\]',r"'{\1}'::timestamp[]", converted)
elif not type(pyval[0]) in t2t:
raise TypeError('py_to_pg not yet supports ARRAY type %s' % (str(type(pyval[0])),))
else: # return an empty array
return '{}'
elif type(pyval) == type(None):
return 'NULL'
elif type(pyval) == bool:
return "'%s'" % (str(pyval)[0],)
else:
raise TypeError('py_to_pg not yet supports type ' + str(type(pyval)))
and the test case...
def test_py_to_pg(self):
assertEquals(py_to_pg([decimal.Decimal(0.3),decimal.Decimal(0.3),decimal.Decimal(0.4)]), "'{0.299999999999999988897769753748434595763683319091796875,0.299999999999999988897769753748434595763683319091796875,0.40000000000000002220446049250313080847263336181640625}'::numeric(5,4)[]")
assertEquals(py_to_pg([1,2,3]), "'{1,2,3}'::int[]")
assertEquals(py_to_pg('Jack Johnson'), "'Jack Johnson'::text")
assertEquals(py_to_pg([decimal.Decimal(0.3),decimal.Decimal(0.3),decimal.Decimal(0.4)]), "'{0.299999999999999988897769753748434595763683319091796875,0.299999999999999988897769753748434595763683319091796875,0.40000000000000002220446049250313080847263336181640625}'::numeric(5,4)[]")
assertEquals(py_to_pg(decimal.Decimal(0.3)), '0.299999999999999988897769753748434595763683319091796875::numeric(5,4)')
assertEquals(py_to_pg(datetime.datetime(2013,7,7,13,0,0)), "'2013-07-07T13:00:00'::timestamp")
assertEquals(py_to_pg(float(0.5)), '0.5::numeric(5,4)')
assertEquals(py_to_pg(0.5),'0.5::numeric(5,4)')
assertEquals(py_to_pg(5), "'5'::int")
assertEquals(py_to_pg(['a','b','c']), "'{a,b,c}'::text[]")
assertEquals(py_to_pg(None), 'NULL')