I want to use dict cursor in psycopg2:
self.__db_conn = psycopg2.extras.DictConnection("host=... dbname=...")here is my query:
cur.execute('INSERT INTO scm_main.tbl_ack(ack_summary_id, ack_local_timestamp, ack_user_id) '
'SELECT summary_id AS ack_summary_id, now() AS ack_local_timestamp, us.user_id AS ack_user_id '
'FROM scm_main.tbl_summary AS s '
'INNER JOIN scm_main.vu_usr_stn AS us ON (s.summary_station_id = us.station_axis_id) '
'WHERE ((s.summary_id > (SELECT COALESCE(max(a.ack_summary_id),0) FROM scm_main.tbl_ack AS a WHERE a.ack_user_id = %(user_id)s)) '
'AND (s.summary_company_specific_id <> 0) '
'AND (us.user_name = %(user_name)s) AND (s.summary_timestamp < (now() - \'00:25:00\'::interval))) '
'ORDER BY s.summary_id ASC', {
'user_id': self.__user_id,
'user_name': self.__company
})But it gives me this:
<class 'psycopg2.ProgrammingError'> exception: can't adapt type 'DictRow'This is likely a TypeError conflict between how you defined your data types in your PostgreSQL table and your parser object return type. ,3) This should get you a successful insertion, then you can walk backwards making sure each element matches the types you need in your table.,For example, using BeautifulSoup4 (bs4) to crawl through your file, the return type of an element in your tree is of type: Tag (see: "Kind of Objects" in the bs4 documentation).,Like above, I would check your data. So if you are trying to import a pandas dataframe into a Postgres Table I would do the following:
2) Convert all your variables to strings: e.g.,
...(str(link), str(titl), str(name1), ...)
Make sure you have stripped out all tags completely. So thoroughly check that first by examining your dataframe as the Tags throw off the psycopg2 driver as it is unable to recognise the object correctly. So make sure you extract all the information you need from HTML first. Use getText() to obtain the more 'stubborn' tags.
list_titles = []
movie_titles = all_movies[n].find_all("h1", {
"class": "blah"
})
for k in movie_titles:
title = k.getText()
list_titles.append(title)Any Python class or type can be adapted to an SQL string. Adaptation mechanism is similar to the Object Adaptation proposed in the PEP 246 and is exposed by the psycopg2.extensions.adapt() function. , You can now make changes to the psycopg2_test database using a normal psycopg2 session, psql , etc. and see the logical decoding stream printed by this demo client. , The following is an example loop using methods fileno() and poll() together with the Python select() function in order to carry on asynchronous operations with Psycopg: , Modern PostgreSQL servers (version 9.0 and above) support replication. The replication protocol is built on top of the client-server protocol and can be operated using libpq , as such it can be also operated by psycopg2 . The replication protocol can be operated on both synchronous and asynchronous connections.
import psycopg2
import psycopg2.extensions
import logging
class LoggingCursor(psycopg2.extensions.cursor):
def execute(self, sql, args = None):
logger = logging.getLogger('sql_debug')
logger.info(self.mogrify(sql, args))
try:
psycopg2.extensions.cursor.execute(self, sql, args)
except Exception, exc:
logger.error("%s: %s" % (exc.__class__.__name__, exc))
raise
conn = psycopg2.connect(DSN)
cur = conn.cursor(cursor_factory = LoggingCursor)
cur.execute("INSERT INTO mytable VALUES (%s, %s, %s);",
(10, 20, 30))>>> from psycopg2.extensions
import adapt, register_adapter, AsIs
>>>
class Point(object):
...def __init__(self, x, y):
...self.x = x
...self.y = y
>>>
def adapt_point(point):
...x = adapt(point.x).getquoted()
...y = adapt(point.y).getquoted()
...
return AsIs("'(%s, %s)'" % (x, y))
>>>
register_adapter(Point, adapt_point)
>>>
cur.execute("INSERT INTO atable (apoint) VALUES (%s)",
...(Point(1.23, 4.56), ))INSERT INTO atable(apoint) VALUES('(1.23, 4.56)');>>> def cast_point(value, cur):
...
if value is None:
...
return None
...
...# Convert from(f1, f2) syntax using a regular expression.
...m = re.match(r "\(([^)]+),([^)]+)\)", value)
...
if m:
...
return Point(float(m.group(1)), float(m.group(2)))
...
else:
...raise InterfaceError("bad point representation: %r" % value)>>> cur.execute("SELECT NULL::point") >>>
point_oid = cur.description[0][1] >>>
point_oid
600>>> cur.execute(""
"
...SELECT pg_type.oid
...FROM pg_type JOIN pg_namespace
...ON typnamespace = pg_namespace.oid
...WHERE typname = % (typename) s
...AND nspname = % (namespace) s ""
",
...{
'typename': 'point',
'namespace': 'pg_catalog'
}) >>>
point_oid = cur.fetchone()[0] >>>
point_oid
6001 week ago Nov 20, 2015 · EyCas changed the title HELP ProgrammingError: can't adapt type 'res.partner ProgrammingError: can't adapt type 'res.partner HELP Nov 20, 2015 Copy link leopard7777777 commented Nov 21, 2015 , 1 week ago ProgrammingError: can't adapt type 'set' sqlalchemy.exc.ProgrammingError: (ProgrammingError) can't adapt type 'UUID' How can I avoid ProgrammingError: can't adapt type 'DateTimeRangeField' when saving a Django model instance to a remote database? sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt type … , 1 day ago Jan 22, 2018 · ProgrammingError: can't adapt type 'crm.activity' #22424. Closed syarofanz opened this issue Jan 22, 2018 · 4 comments Closed ProgrammingError: can't adapt type 'crm.activity' #22424. syarofanz opened this issue Jan 22, 2018 · 4 comments Labels. 9.0 Need information the reports lacks of information. , 1 week ago View detailed information about property 133 73rd St Apt 13, North Bergen, NJ 07047 including listing details, property photos, school and neighborhood data, and much more.
import psycopg2
import xlrd book = xlrd.open_workbook("T:\data.xlsx") sheet = book.sheet_by_name("HCSData") database = psycopg2.connect(database = "", user = "") cursor = database.cursor() delete = ""
"Drop table if exists "
Python ".hcsdata"
""
print(delete) mydata = cursor.execute(delete) cursor.execute(''
'CREATE TABLE "Python".hcsdata (DCAD_Prop_ID varchar(55), Address VARCHAR(50), Addition VARCHAR(100), Block text, Lot integer, Permit_Num varchar(55), Permit_Date date, Foundation_Date date, Frame_Date date, Final_Date date, HCS varchar(55), Project_ID integer );'
'') print "Table created successfully"
query = ""
"INSERT INTO "
Python ".hcsdata (DCAD_Prop_ID,Address,Addition, Block, Lot, Permit_Num,Permit_Date, Foundation_Date, Frame_Date, Final_Date, HCS,Project_ID) VALUES (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s)"
""
for r in range(1, sheet.nrows): DCAD_Prop_ID = sheet.cell(r, 0).value Address = sheet.cell(r, 1).value Addition = sheet.cell(r, 2).value Block = sheet.cell(r, 3).value Lot = sheet.cell(r, 4).value Permit_Num = sheet.cell(r, 5).value Permit_Date = None
if not sheet.cell(r, 6).value
else xlrd.xldate.xldate_as_datetime(sheet.cell(r, 6).value, book.datemode) Foundation_Date = None
if not sheet.cell(r, 7).value
else xlrd.xldate.xldate_as_datetime(sheet.cell(r, 7).value, book.datemode) Frame_Date = None
if not sheet.cell(r, 8).value
else xlrd.xldate.xldate_as_datetime(sheet.cell(r, 8).value, book.datemode) Final_Date = None
if not sheet.cell(r, 9).value
else xlrd.xldate.xldate_as_datetime(sheet.cell(r, 9).value, book.datemode) HCS = sheet.cell(r, 10).value Project_ID = sheet.cell(r, 11).value values = (DCAD_Prop_ID, set(Address), Addition, Block, Lot, Permit_Num, Permit_Date, Foundation_Date, Frame_Date, Final_Date, HCS, Project_ID) cursor.execute(query, values) cursor.close() database.commit() database.close() print ""
print "All Done! Bye, for now."
print ""
columns = str(sheet.ncols) rows = str(sheet.nrows) print "Done"query = ""
"INSERT INTO "
Python ".hcsdata (DCAD_Prop_ID,Address,Addition, Block, Lot, Permit_Num,Permit_Date, Foundation_Date, Frame_Date, Final_Date, HCS,Project_ID) VALUES (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s)"
""import psycopg2
import xlrd book = xlrd.open_workbook("T:\data.xlsx") sheet = book.sheet_by_name("HCSData") database = psycopg2.connect(database = "", user = "") cursor = database.cursor() delete = ""
"Drop table if exists "
Python ".hcsdata"
""
print(delete) mydata = cursor.execute(delete) cursor.execute(''
'CREATE TABLE "Python".hcsdata (DCAD_Prop_ID varchar(55), Address VARCHAR(50), Addition VARCHAR(100), Block text, Lot integer, Permit_Num varchar(55), Permit_Date date, Foundation_Date date, Frame_Date date, Final_Date date, HCSvarchar(55), Project_ID integer );'
'') print "Table created successfully"
query = ""
"INSERT INTO "
Python ".hcsdata (DCAD_Prop_ID,Address,Addition, Block, Lot, Permit_Num,Permit_Date, Foundation_Date, Frame_Date, Final_Date, HCS,Project_ID) VALUES (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s)"
""
for r in range(1, sheet.nrows): DCAD_Prop_ID = sheet.cell(r, 0).valueAddress = sheet.cell(r, 1).valueAddition = sheet.cell(r, 2).valueBlock = sheet.cell(r, 3).valueLot = sheet.cell(r, 4).valuePermit_Num = sheet.cell(r, 5).valuePermit_Date = None
if not sheet.cell(r, 6).value
else xlrd.xldate.xldate_as_datetime(sheet.cell(r, 6).value, book.datemode) Foundation_Date = None
if not sheet.cell(r, 7).value
else xlrd.xldate.xldate_as_datetime(sheet.cell(r, 7).value, book.datemode) Frame_Date = None
if not sheet.cell(r, 8).value
else xlrd.xldate.xldate_as_datetime(sheet.cell(r, 8).value, book.datemode) Final_Date = None
if not sheet.cell(r, 9).value
else xlrd.xldate.xldate_as_datetime(sheet.cell(r, 9).value, book.datemode) HCS = sheet.cell(r, 10).valueProject_ID = sheet.cell(r, 11).valuevalues = (DCAD_Prop_ID, set(Address), Addition, Block, Lot, Permit_Num, Permit_Date, Foundation_Date, Frame_Date, Final_Date, HCS, Project_ID) cursor.execute(query, values) cursor.close() database.commit() database.close() print ""
print "All Done! Bye, for now."
print ""
columns = str(sheet.ncols) rows = str(sheet.nrows) print "Done"Drop table if exists "Python".hcsdata Table created successfully Traceback (most recent call last): File "C:\Users\Programming\PythonSQLNew\HCSData.py", line 53, in <module>cursor.execute(query, values) ProgrammingError: can't adapt type 'set'query = ""
"INSERT INTO "
Python ".hcsdata (DCAD_Prop_ID,Address,Addition, Block, Lot, Permit_Num,Permit_Date, Foundation_Date, Frame_Date, Final_Date, HCS,Project_ID) VALUES (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s)"
""values = (DCAD_Prop_ID, set(Address), Addition, Block, Lot, Permit_Num, Permit_Date, Foundation_Date, Frame_Date, Final_Date, HCS, Project_ID)
Psycopg can adapt Python objects to and from the PostgreSQL json type. With PostgreSQL 9.2 adaptation is available out-of-the-box. To use JSON data with previous database versions (either with the 9.1 json extension, but even if you want to convert text fields to JSON) you can use register_json().,Psycopg offers a Range Python type and supports adaptation between them and PostgreSQL range types. Builtin range types are supported out-of-the-box; user-defined range types can be adapted using register_range().,Adaptation from Python tuples to composite types is automatic instead and requires no adapter registration.,This Python type is only used to pass and retrieve range values to and from PostgreSQL and doesn’t attempt to replicate the PostgreSQL range features: it doesn’t perform normalization and doesn’t implement all the operators supported by the database.
>>> dict_cur = conn.cursor(cursor_factory = psycopg2.extras.DictCursor) >>>
dict_cur.execute("INSERT INTO test (num, data) VALUES(%s, %s)",
...(100, "abc'def")) >>>
dict_cur.execute("SELECT * FROM test") >>>
rec = dict_cur.fetchone() >>>
rec['id']
1
>>>
rec['num']
100
>>>
rec['data']
"abc'def">>> rec[2]
"abc'def"from somewhere import namedtuple import collections collections.namedtuple = namedtuple from psycopg.extras import NamedTupleConnection #...
>>> nt_cur = conn.cursor(cursor_factory = psycopg2.extras.NamedTupleCursor) >>>
rec = nt_cur.fetchone() >>>
rec
Record(id = 1, num = 100, data = "abc'def") >>>
rec[1]
100
>>>
rec.data "abc'def"curs.execute("insert into mytable (jsondata) values (%s)",
[Json({
'a': 100
})])psycopg2.extensions.register_adapter(dict, psycopg2.extras.Json)