Postgresql database creating error python code

zille

New Member

Join Date: Jun 2021
Posts: 2

Postgresql database creating error python code

Jun 14 '21, 10:37 PM

I have a old code for unity3d game.
I was able to update it from 2.7 to python 3.
After running the code as shown below:

Code:

from flask import Flask
#from flask.ext.sqlalchemy import SQLAlchemy
from flask_sqlalchemy import SQLAlchemy

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Sequence, Integer, Float, String, Boolean, ForeignKey, TIMESTAMP, Enum
import datetime
from os import urandom
from binascii import hexlify
from hashlib import sha1


app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql+psycopg2://postgres:neljaD44@localhost'
db = SQLAlchemy(app)

class GameServer(db.Model):
    __tablename__ = 'servers'

    id = Column( Integer, Sequence( "gameserver_id_seq" ), primary_key=True, autoincrement=True )
    host = Column( String(64) )
    port = Column( String(10) )
    fe_username = Column( String(64) )
    fe_password = Column( String(64) )

    def __init__(self, host, port, username, password ):
        self.host = host
        self.port = port
        self.fe_username = username
        self.fe_password = password

    def __repr__(self):
        return "<GameServer %s:%s>" % (self.host, self.port)


class UserSession(db.Model):
    __tablename__ = 'sessions'

    session_id = Column( String(64), primary_key=True, autoincrement=True )
    user_id = Column( Integer )
    created = Column( TIMESTAMP( timezone=True ) )

    def __init__(self, user_id, session_id, created = None ):
        self.session_id = session_id
        self.user_id = user_id
        self.created = created
        if created is None:
            self.created = datetime.datetime.now()

    def __repr__(self):
        return "<Session for user id %d created on %s>" % (self.user_id, str(self.created) )


class User(db.Model):
    """
    ALTER TABLE users ADD COLUMN steam_id varchar(32);
    """
    __tablename__ = 'users'

    id = Column( Integer, primary_key=True, autoincrement=True )
    username = Column( String(64) )
    screenname = Column( String(64) )
    password_hash = Column( String )
    clantag = Column( String(10) )
    allow_login = Column( Boolean )
    account_type = Column( Integer )
    rating = Column( Integer )    # this lives here for now until we move it to a proper player statistics table
    email = Column( String(128) )
    is_online = Column( Boolean )
    steam_id = Column( String(32) )

    def __init__( self, username ):
        self.username = username
        self.is_online = False

    def __repr__(self):
        return "<User %s/%s>" % (self.username, self.screenname)


class UserInventoryItem(db.Model):
    """
    CHANGED 140207: ALTER TABLE user_inventory ADD COLUMN suit varchar(10) NOT NULL;
    CHANGED 140207: ALTER TABLE user_inventory ADD COLUMN is_new boolean NOT NULL default TRUE;
    """
    __tablename__ = "user_inventory"

    id = Column( Integer, primary_key=True, autoincrement=True )
    user_id = Column( Integer, primary_key=True )
    item_category = Column( String(16), primary_key=True )
    item_id = Column( Integer )
    slot = Column( Integer )
    item_name = Column( String(64) )
    suit = Column( String(10), nullable=False )
    is_new = Column( Boolean, nullable=False )

    def __init__( self, user_id, item_category, item_id, suit, item_name = "", slot=-1 ):
        self.user_id = user_id
        self.item_category = item_category
        self.item_id = item_id
        self.item_name = ""
        self.slot = slot
        self.suit = suit
        self.is_new = True


class UserPossession(db.Model):
    """
CREATE SEQUENCE user_possessions_seq;

CREATE TABLE user_possessions(
    id integer not null unique default nextval('user_possessions_seq'::regclass),
    user_id integer not null,
    p_type varchar(16) not null,
    p_item varchar(32) not null,

    grant_information varchar(32),

    primary key( id, user_id ),
    foreign key( user_id ) references users(id)
);
    """
    __tablename__ = "user_possessions"

    id = Column( Integer, primary_key=True, autoincrement=True )
    user_id = Column( Integer, primary_key=True )
    p_type = Column( String(16), nullable=False, primary_key=True )
    p_item = Column( String(32) )
    grant_information = Column( String(32) )

    def __init__(self, user_id, p_type, p_item, grant_info = ""  ):
        self.user_id = user_id
        self.p_type = p_type
        self.p_item = p_item
        self.grant_information = grant_info

class UserBooster(db.Model):
    """
CREATE SEQUENCE user_boosters_seq;

CREATE TABLE user_boosters(
    id integer not null unique default nextval('user_boosters_seq'::regclass),
    user_id integer not null,

    booster_type varchar(16) not null,
    multiplier float not null,

    grant_timestamp timestamp with time zone,
    expiry_timestamp timestamp with time zone,

    grant_information varchar(64),

    primary key( id, user_id, booster_type ),
    foreign key( user_id ) references users(id)


);
"""
    __tablename__ = "user_boosters"

    id = Column( Integer, primary_key=True, autoincrement=True )
    user_id = Column( Integer, primary_key=True )

    booster_type = Column( String(16), nullable=False, primary_key=True )
    multiplier = Column( Float, nullable=False )

    grant_timestamp = Column( TIMESTAMP( timezone=True ) )
    expiry_timestamp = Column( TIMESTAMP( timezone=True ) )

    grant_information = Column( String(64) )

    def __init__(self, user_id, booster_type, booster_multiplier, expiry_timestamp, grant_info = "" ):
        self.user_id = user_id
        self.booster_type = booster_type
        self.multiplier = booster_multiplier

        self.grant_timestamp = datetime.datetime.now()
        self.expiry_timestamp = expiry_timestamp
        self.grant_information = grant_info
        pass


class UserStats(db.Model):
    """
    CHANGED 140129: ALTER TABLE user_stats ADD COLUMN ladder_value float NOT NULL default 13.0;
    CHANGED 140213: UPDATE user_stats SET xp_level=1, xp_current=0, xp_next=212, hard_money=0, soft_money=0, rating=1500; /* reset */
    """
    __tablename__ = "user_stats"

    id = Column( Integer, primary_key=True, autoincrement=True )
    user_id = Column( Integer )

    xp_level = Column( Integer )
    xp_current = Column( Integer )
    xp_next = Column( Integer )
    xp_rested = Column( Integer )
    rating = Column( Integer )
    ladder_value = Column( Float )

    soft_money = Column( Integer )
    hard_money = Column( Integer )

    def __init__(self, userid):
        self.user_id = userid
        self.xp_level = 1
        self.xp_current = 0
        self.xp_next = 212
        self.xp_rested = 0
        self.rating = 1500
        self.hard_money = 0
        self.soft_money = 1500
        self.ladder_value = 13.0


class UserStatEvent(db.Model):
    """
    CHANGED 140129: ALTER TABLE user_stat_events ADD COLUMN old_value integer NOT NULL DEFAULT -1;
    """

    __tablename__ = "user_stat_events"

    id = Column( Integer, primary_key=True, autoincrement=True )
    user_id = Column( Integer, primary_key=True )

    created = Column( TIMESTAMP( timezone=True ) )

    field_name = Column( String(32), nullable=False )
    old_value = Column( Integer, nullable=False, default=-1)
    change_type = Column( String(32), nullable=False )
    change_amount = Column( Integer, nullable=False )

    match_token = Column( String(64) )
    inapp_transaction_id = Column( String(128) )

    description = Column( String )

    def __repr__(self):
        return "<UserStatEvent for user id %d: %s %s %d>" % (self.user_id, self.field_name, self.change_type, self.change_amount)

    def __init__(self, user_id, field_name, change_type, amount, old_value = -1 ):
        self.user_id = user_id
        self.created = datetime.datetime.now()
        self.field_name = field_name
        self.change_type = change_type
        self.change_amount = amount
        self.old_value = old_value


#class FriendRequest(Base):
#    def __init__(self):
#        pass


#class FriendAssociation(Base):
#    def __init__(self):
#       pass


class Match(db.Model):
    __tablename__ = 'match'

    id = Column( Integer, Sequence( "match_id_seq" ), primary_key=True, autoincrement=True )
    token = Column( String(64) )
    map = Column( String(64) )
    server_id = Column( Integer )
    status = Column( Integer )
    result = Column( Integer )
    creation_time = Column( TIMESTAMP( timezone = True ) )
    match_type = Column( Integer )
    start_time = Column( TIMESTAMP( timezone = True ) )
    end_time = Column( TIMESTAMP( timezone = True ) )

    def __init__(self, map, match_type ):
        mid = urandom(32)
        mid_str = ":".join("{0:x}".format(ord(c)) for c in mid)
        self.token = sha1(u'%s%s' % (mid_str, map)).hexdigest()
        self.map = map
        self.server_id = 0
        self.status = 0
        self.result = 0
        self.creation_time = datetime.datetime.now()
        self.match_type = 1
        self.start_time = datetime.datetime( 1970, 1, 1 )
        self.end_time = datetime.datetime( 1970, 1, 1 )

    def __repr__(self):
        return "<Match %s in state %d>" % (self.token, self.status)

class MatchBasicInfo(db.Model):
    """
CREATE SEQUENCE match_basic_infos_seq;

CREATE TABLE match_basic_infos(
    id integer not null unique default nextval('match_basic_infos_seq'::regclass),
    match_id integer not null,
    type_string varchar(16) not null,
    tickets integer not null,
    duration integer not null,
    winning_team integer,
    tickets_team1 integer,
    tickets_team2 integer,

    primary key (id),
    foreign key (match_id) references match(id) on delete restrict
);
    """
    id = Column( Integer, primary_key=True, autoincrement=True )
    match_id = Column( Integer, ForeignKey( "match.id" ), nullable=False, primary_key=True )
    type_string = Column( String(16), nullable=False )
    tickets = Column( Integer, nullable=False )
    duration = Column( Integer, nullable=False )
    winning_team = Column( Integer )
    tickets_team1 = Column( Integer )
    tickets_team2 = Column( Integer )

    __tablename__ = 'match_basic_infos'
    def __init__(self, match_id, type_string, tickets, duration ):
        self.match_id = match_id
        self.type_string = type_string
        self.tickets = tickets
        self.tickets_team1 = tickets
        self.tickets_team2 = tickets
        self.duration = duration
        self.winning_team = -1

class MatchPlayerInfo(db.Model):
    """
CREATE SEQUENCE match_player_infos_seq;

CREATE TABLE match_player_infos(
    id integer not null unique default nextval('match_player_infos_seq'::regclass),
    match_id integer not null,
    player_id integer,
    player_match_index integer,
    player_name varchar(64) not null,
    rank float,
    human boolean,
    team integer,
    hero integer,
    role varchar(16),
    level integer,
    total_experience integer,
    kills integer,
    creep_kills integer,
    deaths integer,
    captures integer,
    gold_collected integer,
    total_damage_out integer,
    total_damage_in integer,
    potions_collected integer,

    primary key (match_id, id),
    foreign key(match_id) references match(id) on delete restrict
);
    """
    id = Column( Integer, primary_key=True, autoincrement=True )
    match_id = Column( Integer, ForeignKey( "match.id" ), nullable=False, primary_key=True )
    player_id = Column( Integer )
    player_match_index = Column( Integer )
    player_name = Column( String(64), nullable=False )
    rank = Column( Float )
    human = Column( Boolean )
    team = Column( Integer )
    hero = Column( Integer )
    role = Column( String(16) )
    level = Column( Integer )
    total_experience = Column( Integer )
    kills = Column( Integer )
    creep_kills = Column( Integer )
    deaths = Column( Integer )
    captures = Column( Integer )
    gold_collected = Column( Integer )
    total_damage_out = Column( Integer )
    total_damage_in = Column( Integer )
    potions_collected = Column( Integer )

    __tablename__ = 'match_player_infos'
    def __init__(self, match_id, player_id, player_name, team, rank):
        self.match_id = match_id
        self.player_id = player_id
        self.player_name = player_name
        self.team = team
        self.rank = rank
        self.level = 1
        self.total_experience = 0
        self.kills = 0
        self.creep_kills = 0
        self.deaths = 0
        self.captures = 0
        self.gold_collected = 0
        self.total_damage_out = 0
        self.total_damage_in = 0
        self.potions_collected = 0


class MatchTeamEvent(db.Model):
    """
CREATE SEQUENCE match_team_events_seq;

CREATE TABLE match_team_events(
    id integer not null unique default nextval('match_team_events_seq'::regclass),
    match_id integer not null,
    time integer not null,
    team integer not null,
    event varchar(32) not null,
    position_x float,
    position_y float,
    position_z float,
    extra_data_1 varchar(64),
    extra_data_2 varchar(64),

    primary key( match_id, id ),
    foreign key(match_id) references match(id) on delete restrict

);
    """
    id = Column( Integer, primary_key=True, autoincrement=True )
    match_id = Column( Integer, ForeignKey( "match.id" ), primary_key=True )
    time = Column( Integer )
    team = Column( Integer )
    event = Column( String(32) )
    position_x = Column( Float )
    position_y = Column( Float )
    position_z = Column( Float )
    extra_data_1 = Column( String(64) )
    extra_data_2 = Column( String(64) )

    __tablename__ = 'match_team_events'
    def __init__(self, match_id, time, team, event, extra_data_1 = None, extra_data_2 = None, position = None):
        self.match_id = match_id
        self.time = time
        self.team = team
        self.event = event
        if extra_data_1 is not None:
            self.extra_data_1 = extra_data_1
        if extra_data_2 is not None:
            self.extra_data_2 = extra_data_2
        if self.position is not None:
            self.position_x, self.position_y, self.position_z = position


class MatchPlayerEvent(db.Model):
    """
CREATE SEQUENCE match_player_events_seq;

CREATE TABLE match_player_events(
    id integer not null unique default nextval('match_player_events_seq'::regclass),
    match_id integer not null,
    time integer not null,
    player_name varchar(64) not null,
    event varchar(32) not null,
    position_x float,
    position_y float,
    position_z float,
    extra_data_1 varchar(64),
    extra_data_2 varchar(64),

    primary key (match_id, id),
    foreign key (match_id) references match(id) on delete restrict
);
    """
    id = Column( Integer, primary_key=True, autoincrement=True )
    match_id = Column( Integer, ForeignKey( "match.id" ), primary_key=True )
    time = Column( Integer )
    player_name = Column( String(64) )
    event = Column( String(32) )
    position_x = Column( Float )
    position_y = Column( Float )
    position_z = Column( Float )
    extra_data_1 = Column( String(64) )
    extra_data_2 = Column( String(64) )

    __tablename__ = 'match_player_events'
    def __init__(self, match_id, time, player_name, event, extra_data_1 = None, extra_data_2 = None, position = None ):
        self.match_id = match_id
        self.time = time
        self.player_name = player_name
        self.event = event
        if extra_data_1 is not None:
            self.extra_data_1 = extra_data_1
        if extra_data_2 is not None:
            self.extra_data_2 = extra_data_2
        if self.position is not None:
            self.position_x, self.position_y, self.position_z = position


class MatchRequest(db.Model):
    __tablename__ = 'match_request'

    id = Column( Integer, primary_key=True, autoincrement=True )
    user_id = Column( Integer )
    gametype = Column( String(10), nullable=True )  # Column( Enum( '1v1', '2v2', name="matchrequest_gametype_enum" ) )
    controltype = Column(String(10), nullable=True ) # Column( Enum('master', 'hero'), name="matchrequest_controltype_enum" )
    map_preference = Column( String(64) )
    found_match_id = Column( Integer )
    status = Column( Integer )
    skill_hint = Column( Integer )
    start_time = Column( TIMESTAMP( timezone = True ) )

    def __init__(self, user_id ):
        self.user_id = user_id
        self.status = 0
        self.skill_hint = 0
        self.start_time = datetime.datetime.now()

    def __repr__(self):
        return "<Match request for user %d>" % (self.user_id,)


class MatchResult(db.Model):
    """
    CHANGED 140129: ALTER TABLE match_result ADD COLUMN old_ladder float;
                    ALTER TABLE match_result ADD COLUMN new_ladder float;
                    ALTER TABLE match_result ADD COLUMN old_soft_money integer;
                    ALTER TABLE match_result ADD COLUMN new_soft_money integer;
                    ALTER TABLE match_result ADD COLUMN old_hard_money integer;
                    ALTER TABLE match_result ADD COLUMN new_hard_money integer;

    """

    __tablename__ = 'match_result'

    id = Column( Integer, primary_key = True, autoincrement=True )
    user_id = Column( Integer, primary_key=True, nullable=False )
    match_token = Column( String(64), nullable=False )
    result = Column( String(20), nullable=True ) # Column( Enum( 'won', 'lost', 'draw', 'in_progress', 'unfinished', name="matchresult_result_enum" ), nullable=False )

    old_xp = Column( Integer )
    new_xp = Column( Integer )
    old_level = Column( Integer )
    new_level = Column( Integer )
    old_rating = Column( Integer )
    new_rating = Column( Integer )
    old_ladder = Column( Float )
    new_ladder = Column( Float )
    old_soft_money = Column( Integer )
    new_soft_money = Column( Integer )
    old_hard_money = Column( Integer )
    new_hard_money = Column( Integer )

    end_time = Column( TIMESTAMP( timezone = True ) )

    def __repr__(self):
        return "<MatchResult on match %s for userid %d: %s>" % (self.match_token, self.user_id, self.result)

    def __init__(self, user_id, match_token, result ):
        self.user_id = user_id
        self.match_token = match_token
        self.result = result


class BetaFeedback(db.Model):
    __tablename__ = 'beta_feedback'

    match_id = Column( String(64), primary_key = True, nullable=False )
    user_id = Column( Integer, primary_key=True, nullable=False )
    rating = Column( Integer, default=0, nullable=False )
    feedback = Column( String(1024), default='', nullable=False )

    def __repr__(self):
        return "<Match feedback for match %s>" % (self.match_id,)

    def __init__(self, match_id, user_id ):
        self.match_id = match_id
        self.user_id = user_id
        self.rating = 0
        self.feedback = ''


class BetaInvite(db.Model):
    __tablename__ = 'beta_invites'

    beta_key = Column( String(64), primary_key=True, nullable=False )
    email_address = Column( String(128), nullable=False )
    requested_username = Column( String(64), nullable=False )
    consumed = Column( Boolean, default=False)
    email_confirmed = Column( Boolean, default=False )
    created_timestamp = Column( TIMESTAMP( timezone=True ) )
    consumed_timestamp = Column( TIMESTAMP( timezone = True ) )
    email_sent = Column( Boolean, default=False )
    email_confirm_key = Column( String(64), nullable=True )
    requested_password = Column( String(64), nullable=False, default='' )

    def __init__(self, beta_key):
        self.beta_key = beta_key
        self.email_address = ''
        self.requested_username = ''
        self.consumed = False
        self.email_confirmed = False
        self.created_timestamp = datetime.datetime.now()
        self.email_sent = False
        self.requested_password = ''



class FriendAssociation(db.Model):
    __tablename__ = "user_friend_assoc"

    id = Column( Integer, autoincrement=True, primary_key=True )
    user_id = Column( Integer, primary_key=True )
    friend_id = Column( Integer )

    def __init__(self, user_id, friend_id):
        self.user_id = user_id
        self.friend_id = friend_id

class FriendRequest(db.Model):
    __tablename__ = "friend_requests"

    id = Column( Integer, autoincrement=True, primary_key=True )
    receiver_user_id = Column( Integer, primary_key=True )
    sender_user_id = Column( Integer, primary_key=True )
    accepted = Column( Boolean, default=False )

    def __init__(self, user_id, friend_id):
        self.receiver_user_id = friend_id
        self.sender_user_id = user_id
        self.accepted = False


class GameInvite(db.Model):
    __tablename__ = "friend_game_invites"

    id = Column( Integer, autoincrement=True, primary_key=True )
    receiver_user_id = Column( Integer, primary_key=True )
    sender_user_id = Column( Integer )
    map_preference = Column( String(64) )
    accepted = Column( Boolean, default=False )
    game_created = Column( Boolean, default=False )
    match_token = Column( String(64) )

    def __init__(self, receiver_id, sender_id ):
        self.receiver_user_id = receiver_id
        self.sender_user_id = sender_id
        self.map_preference = ''
        self.accepted = False
        self.game_created = False
        self.match_token = ''


class Achievement(db.Model):
    """
CREATE SEQUENCE achievements_id_seq;

CREATE TABLE achievements(
    id integer not null unique default nextval('achievements_id_seq'::regclass),
    user_id integer not null,
    achievement_name varchar(32),
    completed timestamp with time zone,
    completion_count integer default 0,
    last_complete_token varchar(64),

    primary key ( id, user_id ),
    foreign key ( user_id ) references users(id) on delete cascade
);
    """
    __tablename__ = "achievements"

    id = Column( Integer, autoincrement=True, primary_key=True )
    user_id = Column( Integer, primary_key=True )
    achievement_name = Column( String(32) )
    completed = Column( TIMESTAMP( timezone=True ) )
    completion_count = Column( Integer, default=0 )
    last_complete_token = Column( String(64) )

    def __init__(self, user_id, achievement, token=""):
        self.user_id = user_id
        self.achievement_name = achievement
        self.completed = datetime.datetime.utcnow()
        self.completion_count = 0
        self.last_complete_token = token


class LogEntry(db.Model):
    """
CREATE SEQUENCE logentries_id_seq;

CREATE TABLE logentries(

    id integer not null unique default nextval('logentries_id_seq'::regclass),
    user_id integer not null,

    entry_timestamp timestamp with time zone,
    entry_type varchar(32),
    extra_data varchar(256),

    primary key (id, user_id),
    foreign key ( user_id ) references users(id) on delete cascade

);
    """
    __tablename__ = "logentries"

    id = Column( Integer, autoincrement=True, primary_key=True )
    user_id = Column( Integer, ForeignKey( "users.id" ), primary_key=True, nullable=False  )
    entry_timestamp = Column( TIMESTAMP( timezone=True ) )
    entry_type = Column( String(32) )
    extra_data = Column( String(256) )

    def __init__(self, user_id, entry_type, extra_data="" ):
        self.user_id = user_id
        self.entry_timestamp = datetime.datetime.now()
        self.entry_type = entry_type
        self.extra_data = extra_data


class ShopSKU(db.Model):
    """
CREATE SEQUENCE shop_item_skus_id_seq START WITH 1000;

CREATE TABLE shop_item_skus(
    id integer not null unique default nextval('shop_item_skus_id_seq'::regclass),

    sku_internal_identifier varchar(32) not null unique,
    name varchar(128) not null,
    description varchar(256) not null,
    category varchar(32),

    purchaseable boolean not null,

    soft_currency_cost integer,
    hard_currency_cost integer,

    steam_sellable boolean,

    primary key(id)
);

INSERT INTO shop_item_skus
  (id, sku_internal_identifier, name, description, category, purchaseable, soft_currency_cost, hard_currency_cost, steam_sellable)
VALUES
  (2001, 'CHARACTER-BEAVER', 'Beaver', 'Beaver hero character', 'character', TRUE, -1, -1, TRUE );

INSERT INTO shop_item_skus
  (id, sku_internal_identifier, name, description, category, purchaseable, soft_currency_cost, hard_currency_cost, steam_sellable)
VALUES
  (2002, 'CHARACTER-FYRE', 'Fyre Engineer', 'Fyre Engineer hero character', 'character', TRUE, -1, -1, TRUE );

INSERT INTO shop_item_skus
  (id, sku_internal_identifier, name, description, category, purchaseable, soft_currency_cost, hard_currency_cost, steam_sellable)
VALUES
  (2003, 'CHARACTER-ALCHEMIST', 'Alchemist', 'Alchemist hero character', 'character', TRUE, -1, -1, TRUE );

INSERT INTO shop_item_skus
  (id, sku_internal_identifier, name, description, category, purchaseable, soft_currency_cost, hard_currency_cost, steam_sellable)
VALUES
  (2004, 'CHARACTER-STONE', 'Stone elemental', 'Stone elemental hero character', 'character', TRUE, -1, -1, TRUE );

INSERT INTO shop_item_skus
  (id, sku_internal_identifier, name, description, category, purchaseable, soft_currency_cost, hard_currency_cost, steam_sellable)
VALUES
  (2005, 'CHARACTER-ROGUE', 'Rogue', 'Rogue hero character', 'character', TRUE, -1, -1, TRUE );


INSERT INTO shop_sku_prices (sku_id, currency, unit_price) VALUES ( 2001, 'EUR', 100 );
INSERT INTO shop_sku_prices (sku_id, currency, unit_price) VALUES ( 2002, 'EUR', 100 );
INSERT INTO shop_sku_prices (sku_id, currency, unit_price) VALUES ( 2003, 'EUR', 100 );
INSERT INTO shop_sku_prices (sku_id, currency, unit_price) VALUES ( 2004, 'EUR', 100 );
INSERT INTO shop_sku_prices (sku_id, currency, unit_price) VALUES ( 2005, 'EUR', 100 );
    """
    __tablename__ = "shop_item_skus"

    id = Column( Integer, autoincrement=True, primary_key=True )
    sku_internal_identifier = Column( String(32), nullable=False, primary_key=True )
    name = Column( String(128), nullable=False )
    description = Column( String(256), nullable=False )
    category = Column( String(32) )

    purchaseable = Column( Boolean, nullable=False, default=False )

    soft_currency_cost = Column( Integer )
    hard_currency_cost = Column( Integer )

    steam_sellable = Column( Boolean, default=False )

    def __init__(self, sku_internal_identifier, name, description, purchaseable = False):
        self.sku_internal_identifier = sku_internal_identifier
        self.name = name
        self.description = description
        self.purchaseable = purchaseable


class ShopSKUPrice(db.Model):
    """
CREATE SEQUENCE shop_sku_prices_id_seq;

CREATE TABLE shop_sku_prices(
    id integer not null unique default nextval('shop_sku_prices_id_seq'::regclass),

    sku_id integer not null,
    currency varchar(5) not null,
    unit_price integer,

    primary key (id, sku_id),
    foreign key (sku_id) references shop_item_skus(id) on delete cascade
);
    """

    __tablename__ = "shop_sku_prices"
    id = Column( Integer, autoincrement=True, primary_key=True )
    sku_id = Column( Integer, ForeignKey("shop_item_skus.id"), nullable=False, primary_key=True )
    currency = Column( String(5), nullable=False )
    unit_price = Column( Integer, nullable=False )

    def __init__(self, sku_id, currency, unit_price):
        self.sku_id = sku_id
        self.currency = currency
        self.unit_price = unit_price


class ShopTransaction(db.Model):
    """
CREATE SEQUENCE shoptransactions_id_seq START WITH 100000;

CREATE TABLE shoptransactions(
    id integer not null unique default nextval('shoptransactions_id_seq'::regclass),
    user_id integer not null,

    merchant_txn_id varchar(64), /* the remote merchant's transaction id */

    purchase_method varchar(16) not null,  /* "ingame", "steam", "apple", "google", but currently always "steam" */
    tx_state varchar(32) not null, /* "open", "wait_payment", "confirmed", "failed", "rejected" */
    items_delivered boolean not null,

    total_price integer not null, /* in 1/100th of the currency unit */
    currency varchar(5) not null,

    start_timestamp timestamp with time zone,
    confirm_timestamp timestamp with time zone,
    delivered_timestamp timestamp with time zone,

    primary key (id, user_id),
    foreign key ( user_id ) references users(id) on delete restrict
);
    """
    __tablename__ = "shoptransactions"
    id = Column( Integer, autoincrement=True, primary_key=True )
    user_id = Column( Integer, ForeignKey( "users.id" ), nullable=False, primary_key=True )
    merchant_txn_id = Column( String(64) )

    purchase_method = Column( String(16), nullable=False )
    tx_state = Column( String(32), nullable=False )
    items_delivered = Column( Boolean, nullable=False, default=False )

    total_price = Column( Integer, nullable=False )
    currency = Column( String(5), nullable=False )

    start_timestamp = Column( TIMESTAMP(timezone=True) )
    confirm_timestamp = Column( TIMESTAMP(timezone=True) )
    delivered_timestamp = Column( TIMESTAMP(timezone=True) )

    def __init__(self, user_id, purchase_method, price, currency ):
        self.user_id = user_id
        self.purchase_method = purchase_method
        self.total_price = price
        self.currency = currency

        self.tx_state = "open"
        self.items_delivered = False

        self.start_timestamp = datetime.datetime.now()


class ShopTransactionItem(db.Model):
    """
CREATE SEQUENCE shoptransaction_items_id_seq;

CREATE TABLE shoptransaction_items(
    id integer not null unique default nextval('shoptransaction_items_id_seq'::regclass),
    tx_id integer not null,

    item_sku_id integer not null,
    quantity integer not null,
    unit_price integer not null,
    total_price integer not null,

    currency varchar(5) not null,

    primary key (id, tx_id),
    foreign key (tx_id) references shoptransactions(id) on delete cascade,
    foreign key (item_sku_id) references shop_item_skus(id) on delete restrict
);
    """

    __tablename__ = "shoptransaction_items"

    id = Column( Integer, autoincrement=True, primary_key=True )
    tx_id = Column( Integer, ForeignKey( "shoptransactions.id"), nullable=False, primary_key=True )

    item_sku_id = Column( Integer, ForeignKey( "shop_item_skus.id" ), nullable=False )
    quantity = Column( Integer, nullable=False )
    unit_price = Column( Integer, nullable=False )
    total_price = Column( Integer, nullable=False )

    currency = Column( String(5), nullable=False )

    def __init__(self, tx_id, item_id, quantity, currency, unit_price):
        self.tx_id = tx_id
        self.item_sku_id = item_id
        self.quantity = quantity
        self.currency = currency
        self.unit_price = unit_price
        self.total_price = quantity * unit_price
db.create_all()
db.session.commit()

After running this code getting following error:

Code:

/usr/local/lib/python3.6/dist-packages/flask_sqlalchemy/__init__.py:873: FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True or False to suppress this warning.
  'SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and '
Traceback (most recent call last):
  File "create_postgresql_database.py", line 903, in <module>
    db.create_all()
  File "/usr/local/lib/python3.6/dist-packages/flask_sqlalchemy/__init__.py", line 1094, in create_all
    self._execute_for_all_tables(app, bind, 'create_all')
  File "/usr/local/lib/python3.6/dist-packages/flask_sqlalchemy/__init__.py", line 1086, in _execute_for_all_tables
    op(bind=self.get_engine(app, bind), **extra)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/schema.py", line 4740, in create_all
    ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 3078, in _run_ddl_visitor
    conn._run_ddl_visitor(visitorcallable, element, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 2081, in _run_ddl_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/visitors.py", line 485, in traverse_single
    return meth(obj, **kw)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/ddl.py", line 850, in visit_metadata
    _is_metadata_operation=True,
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/visitors.py", line 485, in traverse_single
    return meth(obj, **kw)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/ddl.py", line 895, in visit_table
    include_foreign_key_constraints,  # noqa
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1262, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/ddl.py", line 78, in _execute_on_connection
    self, multiparams, params, execution_options
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1350, in _execute_ddl
    dialect=dialect, schema_translate_map=schema_translate_map
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/elements.py", line 517, in compile
    return self._compiler(dialect, **kw)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/ddl.py", line 29, in _compiler
    return dialect.ddl_compiler(dialect, self, **kw)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/compiler.py", line 455, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/compiler.py", line 490, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/compiler.py", line 4032, in visit_create_table
    create_column, first_pk=column.primary_key and not first_pk
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/compiler.py", line 490, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/compiler.py", line 4065, in visit_create_column
    text = self.get_column_specification(column, first_pk=first_pk)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/dialects/postgresql/base.py", line 2474, in get_column_specification
    and column is column.table._autoincrement_column
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/schema.py", line 810, in _autoincrement_column
    return self.primary_key._autoincrement_column
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/langhelpers.py", line 1113, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/schema.py", line 3954, in _autoincrement_column
    _validate_autoinc(col, True)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/schema.py", line 3928, in _validate_autoinc
    "compatible with autoincrement=True" % (col.type, col)
sqlalchemy.exc.ArgumentError: Column type VARCHAR(64) on column 'sessions.session_id' is not compatible with autoincrement=True

Anyone can help me what is wrong?
Is it connection problem as I have checked properly.
Also is it due to new postgresql version.

Tags: None

zille

New Member

Join Date: Jun 2021

Posts: 2
#2

Jun 14 '21, 11:12 PM

After removing all ", autoincrement=T rue "
i get error:
There is no unique constraint matching given key for referenced table"shop_item _skus"
CREATE TABLE shop_sku_prices (
id integer not null unique default nextval('shop_s ku_prices_id_se q'::regclass),

sku_id integer not null,
currency varchar(5) not null,
unit_price integer,

primary key (id, sku_id),
foreign key (sku_id) references shop_item_skus( id) on delete cascade
);
Comment
Banfa

Recognized Expert Expert

Join Date: Feb 2006

Posts: 9067
#3

Jun 15 '21, 08:40 AM

You probably didn't want to remove all the autoincrements, you should have examined the traceback in more detail; last line of the traceback indicates an autoincrement on a VARCHAR(64) (or string(64)) column is invalid. The only place you have a string(64) column with an autoincrement is line 39.

I would alter just that line and see what errors you get.
Comment

Previous template Next

Postgresql database creating error python code

Postgresql database creating error python code

Comment

Comment