Wednesday, May 21, 2014

I've used UberSVN and TortoiseSVN for version control before, but I figured this time I will join the rest of the world and start using git.

I've played around with git before, but never started using it for real. Why? I don't know. I'm sort of an idiot that doesn't version control a lot of my software. I would use subversion for work stuff, but for my own personal projects, I just build the software outright. Generally, I don't really take on giant projects to pursue in my free time.

django-admin.py : The term 'django-admin.py' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:1
+ django-admin.py startprjoect rebates
+ ~~~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (django-admin.py:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

What's the issue? Well, windows really doesn't know about the Python executable created in the Scripts folder when the virtual environment is created, so instead we have to tell it explicitly about the module ((django-jay) PS C:\Python27\home\jtarlecki\djcode\django-jay> python -m django-admin startproject rebates) in which it should look to fire up python. That and, we need to invoke the command with a leading python. We also drop the .py from django-admin.py Observe:

When we first ran django-admin.py startapp rebate , django created a collection of files inside our rebate app:

+---rebate
¦ admin.py
¦ models.py
¦ tests.py
¦ views.py
¦ __init__.py

So, we just added api.py, now we need to add urls.py to our app to help give this thing some specificity within the app. It doesn't really matter where we put it (we could put this at the project level) but for now, let's leave it here:

from django.conf.urls import patterns, include, url
from rebate.api import ZipcodesResource
#now that ArticleResource is imported, create an instance of it
zipcode_resource = ZipcodesResource()
urlpatterns = patterns('',
url(r'^api/',include(zipcode_resource.urls)),
# these urls are automagically created by our ModelResource class from api.py
)

To explain some of the comments above: we're going to pull in the ZipcodesResource() class from api.py then create an instance of it at runtime. Then using some tastypie wizardry, the ModelResource class imported in api.py automagically creates some urls for us to use. Le'ts promise ourselves to read more about it here later.

At this point we are officially daisy-chaining some urls. Make sure your entire django project has some clue about its enclosed apps. Fire up the urls.py from the project directory (rebates):

Basically, this is just a pass-through that allows the /api/ url we defined at the app-level to be the top-level url at the project-level. Confused? Yes, it is confusing. If you mess around with it, you will get it.

as our <project-level-url> is simply a pass-through as described above.

Oops! Tastypie isn't the cleanest with urls. You should see the following message prompting you to put a little additional info into your url

Sorry, not implemented yet. Please append "?format=json" to your URL.

OK. Let's append that to the end of our url.

http://localhost:8000/api/zipcodes/?format=json

Awesome! We get a response, along with some metadata to help give context for what the response actually is.

Its a big table, so tastypie by default gives responses in chunks of twenty (20) records. Screen snippet above.

By default, tastypie also allows you to put a primary key number as the next-level-down url. Say we wanted to see the detail on the first object pictured above (id=45143), we could call it with the following url:

http://localhost:8000/api/zipcodes/45134/?format=json

Which give the following resultant:

response

Some of you probably noticed the recouse_uri field annotated in the previous picture. All I did was copy that in to my browser. Notice that tastypie has a built in url pattern for primary keys of tables you hook into its built-in resources, which has the form:

http://localhost:8000/api/zipcodes/<pk>/?format=json

where <pk> is the primary key number from your table.

I'm still getting xml errors because of something missing in my installed lxml. I tried re-installing from pip, but still get the error. For now, I'm only concerned with .json responses anyway, so I'm just going to skip for now.

If we want to add a filter to our urls (say, we want a response based on a specific query), we can add a filter to the api.py file. Let's say we want to search for zipcode information by zipcode, since someone who is using this api probably has no idea what our primary keys are in our database.

Thus, let's put a zipcode filter in our api.py file.. append the following inside the meta class of the ZipcodesResource class:

filtering = {
"zipcode": ('exact'),
}

There are many different filters you can add, but right now we will look at the exact match.

Access the following url

http://localhost:8000/api/zipcodes/?zipcode=19104&format=json

Voila! Now we have a list of zipcode info for 19104! Read up on the tastypie docs for more info.

I tried for about an hour or two, but then realized that my attempts and full automation were taking longer than me copying over the entire database with partial-automation. These are tough decisions to make. I decided to push forward with some text outputs that would allow me to run psql scripts and quickly achieve some table updates.

In the directory where this python script resides, be sure to save your csv files there as well with correctly corresponding table names. If they aren't, just make some tweaks to the code and you will be all set.

Copy those output queries into your psql shell and you are on your way to semi-automating a miserable task!

Saturday, May 3, 2014

Great this guy has a way for me to define a django model then copy in an entire database--- constraints, defaults, foreign keys and all!

Well, no, I don't have a great way to do that. Is it possible? Probably. But I don't know how to do it.

I have some tables that I want to preserve the relationship structure. So when I add tables in my django project (models.py) then sync up to the database, I need to to disable the automatically generated primary key field, so I can insert the records into the database via the \COPY command. Finally, I need to reinstate the serialization of the id field as a constraint.

Then create the database model in models.py. In a complex data model situation, I like to bring the tables in one at a time. That means, creating a class in models.py, then syncing to the database one table (or collection of related tables) at a time.

Now, connect to that database with psql and drop the constraint of the id field. Basically, the idea here is to change it from a SERIAL field to a simple INTEGER field. The de-serialization drops the field's ability to be primary key in postgreSQL.

Monday, April 28, 2014

I have some tables that exist in another database (which is also remote). Instead of doing cross-database queries, which a lot of RDBMS do not allow with remote queries, let's just copy the tables out into a CSV and then copy them back in.

I know there are a million ways to do this, but I'm going to use psql because its pretty easy and fast. If you have a better way, please feel free to clue me in.

First, boot up psql (I just type psql into windows run), then we log in using our credentials

Obviously, in both the \COPY statements you can omit the WITH CSV HEADER statement. However, I like to have it so I know what the hell the table looks like in case I need to investigate the .csv, which I often do.

Great! That actually took about a minute. But regardless, this is not such a bad way to handle cross database table copying.

Sunday, April 27, 2014

If you are connecting to a remote database, create the database with your remote host first. Go to the cpanel or whatever you have, and add a new PostgreSQL database (In this example, I will be using PostgreSQL). Every hosting account is probably different, so I'm not going to bother with the specifics.

Added the database “rebates”.

I like to also use a database management tool locally. So, at this stage, I like to add my IP address so that I can connect locally to my remote database server. (This is fairly standard for authentication with most hosting sites, I believe). Generally, you will need a specific username, password, and database at this point for authentication purposes.

Added: rebates with user admin from XX.XXX.XXX.0/24

Now, locally I open up my database management tool (I'm using SQL Manager Lite for PostgreSQL) and register my newly created database.

Then, simply connect to the newly registered database. Success!

Great-- but that has nothing to do with our django app. Let's configure the database in the settings.py, which exists in the project directory file. Our project is called "rebates". Inside the "rebates" directory, there is a project specific directory (automatically created by django when you start the project),which also has the same name of "rebates". Inside this directory, you will find your settings.py file. I realize this is a little confusing.

Time to create our database model! So, I already have an idea of how I want to structure my first test table, which is called zipcodes. As you may be able to guess, its a table of zipcodes and other associated info. I already have the data in another database. For the sake of my own sanity, I'm just going to copy the CREATE statement here, so I can translate it into the django database model language, as needed.

Create a superuser if prompted, and viola! You have just synced the database. Django will create a whole slew of tables which are very useful, but I won't explain in this post.

What we care about right now is the zipcodes table. Refresh your local rebates database inside your SQL Manager and lets see how we did. Let's script out a CREATE TABLE statement and view how our model in Django created the table in PostgreSQL