Setup your DB first¶
SQLBatis provides the ability to interact with the database through command-line base on the alembic. There is sqlbatis command is available once the sqlbatis is installed, you can use it directly.
Four common commands will be mentioned here, we will take a quick tour about them, also create a database for your app from scratch step by step, and you can run the command:
sqlbatis --help
to get more details about sqlbatis
Define a Model¶
SQLBatis can mapping the model to the table in the database, but there are some rules that we need to follow, Let’s define a Model first:
from sqlalchemy import Column, Integer, String
from sqlbatis import Model
class User(Model):
id = Column(Integer, primary_key=True)
name = Column(String)
full_name = Column(String)
All the Models should inherit from sqlbatis.model.Model
, so we can find all the models that you defined
in the app, and create them in your database. We will convert the class name to camel case when we mapping the
class to the table, and use it as table name.But if __tablename__ is specified we will use it instead of the camel case:
from sqlalchemy import Column, Integer, String
from sqlbatis import Model
class User(Model):
# will use 'customized_table_name' instead of 'user'
__tablename__ = 'customized_table_name'
id = Column(Integer, primary_key=True)
name = Column(String)
full_name = Column(String)
tips: Column, Integer, String etc. are imported from the sqlalchemy
Scan all the models¶
You can use the command:
sqlbatis scan
to check the models that you defined in the app, the useage of this command:
Usage: sqlbatis scan [OPTIONS]
Show all the models that searched in the directory
Options:
-d, --directory TEXT The main folder that you want to search the models,
default is current work directory
-i, --ignore TEXT Ignore the folder or files through defined regrex
expression, List[regrex]
--help Show this message and exit.
now the default ignore directories are:
['tests', 'build', 'vscode','dist', 'egg', 'migrations', 'sqlbatis']
Initialize the tools¶
Once you confirmed the models that you defined, just use the command:
sqlbatis init
to generate the initial configuration files which will be used in migrate and upgrade stage. the usage of this:
Usage: sqlbatis init [OPTIONS]
Init the db tools
Options:
-d, --directory TEXT The main folder that you want to search the models,
default is current work directory
-i, --ignore TEXT Ignore the folder or files through defined regrex
expression, List[regrex]
-db, --db_url TEXT The database url config
--help Show this message and exit.
This command will generate several configuration files in the migrations folder automatically, the migrate scripts will be generated according to the configuration defined here.
Caution: We need to specify the -db when we execute this command, will remove that in the near future.
Generate the scripts¶
Here we will generate the scripts for upgrade, the command:
Usage: sqlbatis migrate [OPTIONS]
Generate the migrate script
Options:
--help Show this message and exit.
The upgrade script will be generated in the migrations/versions/ folder, and you can find the script name from the console. Before you go to next step, you still have the opportunity to modify the scripts in the versions folder utils you think it’s make sense.
Sync the DB¶
We have not mapping the models to our database table util the upgrade command is executed:
Usage: sqlbatis upgrade [OPTIONS]
Upgrade the db to the version specified, if not sepecified will update to
the latest version
Options:
-v, --version TEXT The version that you want to upgrade to.
--help Show this message and exit.
Now, the table will be created in your DB.