mysql插入数据报错
问题遇到的现象和发生背景
win10 vscode python3.9.2 在mysql中建立一个叫person的表，表里有三个参数，birthday、name、ms，写入数据
```python
from sqlalchemy import Column,create_engine,String,Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base=declarative_base()
class person(Base):
__tablename__="person"
birthday=Column(String(20),primary_key=True)
name=Column(String(50))
ms=Column(Integer)
engine=create_engine("mysql+mysqlconnector://yury:miu0324@localhost:3306/score")
DBSession=sessionmaker(bind=engine)
session=DBSession()
daiya=person(birthday="0101",name="daiya",ms=100)
umi=person(birthday="0315",name="umi",ms=150)
sarah=person(birthday="0504",name="sarah",ms=140)
session.add(daiya)
session.add(umi)
session.add(sarah)
session.commit()
session.close()
###### 运行结果及报错内容
```python
Traceback (most recent call last):
File "D:\python39\lib\site-packages\mysql\connector\connection_cext.py", line 246, in _open_connection
self._cmysql.connect(**cnx_kwargs)
_mysql_connector.MySQLInterfaceError: Access denied for user 'yury'@'localhost' (using password: YES)
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "D:\python39\lib\site-packages\sqlalchemy\engine\base.py", line 3280, in _wrap_pool_connect
return fn()
File "D:\python39\lib\site-packages\sqlalchemy\pool\base.py", line 310, in connect
return _ConnectionFairy._checkout(self)
File "D:\python39\lib\site-packages\sqlalchemy\pool\base.py", line 868, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "D:\python39\lib\site-packages\sqlalchemy\pool\base.py", line 476, in checkout
rec = pool._do_get()
File "D:\python39\lib\site-packages\sqlalchemy\pool\impl.py", line 146, in _do_get
self._dec_overflow()
File "D:\python39\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
compat.raise_(
File "D:\python39\lib\site-packages\sqlalchemy\util\compat.py", line 208, in raise_
raise exception
File "D:\python39\lib\site-packages\sqlalchemy\pool\impl.py", line 143, in _do_get
return self._create_connection()
File "D:\python39\lib\site-packages\sqlalchemy\pool\base.py", line 256, in _create_connection
return _ConnectionRecord(self)
File "D:\python39\lib\site-packages\sqlalchemy\pool\base.py", line 371, in __init__
self.__connect()
File "D:\python39\lib\site-packages\sqlalchemy\pool\base.py", line 666, in __connect
pool.logger.debug("Error on connect(): %s", e)
File "D:\python39\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
compat.raise_(
File "D:\python39\lib\site-packages\sqlalchemy\util\compat.py", line 208, in raise_
raise exception
File "D:\python39\lib\site-packages\sqlalchemy\pool\base.py", line 661, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
File "D:\python39\lib\site-packages\sqlalchemy\engine\create.py", line 590, in connect
return dialect.connect(*cargs, **cparams)
File "D:\python39\lib\site-packages\sqlalchemy\engine\default.py", line 597, in connect
return self.dbapi.connect(*cargs, **cparams)
File "D:\python39\lib\site-packages\mysql\connector\__init__.py", line 272, in connect
return CMySQLConnection(*args, **kwargs)
File "D:\python39\lib\site-packages\mysql\connector\connection_cext.py", line 94, in __init__
self.connect(**kwargs)
File "D:\python39\lib\site-packages\mysql\connector\abstracts.py", line 1052, in connect
self._open_connection()
File "D:\python39\lib\site-packages\mysql\connector\connection_cext.py", line 251, in _open_connection
raise errors.get_mysql_exception(msg=exc.msg, errno=exc.errno,
mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user 'yury'@'localhost' (using password: YES)
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "D:\python39\lib\runpy.py", line 197, in _run_module_as_main
return _run_code(code, main_globals, None,
File "D:\python39\lib\runpy.py", line 87, in _run_code
exec(code, run_globals)
File "c:\Users\hd\.vscode\extensions\ms-python.python-2022.10.0\pythonFiles\lib\python\debugpy\__main__.py", line 39, in <module>
cli.main()
File "c:\Users\hd\.vscode\extensions\ms-python.python-2022.10.0\pythonFiles\lib\python\debugpy/..\debugpy\server\cli.py", line 430, in main
run()
File "c:\Users\hd\.vscode\extensions\ms-python.python-2022.10.0\pythonFiles\lib\python\debugpy/..\debugpy\server\cli.py", line 284, in run_file
runpy.run_path(target, run_name="__main__")
File "c:\Users\hd\.vscode\extensions\ms-python.python-2022.10.0\pythonFiles\lib\python\debugpy\_vendored\pydevd\_pydevd_bundle\pydevd_runpy.py", line 321, in run_path
return _run_module_code(code, init_globals, run_name,
File "c:\Users\hd\.vscode\extensions\ms-python.python-2022.10.0\pythonFiles\lib\python\debugpy\_vendored\pydevd\_pydevd_bundle\pydevd_runpy.py", line 135, in _run_module_code
_run_code(code, mod_globals, init_globals,
File "c:\Users\hd\.vscode\extensions\ms-python.python-2022.10.0\pythonFiles\lib\python\debugpy\_vendored\pydevd\_pydevd_bundle\pydevd_runpy.py", line 124, in _run_code
exec(code, run_globals)
File "d:\python39\shujukushili.py", line 23, in <module>
session.commit()
File "D:\python39\lib\site-packages\sqlalchemy\orm\session.py", line 1451, in commit
self._transaction.commit(_to_root=self.future)
File "D:\python39\lib\site-packages\sqlalchemy\orm\session.py", line 829, in commit
self._prepare_impl()
File "D:\python39\lib\site-packages\sqlalchemy\orm\session.py", line 808, in _prepare_impl
self.session.flush()
File "D:\python39\lib\site-packages\sqlalchemy\orm\session.py", line 3383, in flush
self._flush(objects)
File "D:\python39\lib\site-packages\sqlalchemy\orm\session.py", line 3523, in _flush
transaction.rollback(_capture_exception=True)
File "D:\python39\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
compat.raise_(
File "D:\python39\lib\site-packages\sqlalchemy\util\compat.py", line 208, in raise_
raise exception
File "D:\python39\lib\site-packages\sqlalchemy\orm\session.py", line 3483, in _flush
flush_context.execute()
File "D:\python39\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 456, in execute
rec.execute(self)
File "D:\python39\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 630, in execute
util.preloaded.orm_persistence.save_obj(
File "D:\python39\lib\site-packages\sqlalchemy\orm\persistence.py", line 212, in save_obj
for (
File "D:\python39\lib\site-packages\sqlalchemy\orm\persistence.py", line 373, in _organize_states_for_save
for state, dict_, mapper, connection in _connections_for_states(
File "D:\python39\lib\site-packages\sqlalchemy\orm\persistence.py", line 1737, in _connections_for_states
connection = uowtransaction.transaction.connection(base_mapper)
File "D:\python39\lib\site-packages\sqlalchemy\orm\session.py", line 626, in connection
return self._connection_for_bind(bind, execution_options)
File "D:\python39\lib\site-packages\sqlalchemy\orm\session.py", line 735, in _connection_for_bind
conn = self._parent._connection_for_bind(bind, execution_options)
File "D:\python39\lib\site-packages\sqlalchemy\orm\session.py", line 747, in _connection_for_bind
conn = bind.connect()
File "D:\python39\lib\site-packages\sqlalchemy\engine\base.py", line 3234, in connect
return self._connection_cls(self, close_with_result=close_with_result)
File "D:\python39\lib\site-packages\sqlalchemy\engine\base.py", line 96, in __init__
else engine.raw_connection()
File "D:\python39\lib\site-packages\sqlalchemy\engine\base.py", line 3313, in raw_connection
return self._wrap_pool_connect(self.pool.connect, _connection)
File "D:\python39\lib\site-packages\sqlalchemy\engine\base.py", line 3283, in _wrap_pool_connect
Connection._handle_dbapi_exception_noconnection(
File "D:\python39\lib\site-packages\sqlalchemy\engine\base.py", line 2117, in _handle_dbapi_exception_noconnection
util.raise_(
File "D:\python39\lib\site-packages\sqlalchemy\util\compat.py", line 208, in raise_
raise exception
File "D:\python39\lib\site-packages\sqlalchemy\engine\base.py", line 3280, in _wrap_pool_connect
return fn()
File "D:\python39\lib\site-packages\sqlalchemy\pool\base.py", line 310, in connect
return _ConnectionFairy._checkout(self)
File "D:\python39\lib\site-packages\sqlalchemy\pool\base.py", line 868, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "D:\python39\lib\site-packages\sqlalchemy\pool\base.py", line 476, in checkout
rec = pool._do_get()
File "D:\python39\lib\site-packages\sqlalchemy\pool\impl.py", line 146, in _do_get
self._dec_overflow()
File "D:\python39\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
compat.raise_(
File "D:\python39\lib\site-packages\sqlalchemy\util\compat.py", line 208, in raise_
raise exception
File "D:\python39\lib\site-packages\sqlalchemy\pool\impl.py", line 143, in _do_get
return self._create_connection()
File "D:\python39\lib\site-packages\sqlalchemy\pool\base.py", line 256, in _create_connection
return _ConnectionRecord(self)
File "D:\python39\lib\site-packages\sqlalchemy\pool\base.py", line 371, in __init__
self.__connect()
File "D:\python39\lib\site-packages\sqlalchemy\pool\base.py", line 666, in __connect
pool.logger.debug("Error on connect(): %s", e)
File "D:\python39\lib\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
compat.raise_(
self.connect(**kwargs)
File "D:\python39\lib\site-packages\mysql\connector\abstracts.py", line 1052, in connect
self._open_connection()
File "D:\python39\lib\site-packages\mysql\connector\connection_cext.py", line 251, in _open_connection
raise errors.get_mysql_exception(msg=exc.msg, errno=exc.errno,
sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1045 (28000): Access denied for user 'yury'@'localhost' (using password: YES)
(Background on this error at: https://sqlalche.me/e/14/f405)
访问被拒绝，一般来说这个报错就是说账号密码不对，没有权限
经验证，连我云服务的数据库可以正常连接
所以问题出在我们的连接方式上
1,有可能数据库用户名或者密码不正确
2.检查通过mysql客户端（navicat或者mysql.exe）访问数据库，是否可以正常访问
3.是否修改了yury连接数据库的方式为通过ip连接导致localhost连接失败
localhost与ip远程连接，不能共存，如果已经开启，需要修改localhost为数据库ip，如果只有内网ip填内网ip
数据库用户名或者密码不正确
engine=create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/jdbc?charset=utf8")
创建引擎参考我这个
按照以上的提示，数据库连接有问题
import pymysql
#创建连接
con=pymysql.connect(host='localhost',user='root',password='root',database='python_db',port=3306)
详细的参考我的博客
教你如何用Python连接MySQL，增删改查
https://jeames.blog.csdn.net/article/details/120341020
使用公网IP 访问试试，内网ip肯定访问不了。目前的报错就是数据库拒绝访问
engine=create_engine("mysql+mysqlconnector://yury:miu0324@localhost:3306/score")
密码不正确，使用正确的密码就好了
检查是否有数据库操作权限
检查连接数据库是否正确
数据库连接失败了Access denied for user 'yury'@'localhost' 你先用客户端调试一下，看看能否正常连接，然后在测试脚本
_mysql_connector.MySQLInterfaceError: Access denied for user 'yury'@'localhost' (using password: YES)
数据库连接不成功的问题
数据库连接不成功从以下几个方面去判断：
1、用户名密码是否正确，你可以使用工具或者命令窗口连接一下试试看
2、确认当前用户是不是yury用户仅允许本地 localhost 登录，是的话接着执行，就要修改它的host为%，表示任意IP地址都可以登录。
mysql> update user set host =’%’ where user=‘yury’;