Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

In SQLalchemy, can I have a column with multiple strings?

For the following code, I have the mapping and an example of a "cookie" that contains some info I want to add to the SQLalchemy through python. As you can see some fields like Farmloc, TreasureMap, and Crafts all have multiple entries they point to. I searched and couldn't find much other than Enum, but that was giving me some trouble. Is there a better means than what I've done here. Using Python3.6.1 and SQLalchemy 1.1.11

import csv, sqlalchemy
from sqlalchemy import Column, String, Integer, ForeignKey, Numeric, Boolean
from sqlalchemy.ext.declarative import declarative_base
import sqlite3
from enum import Enum, auto
import enum
from sqlalchemy import create_engine

    engine = create_engine('sqlite:///:memory:', echo=True) #TEST DB

    Base = declarative_base()

    class Crystal(Base):
    __tablename__ = 'crystals'
    dbID = Column(Integer, primary_key=True)
    ItemName = Column(String, index=True, nullable=False) #Full name, (ie. Earth Shard, Wind Crystal, Fire Cluster)
    ItemType = Column(String) #Fire, Earth, Wind, Ice, Water ONLY
    ItemPow = Column(String) #Shard, Crystal, Cluster ONLY
    Farmloc = Column(String) #Where player can farm
    Retainer = Column(Boolean) #If retainer can farm
    RetainerQ = Column(Integer) #Quantity retainer gets
    Levee = Column(Boolean) #Any Levees rewards these.
    TreasureMap = Column(String) #False=NO, otherwise Types listed
    Desynthed = Column(String) #False=NO, otherwise Herb1, Herb2, Ore1, etc
    Crafts = Column(String) #Crafts associated with (ie Earth w LWR)
    Price = Column(Integer) #MB price (Should be used as ref for all craftables)

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()

cc_cookie = Crystal(dbID = 1,
                        ItemName= 'Wind Cluster',
                        ItemType = 'Wind',
                        ItemPow = 'Cluster',
                        Farmloc = 'The Dravanian Hinterlands, Mor Dhona',
                        Retainer = False,
                        RetainerQ = 0,
                        Levee = False,
                        TreasureMap = 'Dragonskin Treasure Map,Gaganaskin Treasure Map,Gazelleskin Treasure Map,Leather Buried Treasure Map',
                        Desynthed = 'Clary Sage,Furymint,Highland Oregano,Windtea Leaves',
                        Crafts = 'CRP,GSM,LWR,WVR',
                        Price = 500)       


Base.metadata.create_all(engine)

session.add(cc_cookie)
session.commit()
like image 203
Donald Clark-Charest Avatar asked Aug 10 '17 04:08

Donald Clark-Charest


People also ask

What is column in SQLAlchemy?

function sqlalchemy.orm. column_property(*columns, **kwargs) Provide a column-level property for use with a mapping. Column-based properties can normally be applied to the mapper's properties dictionary using the Column element directly.

What is subquery in SQLAlchemy?

The grouping is done with the group_by() query method, which takes the column to use for the grouping as an argument, same as the GROUP BY counterpart in SQL. The statement ends by calling subquery() , which tells SQLAlchemy that our intention for this query is to use it inside a bigger query instead of on its own.

Is SQLAlchemy worth using?

SQLAlchemy is the ORM of choice for working with relational databases in python. The reason why SQLAlchemy is so popular is because it is very simple to implement, helps you develop your code quicker and doesn't require knowledge of SQL to get started.


1 Answers

Check out the built-in class sqlalchemy.types.ARRAY

For example:

TreasureMap = Column(ARRAY(String))

Side note: as per convention all column names are all lowercase and under score separated (e.g. treasure_map over TreasureMap.)

like image 175
Sam Dolan Avatar answered Sep 17 '22 14:09

Sam Dolan