Tag Archives: Python

How to Create and Drop Tables in SQL Alchemy

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:

  1. You have iPython installed (pip install ipython #if you don’t have it)
  2. You have sqlAlchemy installed (pip install sqlalchemy)
  3. You have psycopg2 installs (pip install psycopg2)
  4. You have a Postgres connection you can connect to
  5. 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.

database environment for sqlAlchemy (Postgres)

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:python@') # 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)),
 ) # 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.

sqlAlchemy Example

sqlAlchemy creating and dropping tables

This exercise was executed in a Ubuntu Linux virtualenv. =)

Thank you for reading!


Leave a comment

Filed under Linux, Python, SQL

Getting Started with virtualenv

Why should you care about using virtualenv?  Basically it provides you with a development environment for your various python projects.  Not all python projects will call for the same libraries and being able to solely keep track of the needed libraries within a specific environment can make life easier by:

  • Reducing the # of dependencies that need to be maintained in a single environment
  • Reduce the risk of breaking program dependencies of versioned libraries
  • Replicate the needed environment in other machines without re-installing every single library that you may have done once upon a time

Here are some simple steps to get started with virtualenv on a brand new machine (should apply to both linux and os x)

On a new installation of Linux (or OS X instance)
1.) sudo apt-get install pip #install pip so you can install virtualenv
2.) sudo pip install virtualenv #install virtualenv so you can have controlled python environments

3.) virtualenv new_project  #create a project directory and execute this command where new_project = your project name for e.g.

4.) source new_project/bin/activate #to start your virtual environment
5.) pip install psycopg2 #install any python library in your new python virtual environment


6.) pip install jupyter #installs jupyter notebooks! (which can be kicked off on your browser)




7.) deactivate #when you are done working in your virtual environment, deactivate


From here, you can return to step 4 when you want to work on your project again. Or go to step 3 if you want to start a new project.

To capture your python virtual environment components, do the following:
pip freeze > requirements.txt

This way, if you need to recreate your environment, you can use the file by typing:
pip install -r requirements.txt

For more info, go here: http://python-guide-pt-br.readthedocs.io/en/latest/dev/virtualenvs/

Before I go, if you are wondering how to start jupyter in your web browser within your new virtualenv, via the command line execute the following:

source my_project/bin/activate #assuming you exited from your virtualenv

jupyter-notebook #your web browser should open after this



Leave a comment

Filed under Linux, Python

Listing out Built in Functions in Python

To retrieve a list of Built in Functions in Python, type:  dir(__builtins__)

You will get the following list


To understand what each function does, you can type help(fuction_name) to get more details on that function.  Example: help(NameError)



Leave a comment

Filed under Computer - Technical, Python

List of Built in Functions in Python

To get a list of built in functions in Python, type the following:

It will result in a list like the one below



Leave a comment

Filed under Sharing Stuff

Merging text files with python

Here is a simple yet effective program that will concatenate (or append) all text files using python.  Simply define the following three variables at the top of the program:

  1. File extension (example: .txt, .csv, etc…)
  2. Directory path:  If you are using Python under windows, then place an extra forward slash in your file path for every forward slash (ex: C:\\Documents and Settings\\user\\folder)
  3. The name of the results file that is to be created.  This can carry a different extension than the files you are concatenating
#set to the file extension of "to-be-merged" files
ext = '.txt'
#set to your working directory
dir_path = '/home/user/pywork'
#set to the name of your output file
results = 'final.out'

import os
files = os.listdir(dir_path)
for f in files:
  if f.endswith(ext):
    data = open(f)
    out = open(results, 'a')
    for l in data:
      print(l, file=out)

Leave a comment

Filed under Computer - Technical, Python

Getting started with Python and using Komodo Edit

You’re probably here because you recently installed Python on your computer.  You don’t mind IDLE, the interface you use to write and test your code, but in the past you have used tools like Visual Studio and like the code auto completion that it provides.

Fortunately, there is a Python editor that does offer features a bit beyond what the Standard Python IDLE offers.  Although I am not knocking IDLE, auto completion is always nice.  It’s like spell check for programmers.  So now what do you do?

  1. If you don’t already have Python installed, install Python (http://www.python.org/download/).
  2. Install Komodo Edit (http://www.activestate.com/komodo-edit).
  3. In Komodo Edit create a new File and Save it with a .py extension
  4. Write some code, like the code sample given below.
  5. Go to Tools > Run Command (Be sure the box has the value: %(python) “%F”

After that you’re program will execute.  See the slideshow below.  Also, you may want to enclose the %F with double quotes.  This will ensure that your program is run even when it is inside a directory named with spaces.  Also, below the slideshow is the sample code used in the program.  Copy and paste and test along with the slide show images.

This slideshow requires JavaScript.

print("what is your name? ")
n = input()
print("you are awesome!", n)

1 Comment

Filed under Computer - Technical