ssh + mysql connection python 3.4.3

[desudesu]

Hello, im having a problem connecting to remote mysql database. i am able to manually ssh to the server and connect to mysql using same authentication (username/pass) as im using in code, but im not able to connect to the database from the program, below is code and errors.

from __future__ import print_function
import paramiko
import pymysql

ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect('ip_of_server', username='uname', password='password')

conn = pymysql.connect(host='localhost', port=3306, user='sql_uname', passwd='sql_passwd', db='dbname')
cur = conn.cursor()
cur.execute("SELECT * from dbname")
print(cur.description)
print()

for row in cur:
   print(row)
cur.close()
conn.close()

Here is the erroroutput:

Error:
/usr/lib/python3/dist-packages/Crypto/Cipher/blockalgo.py:141: FutureWarning: CTR mode needs counter parameter, not IV
 self._cipher = factory.new(key, *args, **kwargs)
Traceback (most recent call last):
 File "/home/tekniikka/PycharmProjects/project/connectionToDatabase.py", line 9, in <module>
   conn = pymysql.connect(host='localhost', port=3306, user='sql_uname', passwd='sql_passwd', db='dbname')
 File "/usr/local/lib/python3.4/dist-packages/pymysql/__init__.py", line 90, in Connect
   return Connection(*args, **kwargs)
 File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 706, in __init__
   self.connect()
 File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 932, in connect
   self._request_authentication()
 File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 1152, in _request_authentication
   auth_packet = self._read_packet()
 File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 1014, in _read_packet
   packet.check_error()
 File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 393, in check_error
   err.raise_mysql_exception(self._data)
 File "/usr/local/lib/python3.4/dist-packages/pymysql/err.py", line 107, in raise_mysql_exception
   raise errorclass(errno, errval)
pymysql.err.OperationalError: (1045, "Access denied for user 'sql_uname'@'localhost' (using password: YES)")

Process finished with exit code 1

[jogl]

I think that you have user and password issues at the mysql server end.  I had the same.  

I forget the details, but it boils down to userz@ip, and userz@localhost are not the same user in mysql's world.

Google on it, there is some good info out there.  I think some info at StackOverflow got me going.

P.S.:

Check these threads:

http://stackoverflow.com/questions/10299...sing-passw
http://stackoverflow.com/questions/40680...ssword-yes

Also, I found that the placement of quotations in the user names was tricky.

[nilamo]

problem connecting to remote mysql database.

...

pymysql.connect(host='localhost'

You're trying to connect to a database on your local machine, but you're using credentials for a remote database.
Why is the ssl stuff there? Are you trying to setup some sort of tunnel to "fake" like it's a local database?

[desudesu]

I have a virtual server on local machine and im trying to connect to database located on the virtual server, read some guides and they tell to ssh to remote server and connect to db as local. If theres a way to simply access the database remotely without ssh, im all ears :)

[jogl]

This is the first few lines of what I have for remote access to database.

# working live JP 20170312
# boilergraph.py to display boiler temperatures 20170502
import MySQLdb
import time
import datetime
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import matplotlib.animation as animation
from matplotlib import style
from time import sleep

style.use('ggplot')
fig = plt.figure()
ax1 = fig.add_subplot(1,1,1)

dt = []
a = []
b = []
ca = []
d = []
dtf=[]
dtg=[]
i=0
lr=0

def read_from_db():
    del dt[:]
    del a[:]
    del b[:]
    del ca[:]
    del d[:]
    del dtf[:]
    del dtg[:]
    global lr
    conn = MySQLdb.connect("192.168.1.162","user1","passuser1","jp1")
    c = conn.cursor()
    c.execute('SELECT * FROM boiltbl WHERE dt >= now() - interval 2 hour')

Just ignore the poor form and extra stuff.  This works great, although, as mentioned took some effort to get the users and passwords set up to mySQL's liking.  

Don't know what pymysql is but to my knowledge all the mySQL derivatives work the same.

[desudesu]

OK, so i have made some progress, i have managed to get access to my db remotely and i can get values from it, but now i need to assign values from the db to specific values and i cant figure out how to do it., heres what i got:

import mysql.connector
from mysql.connector import Error

class dbcon:
   """ Connect to MySQL database """
   ip = 0
   port = 0

   try:
       print('Trying to establish connection to database...')
       connection = mysql.connector.connect(host='db.sample.com',
                                            database='ipinfo',
                                            user='user',
                                            password='password')
       if connection.is_connected():
           print('Connection Successful!!!')
           cursor = connection.cursor()
           cursor.execute("SELECT ip, port FROM stations")

           row = cursor.fetchone()

           while row is not None:
               print(row)
               row = cursor.fetchone()
                """ parse ip from row to variable ip """
                """ parse port from row to variable port """

   except Error as e:
       print(e)

   finally:
       print("Connection to database closed!")
       connection.close()

Cant figure out how to do this... pls help

[nilamo]

           row = cursor.fetchone()
 
           while row is not None:
               print(row)
               row = cursor.fetchone()
                """ parse ip from row to variable ip """
                """ parse port from row to variable port """

What output do you get? Isn't row just a tuple of values?

[desudesu]

What im getting now is just this:

Trying to establish connection to database...
Connection Successful!!!
('domain.name.com', '22')
('test1', '22')
Connection to database closed!

Process finished with exit code 0

what i need is to seperate the values and put them into ip and port variables that i can use later when connecting to remote hosts with SSH using paramiko.

I think i need to be ablo to put the db output into a list or array so i can assign values for ip and port.
like:
hostlist(0,1) = domain.name.com, 22
hostlist(2,3) = test1, 22 etc....

[nilamo]

host, ip = row

#or, if you define a hostlist above...
hostlist.append(row)

[desudesu]

import mysql.connector
from mysql.connector import Error

class dbcon:
  """ Connect to MySQL database """
   hostlist = []
   ip = 0
   port = 0
  try:
      print('Trying to establish connection to database...')
      connection = mysql.connector.connect(host='db.sample.com',
                                           database='ipinfo',
                                           user='user',
                                           password='password')
      if connection.is_connected():
          print('Connection Successful!!!')
          cursor = connection.cursor()
          cursor.execute("SELECT ip, port FROM stations")

          row = cursor.fetchone()

          while row is not None:
              hostlist.append(row)
              row = cursor.fetchone()
              
  except Error as e:
      print(e)

   finally:
       ip = hostlist[0]    # this needs to be ip from the first row 
       port = hostlist[1]  # this needs to be port from the first row
       
       print(ip, port)     # this now prints ('domain.name.com', '22') ('test1', '22')
       print("Connection to database closed!")
       connection.close()