[python]SQLAlchemy usage example

This is an aggregation on how to use SQLAlchemy to create a database, table and insert data into the created table. Refer to this tutorial on how to use SQLAlchemy. from sqlalchemy_utils import database_exists, create_database from sqlalchemy import create_engine, Column, Integer, String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker url = "mysql+pymysql://username:password@database_address/database_name" # Create … Continue reading [python]SQLAlchemy usage example →

[python]VARCHAR requires a length on dialect mysql with sqlalchemy

I remember I did not need to define the varchar length, however I encountered this error: sqlalchemy.exc.CompileError: (in table 'test_table', column 'name'): VARCHAR requires a length on dialect mysql So I would need to define the length of varchar with the String object from SqlAlchemy. meta = MetaData() """ Define the table. The below is … Continue reading [python]VARCHAR requires a length on dialect mysql with sqlalchemy →

Mariadb for remote access

The default service is bind to localhost:3306 to allow remote client to access to the mariadb, modify this file: /etc/mysql/mariadb.conf.d/50-server.cnf, comment the bind-address and add the two new lines as shown below: Then restart the mariadb service: sudo systemctl restart mariadb

[python]Flask Migrate

Flask Migrate advantage This is for easy update of the existing database, such as create a new column, create a new table, drop a table, revert previous database. With migrate, I do not need to do the db.create_all() anymore. A note on sqlite3, once a table is created you cannot insert column directly, need to … Continue reading [python]Flask Migrate →

[python]Query object from table with Flask SQLAlchemy

The purpose of using SQLAlchemy is to avoid using SQL syntax, and Flask's extension of SQLAlchemy makes usage easier than the original SQLAlchemy. So here are two demonstrations to query every row and query one row with a condition.

[python]Create database with SQLAlchemy

The objective of learning SQLAlchemy is to use its Object Relational Mapper (ORM), this allows programmer who does not do SQL syntax to also do CRUD on supported database, the ORM does the "translation or mapping" for us in the background, in our code we only need to do CRUD with python syntax. The SQLAlchemy … Continue reading [python]Create database with SQLAlchemy →

[database]Functions provided by DBMS

Here is a list of non-exhaustive functions provided by DBMS (Database management system) To prevent data lost during system failure, it should provide reliable recovery service. Provide transaction service for user such as read (select), update, delete, commit, insert, rollback. Provide authorisation service such as permission for user to insert (write new data), update (update … Continue reading [database]Functions provided by DBMS →

[DATABASE] File base vs database

This post illustrates the need for database approach to manage data. Problems with file base approach Data is stored in file defined by application, hence there is a possibility of potential useful data needed by another application but not aware by the application that requires it. Because the data is stored in file defined by … Continue reading [DATABASE] File base vs database →

[DATABASE]ACID property of DBMS

Atomicity refers to a transaction is either fully completed or not completed at all, there is no partial complete transaction. This also refers to all or none rule. Consistency refers during transaction the integrity constraints are maintained so that the data are consistent before and after transactions. Isolation refers to changes of one transaction are … Continue reading [DATABASE]ACID property of DBMS →

[DATABASE] VIEWS

Views are virtual tables derived from columns and rows from the base table. Views provide what data are needed by the users, saving the trouble for users to understand the complexity and abstraction of the base tables. Views are dynamic, updates in base tables will reflect on views. Due to views only have limited access … Continue reading [DATABASE] VIEWS →