Quick and easy step by step approach to creating and dropping tables in SQL Alchemy 1.1 and 1.2. All done with a few lines of code.
The following assumptions are made:
- You have iPython installed (pip install ipython #if you don’t have it)
- You have sqlAlchemy installed (pip install sqlalchemy)
- You have psycopg2 installs (pip install psycopg2)
- You have a Postgres connection you can connect to
- Your Postgres users has the ability to create and drop tables
Perfect, let’s get started
First, I want to begin looking at my existing database structure. I opened up pgAdminIII. In Postgres, I have 2 databases, postgres and production. I am interested in connecting and working in the production database. In the production database, I have 2 schemas, public and python. I will be working in the python schema in this example. If you have any questions up to this point, please comment.
Open your terminal (OS X, Linux) or your command line (Windows)
Type ‘ipython’ to open iPython then, execute the following lines of code. Anything after the hashtag (#) is optional, as that is a comment regarding the line of code
from sqlalchemy import * # imports all needed modules from sqlalchemy engine = create_engine('postgresql://python:email@example.com/production') # connection properties stored metadata = MetaData() # stores the 'production' database's metadata users = Table('users', metadata, Column('user_id', Integer), Column('first_name', String(150)), Column('last_name', String(150)), Column('email', String(255)), schema='python' ) # defines the 'users' table structure in the 'python' schema of our connection to the 'production' db users.create(engine) # creates the users table
Let’s take a look at the table we created in pgAdmin
As you an see, the table users has been created with the exact same structure you defined in your code.
What if you want to drop the table you created via sqlAlchemy? Easy peasy, just execute the following statement
users.drop(engine) # drops the Now, let say you want to drop the table you created. You simply execute the following script:users table
Go back to your pgAdmin, refresh your schema, and confirm that it is gone!
Below is the entire code output in the iPython terminal.
This exercise was executed in a Ubuntu Linux virtualenv. =)
Thank you for reading!