написал учебный телеграм бот из этого ютуб ролика:
https://www.youtube.com/watch?v=OQ6cEG0ykVs&list=P....
Он использует базу в sqlite и он как-то легко подключил через pgadmin. У меня такого инструмента нету, на сервере. хотелосьбы в командной строке настроить. Как мне перенести базу на postgresql?
Базу я создал, к базе я без проблем могу подключиться. но когда я запускаю программу выдает такую ошибку:
$ python main.py
Traceback (most recent call last):
File "/home/tuser/TelegramBotAiogram4/main.py", line 17, in <module>
asyncio.run(main())
File "/usr/lib/python3.10/asyncio/runners.py", line 44, in run
return loop.run_until_complete(main)
File "/usr/lib/python3.10/asyncio/base_events.py", line 649, in run_until_complete
return future.result()
File "/home/tuser/TelegramBotAiogram4/main.py", line 8, in main
await async_main()
File "/home/tuser/TelegramBotAiogram4/app/database/models.py", line 30, in async_main
async with engine.begin() as conn:
File "/usr/lib/python3.10/contextlib.py", line 199, in __aenter__
return await anext(self.gen)
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/ext/asyncio/engine.py", line 1063, in begin
async with conn:
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/ext/asyncio/base.py", line 121, in __aenter__
return await self.start(is_ctxmanager=True)
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/ext/asyncio/engine.py", line 273, in start
await greenlet_spawn(self.sync_engine.connect)
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 201, in greenlet_spawn
result = context.throw(*sys.exc_info())
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3276, in connect
return self._connection_cls(self)
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 146, in __init__
self._dbapi_connection = engine.raw_connection()
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3300, in raw_connection
return self.pool.connect()
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 449, in connect
return _ConnectionFairy._checkout(self)
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1263, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 712, in checkout
rec = pool._do_get()
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 179, in _do_get
with util.safe_reraise():
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 177, in _do_get
return self._create_connection()
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
return _ConnectionRecord(self)
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 674, in __init__
self.__connect()
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 900, in __connect
with util.safe_reraise():
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 896, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 643, in connect
return dialect.connect(*cargs, **cparams)
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 620, in connect
return self.loaded_dbapi.connect(*cargs, **cparams)
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 937, in connect
await_only(creator_fn(*arg, **kw)),
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 131, in await_only
return current.driver.switch(awaitable) # type: ignore[no-any-return]
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 196, in greenlet_spawn
value = await result
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/asyncpg/connection.py", line 2329, in connect
return await connect_utils._connect(
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/asyncpg/connect_utils.py", line 991, in _connect
conn = await _connect_addr(
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/asyncpg/connect_utils.py", line 828, in _connect_addr
return await __connect_addr(params, True, *args)
File "/home/tuser/Projects/pyenv/lib/python3.10/site-packages/asyncpg/connect_utils.py", line 876, in __connect_addr
await connected
asyncpg.exceptions.InvalidCatalogNameError: database "tuser:tpassword@192.168.122.98/shop" does not exist
Вот как я настроил базу в python:
$ cat .env
TOKEN=...
SQLALCHEMY_URL=postgresql+asyncpg:///tuser:tpassword@192.168.122.98/shop
$ cat main.py
import asyncio
from aiogram import Bot, Dispatcher
from app.handlers import router
from app.database.models import async_main
import os
from dotenv import load_dotenv
async def main():
await async_main()
load_dotenv()
bot = Bot(token=os.getenv('TOKEN'))
dp = Dispatcher()
dp.include_router(router)
await dp.start_polling(bot)
if __name__ == '__main__':
try:
asyncio.run(main())
except KeyboardInterrupt:
print('Bot disabled')
$ cat app/database/models.py
from sqlalchemy import BigInteger, String, ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy.ext.asyncio import AsyncAttrs, async_sessionmaker, create_async_engine
import os
from dotenv import load_dotenv
load_dotenv()
engine = create_async_engine(url=os.getenv('SQLALCHEMY_URL'))
async_session = async_sessionmaker(engine)
class Base(AsyncAttrs, DeclarativeBase):
pass
class User(Base):
__tablename__ = 'users'
id: Mapped[int] = mapped_column(primary_key=True)
tg_id = mapped_column(BigInteger)
class Category(Base):
__tablename__ = 'categories'
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(25))
class Item(Base):
__tablename__ = 'items'
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(25))
description: Mapped[str] = mapped_column(String(120))
price: Mapped[int] = mapped_column()
category: Mapped[int] = mapped_column(ForeignKey('categories.id'))
async def async_main():
async with engine.begin() as conn:
await conn.run_sync(Base.metadata.create_all)
Итак, каким образом нужно перенести данные с Sqlite3 на Postgresql?
Средний