sqlalchemy not working????..what is the problem here?

Question

THE CODING:

import pandas as pd

import sqlalchemy as sq

con=sq.create_engine("mysql+mysqlconnector://root:'*******'@localhost:3306/railways")

df = pd.DataFrame({'Name':['Tom', 'nick', 'krish', 'jack'],'Age':[20, 21, 19, 18]})

print("DataFramecontents")

print(df)

df.to_sql("cricket_schoolteam",con)

THE OUTCOME:

DataFramecontents
    Name  Age
0    Tom   20
1   nick   21
2  krish   19
3   jack   18

Traceback (most recent call last):
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection_cext.py", line 233, in _open_connection
    self._cmysql.connect(**cnx_kwargs)
_mysql_connector.MySQLInterfaceError: Access denied for user 'root'@'localhost' (using password: YES)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\base.py", line 2336, in _wrap_pool_connect
    return fn()
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 364, in connect
    return _ConnectionFairy._checkout(self)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 778, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 495, in checkout
    rec = pool._do_get()
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\impl.py", line 140, in _do_get
    self._dec_overflow()
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    with_traceback=exc_tb,
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\compat.py", line 182, in raise_
    raise exception
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\impl.py", line 137, in _do_get
    return self._create_connection()
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 309, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 440, in __init__
    self.__connect(first_connect_check=True)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 661, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    with_traceback=exc_tb,
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\compat.py", line 182, in raise_
    raise exception
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 656, in __connect
    connection = pool._invoke_creator(self)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\default.py", line 493, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\__init__.py", line 270, in connect
    return CMySQLConnection(*args, **kwargs)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection_cext.py", line 86, in __init__
    self.connect(**kwargs)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\abstracts.py", line 985, in connect
    self._open_connection()
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection_cext.py", line 236, in _open_connection
    sqlstate=exc.sqlstate)
mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\lenovo\Desktop\classes liza\plan\lizzaa.py", line 7, in <module>
    df.to_sql("cricket_schoolteam",con)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\core\generic.py", line 2615, in to_sql
    method=method,
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\io\sql.py", line 598, in to_sql
    method=method,
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\io\sql.py", line 1393, in to_sql
    table.create()
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\io\sql.py", line 721, in create
    if self.exists():
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\io\sql.py", line 708, in exists
    return self.pd_sql.has_table(self.name, self.schema)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\io\sql.py", line 1431, in has_table
    self.connectable.dialect.has_table, name, schema or self.meta.schema
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\base.py", line 2211, in run_callable
    with self._contextual_connect() as conn:
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\base.py", line 2302, in _contextual_connect
    self._wrap_pool_connect(self.pool.connect, None),
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\base.py", line 2340, in _wrap_pool_connect
    e, dialect, self
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\base.py", line 1584, in _handle_dbapi_exception_noconnection
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\compat.py", line 182, in raise_
    raise exception
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\base.py", line 2336, in _wrap_pool_connect
    return fn()
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 364, in connect
    return _ConnectionFairy._checkout(self)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 778, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 495, in checkout
    rec = pool._do_get()
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\impl.py", line 140, in _do_get
    self._dec_overflow()
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    with_traceback=exc_tb,
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\compat.py", line 182, in raise_
    raise exception
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\impl.py", line 137, in _do_get
    return self._create_connection()
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 309, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 440, in __init__
    self.__connect(first_connect_check=True)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 661, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
    with_traceback=exc_tb,
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\util\compat.py", line 182, in raise_
    raise exception
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\pool\base.py", line 656, in __connect
    connection = pool._invoke_creator(self)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\sqlalchemy\engine\default.py", line 493, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\__init__.py", line 270, in connect
    return CMySQLConnection(*args, **kwargs)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection_cext.py", line 86, in __init__
    self.connect(**kwargs)
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\abstracts.py", line 985, in connect
    self._open_connection()
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection_cext.py", line 236, in _open_connection
    sqlstate=exc.sqlstate)
sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

(Background on this error at: http://sqlalche.me/e/13/f405)

WHILE MYSQLCONNECOR WORKS?

import mysql.connector as sa

db=sa.connect(host="localhost",user="root",password="******",database="railways")

print(db)

<mysql.connector.connection_cext.CMySQLConnection object at 0x000001EF02248D88>

`Access denied for user 'root'@'localhost'` → You have an authentication problem, maybe because of the extra quotation marks around the password. — Klaus D., Dec 01 '20 at 15:28
Further exploration idea: check the mysql logs as well. does the user have permissions to create (or write to) the desired table? — van, Dec 01 '20 at 15:31

sqlalchemy not working????..what is the problem here?

0 Answers0