Blog
SQLAlchemy and Stored Procedures
I’m so excited that I solved this problem, I had to blog about it. I couldn’t find anything online to address this specific problem: Getting stored procedure results in key-value format using SQLAlchemy.
I have a stored procedure that does a SELECT query, and requires a few parameters. Let’s call my stored procedure get_my_data. And it requires three params, userid, url, and date.
Looking at the SQLAlchemy docs, I found this page regarding stored procedures. So I started there.
# My function has a db session, so I had to get the cursor from there
connection = session.connection()
# In order to get the cursor, you must get the connection's connection, which seems insane, but let's go with it.
dbapi_conn = connection.connection
# Finally get the cursor and call the stored procedure, as recommended by the SQLAlchemy docs.
cursor = dbapi_conn.cursor()
cursor.callproc('get_my_data', [userid, url, date])
# Get a list of rows, without the column names
results = cursor.fetchall()
# Clean everything up
cursor.close()
dbapi_conn.close()
# Return the db rows
return results
The problem was with the format of the results. I got a list, and each item in the list was another list of the values. The column names were nowhere to be found. A list of values without keys wasn’t going to help the consumers of this endpoint! So at first I tried to iterate over the values in each row, and create a dict. This was based on the index of the value in the row, which I soon found out wasn’t maintainable if the columns returned changed. Plus, it didn’t feel right. I knew there must be some way to get the column names out of the query without hard-coding which column was at which index.
After some more searching, I found that the cursor has a description property. I could use that to find the column names. Add some Python to the mix, and I had a my list of rows. Each row is now a dict with the correct column name as the key.
# My function has a SQLAlchemy session, so I had to get the cursor from there
connection = session.connection()
# In order to get the cursor, you must get the connection's connection, which seems insane, but let's go with it.
dbapi_conn = connection.connection
# Finally get the cursor and call the stored procedure, as recommended by the SQLAlchemy docs.
cursor = dbapi_conn.cursor()
cursor.callproc('get_my_data', [userid, url, date])
# Get a list of rows, without the column names
results = cursor.fetchall()
# Get the column names for the results
indices = [item[0] for item in cursor.description]
# Create a list of mapped column names to results
results_list = [dict(zip(indices, result)) for result in results]
# Clean everything up
cursor.close()
dbapi_conn.close()
# Return the db rows
return results_list
Geeks Girls Carrots Mentoring
One of my fave meetup groups has always been Geek Girls Carrots (not to be confused with Girl Geek Dinners, another awesome networking/professional development group with equally awesome food and locations). Last spring, Geek Girls Carrots led the second of the Code Carrots courses. Ladies with any level of programming experience come together, form groups, pick an “app” to develop, decide which stack to use, follow an “agile” schedule of week-long sprints, and work with a mentor to advise on all of the above. I was lucky enough to be one of those mentors to a group of four ladies working on a Django project related to reporting sexual harassment in the workplace.
The main challenge here was time. We all worked full-time, and met after work once or twice a week. There was rarely a week when all group members could meet in person at the same time. I set up a Slack account for us to use, and it really helped when one or two group members were remote. I was also a git/github resource – such a tricky technology to novice programmers to get used to. It’s hard to keep the cognitive load low enough for actual learning – trying to use git correctly, not “break everything”, get code to work, and write code well at the same time (as any developer knows, even when years into their career).
In fact, on the first day of the project, I was called on to do an impromptu intro class to using git and github to the whole group of about twenty women. Let me clarify – I had zero idea I was going to do this until the organizers said, “Jessica! You know github, right?”. It was really challenging, but really fun, and reminded me of what I enjoyed about teaching. By the end of the class, everyone was able to clone a repo and contribute to it. Success!
After eight weeks, all the groups presented their projects. All had a working prototype, and many had one hosted online, like Heroku. It was a great experience that I hope they repeat soon (nudge, nudge).
Volunteering at Ada Developers Academy
One of my goals during my career transition to tech was to be a mentor and role model for other women who wanted to transition to a better career. Luckily, I’ve been able to do that formally in two wonderful contexts (see my post on Geek Girls Carrots Code Carrots 2.0 mentoring), and informally in dozens more.
Currently, I have the privilege of being able to TA with Ada Developers Academy. If you’re not familiar with Ada, here is the info.
I spend five hours every other week helping new developers on their coding projects. Depending on the stage of the cohort, it could be working through nested loops in Rails or trying to figure out how to stub an API response in a new testing framework, or even trying to plan a long-term capstone project. I even recently taught a fellow volunteer how to write his first React.js app in our down time. Frequently, I’m a live rubber duck for students to work through problems out loud. Most importantly, I’m an empathetic ear who has definitely been through these same frustrations and come out a better developer in the end.
Teaching is an Agile Process
If you’re like me, you get a special satisfaction out of pronouncing “processes” like “processEEs”. In that case, you are awesome.
Yes, it is late, and yes, I spent all day trying to incorporate a Google places details search over my Google nearby places search while learning about Casper.js testing, modularizing and objectifying (sorry, couldn’t help myself there) code, in addition to using the Google autocomplete function after sprinkling on some regex to validate user input although I still want to go in and run all input through some escaping a la OWASP recommendations but let’s face it, I need some metaphors, and teaching and programming are a great combination.
So, back to my topic: teaching is an agile process. Let’s take a look at agile and what that entails:
Quick turnaround. Instead of creating a finished, “perfect” product a long time from now, create the project in “sprints” (stages) and check in with stakeholders after each stage. That way, it’s easy to incorporate feedback before the next step.
It feels unnecessary to even explain how that’s like teaching, but let me spell it out anyway. In teaching, you must teach a class at a certain time. If you don’t have a “perfect” lesson planned (and you never do – that’s the dangling carrot that keeps you up at night and working on the 4th of July), you must teach anyway. And your “stakeholders” will give you feedback immediately (both non-verbally and verbally, not to mention on assignments and tests later on) on how lacking your lesson planning is and where to improve.
In agile, you can make small changes immediately in response to feedback. You might even adjust features and perhaps alter your overall course.
In teaching, if your class is feeling lackluster, you can change your plan in the moment to a different activity. Not only can you, but you must. Or, if your class is not following your carefully-planned content, you must change your plan accordingly. You cannot wait two months. You must do it now before Jim falls asleep, Sally starts texting, and Bob asks an off-topic question. If you realize the next day that what you had planned to to on Thursday is not what the students actually need, you must change your goals for the week.
Although maybe it’s not specific to agile, test-driven development is a big deal (again, it’s late, forgive my lacking vocabulary). This means that you write tests and expectations, followed by actually writing code. This is an awesome concept that mirrors teaching.
In education, assessment is all the rage. And with good reason – there must be some way to “measure” the black box of the human mind and learning. Yes, I will continue to use “” with “measure” because obviously there’s NO way to measure learning empirically, but we do our best to get a rough estimate, notwithstanding affective filters, investment, hunger, temperature, relationships, money, daydreaming, rectangle-gazing, parents, children, landlords, boredom, depression, cold, rain, snow, sun, lightening, dirty classrooms, raccoons, religion, gender, clothing, food, bedbugs… yes, these are all things that affect student learning and assessment accuracy. And yes, these are all things that have distracted students in my classroom. I’m especially proud of the raccoon.
Finally, in agile your client is not always aware of what it is they need and what exactly you do in order to produce their result. Same in teaching – when students complain of one hour of homework, they don’t realize the dozens of hours you will spend grading said homework. Also, students often think they want to do one thing (watch a movie, do fewer presentations, take more multiple choice tests) when in reality a teacher knows a better way to get them to their goal. And when students endure an activity or lecture, there’s little realization of the time and effort put into preparing that.
I’m certain there are more parallels to be drawn, but for now there are more Google APIs to be discovered.
RESTful API – Hold on to your mongeese!
Requirements: Node with express and mongoose installed. MondoDB installed globally. A lot of patience.
First, make sure you can start your mongo database with commands like mongod and mongo. You can interact with the database directly from the command line. Or try this website that is a mongoose playground. http://try.mongodb.org/
Next, set up your root directory, complete with node modules and package.json files.
Then, set up the node server to work with the mongodb using mongoose.
You will need some schemas that dictate what types of data are going into your json objects.
Create functions for POST, PUT, GET, DELETE.
Use jquery in the console to try out your CRUD system.
You can always check out your database via command line: db.<yourdb>.find()[0]; can find and prettify the first item in your database, for example.
See this tutorial http://pixelhandler.com/blog/2012/02/09/develop-a-restful-api-using-node-js-with-express-and-mongoose/ for a nice, more complex than a todo list, example. You will have to fill in some blanks for yourself: check out the mongoose documentation.
In order to hook up your api to an actual web site/app with an interface, just connect your site to the api (will be doing in an upcoming post).
Please see and fork and clone my example repo: https://github.com/jwicksnin/restful_tutorial
Why Did You Leave Teaching for Web Development?
This is a question I’ve gotten asked a lot in my quest to change careers. If you’ve ever worked in education, especially as a teacher, you no doubt already have some ideas about what my answer will be. If you haven’t, here’s the explanation:
First, I decided I needed to leave teaching. I was looking for something that involved less direct “customer contact”, was more financially viable for a single person in Seattle, and was related to tech (a growing field with endless things to learn about). I wasn’t sure if I wanted to go into instructional design, engineering, project management, or technical writing, so I took some time to explore.
After interviewing career advisors, multiple tech-related departments at UW, taking informal Web Design courses, and talking to my friends in various careers, I decided to take the intro CSE courses at UW (starting with Java) while continuing to teach. Not only did I pass said course, I found it wonderfully challenging and relevant. I took another, this time in web programming, and I loved it. I felt powerful. I felt like I actually accomplished something. I felt like I was learning and enjoying working with others. Programming is about language just as much as teaching ESL. Syntax, semantics, vocabulary, grammar. They are all related. I’m just happy I didn’t wait any longer than I did to make the switch.
Ulterior Motives
It’s funny. You never hear the word “ulterior” unless it’s collocated (associated) with “motives”. I guess it means “hidden”, “not obvious” or even “devious”? You hear a lot about exterior and interior, but how “ulterior” fits in is not entirely clear.
Anyway, you may have heard the expression, “There’s no such thing as a free lunch.” Regardless of the fact that this was coined somewhere around 1930 when indeed, free lunches were hard to come by, in the tech community in Seattle you might change it to “There’s no such thing as a free beer.” But actually, this week, I have indeed enjoyed both free beer and a free lunch. Oh, and a free JavaScript tutorial from an actual human being!
On Monday, I joined the “Learn JavaScript the Right Way” meetup in Ballard to go over homework assignments. One highlight was drinking hot chocolate in a tea shop. Another highlight was breaking Code Academy via an infinite loop in one of their JavaScript exercises. Surprising, because I’m sure I’m not the first person to write an infinite loop on Code Academy. A further highlight was the helpful spirit and community feel of the meetup. We had one “expert” who happily answered all our silly (and not so silly) questions. We shared resources, and I’m really looking forward to next week!
On Tuesday, it was South Lake Union for Code Fellows’ open house. You’ve no doubt heard of these coding “bootcamps” and have strong opinions about them. Let’s skip that. I mostly wanted to see what the place was like, how it compared to another for-profit school (that may or may not contain the letters “ITT” in the name), and get the aforementioned free beer. Check, check, and check. Way more legit than the unnamed school (I used to work there so I guess I’d know), and totally relevant to Seattle’s job market. Wow, I’m kind of talking myself into applying as I write this. If anyone would like to “sponsor” me, I can promise you free access to my blog posts for life.
On Wednesday, it was to “Impact HUB” (not to be confused with The Hub at UW), located on the block in Seattle that receive more 911 reports than any other single street. Awesome. Despite subtly closed blinds and muffled homeless yelling across the street, we enjoyed an 8+ hour workshop on using PhoneGap, Apigee, and Codiqa to create mobile apps using HTML, CSS, and jQuery (and jQuery mobile). And that’s where the free lunch comes in. I got much more comfortable with the command line, learned more about jQuery mobile (although I’m not sure how much of a “thing” it really is), and am psyched to see Worry Wart in an app store near you soon.
So I’m sorry to say that I am very cynical and fully expected a “pitch” at both Tuesday’s and Wednesday’s events. I even had my cynical face on, which I’m making at this very moment. Since I don’t want to scare you away, I’ll just pretend that this Mac doesn’t have a camera built in right now. Luckily, I was proven wrong, and on top of free beer and a free lunch, I met some cool people and learned some cool things. And I’ll stop there because based on that last sentence, I’m running out of adjectives.
Obligatory New Year’s Post
What I’m most excited for this year:
Another great year in a great house with a great dog and a host of great friends and family coming and going. Here’s to wassail!
Learning even more about coding and web development: currently taking a Udacity Mobile Web course online and a Python course through Girl Develop It (who are awesome) “irl”. I can never decide if using “irl” makes me sound like I’m stuck somewhere in ICQ circa 1998 or like I’m an awesome text-savvy social media user not a day over 25. Hmmmm…
Ramping up career from intern/learner/networker/experimenter to full-time-employee/learner/networker/experimenter. Hint, hint.
And in case you were wondering how long I would go with just “the girl the dog”, I am finally being well-distracted from my increasing nerdiness on weekends by, you guessed, it, “the boyfriend”(!!!). So that is one other, non-code-related item that turns out to make everything in this new year just that much brighter.
Happy New Year!
Santa Claus is Coming to Town
Christmas is over. So why haven’t I taken down my extraordinary (pink) Christmas tree? If the preceeding question doesn’t answer itself, then we have nothing left to talk about.
But I wanted to explain (read: brag about) my awesome side projects (Christmas presents) that were done in addition to completing a quarter at school and setting up an entirely new website at the internship (to go live sometime this month!).
First challenge: A “Worry Wart” website for Mom/every woman in the world to store worries for them. This needed a database, tables interacting with each other, a somewhat secure way to access, read, and write to the database, login, log out, create a new user, store session variables, count worries that came true vs worries that never happened, add and delete worries. Whew! I think that might be about it.
If you’re interested, check out worrywart.jessicawicksnin.com. If you’re really interested, check out my code on git hub (jwicksnin).
Second challenge: A “Benjamin Speaks” app for Dad/every retirement-savvy person to aid them in financial decision making. This took CSS to create a neat shadowed “quote bubble” and awesome jQuery to take a submitted question, find a suitable (random) answer in a text file, and output the random answer. Nothing too fancy, but the image of a $100 bill that I gleaned from Google addressing my dad directly is really cute.
Third challenge: A “Facebook for Two” for my boyfriend/every couple who too busy actually having a relationship “IRL” to document every stage of their relationship on actual Facebook. This took some serious JavaScript because a text file wasn’t good enough – I was determined to use the word AJAX as much as possible. I ended up using a quick PHP file to write from JavaScript to the existing XML file on the server. So now I feel very comfortable reading and writing XML files based on user input via AJAX requests. It’s basically a poorly-styled Facebook, which made me question the value of Facebook at $100,000(?) a share if I can build the same thing in a couple weeks with nothing but Google and Web Programming Step by Step (thank you CSE at UW) for help.
Life Without Plugins
Here are some things I heart doing in WordPress:
Creating new custom post types without a plugin
Creating custom fields (i.e. meta boxes) without a plugin
Creating widgets without a plugin
Editing post types and widgets without a plugin
Importing styles without a plugin
Hmmm.. do we see a pattern emerging? Somewhere in the past month or so I think I may have made friends with WordPress, because lately, instead of hunting through files for hours looking for one certain variable, I’m able to blow through a “To Do” list and continue to be amazed when my first or second attempt simply works.
And I’ve decided that I definitely belong to the “less is more” camp when it comes to plugins. Especially since we have multiple users and developers and who knows who doing who knows what on the admin side, keeping plugins like Custom Post Type at a minimum is essential to make sure that changes to the functionality of the site are only done by someone relatively qualified. As we learned on the current (old) site, even something simple like slug, category, and page names can wreak havoc on something as major as permalinks and redirects. Isn’t that the point of WordPress, to make content easy to add and change without having to affect code?
Here are some awesome things I’ve done recently sans plugin:
- Sort posts alphabetically and by custom field (meta) value
- Create new custom post types loosely based on existing custom post types
- Trick WordPress into not pre-prending the entire blog url to an inputed url (http://www.google.com as opposed to http://www.myblog.com/category/www.google.com)
- Even though the current theme relies on an icon library linked to CSS rules like “content: /e701”; to display sociable icons (instead of using separate icon files), maintain the styles for the icon library while using my own sociable icon not included in the library. Sort of hacky, totally awesome. This took CSS, PHP, WordPress, and icon library know-how.
- Formatting widgets in the admin side and how they are displayed on the live site.
Recent Comments