ssh using public key with Python

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 = "10.10.10.10",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('10.10.10.10', 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""")
EEBbrownAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

clockwatcherCommented:
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("192.168.1.1",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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EEBbrownAuthor Commented:
Thanks clockwatcher, I will try this out early in the week and let you know if i'm successful
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Languages-Other

From novice to tech pro — start learning today.