[DOCKER] I tried to create an article in Wiki.js with SQLAlchemy

What I tried

It's a multifunctional Wiki.js, but I didn't know how to batch import existing data, so I tried to force the database to be updated. This is not a formal method, so please try it at your own risk.

procedure

--Preparation --Launch Wiki.js with docker-compose --Build SQLAlchemy environment locally --Create a class from the database --Create an article with SQLAlchemy

Please also refer to GIGAZINE article.

Preparation

The work is confirmed with bash of macOS. Please have docker-compose and Python 3.8 installed.

Launch Wiki.js with docker-compose

There are various startup methods in "Wiki.js --Docs", but docker-compose is used. First, create docker-compose.yml.

bash


mkdir wikijs
cd wikijs
cat << EOF > docker-compose.yaml
version: "3"
services:

  db:
    image: postgres:11-alpine
    environment:
      POSTGRES_DB: wiki
      POSTGRES_PASSWORD: wikijsrocks
      POSTGRES_USER: wikijs
    ports:
      - "5432:5432"
    logging:
      driver: "none"
    restart: unless-stopped
    volumes:
      - db-data:/var/lib/postgresql/data

  wiki:
    image: requarks/wiki:2
    depends_on:
      - db
    environment:
      DB_TYPE: postgres
      DB_HOST: db
      DB_PORT: 5432
      DB_USER: wikijs
      DB_PASS: wikijsrocks
      DB_NAME: wiki
    restart: unless-stopped
    ports:
      - "80:3000"

volumes:
  db-data:
EOF

If you change the port 5432 on the local side, change "5432: 5432" to "15432: 5432", and change the subsequent 5432 as well.

Launch & login

Wiki.js starts with docker-compose up -d. The stop is docker-compose down. Wiki.js is available at http: // localhost /.

Create ADMINISTRATOR ACCOUNT appropriately. Then log in with the account you created.

Top page creation

Immediately after logging in, there are no pages. First, create the top page. The path of the top page is / <language> / home. Initially, you can only use English (ʻen`) as the language.

Here, we will use Japanese (ja) as the language. First, after creating the English top page, install the Japanese language, delete the English top page, and create the Japanese top page.

Press " + CREATE HOME PAGE ". Press "Markdown", press " ✓ OK ", and then press" ✓ CREATE".

The top page (http: // localhost / en / home) opens. Since you are logged in as Administrator, open the management screen (http: // localhost / a / dashboard) with " "in the upper right.

Select " Locale "from the left side. Select "Japanese" from " Download Locale "and press" Download "in the" cloud with down arrow mark ". Then go back up and set " Locale Settings "to" Japanese "and press" ✓ APPLY" in the upper right.

Since the active namespace has been changed to Japanese, the top page needs to be recreated in Japanese. Select " Page "from the left side. Select the item whose Path is "en / home" and delete it with " Delete "of" ʻACTIONS v` ".

Click "Close" on the upper right to return to the first screen, so please create a Japanese top page.

Build SQLAlchemy environment locally

Wiki.js allows you to create an article in a browser, but here we will try to create an article by editing the database directly from Python. First, create a Python virtual environment.

bash


python3.8 -m venv venv
source venv/bin/activate
pip install psycopg2-binary SQLAlchemy

Create class from database

Create a class from the database for use with SQLAlchemy. Execute the following to create db_class.py.

bash


cat << EOF | python
import re

from sqlalchemy import MetaData, create_engine
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine("postgresql+psycopg2://wikijs:wikijsrocks@localhost:5432/wiki")

metadata = MetaData()
metadata.reflect(engine)

Base = declarative_base(metadata=metadata)


def _subfunc(m):
    s = ".".join(m.group(1).split(".")[-2:])
    return rf"ForeignKey('{s}')"


def make_class(cls):
    lst = [f"class {str(cls).split('.')[-1][:-2]}(Base):"]
    lst.append(f'    __tablename__ = "{cls.__tablename__}"\n')
    for column in cls.__table__.columns:
        s = repr(column)
        nam = s.split("'")[1]
        s = re.sub(r", table=[^>]+>", "", s)
        s = re.sub(r", server_default=[^)]+\)", "", s)
        s = re.sub(r"ForeignKey\('([^']+)'\)", _subfunc, s)
        lst.append(f"    {nam} = {s}")
    res = "\n".join(lst) + "\n"
    res = res.replace("metadata = Column", "metadata_ = Column")
    return res


def make_classes():
    lst = [
        """\
# made by make_classes
from typing import Any
from sqlalchemy import Column, ForeignKey, Text
from sqlalchemy.dialects.postgresql import (
    BOOLEAN, BYTEA, INTEGER, JSON, TEXT, TIMESTAMP, VARCHAR
)
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()  # type: Any
"""
    ]
    dc = {"__table_args__": {"autoload": True}}
    for tbl in sorted(metadata.tables):
        if tbl in {"brute"}:
            continue
        typ = type(tbl.title(), (Base,), dict(__tablename__=tbl, **dc))
        lst.append(make_class(typ))
    with open("db_class.py", "w") as fp:
        fp.write("\n".join(lst))


make_classes()
EOF

Create an article with SQLAlchemy

First, create a file.

bash


cat << EOF > main.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from db_class import *

engine = create_engine("postgresql+psycopg2://wikijs:wikijsrocks@localhost:5432/wiki")
Session = sessionmaker(bind=engine)
session = Session()
pgid = max(i.id for i in session.query(Pages).all()) + 1
ptid = max(i.id for i in session.query(Pagetree).all()) + 1
pg = Pages(
    id=pgid,
    path=f"test{pgid}",
    hash="0123456789001234567890abcdefghijklmnopqr",
    title=f"Test page{pgid}",
    isPrivate=False,
    isPublished=True,
    content="",
    contentType="markdown",
    createdAt="2020-10-04T09:00:00.000Z",
    updatedAt="2020-10-04T09:00:00.000Z",
    editorKey="markdown",
    localeCode="ja",
    description="",
    authorId=1,
    creatorId=1,
    render="<div></div>",
    toc=[{"title": "", "anchor": "", "children": []}],
    extra={"js": "", "css": ""},
    publishStartDate="",
    publishEndDate="",
)
pt = Pagetree(
    id=ptid,
    path=pg.path,
    localeCode="ja",
    pageId=pgid,
    title=pg.title,
    depth=1,
    isPrivate=False,
    isFolder=False,
    ancestors=[],
)
session.add(pg)
session.add(pt)
session.commit()
session.close()
EOF

Above, create main.py. python main.py creates an empty page calledTest Page ....

The content is described in Pages.content and Pages.render. It will not be displayed if you just described it in content, but it will be displayed if you edit and save it.

backup

You can dump the contents of the database with pg_dump -h localhost -p 5432 -U wikijs wiki -f data.sql.

You can use psql -h host -p 5432 -U user database -f data.sql to return data to an empty PostgreSQL.

Recommended Posts

I tried to create an article in Wiki.js with SQLAlchemy
I tried to integrate with Keras in TFv1.1
I tried to detect an object with M2Det!
I tried to create a table only with Django
I tried to implement an artificial perceptron with python
I tried to find an alternating series with tensorflow
I tried to create an environment to check regularly using Selenium with AWS Fargate
I tried to build an environment with WSL + Ubuntu + VS Code in a Windows environment
I tried to create API list.csv in Python from swagger.yaml
I tried to automatically create a report with Markov chain
I tried to create a class to search files with Python's Glob method in VBA
I tried to explain how to get the article content with MediaWiki API in an easy-to-understand manner with examples (Python 3)
I tried to create an environment of MkDocs on Amazon Linux
I tried to describe the traffic in real time with WebSocket
I tried to create a list of prime numbers with python
I tried to process the image in "sketch style" with OpenCV
How to create a heatmap with an arbitrary domain in Python
I tried to make an analysis base of 5 patterns in 3 years
I tried to create Bulls and Cows with a shell program
I tried to process the image in "pencil style" with OpenCV
I tried to log in to twitter automatically with selenium (RPA, scraping)
I tried to make an image similarity function with Python + OpenCV
I tried to create a linebot (implementation)
I tried to implement PLSA in Python
I tried sending an SMS with Twilio
I tried to implement Autoencoder with TensorFlow
I tried to implement permutation in Python
I tried to create a linebot (preparation)
I tried to visualize AutoEncoder with TensorFlow
I tried to get started with Hy
I tried to implement PLSA in Python 2
I tried sending an email with python.
I tried to implement PPO in Python
I tried to build an estimation model of article titles that are likely to buzz with Qiita
[Python] I tried to summarize the set type (set) in an easy-to-understand manner.
I tried to make an open / close sensor (Twitter cooperation) with TWE-Lite-2525A
I tried to create a plug-in with HULFT IoT Edge Streaming [Development] (2/3)
I tried to summarize Cpaw Level1 & Level2 Write Up in an easy-to-understand manner
I tried to create a plug-in with HULFT IoT Edge Streaming [Execution] (3/3)
I tried to create CSV upload, data processing, download function with Django
I tried to summarize Cpaw Level 3 Write Up in an easy-to-understand manner
[Outlook] I tried to automatically create a daily report email with Python
I tried to create a plug-in with HULFT IoT Edge Streaming [Setup] (1/3)
I want to create an Ubuntu chrome User Profile with Colab only
I tried to predict next year with AI
I tried to detect Mario with pytorch + yolov3
I tried to implement reading Dataset with PyTorch
I tried to use lightGBM, xgboost with Boruta
I tried to learn logical operations with TF Learn
I tried to move GAN (mnist) with keras
I tried to get an image by scraping
I tried to save the data with discord
I want to create a window in Python
I tried to detect motion quickly with OpenCV
I want to be an OREMO with setParam!
I tried to output LLVM IR with Python
I tried to implement TOPIC MODEL in Python
Create an image with characters in python (Japanese)
I tried to automate sushi making with python
I tried to operate Linux with Discord Bot
I tried sending an email with SendGrid + Python