Mixing
using psycopg2 to call postgres function which takes an array of a custom type
I currently have a custom type in postgres:
create type times_type as
(
start varchar,
end varchar,
rate real
);
I also have a function which takes an array of times_type:
create function insert_data(times_values times_type)
When I was using named tuples and using callproc to insert one value at a time it worked fine i.e:
Document = namedtuple('times_type', 'start
end rate')
cur.callproc('insert_data',
(Document('13:00', '16:00', 12.56))
But now the function is expecting an array, so i attempted:
Document = namedtuple('times_type', 'start
end rate')
cur.callproc('insert_data',
([Document('13:00', '16:00', 12.56),
Document('17:00', '18:00', 12.56),
Document('19:00', '20:00', 12.56)])
psycopg2 gives me an error that the function does not exist. Is there something you have to do when dealing with an array of custom types?
python-3.x postgresql psycopg2
asked Nov 20 '18 at 16:03
ChimmyChimmy
11
add a comment |
I currently have a custom type in postgres:
create type times_type as
(
start varchar,
end varchar,
rate real
);
I also have a function which takes an array of times_type:
create function insert_data(times_values times_type)
When I was using named tuples and using callproc to insert one value at a time it worked fine i.e:
Document = namedtuple('times_type', 'start
end rate')
cur.callproc('insert_data',
(Document('13:00', '16:00', 12.56))
But now the function is expecting an array, so i attempted:
Document = namedtuple('times_type', 'start
end rate')
cur.callproc('insert_data',
([Document('13:00', '16:00', 12.56),
Document('17:00', '18:00', 12.56),
Document('19:00', '20:00', 12.56)])
psycopg2 gives me an error that the function does not exist. Is there something you have to do when dealing with an array of custom types?
python-3.x postgresql psycopg2
asked Nov 20 '18 at 16:03
ChimmyChimmy
11
add a comment |
I currently have a custom type in postgres:
create type times_type as
(
start varchar,
end varchar,
rate real
);
I also have a function which takes an array of times_type:
create function insert_data(times_values times_type)
When I was using named tuples and using callproc to insert one value at a time it worked fine i.e:
Document = namedtuple('times_type', 'start
end rate')
cur.callproc('insert_data',
(Document('13:00', '16:00', 12.56))
But now the function is expecting an array, so i attempted:
Document = namedtuple('times_type', 'start
end rate')
cur.callproc('insert_data',
([Document('13:00', '16:00', 12.56),
Document('17:00', '18:00', 12.56),
Document('19:00', '20:00', 12.56)])
psycopg2 gives me an error that the function does not exist. Is there something you have to do when dealing with an array of custom types?
python-3.x postgresql psycopg2
asked Nov 20 '18 at 16:03
ChimmyChimmy
11
I currently have a custom type in postgres:
create type times_type as
(
start varchar,
end varchar,
rate real
);
I also have a function which takes an array of times_type:
create function insert_data(times_values times_type)
When I was using named tuples and using callproc to insert one value at a time it worked fine i.e:
Document = namedtuple('times_type', 'start
end rate')
cur.callproc('insert_data',
(Document('13:00', '16:00', 12.56))
But now the function is expecting an array, so i attempted:
Document = namedtuple('times_type', 'start
end rate')
cur.callproc('insert_data',
([Document('13:00', '16:00', 12.56),
Document('17:00', '18:00', 12.56),
Document('19:00', '20:00', 12.56)])
psycopg2 gives me an error that the function does not exist. Is there something you have to do when dealing with an array of custom types?
python-3.x postgresql psycopg2
python-3.x postgresql psycopg2
asked Nov 20 '18 at 16:03
ChimmyChimmy
11
asked Nov 20 '18 at 16:03
ChimmyChimmy
11
asked Nov 20 '18 at 16:03
ChimmyChimmy
11
asked Nov 20 '18 at 16:03
ChimmyChimmy
11
asked Nov 20 '18 at 16:03
ChimmyChimmy
11
11
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
The problem with arrays of a composite type is that they have to be explicitly cast, otherwise Postgres see them as record. Use register_adapter(class, adapter).
Document = namedtuple('times_type', 'start end rate')
class DocumentAdapter:
def __init__(self, x):
self.adapted = psycopg2.extensions.SQL_IN(x)
def prepare(self, conn):
self.adapted.prepare(conn)
def getquoted(self):
return self.adapted.getquoted() + b'::times_type'
psycopg2.extensions.register_adapter(Document, DocumentAdapter)
cur.callproc('insert_data',
([Document('13:00', '16:00', 12.56),
Document('17:00', '18:00', 12.56),
Document('19:00', '20:00', 12.56)],)) # comma added, this should be a tuple
conn.commit(); # dont forget to commit
answered Nov 21 '18 at 20:15
klinklin
57.8k65080
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53396963%2fusing-psycopg2-to-call-postgres-function-which-takes-an-array-of-a-custom-type%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The problem with arrays of a composite type is that they have to be explicitly cast, otherwise Postgres see them as record. Use register_adapter(class, adapter).
Document = namedtuple('times_type', 'start end rate')
class DocumentAdapter:
def __init__(self, x):
self.adapted = psycopg2.extensions.SQL_IN(x)
def prepare(self, conn):
self.adapted.prepare(conn)
def getquoted(self):
return self.adapted.getquoted() + b'::times_type'
psycopg2.extensions.register_adapter(Document, DocumentAdapter)
cur.callproc('insert_data',
([Document('13:00', '16:00', 12.56),
Document('17:00', '18:00', 12.56),
Document('19:00', '20:00', 12.56)],)) # comma added, this should be a tuple
conn.commit(); # dont forget to commit
answered Nov 21 '18 at 20:15
klinklin
57.8k65080
add a comment |
The problem with arrays of a composite type is that they have to be explicitly cast, otherwise Postgres see them as record. Use register_adapter(class, adapter).
Document = namedtuple('times_type', 'start end rate')
class DocumentAdapter:
def __init__(self, x):
self.adapted = psycopg2.extensions.SQL_IN(x)
def prepare(self, conn):
self.adapted.prepare(conn)
def getquoted(self):
return self.adapted.getquoted() + b'::times_type'
psycopg2.extensions.register_adapter(Document, DocumentAdapter)
cur.callproc('insert_data',
([Document('13:00', '16:00', 12.56),
Document('17:00', '18:00', 12.56),
Document('19:00', '20:00', 12.56)],)) # comma added, this should be a tuple
conn.commit(); # dont forget to commit
answered Nov 21 '18 at 20:15
klinklin
57.8k65080
add a comment |
The problem with arrays of a composite type is that they have to be explicitly cast, otherwise Postgres see them as record. Use register_adapter(class, adapter).
Document = namedtuple('times_type', 'start end rate')
class DocumentAdapter:
def __init__(self, x):
self.adapted = psycopg2.extensions.SQL_IN(x)
def prepare(self, conn):
self.adapted.prepare(conn)
def getquoted(self):
return self.adapted.getquoted() + b'::times_type'
psycopg2.extensions.register_adapter(Document, DocumentAdapter)
cur.callproc('insert_data',
([Document('13:00', '16:00', 12.56),
Document('17:00', '18:00', 12.56),
Document('19:00', '20:00', 12.56)],)) # comma added, this should be a tuple
conn.commit(); # dont forget to commit
answered Nov 21 '18 at 20:15
klinklin
57.8k65080
The problem with arrays of a composite type is that they have to be explicitly cast, otherwise Postgres see them as record. Use register_adapter(class, adapter).
Document = namedtuple('times_type', 'start end rate')
class DocumentAdapter:
def __init__(self, x):
self.adapted = psycopg2.extensions.SQL_IN(x)
def prepare(self, conn):
self.adapted.prepare(conn)
def getquoted(self):
return self.adapted.getquoted() + b'::times_type'
psycopg2.extensions.register_adapter(Document, DocumentAdapter)
cur.callproc('insert_data',
([Document('13:00', '16:00', 12.56),
Document('17:00', '18:00', 12.56),
Document('19:00', '20:00', 12.56)],)) # comma added, this should be a tuple
conn.commit(); # dont forget to commit
answered Nov 21 '18 at 20:15
klinklin
57.8k65080
answered Nov 21 '18 at 20:15
klinklin
57.8k65080
answered Nov 21 '18 at 20:15
klinklin
57.8k65080
answered Nov 21 '18 at 20:15
klinklin
57.8k65080
57.8k65080
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53396963%2fusing-psycopg2-to-call-postgres-function-which-takes-an-array-of-a-custom-type%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
