Writing values to Postgresql type Float

[wilbur]

Hello,

I am having problems using sqlalchemy to write values to Postgresq
columns of type Float. I am getting "sqlalchemy.exc.ProgrammingError:
(ProgrammingError) can't adapt" errors when I try to insert records.

My Postgresql table is defined as:

Column | Type
| Modifiers
----------------+--------------------------------
+------------------------------------------------------------------
max_pm25 | double precision |
mean_pm25 | double precision |


After importing the Postgresql dialect:

from sqlalchemy.dialects.postgresql import *

I define my sqlalchemy table as:

class dream4_eta_15km_pm10(Base):
__tablename__='pm10_dream_rasters'
max_pm10=Column(DOUBLE_PRECISION)
mean_pm10=Column(DOUBLE_PRECISION)

Any help appreciated,

Bill

[Michael Bayer]

Here's a tested example of DOUBLE_PRECISION using both float and Decimal versions. Make sure you're on a recent release of psycopg2:

from sqlalchemy import Column, create_engine, Integer
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION
from decimal import Decimal

Base = declarative_base()

class dream4_eta_15km_pm10(Base):
__tablename__='pm10_dream_rasters'

id = Column(Integer, primary_key=True)

# use float values
max_pm10=Column(DOUBLE_PRECISION)

# use Decimal values
mean_pm10=Column(DOUBLE_PRECISION(asdecimal=True))

def __repr__(self):
return "dream4_eta_15km_pm10(%r, %r)" % (self.max_pm10, self.mean_pm10)

engine = create_engine('postgresql://scott:tiger@localhost/test', echo=True)

Base.metadata.create_all(engine)

sess = Session(engine)

sess.add(dream4_eta_15km_pm10(max_pm10=76945.283959, mean_pm10=Decimal("7683.27835")))

sess.commit()

print sess.query(dream4_eta_15km_pm10).all()

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[wilbur]

Thanks for responding,

I am using Postgresql 8.3.8 and Postgis 1.4. I have tried using both
DOUBLE_PRECISION and DOUBLE_PRECISION(asdecimal=True), with the same
errors.

thanks

[Michael Bayer]

I am assuming you ran the script that I sent previously, and it produced the same errors. If this is not the case, please run that script. If it runs without errors, then the solution is to ensure your program is passing numerical values in the identical fashion as the test script, and that your table was created in postgresql using "DOUBLE PRECISION" as the data type.

Assuming you ran the script I gave you and it produces the same errors, here is a psycopg2 script. Run it on your environment - if it fails, please report the issue to the psycopg2 list, including psycopg2 and postgresql version information, at: http://mail.postgresql.org/mj/mj_wwwusr/domain=postgresql.org?func=lists-long-full&extra=psycopg

import psycopg2

conn = psycopg2.connect(user='scott', password='tiger', host='localhost', database='test')

cursor = conn.cursor()
cursor.execute("""
CREATE TABLE double_prec_test (
double_value DOUBLE PRECISION
)
""")

cursor.execute("INSERT INTO double_prec_test VALUES (%(value)s)", {'value':7684.4933})

cursor.execute("SELECT * FROM double_prec_test")

print cursor.fetchall()

cursor.close()
conn.close()

[wilbur]

Thanks again,

Your first script ran fine. I ended up doing something like this:


session.add(test_floats(id=2,max_pm10=Decimal(str(76945.283959)),
mean_pm10=Decimal(str(7683.27835))))

It was the only thing that eventually worked...

On Jan 19, 10:37 am, Michael Bayer <mike...@zzzcomputing.com> wrote:
> I am assuming you ran the script that I sent previously, and it produced the same errors.    If this is not the case, please run that script.  If it runs without errors, then the solution is to ensure your program is passing numerical values in the identical fashion as the test script, and that your table was created in postgresql using "DOUBLE PRECISION" as the data type.
>

> Assuming you ran the script I gave you and it produces the same errors, here is a psycopg2 script.  Run it on your environment - if it fails, please report the issue to the psycopg2 list, including psycopg2 and postgresql version information, at:  http://mail.postgresql.org/mj/mj_wwwusr/domain=postgresql.org?func=li...