I'am trying to create mysql database by using sqlalchemy and running into this error. Adding full traceback of an error:
Traceback (most recent call last):
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 145, in __init__
self._dbapi_connection = engine.raw_connection()
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 3292, in raw_connection
return self.pool.connect()
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py", line 452, in connect
return _ConnectionFairy._checkout(self)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py", line 1269, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py", line 716, in checkout
rec = pool._do_get()
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\impl.py", line 169, in _do_get
with util.safe_reraise():
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\util\langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\impl.py", line 167, in _do_get
return self._create_connection()
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py", line 393, in _create_connection
return _ConnectionRecord(self)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py", line 678, in __init__
self.__connect()
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py", line 902, in __connect
with util.safe_reraise():
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\util\langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py", line 898, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\create.py", line 637, in connect
return dialect.connect(*cargs, **cparams)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\default.py", line 616, in connect
return self.loaded_dbapi.connect(*cargs, **cparams)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\connections.py", line 353, in __init__
self.connect()
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\connections.py", line 633, in connect
self._request_authentication()
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\connections.py", line 932, in _request_authentication
auth_packet = _auth.caching_sha2_password_auth(self, auth_packet)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\_auth.py", line 239, in caching_sha2_password_auth
pkt = conn._read_packet()
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\connections.py", line 725, in _read_packet
packet.raise_for_error()
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\protocol.py", line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\err.py", line 143, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.OperationalError: (1049, "Unknown database 'books'")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "C:\Program Files\JetBrains\PyCharm 2023.3\plugins\python\helpers\pydev\pydevconsole.py", line 364, in runcode
coro = func()
File "<input>", line 1, in <module>
File "C:\Program Files\JetBrains\PyCharm 2023.3\plugins\python\helpers\pydev\_pydev_bundle\pydev_umd.py", line 197, in runfile
pydev_imports.execfile(filename, global_vars, local_vars) # execute the script
File "C:\Program Files\JetBrains\PyCharm 2023.3\plugins\python\helpers\pydev\_pydev_imps\_pydev_execfile.py", line 18, in execfile
exec(compile(contents+"\n", file, 'exec'), glob, loc)
File "C:\Users\baron\Python_Darbai\Turing_college\GIT\Sprint_3\dbaron-DE1.3\test_db_creaton.py", line 183, in <module>
db_manager.create_database()
File "C:\Users\baron\Python_Darbai\Turing_college\GIT\Sprint_3\dbaron-DE1.3\test_db_creaton.py", line 29, in create_database
self.session.execute(create_database_query)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\session.py", line 2308, in execute
return self._execute_internal(
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\session.py", line 2180, in _execute_internal
conn = self._connection_for_bind(bind)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\session.py", line 2047, in _connection_for_bind
return trans._connection_for_bind(engine, execution_options)
File "<string>", line 2, in _connection_for_bind
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\state_changes.py", line 139, in _go
ret_value = fn(self, *arg, **kw)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\orm\session.py", line 1143, in _connection_for_bind
conn = bind.connect()
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 3268, in connect
return self._connection_cls(self)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 147, in __init__
Connection._handle_dbapi_exception_noconnection(
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 2430, in _handle_dbapi_exception_noconnection
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 145, in __init__
self._dbapi_connection = engine.raw_connection()
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.py", line 3292, in raw_connection
return self.pool.connect()
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py", line 452, in connect
return _ConnectionFairy._checkout(self)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py", line 1269, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py", line 716, in checkout
rec = pool._do_get()
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\impl.py", line 169, in _do_get
with util.safe_reraise():
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\util\langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\impl.py", line 167, in _do_get
return self._create_connection()
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py", line 393, in _create_connection
return _ConnectionRecord(self)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py", line 678, in __init__
self.__connect()
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py", line 902, in __connect
with util.safe_reraise():
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\util\langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\pool\base.py", line 898, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\create.py", line 637, in connect
return dialect.connect(*cargs, **cparams)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\default.py", line 616, in connect
return self.loaded_dbapi.connect(*cargs, **cparams)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\connections.py", line 353, in __init__
self.connect()
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\connections.py", line 633, in connect
self._request_authentication()
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\connections.py", line 932, in _request_authentication
auth_packet = _auth.caching_sha2_password_auth(self, auth_packet)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\_auth.py", line 239, in caching_sha2_password_auth
pkt = conn._read_packet()
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\connections.py", line 725, in _read_packet
packet.raise_for_error()
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\protocol.py", line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File "C:\Users\baron\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\err.py", line 143, in raise_mysql_exception
raise errorclass(errno, errval)
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1049, "Unknown database 'books'")
(Background on this error at: https://sqlalche.me/e/20/e3q8)
All database credentials are in .env file. Adding code snippet, maybe something wrong there:
from sqlalchemy import Column, Integer, String, ForeignKey, Float
from sqlalchemy.orm import relationship, declarative_base
import os
from dotenv import load_dotenv
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
import pymysql
Base = declarative_base()
class DatabaseManager:
def __init__(self):
load_dotenv()
self.root_username = os.getenv("ROOT_USERNAME")
self.root_password = os.getenv("ROOT_PASSWORD")
self.new_database = os.getenv("NEW_DATABASE")
if None in [self.root_username, self.root_password, self.new_database]:
raise ValueError("Missing environment variables")
self.DB_URI = f"mysql+pymysql://{self.root_username}:{self.root_password}@localhost/{self.new_database}"
self.engine = create_engine(self.DB_URI, echo=True)
self.Session = sessionmaker(bind=self.engine)
self.session = self.Session()
def create_database(self):
create_database_query = text(f"CREATE DATABASE IF NOT EXISTS {self.new_database}")
self.session.execute(create_database_query)
self.session.commit()
def create_tables(self):
Base.metadata.create_all(self.engine)
def close_connection(self):
self.session.close()
What is wrong with this part of my code (not adding all, because it's tooo long, and there's basically only tables) ?
You have to create the database books before you run this code. SQLAlchemy doesn't create database for you, it expects that database already exists