0

I am trying to load an excel file into a database for which I have to first connect to my SQL server using python. MYSQL server is already running in the background. Now when I try to run this code:

import xlrd
import pymysql
xl_data = xlrd.open_workbook('C:/Users/xxx/Desktop/xyz.xlsx')
mydb = pymysql.connect( host = 'localhost' ,  user ="x" ,  passwd = "x" , db = "")  
cursor = mydb.cursor()

I get the following error:

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
<ipython-input-2-d43a74bc83ce> in <module>
      3 
      4 xl_data = xlrd.open_workbook('C:/Users/Sankalp/Desktop/Problem_Sample Linkedin Data.xlsx')
----> 5 mydb = pymysql.connect( host = 'localhost' ,  user ="x" ,  passwd = "x" , db = "")
      6 cursor = mydb.cursor()

D:\Softwares\Anaconda\lib\site-packages\pymysql\__init__.py in Connect(*args, **kwargs)
     92     """
     93     from .connections import Connection
---> 94     return Connection(*args, **kwargs)
     95 
     96 from . import connections as _orig_conn

D:\Softwares\Anaconda\lib\site-packages\pymysql\connections.py in __init__(self, host, user, password, database, port, unix_socket, charset, sql_mode, read_default_file, conv, use_unicode, client_flag, cursorclass, init_command, connect_timeout, ssl, read_default_group, compress, named_pipe, autocommit, db, passwd, local_infile, max_allowed_packet, defer_connect, auth_plugin_map, read_timeout, write_timeout, bind_address, binary_prefix, program_name, server_public_key)
    323             self._sock = None
    324         else:
--> 325             self.connect()
    326 
    327     def _create_ssl_ctx(self, sslp):

D:\Softwares\Anaconda\lib\site-packages\pymysql\connections.py in connect(self, sock)
    597 
    598             self._get_server_information()
--> 599             self._request_authentication()
    600 
    601             if self.sql_mode is not None:

D:\Softwares\Anaconda\lib\site-packages\pymysql\connections.py in _request_authentication(self)
    869             plugin_name = auth_packet.read_string()
    870             if self.server_capabilities & CLIENT.PLUGIN_AUTH and plugin_name is not None:
--> 871                 auth_packet = self._process_auth(plugin_name, auth_packet)
    872             else:
    873                 # send legacy handshake

D:\Softwares\Anaconda\lib\site-packages\pymysql\connections.py in _process_auth(self, plugin_name, auth_packet)
    900             return _auth.caching_sha2_password_auth(self, auth_packet)
    901         elif plugin_name == b"sha256_password":
--> 902             return _auth.sha256_password_auth(self, auth_packet)
    903         elif plugin_name == b"mysql_native_password":
    904             data = _auth.scramble_native_password(self.password, auth_packet.read_all())

D:\Softwares\Anaconda\lib\site-packages\pymysql\_auth.py in sha256_password_auth(conn, pkt)
    181         data = b''
    182 
--> 183     return _roundtrip(conn, data)
    184 
    185 

D:\Softwares\Anaconda\lib\site-packages\pymysql\_auth.py in _roundtrip(conn, send_data)
    120 def _roundtrip(conn, send_data):
    121     conn.write_packet(send_data)
--> 122     pkt = conn._read_packet()
    123     pkt.check_error()
    124     return pkt

D:\Softwares\Anaconda\lib\site-packages\pymysql\connections.py in _read_packet(self, packet_type)
    682 
    683         packet = packet_type(buff, self.encoding)
--> 684         packet.check_error()
    685         return packet
    686 

D:\Softwares\Anaconda\lib\site-packages\pymysql\protocol.py in check_error(self)
    218             errno = self.read_uint16()
    219             if DEBUG: print("errno =", errno)
--> 220             err.raise_mysql_exception(self._data)
    221 
    222     def dump(self):

D:\Softwares\Anaconda\lib\site-packages\pymysql\err.py in raise_mysql_exception(data)
    107         errval = data[3:].decode('utf-8', 'replace')
    108     errorclass = error_map.get(errno, InternalError)
--> 109     raise errorclass(errno, errval)

**OperationalError: (1045, "Access denied for user 'x'@'localhost' (using password: YES)")**

How to rectify this error? Can anyone help?

Is it the permissions problem? Or incorrect details?

I need a heads up.

Thanks!

Sank_BE
  • 33
  • 5
  • You are connecting to "my SQL Server" or to a "MySQL Server"? "MySQL" and "SQL Server" are different products and you need to use different drivers to connect to each of them. – Ali Tou Apr 27 '20 at 11:36
  • From the last line of your stack trace, apparently you're actually using MySQL. So, are you sure that `user` has privilege to access that `db`? Check this out if you haven't create any users or you're not using the pre-existing `root` user: https://stackoverflow.com/a/20353481/8543203 – Ali Tou Apr 27 '20 at 11:42
  • "Is it the permissions problem? Or incorrect details?" - I think this is incorrect details. From a quick test if you try and access a database you don't have permission to you get error 1044 not 1045. – Rup Apr 27 '20 at 11:46
  • Thanks for the reply. Actually I cannot even run mysql from mysql server. I gives the following error: ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO) – Sank_BE Apr 27 '20 at 11:46
  • Then you need to give it a username and password: `mysql -u -p`, and also `-D `. And don't you need a database name in your pymysql.connect() ? – Rup Apr 27 '20 at 11:47
  • Which command line should I run? I need to find out the correct details if that's the case. – Sank_BE Apr 27 '20 at 11:50
  • I tried running mysql -u user -p -D mysqldb from cmd. It still gives same error. – Sank_BE Apr 27 '20 at 11:53
  • Then you should ask whoever set up the database for you. If you have the root password to the mysql instance then you can reset the password for the database user but you can't recover it. Since you're on Windows I'd recommend the MySQL Workbench or similar IDE if you're not comfortable with command lines. – Rup Apr 27 '20 at 11:53
  • I have MySQL Workbench. It shows "Hostname", "Port", "Username". I know the password also. It also tells me the "Connection Name". – Sank_BE Apr 27 '20 at 11:55
  • OK, so give it hostname = localhost, port = 3306 (or just leave it blank, that's the default), and fill in the correct username: then try connecting, and it should ask you for the password. Connection name can be anything, it's just to save the connection details for later. – Rup Apr 27 '20 at 11:58
  • "I tried running mysql -u user -p -D mysqldb from cmd" - oh, I missed that. If that doesn't work then your connection details are wrong. You'll either have to login as the root user and reset the password for 'user', or ask whoever set the database up in the first place. – Rup Apr 27 '20 at 12:01
  • It is by default: hostname = localhost, Port = 3306, Username = Root. Now if I click on "Test Connection", it says "The specified module cannot be found". – Sank_BE Apr 27 '20 at 12:08
  • That's a missing DLL. It sounds like your Workbench installation is broken then. (And it's normally 'root' with a lower-case-r, but sounds like that's not the problem here.) – Rup Apr 27 '20 at 12:10
  • Just changed user = "root" in my python script and now it does not show error. Looks like my details were all wrong. db is still left empty. What should I set by database to? – Sank_BE Apr 27 '20 at 12:12
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/212614/discussion-between-sank-be-and-rup). – Sank_BE Apr 27 '20 at 12:15

0 Answers0