[Webinar] Streamline your web hosting managementRegister Today


ssh using public key with Python

Posted on 2010-03-25
Medium Priority
Last Modified: 2012-06-21
Hello all,
Been a long time since I was a member of this community! Glad to be back!

I am looking for a solution using python, my script currently connects to a mysql db (via IP/Usr/Pwd) and runs a query that outputs a file(.csv). Next I need to ssh into the box (with key) to retrieve (email) said file. These are remote servers "in the cloud". Any suggestions would be appricated, I have paramiko installed along with an ssh python module but I am unsuccessful:

import MySQLdb
import paramiko
import os
import ssh
# Connect to MySQL-Run Query-close connection
conn = MySQLdb.connect (host = "",user = "username here",passwd = "passwd here",db = "dbname here")
cursor = conn.cursor ()
cursor.execute (""" select t.Creator, u.EmailAddress, t.Queue INTO OUTFILE '/mnt/tmp/filename.csv' FIELDS TERMINATED BY ',' OPTIONALLY   ENCLOSED BY '"' LINES TERMINATED BY '\n' from Tickets t, Users u where status != 'resolved' and t.Creator = u.id and t.Queue = 38; """)
cursor.close ()
conn.close ()
# ssh to root to get file # Email CSV file

#privatekeyfile = os.path.expanduser('/root/.ssh/Farms/key.pem file here')
# mykey = paramiko.RSAKey.from_private_key_file(privatekeyfile)
x = ssh.Connection('', username = 'root', private_key = '/root/.ssh/Farms/key.pem')
cursor =x.cursor()
cusor.execute("""gzip -m9 /mnt/tmp/filename.csv ; uuencode /mnt/tmp/filename.csv.gz filename.csv.gz | mail -s "Emails" recipient@here.com""")
Question by:EEBbrown
LVL 25

Accepted Solution

clockwatcher earned 2000 total points
ID: 28805336
Haven't tried the ssh module but paramiko seems to work well for me.  Where are you running into problems with it?

import paramiko

client = paramiko.SSHClient()
client.load_host_keys("hostkeys") # if you don't have a host key file, you can create an empty file and let paramiko populate it for you
client.set_missing_host_key_policy(paramiko.AutoAddPolicy())  # probably want to remove this line after you have a file of host keys you trust
client.connect("",username="username", key_filename="private_dsa.key")
client.exec_command('gzip -m9 /mnt/tmp/filename.csv ; uuencode /mnt/tmp/filename.csv.gz filename.csv.gz | mail -s "Emails" recipient@here.com')

Open in new window


Author Comment

ID: 28979966
Thanks clockwatcher, I will try this out early in the week and let you know if i'm successful

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A set of related code is known to be a Module, it helps us to organize our code logically which is much easier for us to understand and use it. Module is an object with arbitrarily named attributes which can be used in binding and referencing. …
If you haven’t already, I encourage you to read the first article (http://www.experts-exchange.com/articles/18680/An-Introduction-to-R-Programming-and-R-Studio.html) in my series to gain a basic foundation of R and R Studio.  You will also find the …
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.
Suggested Courses

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question