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()
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.
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.
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.
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
.)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With