SQLAlchemy create_all

Definition of SQLAlchemy create_all

Sqlalchemy create_all method is used to create a new table into the database. This method will first check whether the table exists in the database or not if suppose it has found an existing table it will not create any table. If suppose the sqlalchemy create_all method has not found any table it will create a new table into the database. Sqlalchemy create_all method will create the foreign key constraints.

Overview of SQLAlchemy create_all

• There are options available to change this type of behavior in the sqlalchemy create_all method. We can use alter table command instead of the create_all method.
• As we create all the tables by using the create_all method in sqlalchemy. Suppose we want to delete all the tables from the database we are using the drop_all method in sqlalchemy.
• The sqlalchemy drop_all method is opposite to the method of create_all. Also, sqlalchemy drop_all method will first check whether the table exists in the database or not, if suppose it has found an existing table it will not drop the table. Suppose the sqlalchemy drop_all method has not found any table it will drop all the tables from the database.
• This method is not used for creating a single table, it will be applied only for creating multiple tables into the database server.
• Suppose we want to create a single table then we need to use create method in sqlalchemy.
• The create method in the background of the database server will issue the create table command by default.
• The autoIncrement in this method will refer to the DDL which was emitted from the column within our create table statement.
• At the time of creating multiple tables into the database, we are using the sqlalchemy create_all method.
• Suppose we want to create a single table into the database server then we are using the table. create method.
• As per creating the table into the database insert statement will compile and insert the data into the table.
• At the time of using the sqlalchemy create_all method on the column, there is no other construct that will be ascociated with it, this parameter will be ascociated with database-specific keywords.

How to use SQLAlchemy create_all?

• To use the sqlalchemy create_all method we need to install the sqlalchemy package first in our system. Without installing this package we cannot use the same in our code.
• Sqlalchemy module is not coming at the time of installing the python package in our system. To use sqlalchemy we need to install the sqlalchemy module by using the pip command. Below steps that show how to install the sqlalchemy module are as follows.
• In the first step, we are installing the sqlalchemy module by using the pip command. We can install the sqlalchemy module in any operating system on which python is installed. In the below example, we are installing the sqlalchemy module.

pip install sqlalchemy

• After installing all the modules we are opening the python shell by using the python3 command.

python3

• After login into the python shell in this step, we are checking sqlalchemy package is installed in our system.

import sqlalchemy

• After checking whether the sqlalchemy package is installed or not then we are importing the following modules by using the sqlalchemy package. We are importing the MetaData, create_engine, Column, Table, Integer, and string packages by using the sqlalchemy package as follows. We have imported all the modules while executing separate commands. We are importing all the modules by using the import keyword.

from sqlalchemy import MetaData
from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import Table
from sqlalchemy import Integer
from sqlalchemy import String

5. After importing all the modules in this step we are giving the sqluri of the database server and also we are calling the create_engine method to create the table into the database server. Also, we are defining the MetaData for the SQL engine.

sql_uri = 'sqlite:///db.sqlite'
sql_engine = create_engine (sql_uri)
sql_meta = MetaData (sql_engine)

6. After defining the sql_uri and calling the create_engine method in this step we are creating the multiple tables. In the below example, we are creating two table names as sql_stud1 and sql_stud2 are as follows. In sql_stud1 we have to create the two column names as stud_id and stud_name also in sql_stud2 table we have to create two column names as stud_id and stud_addr.

sql_stud1 = Table ('EX1', sql_meta,
Column ('stud_id', Integer, primary_key=True),
Column ('stud_name', String))
sql_stud2 = Table ('EX2', sql_meta,
Column ('stud_id', Integer, primary_key = True),
Column ('stud_addr', String))

7. After creating the table we are calling the create_all method to create the table into the database server as follows.

sql_meta.create_all()

SQLAlchemy create_all Examples

• Below is the example of the sqlalchemy create_all method in the below example we are creating three tables with the sqlalchemy create_all methods as follows. In the below example, we have imported the MetaData, create_engine, Column, Table, integer, and string modules.
• After importing the module we are providing the database url and call the create_engine method then we are defining the MetaData method. Then we are creating the three tables by using the create_all method. At last we are calling the create_all method.

Code –

from sqlalchemy import MetaData
from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import Table
from sqlalchemy import Integer
from sqlalchemy import String
py_uri = 'sqlite:///db.sqlite'
py_engine = create_engine (sql_uri)
py_meta = MetaData (py_engine)
stud1 = Table ('EX1', py_meta,
Column ('stud_id', Integer, primary_key=True),
Column ('stud_name', String))
stud2 = Table ('EX2', py_meta,
Column ('stud_id', Integer, primary_key=True),
Column ('stud_addr', String))
stud3 = Table ('EX3', py_meta,
Column ('id', Integer, primary_key=True),
Column ('addr', String))
py_meta.create_all ()

• In the below example we are creating the single table by using the create_all method as follows.

Code –

from sqlalchemy import MetaData
from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import Table
from sqlalchemy import Integer
from sqlalchemy import String
py_uri = 'sqlite:///db.sqlite'
py_engine = create_engine (sql_uri)
py_meta = MetaData (py_engine)
py_stud = Table('EX1', py_meta,
Column ('id', Integer, primary_key=True),
Column ('name', String))
py_meta.create_all()

Conclusion

Sqlalchemy create_all method will create the foreign key constraints by using tables at the time of creating the table definition. Sqlalchemy create_all method will create foreign key constraint at the time of creating table so it will generate an order of table as per dependency.

Recommended Article

This is a guide to SQLAlchemy create_all. Here we discuss definition, overviews, How to use SQLAlchemy create_all, examples along with code implementation and output. You may also have a look at the following articles to learn more –

1. PL/SQL Raise Exception
2. MySQL Partitioning
3. PostgreSQL Update
4. PostgreSQL Export CSV