Voice of the DBA

Author: way0utwest

Python and Tweepy
One of the projects that’s been on my list lately is to programmatically access Twitter for a few ideas I want to play with. Since I’ve been trying to learn some Python, I thought I would take a look using Python to update status and read status.

A quick Google search showed me lots of Python clients, but Tweepy caught my eye for some reason. I’m not sure why, but I ended up popping a command line open and downloading the library.

From there, I saw a short tutorial over at Python Central. I started by creating an app at Twitter for myself, which was very simple. Once that was done, I had a set of consumer tokens (key and secret), that I could use. Another click got me to the access key and secret. Note, the easy way to do this is over at dev.twitter.com.

My first attempt was using this sample code.
```
import tweepy
```
consumer_key = “ABCDE”
consumer_secret = “12345”
access_token = ‘asdfasdf’
access_token_secret = ‘98765’

auth = tweepy.OAuthHandler(consumer_key, consumer_secret)
auth.set_access_token(access_token, access_token_secret)

api = tweepy.API(auth)

public_tweets = api.user_timeline()
for tweet in public_tweets:
print(tweet.text)

This gives me a list of my tweets. At least, the last 20.

That’s progress.

I then went to make an update by using this:
```
api.update_status('Hello, Pyton')
```
However that returned an error:

Hmmm, I double checked the API, and this should work, but I’m guessing there’s another issue. I searched, and found there’s a bug. However, I should be using named parameters, so no big deal. Change the code.
```
api.update_status(status='Hello, Pyton')
```
Now it works.

This is the first step for me to look at building an app that might send some tweets on my behalf, perhaps with the data stored somewhere, like, I don’t know, maybe a database?
November 20, 2015
Shrinking the Budget

Most of us rarely have to build or manage budgets in our organizations, but almost all of us are affected by the budget process. It’s tempting to ignore budgets and just do your job, but sooner or later you might find yourself arguing for more funding for a project, training, or even hardware.

I ran across a short piece on 10 ways to shrink your IT budget and found it a bit scary. I would bet that many of you have had conversations, or been affected by decisions, that follow some of the advice in the piece. The push for open source, virtualization, hosted (or cloud) migrations, or more can cause stress and anxiety when your job is making sure the servers run without complaints from end users.

I don’t expect that all of these would be followed in a specific organization, but some of these might be proposed to you. What I would recommend is you understand the reasons why, or why not, you might adopt any of these ideas. Certainly the time lost from retraining people or rewriting code can overwhelm the savings for many years, but even seemingly smaller changes, like changing priorities can affect the way your clients and customers view IT. It seems that sometimes budgets get changed to provide a short term view that technology spending is being managed efficiently, only to find out later we will remove all our savings by undoing a poor decision.

I’d suggest that you approach budget issues with transparency and honesty. Take a hard look at your costs and determine what items are really needed, and what items aren’t. However, I’d also urge you to carefully consider whether it’s really valuable to save money by not taking care of your staff. While labor is an expensive part of your IT cost, good staff are worth much more than they cost, often by an order of magnitude.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.0MB) podcast or subscribe to the feed at iTunes and LibSyn.

November 19, 2015
Viewing Extended Properties for Information
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as#SQLNewBloggers.

I’ve been working a little with extended properties, adding and updating them for various objects. However in addition to altering properties, viewing the properties on an object is often necessary. This post will look at how we get the properties in a few different ways.

The easiest way to see extended properties is to look at the properties of an object in the SSMS Object Explorer. For example, I can right click on a table in OE.

Once I click Properties, I get a dialog with a lot of items on the left. The bottom one is for Extended Properties, with a simple add/edit/delete grid. Here I can see the property(ies) I’ve added.

However this is cumbersome for me. I’d much rather find a way to query the information, which is what I need to do with an application of some sort. I’d think sp_help would work, but it doesn’t. If I run this, I get the following result sets:
- header with owner, type, and creation date.
- column list with meta data
- identity property information.
- RowGuid column information
- filegroup storage location.
- Messages with index, constraint, FK, and schemabinding relations.
Not very helpful in this case.

I do know that extended property information is in sys.extended_properties. I can query this view, which gives me some information, but I need to join this with sys.objects for easy to understand information.

This works, and this is one of the ways in which I do query properties in various tSQLt tests.

There is one other way I’ve seen to query extended properties. When perusing the BOL page for sp_updateextendedproperty, I found sys.fn_listextendedpropery. This is a DMF, a function, that you can use to query for property values. Since it’s a TVF function, I need to use it in a query as a functional object.

There are lots of parameters in this function. However you can guess what they are after working with the other extended property procedures. In fact, the first time I started this post, I was disconnected and had to experiment with the function, adding parameters until it ran without an error.

The first parameter is the name of the property. This can be NULL, in which case you’ll get all the properties that exist.

The rest of the properties correspond to the level 0, 1, 2 types and names that you are using to filter the results. This is actually a good technique to use with this function, and I’ll be using this more in the future.

SQLNewBlogger

This post followed on from the previous ones. In this case, I started this disconnected, using the knowledge I had to write the basics with SSMS and the system table. That took about 20 minutes to document and then I spent 5 minutes experimenting with the function, whose name I had on an open browser tab. Once I worked through that, I spent another 5 minutes writing.

Thirty minutes to a post. You can do this.

Reference

A few items from BOL:

sp_help – https://msdn.microsoft.com/en-us/library/ms187335.aspx

sys.extended_properties – https://msdn.microsoft.com/en-us/library/ms177541.aspx

sys.fn_listextendedproperty – https://msdn.microsoft.com/en-us/library/ms179853.aspx
November 18, 2015
Relational By Default

I’ve read a few pieces from Vladimir Khorikov and I tend to like the thoughtful way in which he approaches building software. When I saw a link to his SQL v NoSQL databases, I was intrigued to see what his thoughts would be. It’s a good read, though with relatively few comments or debates posted at the time I read the article. I was hoping for more and I encourage you to add your thoughts.

The main contentions in the piece are that any of the simpler development techniques that we can use with NoSQL databases don’t remove the need for implementing data quality or management features. They just require those features to be implemented by the developer. Specifically the need to deal with multiple schema versions over time, or the requirement we enforce parent child relationships.

Perhaps that’s not too bad. After all, if we evolve schemas and the code can easily deal with the changes, that’s good. However I think the point about having relational storage when you can, and adding in other stores makes sense. This is what I really believe as well, since we often need to query and combine data, which is something relational stores do very well and very efficiently.

Ultimately I’m sure we will continue to see arguments about relational and NoSQL models being better or worse for particular problems. I actually welcome the arguments because there are certainly domains of problems where one system works better than the other, and it’s good to develop some clarity about those particular problems. However there are also likely many situations where multiple platforms can work, and in those cases, we should use what works well for our team.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 1.9MB) podcast or subscribe to the feed at iTunes and LibSyn.

November 18, 2015