I am trying to store feedparser data into a Postgres database via asyncpg and I am getting an error while storing pubdate that is of type timestamptz in the database I have a Postgres table test with the following structure
+---------------------+----------------------+
| feed_item_id (uuid) | pubdate(timestamptz) |
+---------------------+----------------------+
| ... | ... |
+---------------------+----------------------+
I am using feedparser to load data from RSS and save it to this table
def md5(text):
import hashlib
return hashlib.md5(text.encode('utf-8')).hexdigest()
def fetch():
import feedparser
data = feedparser.parse('https://cointelegraph.com/rss')
return data
async def insert(rows):
import asyncpg
async with asyncpg.create_pool(user='postgres', database='postgres') as pool:
async with pool.acquire() as conn:
results = await conn.executemany('INSERT INTO test (feed_item_id, pubdate) VALUES($1, $2)', rows)
print(results)
async def main():
data = fetch()
first_entry = data.entries[0]
await insert([(md5(first_entry.guid), first_entry.published)])
import asyncio
asyncio.run(main())
I immediately get this error
Traceback (most recent call last):
File "asyncpg/protocol/prepared_stmt.pyx", line 168, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
File "asyncpg/protocol/codecs/base.pyx", line 206, in asyncpg.protocol.protocol.Codec.encode
File "asyncpg/protocol/codecs/base.pyx", line 111, in asyncpg.protocol.protocol.Codec.encode_scalar
File "asyncpg/pgproto/./codecs/datetime.pyx", line 208, in asyncpg.pgproto.pgproto.timestamptz_encode
TypeError: expected a datetime.date or datetime.datetime instance, got 'str'
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/opt/homebrew/Cellar/python@3.9/3.9.13_1/Frameworks/Python.framework/Versions/3.9/lib/python3.9/asyncio/runners.py", line 44, in run
return loop.run_until_complete(main)
File "/opt/homebrew/Cellar/python@3.9/3.9.13_1/Frameworks/Python.framework/Versions/3.9/lib/python3.9/asyncio/base_events.py", line 647, in run_until_complete
return future.result()
File "<stdin>", line 4, in main
File "<stdin>", line 5, in insert
File "/Users/vr/.local/share/virtualenvs/python-load-feed-items-uopsj7-P/lib/python3.9/site-packages/asyncpg/connection.py", line 358, in executemany
return await self._executemany(command, args, timeout)
File "/Users/vr/.local/share/virtualenvs/python-load-feed-items-uopsj7-P/lib/python3.9/site-packages/asyncpg/connection.py", line 1697, in _executemany
result, _ = await self._do_execute(query, executor, timeout)
File "/Users/vr/.local/share/virtualenvs/python-load-feed-items-uopsj7-P/lib/python3.9/site-packages/asyncpg/connection.py", line 1731, in _do_execute
result = await executor(stmt, None)
File "asyncpg/protocol/protocol.pyx", line 254, in bind_execute_many
File "asyncpg/protocol/coreproto.pyx", line 958, in asyncpg.protocol.protocol.CoreProtocol._bind_execute_many_more
File "asyncpg/protocol/protocol.pyx", line 220, in genexpr
File "asyncpg/protocol/prepared_stmt.pyx", line 197, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
asyncpg.exceptions.DataError: invalid input for query argument $2 in element #0 of executemany() sequence: 'Thu, 04 Aug 2022 03:29:19 +0100' (expected a datetime.date or datetime.datetime instance, got 'str')
I tried to use the published_parsed field instead of the published and I got another error
async def main():
...
await insert([(md5(first_entry.guid), first_entry.published_parsed)])
asyncio.run(main())
This time the error is complaining about struct_time
Traceback (most recent call last):
File "asyncpg/protocol/prepared_stmt.pyx", line 168, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
File "asyncpg/protocol/codecs/base.pyx", line 206, in asyncpg.protocol.protocol.Codec.encode
File "asyncpg/protocol/codecs/base.pyx", line 111, in asyncpg.protocol.protocol.Codec.encode_scalar
File "asyncpg/pgproto/./codecs/datetime.pyx", line 208, in asyncpg.pgproto.pgproto.timestamptz_encode
TypeError: expected a datetime.date or datetime.datetime instance, got 'struct_time'
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/opt/homebrew/Cellar/python@3.9/3.9.13_1/Frameworks/Python.framework/Versions/3.9/lib/python3.9/asyncio/runners.py", line 44, in run
return loop.run_until_complete(main)
File "/opt/homebrew/Cellar/python@3.9/3.9.13_1/Frameworks/Python.framework/Versions/3.9/lib/python3.9/asyncio/base_events.py", line 647, in run_until_complete
return future.result()
File "<stdin>", line 4, in main
File "<stdin>", line 5, in insert
File "/Users/vr/.local/share/virtualenvs/python-load-feed-items-uopsj7-P/lib/python3.9/site-packages/asyncpg/connection.py", line 358, in executemany
return await self._executemany(command, args, timeout)
File "/Users/vr/.local/share/virtualenvs/python-load-feed-items-uopsj7-P/lib/python3.9/site-packages/asyncpg/connection.py", line 1697, in _executemany
result, _ = await self._do_execute(query, executor, timeout)
File "/Users/vr/.local/share/virtualenvs/python-load-feed-items-uopsj7-P/lib/python3.9/site-packages/asyncpg/connection.py", line 1731, in _do_execute
result = await executor(stmt, None)
File "asyncpg/protocol/protocol.pyx", line 254, in bind_execute_many
File "asyncpg/protocol/coreproto.pyx", line 958, in asyncpg.protocol.protocol.CoreProtocol._bind_execute_many_more
File "asyncpg/protocol/protocol.pyx", line 220, in genexpr
File "asyncpg/protocol/prepared_stmt.pyx", line 197, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
asyncpg.exceptions.DataError: invalid input for query argument $2 in element #0 of executemany() sequence: time.struct_time(tm_year=2022, tm_mon=8,... (expected a datetime.date or datetime.datetime instance, got 'struct_time')
How do I store this feedparser date into Postgres using asyncpg?
