Category Archives: SQL

Helpful SQL Queries for those odd situations

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

tPostgresqlOutput_3 org.postgresql.util.PSQLException: ERROR: length for type varchar must be at least 1

There are several reasons why you may receive this error when using Talend Open Studio, but the one I recently encountered was user error.  When I created my schema, I had placed my varchar length values under the “Precision” column instead of “Length” column.  This was the cause of my error.

Misplaced varchar lengths in Talend Open Studio schema

I prefer these errors over product bugs!



Leave a comment

Filed under SQL

Talend Data Integration – Exception in component tOracleOutput_1java.lang.ArrayIndexOutOfBoundsException: -32703

You are probably seeing this error as you attempt to transfer data with many date values from one Oracle database to another Oracle database using Talend Studio.

Well quick fix (not sure why it works) is to decrease the commit size in your OracleOutput node. The default is 10,000.  I reduced mine to 500.  See illustration below.

Changing Commit In Talend Data Integration

Comments are always welcome.

Leave a comment

Filed under SQL