Asked  7 Months ago    Answers:  5   Viewed   51 times

Suppose I want to store thousands of days in a table how will I retrieve it from the calendar?

 Answers

85

Here is a generic script you can use in SQL server. just amend the start and end dates:

IF EXISTS (SELECT * FROM information_schema.tables WHERE Table_Name = 'Calendar' AND Table_Type = 'BASE TABLE')
BEGIN
DROP TABLE [Calendar]
END

CREATE TABLE [Calendar]
(
    [CalendarDate] DATETIME
)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = GETDATE()
SET @EndDate = DATEADD(d, 365, @StartDate)

WHILE @StartDate <= @EndDate
      BEGIN
             INSERT INTO [Calendar]
             (
                   CalendarDate
             )
             SELECT
                   @StartDate

             SET @StartDate = DATEADD(dd, 1, @StartDate)
      END

If you want a more advanced calendar here is one I found on the net a while ago:

CREATE SCHEMA Auxiliary
-- We put our auxiliary tables and stuff in a separate schema
-- One of the great new things in SQL Server 2005
go

CREATE FUNCTION Auxiliary.Computus
-- Computus (Latin for computation) is the calculation of the date of
-- Easter in the Christian calendar
-- http://en.wikipedia.org/wiki/Computus
-- I'm using the Meeus/Jones/Butcher Gregorian algorithm
(
    @Y INT -- The year we are calculating easter sunday for
)
RETURNS DATETIME
AS
BEGIN
DECLARE
@a INT,
@b INT,
@c INT,
@d INT,
@e INT,
@f INT,
@g INT,
@h INT,
@i INT,
@k INT,
@L INT,
@m INT

SET @a = @Y % 19
SET @b = @Y / 100
SET @c = @Y % 100
SET @d = @b / 4
SET @e = @b % 4
SET @f = (@b + 8) / 25
SET @g = (@b - @f + 1) / 3
SET @h = (19 * @a + @b - @d - @g + 15) % 30
SET @i = @c / 4
SET @k = @c % 4
SET @L = (32 + 2 * @e + 2 * @i - @h - @k) % 7
SET @m = (@a + 11 * @h + 22 * @L) / 451
RETURN(DATEADD(month, ((@h + @L - 7 * @m + 114) / 31)-1, cast(cast(@Y AS VARCHAR) AS Datetime)) + ((@h + @L - 7 * @m + 114) % 31))
END
GO


CREATE TABLE [Auxiliary].[Calendar] (
-- This is the calendar table
  [Date] datetime NOT NULL,
  [Year] int NOT NULL,
  [Quarter] int NOT NULL,
  [Month] int NOT NULL,
  [Week] int NOT NULL,
  [Day] int NOT NULL,
  [DayOfYear] int NOT NULL,
  [Weekday] int NOT NULL,
  [Fiscal_Year] int NOT NULL,
  [Fiscal_Quarter] int NOT NULL,
  [Fiscal_Month] int NOT NULL,
  [KindOfDay] varchar(10) NOT NULL,
  [Description] varchar(50) NULL,
  PRIMARY KEY CLUSTERED ([Date])
)
GO

ALTER TABLE [Auxiliary].[Calendar]
-- In Celkoish style I'm manic about constraints (Never use em ;-))
-- http://www.celko.com/

ADD CONSTRAINT [Calendar_ck] CHECK (  ([Year] > 1900)
AND ([Quarter] BETWEEN 1 AND 4)
AND ([Month] BETWEEN 1 AND 12)
AND ([Week]  BETWEEN 1 AND 53)
AND ([Day] BETWEEN 1 AND 31)
AND ([DayOfYear] BETWEEN 1 AND 366)
AND ([Weekday] BETWEEN 1 AND 7)
AND ([Fiscal_Year] > 1900)
AND ([Fiscal_Quarter] BETWEEN 1 AND 4)
AND ([Fiscal_Month] BETWEEN 1 AND 12)
AND ([KindOfDay] IN ('HOLIDAY', 'SATURDAY', 'SUNDAY', 'BANKDAY')))
GO




SET DATEFIRST 1;
-- I want my table to contain datedata acording to ISO 8601
-- http://en.wikipedia.org/wiki/ISO_8601
-- thus first day of a week is monday
WITH Dates(Date)
-- A recursive CTE that produce all dates between 1999 and 2020-12-31
AS
(
SELECT cast('1999' AS DateTime) Date -- SQL Server supports the ISO 8601 format so this is an unambigious shortcut for 1999-01-01
UNION ALL                            -- http://msdn2.microsoft.com/en-us/library/ms190977.aspx
SELECT (Date + 1) AS Date
FROM Dates
WHERE
Date < cast('2021' AS DateTime) -1
),

DatesAndThursdayInWeek(Date, Thursday)
-- The weeks can be found by counting the thursdays in a year so we find
-- the thursday in the week for a particular date
AS
(
SELECT
Date,
CASE DATEPART(weekday,Date)
WHEN 1 THEN Date + 3
WHEN 2 THEN Date + 2
WHEN 3 THEN Date + 1
WHEN 4 THEN Date
WHEN 5 THEN Date - 1
WHEN 6 THEN Date - 2
WHEN 7 THEN Date - 3
END AS Thursday
FROM Dates
),

Weeks(Week, Thursday)
-- Now we produce the weeknumers for the thursdays
-- ROW_NUMBER is new to SQL Server 2005
AS
(
SELECT ROW_NUMBER() OVER(partition by year(Date) order by Date) Week, Thursday
FROM DatesAndThursdayInWeek
WHERE DATEPART(weekday,Date) = 4
)
INSERT INTO Auxiliary.Calendar
SELECT
d.Date,
YEAR(d.Date) AS Year,
DATEPART(Quarter, d.Date) AS Quarter,
MONTH(d.Date) AS Month,
w.Week,
DAY(d.Date) AS Day,
DATEPART(DayOfYear, d.Date) AS DayOfYear,
DATEPART(Weekday, d.Date) AS Weekday,

-- Fiscal year may be different to the actual year in Norway the are the same
-- http://en.wikipedia.org/wiki/Fiscal_year
YEAR(d.Date) AS Fiscal_Year,
DATEPART(Quarter, d.Date) AS Fiscal_Quarter,
MONTH(d.Date) AS Fiscal_Month,

CASE
-- Holidays in Norway
-- For other countries and states: Wikipedia - List of holidays by country
-- http://en.wikipedia.org/wiki/List_of_holidays_by_country
    WHEN (DATEPART(DayOfYear, d.Date) = 1)          -- New Year's Day
    OR (d.Date = Auxiliary.Computus(YEAR(Date))-7)  -- Palm Sunday
    OR (d.Date = Auxiliary.Computus(YEAR(Date))-3)  -- Maundy Thursday
    OR (d.Date = Auxiliary.Computus(YEAR(Date))-2)  -- Good Friday
    OR (d.Date = Auxiliary.Computus(YEAR(Date)))    -- Easter Sunday
    OR (d.Date = Auxiliary.Computus(YEAR(Date))+39) -- Ascension Day
    OR (d.Date = Auxiliary.Computus(YEAR(Date))+49) -- Pentecost
    OR (d.Date = Auxiliary.Computus(YEAR(Date))+50) -- Whitmonday
    OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 1)      -- Labour day
    OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 17)     -- Constitution day
    OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 25)    -- Cristmas day
    OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 26)    -- Boxing day
    THEN 'HOLIDAY'
    WHEN DATEPART(Weekday, d.Date) = 6 THEN 'SATURDAY'
    WHEN DATEPART(Weekday, d.Date) = 7 THEN 'SUNDAY'
    ELSE 'BANKDAY'
END KindOfDay,
CASE
-- Description of holidays in Norway
    WHEN (DATEPART(DayOfYear, d.Date) = 1)            THEN 'New Year''s Day'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-7)  THEN 'Palm Sunday'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-3)  THEN 'Maundy Thursday'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-2)  THEN 'Good Friday'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date)))    THEN 'Easter Sunday'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+39) THEN 'Ascension Day'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+49) THEN 'Pentecost'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+50) THEN 'Whitmonday'
    WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 1)      THEN 'Labour day'
    WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 17)     THEN 'Constitution day'
    WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 25)    THEN 'Cristmas day'
    WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 26)    THEN 'Boxing day'
END Description

FROM DatesAndThursdayInWeek d
-- This join is for getting the week into the result set
     inner join Weeks w
     on d.Thursday = w.Thursday

OPTION(MAXRECURSION 0)
GO

CREATE FUNCTION Auxiliary.Numbers
(
@AFrom INT,
@ATo INT,
@AIncrement INT
)
RETURNS @RetNumbers TABLE
(
[Number] int PRIMARY KEY NOT NULL
)
AS
BEGIN
WITH Numbers(n)
AS
(
SELECT @AFrom AS n
UNION ALL
SELECT (n + @AIncrement) AS n
FROM Numbers
WHERE
n < @ATo
)
INSERT @RetNumbers
SELECT n from Numbers
OPTION(MAXRECURSION 0)
RETURN;
END
GO

CREATE FUNCTION Auxiliary.iNumbers
(
@AFrom INT,
@ATo INT,
@AIncrement INT
)
RETURNS TABLE
AS
RETURN(
WITH Numbers(n)
AS
(
SELECT @AFrom AS n
UNION ALL
SELECT (n + @AIncrement) AS n
FROM Numbers
WHERE
n < @ATo
)
SELECT n AS Number from Numbers
)
GO
Tuesday, June 1, 2021
 
mario
answered 7 Months ago
30

Use following syntax to create new table from old table in SQL server 2008

Select * into new_table  from  old_table 
Monday, June 7, 2021
 
o_flyer
answered 7 Months ago
57

Try this:

create table tbl1(id int primary key, dt datetime default current_timestamp);

Background:

The DEFAULT constraint specifies a default value to use when doing an INSERT. The value may be NULL, a string constant, a number, or a constant expression enclosed in parentheses. The default value may also be one of the special case-independant keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. If the value is NULL, a string constant or number, it is inserted into the column whenever an INSERT statement that does not specify a value for the column is executed. If the value is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the current UTC date and/or time is inserted into the columns. For CURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE, YYYY-MM-DD. The format for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".

From http://www.sqlite.org/lang_createtable.html

Sunday, August 8, 2021
 
keisar
answered 4 Months ago
53

Yes,sqlalchemy does create a database for you.I confirmed it on windows using this code

from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Date, Integer, String
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///C:\sqlitedbs\school.db', echo=True)
Base = declarative_base()


class School(Base):

    __tablename__ = "woot"

    id = Column(Integer, primary_key=True)
    name = Column(String)  


    def __init__(self, name):

        self.name = name    


Base.metadata.create_all(engine)
Sunday, August 8, 2021
 
rojo
answered 4 Months ago
49

I just got done setting up a Flask app and I dealt with this kind of problem.

I strongly suspect the problem here is that the instance of db that you are creating in __init__.py is unaware of the contents of models.py, including the User class. The db object in __init__.py is a totally separate object from the db you are creating in models.py. So when you run db.create_all() in __init__.py, it is checking the list of tables that it knows about and isn't finding any. I ran into this exact issue.

What I discovered is that the models (like User) are registered with the particular db object that is listed in the model's class definition (e.g. class User(db.Model):).

So basically my understanding is that the way to fix this is to run db.create_all() using the same instance of db that is being used to define the models. In other words, run db.create_all() from within models.py.

Here's my code so you can see how I have it set up:

app.py:

#!flask/bin/python
import os

from flask import Flask


class CustomFlask(Flask):
    jinja_options = Flask.jinja_options.copy()
    jinja_options.update(dict(
        variable_start_string='%%',  # Default is '{{', I'm changing this because Vue.js uses '{{' / '}}'
        variable_end_string='%%',
    ))
app = CustomFlask(__name__)

app.config['SECRET_KEY'] = 'hard to guess string'

import yaml
if os.environ['SERVER_ENVIRONMENT'] == 'PRODUCTION':
    config_filename = "production.yaml"
elif os.environ['SERVER_ENVIRONMENT'] == 'LOCAL':
    config_filename = "local.yaml"
else:
    config_filename = "local.yaml"

base_directory = path = os.path.dirname(os.path.realpath(__file__))

with open(base_directory + "/config/" + config_filename) as config_file:
    config = yaml.load(config_file)

db_config = config['database']
SQLALCHEMY_DATABASE_URI = "mysql+mysqlconnector://{username}:{password}@{hostname}/{databasename}".format(
    username=db_config['username'],
    password=db_config['password'],
    hostname=db_config['hostname'],
    databasename=db_config['databasename'],
)
app.config["SQLALCHEMY_DATABASE_URI"] = SQLALCHEMY_DATABASE_URI
app.config["SQLALCHEMY_POOL_RECYCLE"] = 299

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy(app)
db.app = app


def clear_the_template_cache():
    app.jinja_env.cache = {}

app.before_request(clear_the_template_cache)

from flask_login import LoginManager
login_manager = LoginManager()
login_manager.init_app(app)


@login_manager.user_loader
def load_user(email):
    from models import User
    return User.query.filter_by(email=email).first()


if __name__ == '__main__':
    from routes import web_routes
    app.register_blueprint(web_routes)

    from api import api
    app.register_blueprint(api)

    # To get PyCharm's debugger to work, you need to have "debug=False, threaded=True"
    #app.run(debug=False, threaded=True)
    app.run(debug=True)

models.py:

from app import db

import datetime
from werkzeug.security import generate_password_hash, 
     check_password_hash


class Song(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(80))
    datetime_created = db.Column(db.DateTime, default=datetime.datetime.utcnow())
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    lines = db.relationship('Line', cascade="all,delete", backref=db.backref('song', lazy='joined'), lazy='dynamic')
    is_deleted = db.Column(db.Boolean, default=False)


class Line(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    song_id = db.Column(db.Integer, db.ForeignKey('song.id'))
    spans_of_time = db.relationship('SpanOfTime', cascade="all,delete", backref=db.backref('line', lazy='joined'), lazy='dynamic')


class SpanOfTime(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    line_id = db.Column(db.Integer, db.ForeignKey('line.id'))
    starting_64th = db.Column(db.Integer)  # I'm assuming the highest-granularity desired will be a 1/64th note-length.
    length = db.Column(db.Integer)  # I guess this'll be in 1/64th notes, so a 1/16th note will be '4'.
    content = db.Column(db.String(80))


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    email = db.Column(db.String(80), primary_key=True, unique=True)
    display_name = db.Column(db.String(80), default="A Rhymecraft User")
    password_hash = db.Column(db.String(200))
    datetime_subscription_valid_until = db.Column(db.DateTime, default=datetime.datetime.utcnow() - datetime.timedelta(days=1))
    datetime_joined = db.Column(db.DateTime, default=datetime.datetime.utcnow())
    songs = db.relationship('Song', cascade="all,delete", backref=db.backref('user', lazy='joined'), lazy='dynamic')

    def __init__(self, email, password):
        self.email = email
        self.set_password(password)

    def __repr__(self):
        return '<User %r>' % self.email

    def set_password(self, password):
        self.password_hash = generate_password_hash(password)

    def check_password(self, password):
        return check_password_hash(self.password_hash, password)

    def is_authenticated(self):
        return True

    def is_active(self):
        return True

    def is_anonymous(self):
        return False

    def get_id(self):
        return str(self.email)


def init_db():
    db.create_all()

    # Create a test user
    new_user = User('a@a.com', 'aaaaaaaa')
    new_user.display_name = 'Nathan'
    db.session.add(new_user)
    db.session.commit()

    new_user.datetime_subscription_valid_until = datetime.datetime(2019, 1, 1)
    db.session.commit()


if __name__ == '__main__':
    init_db()
Monday, August 9, 2021
 
Mirko
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share