secret
Last active

store a list in a column using sqlalchemy

  • Download Gist
as_a_blob.py
Python
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
# -*- coding: utf-8 -*-
import json
 
from sqlalchemy import Column, String, TypeDecorator
from sqlalchemy.ext.declarative import declarative_base
 
class Json(TypeDecorator):
 
impl = String
 
def process_bind_param(self, value, dialect):
return json.dumps(value)
 
def process_result_value(self, value, dialect):
return json.loads(value)
 
Base = declarative_base()
 
class Word(Base):
__tablename__ = "words"
 
eng = Column(String(32), primary_key=True)
chinese = Column(Json(128))
 
def __repr__(self):
return "Word(%r, %r)" % (self.eng, self.chinese)
 
# connect to db; create tables, session maker
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
Session = scoped_session(sessionmaker(bind=engine,
autocommit=False, autoflush=False))
 
# add word to db
## from .model import Session, Word
session = Session()
word = Word(eng='art', chinese=[u'艺术',u'美术'])
session.add(word)
session.commit()
 
# get word from db
session = Session()
word = session.query(Word).filter_by(eng='art').one()
print("\n".join(map(repr, [word, word.eng, word.chinese, word.chinese[0]])))
model.py
Python
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53
# -*- coding: utf-8 -*-
from sqlalchemy import Column, Integer, ForeignKey, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
 
Base = declarative_base()
 
class Word(Base):
__tablename__ = "words"
 
id = Column(Integer, primary_key=True)
eng = Column(String(32), unique=True)
chinese = relationship("Chinese", backref="eng")
 
def __init__(self, eng, chinese):
self.eng = eng
self.chinese = map(Chinese, chinese)
 
def __repr__(self):
return "Word(%r, %r)" % (self.eng, [c.word for c in self.chinese])
 
class Chinese(Base):
__tablename__ = "chinese_words"
 
word = Column(String(128), primary_key=True)
eng_id = Column(Integer, ForeignKey('words.id'), primary_key=True)
 
def __init__(self, word):
self.word = word
 
def __repr__(self):
return "Chinese(%r)" % (self.word,)
 
 
# connect to db; create tables, session maker
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
Session = scoped_session(sessionmaker(bind=engine,
autocommit=False, autoflush=False))
 
# add word to db
## from .model import Session, Word
session = Session()
word = Word(eng='art', chinese=[u'艺术',u'美术'])
session.add(word)
session.commit()
 
# get word from db
session = Session()
word = session.query(Word).filter_by(eng='art').one()
print("\n".join(map(str, [word, word.eng, word.chinese, word.chinese[0].eng])))

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.