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@127.0.0.1/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.

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

Import Data from DB2 to SQL Server 2012


Below are steps that you can take to import data from IBMs DB2 database into SQL Server.  I’m using SQL Server 2012 Express edition.

Figuring this out has been one of the more frustrating tasks for me, so I hope this helps others like me.

Step 1.) Download and install DB2 Data Server Runtime Client (9.7 Fix Pack 4). Found in IBMs download page.

Step 2.) Extract the contents of your download to Program Files / IBM

Extract and paste the folder and it's contents under Program Files /IBM

Extract and paste the folder and it’s contents under Program Files /IBM

Step 3.) Open your command prompt (as Administrator).  Navigate to the “bin” folder under that folder you just copied into your directory.

Type db2oreg1 -i then type db2oreg1 -setup

Registering and setting up your DB2 ODBC Driver in Windows

Registering and setting up your DB2 ODBC Driver in Windows

Step 4.) Open up SQL Server Management Studio.  Right click on the database that you want to import DB2 data into

Right click on database name, select Tasks, select Data Import

Right click on database name, select Tasks, select Data Import

Step 5.) Select the proper driver for DB2

Select the DB2 Driver that you need to connect to the database

Select the DB2 Driver that you need to connect to the database

Step 6.) Set the connection properties to your DB2 database

Set DB2 Connection properties and test connection

Set DB2 Connection properties and test connection

Step 7 .)  Proceed with the wizard (Write a query to extract data or copy a table).  I chose to write a query.

Copy a table or write a query

Step 8.) Preview mapping of query or table copy

Destination attribute types based on SQL evaluation of values from query

Destination attribute types based on SQL evaluation of values from query

Step 9). Success!

Successful Transfer from DB2 to SQL Server 2012

If you experience any problems and would like some help, please be as specific as possible with regards to the steps you took to get where you are.

 

Best wishes!

Leave a comment

Filed under Computer - Technical, SQL

SELECT without table (FROM CLAUSE) in DB2


You are here because you are trying to generate a string using a SELECT statement in DB2, much like you would do in SQL SERVER.  In other words, you want to execute a SELECT statement without the FROM clause in DB2 as you would in SQL SERVER.  Below is how you do that.

In SQL Server, if you wanted to a string of text to be returned to you, you would simply type:

SELECT 'Whatever you want your string to say'

The results would be :

Whatever you want your string to say  

In the results tab.

If you want to accomplish the same thing in DB2, instead of SELECT, use the word VALUES.  So your statement would be typed like this:

VALUES 'Whatever  you want your string to say'

The results will be the same

This is comparable to a SELECT statement with NO FROM clause in SQL SERVER

This is comparable to a SELECT statement with NO FROM clause in SQL SERVER

Leave a comment

Filed under SQL

Installing the sample database in IBM DB2 C in Linux (Ubuntu)


After you install DB2 on your Linux system (along with IBMs Data Studio) you may have problems connecting to the sample database.  It may be that it did not install.  If you have any questions about the above, post a comment.

To install the sample database, first be sure you log in to your instance.  By default during your first install, your instance name should be db2inst1

 

 

Login to your db2inst1 account

Once you are in you’re logged into your instance session, navigate to your sqllib/samples folder.   Execute the db2sampl file.  This will begin the Sample Database installation process.

installing sample DB2 database in Linux

 

Once the installation is complete, connect to the database by typing: db2 connect to sample.

You can list the tables in the database by typing: db2 list tables

connect to and list tables in database

 

Now that you’ve set up your playground, have fun.

Leave a comment

Filed under Linux, SQL

How to upload multiple Excel 2007 files to SQL Server 2005


If you are here, then you are probably encountering the following:

  • SQL Server 2005 Import/Export wizard is not recognizing your Excel 2007 (xlsx) file(s)
  • You have many files that you need to upload and can’t afford to do them one by one
The good news is that you don’t have to.  Also, this post applies to Excel 97-2003 files, text files and anything else you can upload to SQL Server 2005.  I figured I would begin with one of the more difficult approaches and anything simpler can be deduced from these steps.  For this post, I am using a lot of images and few words.  If you have any questions, please leave a comment.
Also, to accomplish the entire task, you will need a version of SQL Server 2005 (or later) and SQL Server Business Intelligence Development Studio (BIDS).  If you have SQL Server Express, you can download BIDS from Microsoft here.
The process is going to be simple.
  1. Use the Import/Export Wizard to import data and save the SSIS package to your computer.
  2. Open up BIDS and modify your SSIS package.
  3. Run your SSIS package.

This slideshow requires JavaScript.

Below is a narrative for each picture.

Leave a comment

Filed under Computer - Technical, SQL