Does Python's pyodbc support server side cursor (for Netezza)?
I need to query a Netezza database using Python. There are tens of millions of matched rows so I am currently running out of memory before my query can complete. I see that a server-side cursor can be used with psycopg2, but I don't see a way to connect to my Netezza database using psycopg2 or a way to change the pyodbc connection I create to use a server-side cursor.
My connection and query currently looks like:
import pyodbc
conn = pyodbc.connect(dsn='NZSQL;SERVER='+server+';DATABASE='+database+';UID='+uid+';PWD='+pw)
cur = conn.cursor()
data = pd.read_sql('Select * from table join table2 on table1.var1=table2.var2', conn)
Is it possible to use a server-side cursor with Netezza in Python? If not, any suggested workarounds?
python sql pyodbc netezza
asked Nov 14 '18 at 15:37
Alex JuliusAlex Julius
13
add a comment |
I need to query a Netezza database using Python. There are tens of millions of matched rows so I am currently running out of memory before my query can complete. I see that a server-side cursor can be used with psycopg2, but I don't see a way to connect to my Netezza database using psycopg2 or a way to change the pyodbc connection I create to use a server-side cursor.
My connection and query currently looks like:
import pyodbc
conn = pyodbc.connect(dsn='NZSQL;SERVER='+server+';DATABASE='+database+';UID='+uid+';PWD='+pw)
cur = conn.cursor()
data = pd.read_sql('Select * from table join table2 on table1.var1=table2.var2', conn)
Is it possible to use a server-side cursor with Netezza in Python? If not, any suggested workarounds?
python sql pyodbc netezza
asked Nov 14 '18 at 15:37
Alex JuliusAlex Julius
13
Have you taken a look at thechunksizeargument forread_sql?
– Scratch'N'Purr
Nov 14 '18 at 15:46
Usingchuncksizeseems to work when my query doesn't require joining the large tables. When I generated the data chunk-wise and assembled the full dataframe from the individual pieces afterwards, it worked, but once I try a query that requires joining the tables, the whole dataframes are still simply too large to fit in memory and the join needs to be executed on the full datasets, rather than chunks.
– Alex Julius
Nov 14 '18 at 16:12
You could still run the joined query and output by chunks, e.g.data = pd.read_sql('SELECT a.*, b.* FROM table1 AS a INNER JOIN table2 AS b ON a.field=b.field', conn, chunksize=1000). The join will happen on the server side, so you wouldn't have to worry about memory overflow, and you will get the joined results as chunks to your local machine.
– Scratch'N'Purr
Nov 14 '18 at 16:26
add a comment |
I need to query a Netezza database using Python. There are tens of millions of matched rows so I am currently running out of memory before my query can complete. I see that a server-side cursor can be used with psycopg2, but I don't see a way to connect to my Netezza database using psycopg2 or a way to change the pyodbc connection I create to use a server-side cursor.
My connection and query currently looks like:
import pyodbc
conn = pyodbc.connect(dsn='NZSQL;SERVER='+server+';DATABASE='+database+';UID='+uid+';PWD='+pw)
cur = conn.cursor()
data = pd.read_sql('Select * from table join table2 on table1.var1=table2.var2', conn)
Is it possible to use a server-side cursor with Netezza in Python? If not, any suggested workarounds?
python sql pyodbc netezza
asked Nov 14 '18 at 15:37
Alex JuliusAlex Julius
13
I need to query a Netezza database using Python. There are tens of millions of matched rows so I am currently running out of memory before my query can complete. I see that a server-side cursor can be used with psycopg2, but I don't see a way to connect to my Netezza database using psycopg2 or a way to change the pyodbc connection I create to use a server-side cursor.
My connection and query currently looks like:
import pyodbc
conn = pyodbc.connect(dsn='NZSQL;SERVER='+server+';DATABASE='+database+';UID='+uid+';PWD='+pw)
cur = conn.cursor()
data = pd.read_sql('Select * from table join table2 on table1.var1=table2.var2', conn)
Is it possible to use a server-side cursor with Netezza in Python? If not, any suggested workarounds?
python sql pyodbc netezza
python sql pyodbc netezza
asked Nov 14 '18 at 15:37
Alex JuliusAlex Julius
13
asked Nov 14 '18 at 15:37
Alex JuliusAlex Julius
13
edited Nov 14 '18 at 16:14
Alex Julius
asked Nov 14 '18 at 15:37
Alex JuliusAlex Julius
13
asked Nov 14 '18 at 15:37
Alex JuliusAlex Julius
13
asked Nov 14 '18 at 15:37
Alex JuliusAlex Julius
13
13
Have you taken a look at thechunksizeargument forread_sql?
– Scratch'N'Purr
Nov 14 '18 at 15:46
Usingchuncksizeseems to work when my query doesn't require joining the large tables. When I generated the data chunk-wise and assembled the full dataframe from the individual pieces afterwards, it worked, but once I try a query that requires joining the tables, the whole dataframes are still simply too large to fit in memory and the join needs to be executed on the full datasets, rather than chunks.
– Alex Julius
Nov 14 '18 at 16:12
You could still run the joined query and output by chunks, e.g.data = pd.read_sql('SELECT a.*, b.* FROM table1 AS a INNER JOIN table2 AS b ON a.field=b.field', conn, chunksize=1000). The join will happen on the server side, so you wouldn't have to worry about memory overflow, and you will get the joined results as chunks to your local machine.
– Scratch'N'Purr
Nov 14 '18 at 16:26
add a comment |
Have you taken a look at thechunksizeargument forread_sql?
– Scratch'N'Purr
Nov 14 '18 at 15:46
Usingchuncksizeseems to work when my query doesn't require joining the large tables. When I generated the data chunk-wise and assembled the full dataframe from the individual pieces afterwards, it worked, but once I try a query that requires joining the tables, the whole dataframes are still simply too large to fit in memory and the join needs to be executed on the full datasets, rather than chunks.
– Alex Julius
Nov 14 '18 at 16:12
You could still run the joined query and output by chunks, e.g.data = pd.read_sql('SELECT a.*, b.* FROM table1 AS a INNER JOIN table2 AS b ON a.field=b.field', conn, chunksize=1000). The join will happen on the server side, so you wouldn't have to worry about memory overflow, and you will get the joined results as chunks to your local machine.
– Scratch'N'Purr
Nov 14 '18 at 16:26
Have you taken a look at the
chunksize argument for read_sql?– Scratch'N'Purr
Nov 14 '18 at 15:46
Have you taken a look at the
chunksize argument for read_sql?– Scratch'N'Purr
Nov 14 '18 at 15:46
Using
chuncksize seems to work when my query doesn't require joining the large tables. When I generated the data chunk-wise and assembled the full dataframe from the individual pieces afterwards, it worked, but once I try a query that requires joining the tables, the whole dataframes are still simply too large to fit in memory and the join needs to be executed on the full datasets, rather than chunks.– Alex Julius
Nov 14 '18 at 16:12
Using
chuncksize seems to work when my query doesn't require joining the large tables. When I generated the data chunk-wise and assembled the full dataframe from the individual pieces afterwards, it worked, but once I try a query that requires joining the tables, the whole dataframes are still simply too large to fit in memory and the join needs to be executed on the full datasets, rather than chunks.– Alex Julius
Nov 14 '18 at 16:12
You could still run the joined query and output by chunks, e.g.
data = pd.read_sql('SELECT a.*, b.* FROM table1 AS a INNER JOIN table2 AS b ON a.field=b.field', conn, chunksize=1000). The join will happen on the server side, so you wouldn't have to worry about memory overflow, and you will get the joined results as chunks to your local machine.– Scratch'N'Purr
Nov 14 '18 at 16:26
You could still run the joined query and output by chunks, e.g.
data = pd.read_sql('SELECT a.*, b.* FROM table1 AS a INNER JOIN table2 AS b ON a.field=b.field', conn, chunksize=1000). The join will happen on the server side, so you wouldn't have to worry about memory overflow, and you will get the joined results as chunks to your local machine.– Scratch'N'Purr
Nov 14 '18 at 16:26
add a comment |
0
active
oldest
votes
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%2f53303771%2fdoes-pythons-pyodbc-support-server-side-cursor-for-netezza%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53303771%2fdoes-pythons-pyodbc-support-server-side-cursor-for-netezza%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
Have you taken a look at the
chunksizeargument forread_sql?– Scratch'N'Purr
Nov 14 '18 at 15:46
Using
chuncksizeseems to work when my query doesn't require joining the large tables. When I generated the data chunk-wise and assembled the full dataframe from the individual pieces afterwards, it worked, but once I try a query that requires joining the tables, the whole dataframes are still simply too large to fit in memory and the join needs to be executed on the full datasets, rather than chunks.– Alex Julius
Nov 14 '18 at 16:12
You could still run the joined query and output by chunks, e.g.
data = pd.read_sql('SELECT a.*, b.* FROM table1 AS a INNER JOIN table2 AS b ON a.field=b.field', conn, chunksize=1000). The join will happen on the server side, so you wouldn't have to worry about memory overflow, and you will get the joined results as chunks to your local machine.– Scratch'N'Purr
Nov 14 '18 at 16:26