0

The Problem: I want to read data from an xls file into MySQL database using python, I am new to MySQL, I have my database ic4projournal and the table ic4projournal too, the table contained up to 30 columns.

EDIT: This what I get after running the select query:myuser-workbench-query-table Anytime I run this query: SHOW GRANTS FOR root I get this error:

Error Code: 1141. There is no such grant defined for user 'root' on host '%'

mysql version: 8.0.29

My Solution:

import pymysql
import xlrd

HOST = 'localhost'
PORT = 3306
USER = "root"
PASSWORD = "password"
DB = 'ic4projournal'

book = xlrd.open_workbook('ic4projournal.xls')
sh = book.sheet_by_index(0)

# Connect to database
connection = pymysql.connect(host=HOST,
                             user=USER,
                             password=PASSWORD,
                             db=DB,
                             port=PORT,
                             autocommit=True)

cursor = connection.cursor()
# Insert in to already created database in mysql
query = """INSERT INTO ic4projournal transid,
                                    branchid,
                                    branchcode,
                                    currentdate,
                                    postingdatestamp,
                                    psdate,
                                    postingdate,
                                    posteddate,
                                    postedtime,
                                    intpostingdates,
                                    postingtime,
                                    postingdates,
                                    accountno,
                                    accountid,
                                    accountdesc,
                                    amount,
                                    currcode,
                                    transitcode,
                                    transcode,
                                    transtype,
                                    transtypeid,
                                    translocation,
                                    prodcode,
                                    narrative,
                                    branchadded,
                                    depositorname,
                                    addedby,
                                    addedapprovedby,
                                    workstationipadded,
                                    transmode,
                                    transtime VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""

# loop over each row
# Resolving values to insert
for row in range(1, sh.nrows):
    values = [sh.cell_value(row, col) for col in range(sh.ncols)]
    # write pair to db
    cursor.execute(query, *values)

# close everything
cursor.close()
connection.close()

The error:

raise errorclass(errno, errval)
pymysql.err.OperationalError: (1045, "Access denied for user 'root'@'localhost' (using password: YES)")

My question: How do I resolve this error?

Thank you

densteam-io
  • 33
  • 8
  • This is an extremely common question - have you tried searching for the error message online, or looking at any of the links in the "Related" column in the sidebar on the right hand side of this page? – snakecharmerb Jul 16 '22 at 10:07
  • @snakecharmerb Yes, I have followed the instruction in those links but it still didn't work, I really want to understand why I am getting this error and how to resolve it quickly. Thank you – densteam-io Jul 16 '22 at 10:15
  • The solution is almost always to follow the steps in [this answer](https://stackoverflow.com/a/53487418/5320906). If those steps don't work, you need to provide more information (such as the output of the `SELECT` query at the beginning of the answer) because we have no way of knowing how your server or environment is configured. – snakecharmerb Jul 16 '22 at 10:38
  • As @snakecharmerb mentioned you need to check similar questions and their answers first. Another way for debugging the issue is to break it down, forget about your code and try to connect to the database directly using the same credentials through CLI or mysql workbench, are you able to connect? – Hasnaa Ibraheem Jul 16 '22 at 11:42
  • @snakecharmerb I have looked into the link and I have done all that they said, but still getting the same error; above is the edited version of my code. – densteam-io Jul 16 '22 at 18:54
  • @HasnaaIbraheem Yes, I'm able to connect – densteam-io Jul 16 '22 at 20:10
  • In the results of the select query you can see the authentication plugin is `caching_sha2_password`, not `mysql_native_password`, so you might want to try the steps in [this answer](https://stackoverflow.com/a/63054924/5320906). – snakecharmerb Jul 17 '22 at 05:14

0 Answers0