Use psqlodbc to connect to Redshift on Linux server

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

I currently successfully connect to Redshift using SQLAlechemy and Psycopg2 like so:

HOST = "host"

PORT = "5439"

DATABASE = "db"

USER = "user"

PASSWORD = "pass"



server = "redshift+psycopg2://%s:%s@%s:%s/%s" % (USER,PASSWORD,HOST,str(PORT),DATABASE)

engine = sa.create_engine(server, connect_args={'sslmode': 'verify-ca'})

conn = engine.raw_connection()

I am trying to leverage ODBC (in particular the fast_executemany solution as per Speeding up pandas.DataFrame.to_sql with fast_executemany of pyODBC) instead, so I am attempting to re-write it via PyODBC and psqlODBC driver since it is available via conda install unlike Redshift ODBC, like so:

conn = pyodbc.connect(

    Driver='{/usr/local/anaconda/envs/jupyter-atp/lib/psqlodbcw.so}', 

    Server='host', 

    Port='5439',

    Database='db', 

    UID='user', 

    PWD='pass',

    sslmode='verify-ca'

    )

I also tried sslmode='require' as per https://www.connectionstrings.com/postgresql-odbc-driver-psqlodbc/ssl/ however, still getting the following: Error: ('28000', '[28000] FATAL: no pg_hba.conf entry for host "::ffff:10.5.135.13", user "user", database "db", SSL off (210) (SQLDriverConnect)'). I was getting a similar error for SQLAlchemy but sslmode='verify-ca' fixed it..

Any suggestions how I can add a connection argument to PyODBC to turn SSL on?

edited Jan 3 at 17:12

asked Jan 3 at 16:49

user8834780

591224

add a comment |

I currently successfully connect to Redshift using SQLAlechemy and Psycopg2 like so:

HOST = "host"

PORT = "5439"

DATABASE = "db"

USER = "user"

PASSWORD = "pass"



server = "redshift+psycopg2://%s:%s@%s:%s/%s" % (USER,PASSWORD,HOST,str(PORT),DATABASE)

engine = sa.create_engine(server, connect_args={'sslmode': 'verify-ca'})

conn = engine.raw_connection()

conn = pyodbc.connect(

    Driver='{/usr/local/anaconda/envs/jupyter-atp/lib/psqlodbcw.so}', 

    Server='host', 

    Port='5439',

    Database='db', 

    UID='user', 

    PWD='pass',

    sslmode='verify-ca'

    )

Any suggestions how I can add a connection argument to PyODBC to turn SSL on?

edited Jan 3 at 17:12

asked Jan 3 at 16:49

user8834780

591224

add a comment |

I currently successfully connect to Redshift using SQLAlechemy and Psycopg2 like so:

HOST = "host"

PORT = "5439"

DATABASE = "db"

USER = "user"

PASSWORD = "pass"



server = "redshift+psycopg2://%s:%s@%s:%s/%s" % (USER,PASSWORD,HOST,str(PORT),DATABASE)

engine = sa.create_engine(server, connect_args={'sslmode': 'verify-ca'})

conn = engine.raw_connection()

conn = pyodbc.connect(

    Driver='{/usr/local/anaconda/envs/jupyter-atp/lib/psqlodbcw.so}', 

    Server='host', 

    Port='5439',

    Database='db', 

    UID='user', 

    PWD='pass',

    sslmode='verify-ca'

    )

Any suggestions how I can add a connection argument to PyODBC to turn SSL on?

edited Jan 3 at 17:12

asked Jan 3 at 16:49

user8834780

591224

I currently successfully connect to Redshift using SQLAlechemy and Psycopg2 like so:

HOST = "host"

PORT = "5439"

DATABASE = "db"

USER = "user"

PASSWORD = "pass"



server = "redshift+psycopg2://%s:%s@%s:%s/%s" % (USER,PASSWORD,HOST,str(PORT),DATABASE)

engine = sa.create_engine(server, connect_args={'sslmode': 'verify-ca'})

conn = engine.raw_connection()

conn = pyodbc.connect(

    Driver='{/usr/local/anaconda/envs/jupyter-atp/lib/psqlodbcw.so}', 

    Server='host', 

    Port='5439',

    Database='db', 

    UID='user', 

    PWD='pass',

    sslmode='verify-ca'

    )

Any suggestions how I can add a connection argument to PyODBC to turn SSL on?

python sql odbc

edited Jan 3 at 17:12

asked Jan 3 at 16:49

user8834780

591224

edited Jan 3 at 17:12

asked Jan 3 at 16:49

user8834780

591224

edited Jan 3 at 17:12

asked Jan 3 at 16:49

user8834780

591224

asked Jan 3 at 16:49

user8834780

591224

asked Jan 3 at 16:49

user8834780

591224

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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54026494%2fuse-psqlodbc-to-connect-to-redshift-on-linux-server%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

0

active

oldest

votes

0

active

oldest

votes

draft saved

draft discarded

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu