I'm rebuilding my site's backend from scratch using Python (no frameworks, and I'm also new to Python), and I'm working on the scripts and models to build a database for my posts and articles using SQLite, SQLAlchemy, and Alembic (I'm new to all three of these too). So far I've got Python scripts to build the database engine and session (dbsite.py), a script for the database models (models.py), a script to build the database (dbcreate.py), and a test script to add posts to the database once I can build it with no errors (addpost.py). Here's the code for all of these scripts:
dbsite.py
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
engine = create_engine('sqlite:///site.db', echo=True)
# Create a base class for our models
Base = declarative_base()
# Create a session factory bound to our engine
SessionLocal = sessionmaker(bind=engine)
models.py
from sqlalchemy import Column, Integer, String, Text, Date
from datetime import datetime, timezone
from dbsite import Base
class Post(Base):
__tablename__ = 'posts'
id = Column(Integer, primary_key=True)
title = Column(String(150), nullable=False)
body = Column(Text)
date = Column(datetime(timezone=True), default=func.now()))
# Tags = TBD, but this will probably be another table for cross-referencing
def __repr__(self):
return f"<Post(title='{self.title}', body={self.body}, date={self.date})>"
dbcreate.py
from dbsite import engine, Base
from models import Post
# Create all tables defined in our models
Base.metadata.create_all(engine)
print("Site database table created!")
Currently, dbcreate.py imports from dbsite.py and models.py, and when I run that script, I get this error in the terminal:
TypeError: function missing required argument 'year' (pos 1).
After digging around on here and other parts of the internet for a bit, it seems like SQLite doesn't handle datetime well out of the box, so I need to write some code using SQLAlchemy to work around this, but I'm not sure what I need to do because some examples I find online have extra bits that my database doesn't need. For example, this article adds metadata to the engine/database, and looks like it's just a standalone example, instead of using a real-world example (unless I'm misunderstanding the code).
All I'm trying to do is add a Date column to a database based on the date that I add an article or post to my database, based on when it's added to the server. I think I want to use UTC so the time adapts to wherever readers are located, and I want to include YYYY MM DD, plus day of the week, as well as HH:MM:SS — I'll be pulling parts of that data for different parts of my site, like all the date/day of week stuff for each article itself, and then all that plus the time for my RSS feed. Keep in mind, I mentioned Tags in models.py for future cross-referencing, and I might need to cross-reference Date too, because I plan to build archives of posts based on year and month/year. Not sure if that changes how I should code this, but I wanted to mention it since I haven't gotten that far yet with building out the models.
With all that in mind, how should I build my scripts? I'm not wedded to any of the code, I'm just trying to get the stuff to work, so if I need to start from scratch, I'm open to that.
The error stems from a typo in the column definition. The first attribute that Column accepts is the data type for the column. In your case, this corresponds to sqlalchemy.DateTime. You accidentally used the Python data type datetime.datetime, which expects different attributes.
Furthermore, I recommend using the server_default attribute for a default date and time value. This ensures that the value is automatically set when a record is inserted, as specified in the CREATE TABLE statement.
This is how the corrected code would look in models.py.
from sqlalchemy import Column, Integer, String, Text, DateTime, func
from dbsite import Base
class Post(Base):
__tablename__ = 'posts'
id = Column(Integer, primary_key=True)
title = Column(String(150), nullable=False)
body = Column(Text)
date = Column(DateTime(timezone=True), server_default=func.now())
# Tags = TBD, but this will probably be another table for cross-referencing
def __repr__(self):
return f"<Post(title='{self.title}', body={self.body}, date={self.date})>"