Custom queries II: SQLite

Let's see how to connect to an SQLite database from Python. The current version is SQLite3.
Open a Python file, and import the library:
import sqlite3

Now, open a connection to the database. If the .db file already exists, connection to that file is opened, else a file of that name is created and connection is opened.
conn = sqlite3.connect ("/foo/bar/filename.db")

For executing queries, we need a cursor. So let's initialize that:
cur = conn.cursor ()

Now let's write a query.
cur.execute ("select something from some_table")

You can directly pass the string as a parameter, but for more customisations, you should initialize it separately and pass it as follows:
query = "select foo from bar"
cur.execute (query)

If you want to insert data dynamically into your query, the following is the syntax:
THIS DOES NOT WORK:
query = "select empid from employee where name = '%s'" % name
cur.execute (query)

THIS SHOULD BE DONE:
query = "select empid from employee where name = ?"
data = (name,)
cur.execute (query)

Note that data has to be a list or tuple. The syntax shown forces a single element to be a tuple. However, if there are multiple elements, just list them out normally like this:
query = "select empid, dept from employee where name = ? and salary = ?"
data = (name, sal)
cur.execute (query)

No data needs to be passed for insert and update queries, so just write those normally.
For delete, pass data in the same way as shown.
Now we need to fetch the data we get from select statement. Here's how you do it (assume I executed the previous query):
for row in cur:
    print row[0], row[1]

We selected two values here, namely empid and dept. So row[0] and row[1] will hold empid and dept respectively. This can be extended for several values.

Once you're done, you should commit the changes and close the connection.
conn.commit ()
conn.close ()

In the next article, we'll see how to handle a MySQL database in Python.
Cheers!

Custom queries I: Comparison between SQLite and MySQL

I've already said in earlier posts that you can use SQLite or MySQL as a database for your website. Recently when I was working on one I had to move to MySQL (I'll tell you why later in this article). So I thought I'll tell you guys how to transition.
But first, let's compare.

SQLite:

• It is a lightweight DBMS
• It is mainly used for testing and for small websites that do not generate too much data.
• The biggest advantage is that it handles all the data through a .db file which can be transported elsewhere and used in the same way
• It supports up to 2^64 rows (wow!)
• On the downside, for the kind of data that giants like Facebook and Google generate, SQLite falls laughably short
• It also has low concurrency control, which means that it crashes if too many people are trying to access the same data

The last point, the concurrency control, was the main reason I decided to switch to MySQL. The database of the website was crashing again and again. Let's look at MySQL now.

MySQL:

• It is tough, inflexible and an extremely powerful DBMS
• It is used by Facebook, Google, Twitter, pretty much all the web giants that exist today
• It has very high concurrency control
• It has the potential to store and handle an unlimited amount of data
• It has quick crash recovery
• There is very low chance of deadlocks
• All in all, if you plan to build a large site this is the DBMS for you
• The only downside is that it does not generate any file as earlier, so there has to be one native machine running the DBMS

This article is just the first of three of the series Custom queries. The reason I'm writing these three is that the query API that Django provides is good, but it does not allow custom queries (as far as my knowledge goes). For example, you want to customize search parameters and find all people that satisfy multiple conditions. To do this, Python's database API is very useful and can be used very well in Django.

In the next article, we'll see how to write custom queries for SQLite.

Cheers!

Sending emails

If you've noticed, on many sites, once you sign up, it sends you an automated confirmation email. This is another function your site should be able to perform. So today let's take a look at that.
Let's assume you have a Gmail account (if you don't, create one today, it's great!). We'll be using Gmail's email host (wow that rhymed).
Go to the settings.py file and add the following code anywhere in between.

EMAIL_USE_TLS = True
EMAIL_HOST = "smtp.gmail.com"
EMAIL_HOST_USER = "your_name@example.com"
EMAIL_HOST_PASSWORD = "your_password"
EMAIL_PORT = 587

The port number 587 is pretty standard. Also, it is very important to set TLS to True. We'll go into why later.
You have to enter your Gmail password there, but I assure you it is secure. Only the programmer(s) can see it. You may also create another common account and use those credentials instead.
Ok, now we'll need to write the Python code to send emails.
Open the views.py file in the appropriate app and write the following code:

At the top, import the Django's send_mail:
from django.core.mail import send_mail

Also import settings.py:
from django.conf import settings

Later, create this function:
def foo (request):
    title = "some title"
    message = "some message"
    host = settings.EMAIL_HOST_USER
    receiver = ["someone@example.com"]
    send_mail (title, message, host, receiver, fail_silently = False)
    return HttpResponse ("Mail sent successfully")

Some important things to note here:

• You can directly write send_mail () and pass all the above values as arguments. I just wrote them separately so it's easier to understand.
• receiver variable has to be a list or tuple, as Django supports sending emails to multiple people (a.k.a. multicasting).
• fail_silently = False enables you to see what error was generated in case the email wasn't successfully sent. You can then use error handling to do stuff for each error. Or if the mail you're sending is not important (or an advertisement, 'cause people hate that), you can set it to True and set your mind at ease.

That's all for now! I just covered the basics here, there's a lot you can do with exceptions, responses etc. If you have questions, or are getting some error you can't identify the solution for, please feel free to ask in the comments.

Cheers!

Github

Hey guys! For those of you who don't know, Github is a code sharing site on which you can review and edit other people's code and post your own. I have shown you a lot of code on this blog. Some of the code like file upload, login sessions etc is the same and can be reused. So I'm going to post such pieces of code on Github for you guys to review and edit. Please do so, I would be very happy if someone improved upon the code I've written.
Also, my favorite language is Python and I do a lot of other things like software development, hacking in it. I've posted repos for these also. Please feel free to review and edit those too.

Here's the link:

https://github.com/AgentK1729

Cheers!

File upload

One of the most common functions your website should be able to do is to upload/download pictures, PDFs and other files. In this tutorial I will show you how to create an upload script and save the files to server.
If you recall, I already showed you how to host your site on a free server. Now there are two parts to uploading files:

1. Creating an upload script post which the file gets stored on either the server or temporary storage on your computer depending on which has more space.
2. Copying the file from temporary storage to a permanent destination.

Let's take care of this in two parts.

Part I: Creating an upload script

Ok, first we need to create an HTML form. In a file called upload.html, type the following code.

<form action = "/uploaded/" method = "post" enctype="multipart/form-data">

Select an image: <input type = "file" name = "img"><br><br>

<input type = "submit" name = "upload" value = "Upload picture">

</form>

The enctype="multipart/form-data" is extremely important, as it specifies the encryption type of the temp file. With this done, when the 'upload' button is pressed, the file gets stored on temp storage. To render the upload page, write a simple view.

def upload (request):

    return render (request, "upload.html")

The view that we need to write for uploaded is important. But that is in part two.

Part II: Copying file to permanent storage

Now that we have the file, let's copy it to the server. We'll do this in the uploaded view.

def uploaded (request):

  file = request.FILES['img']

  dest = open ("/foo/bar/image_name.jpeg", "w")

for bit in file.read ():

dest.write (bit)

return HttpResponse ("Uploaded")

Note that the extensions of both the files should be same. We can access the temp file through request.FILES. We're reading each unit of the temp file and putting it into our permanent one.

Easy enough, right?
Don't forget to add the URLs in ulrs.py

url (r'^upload/', 'myproject.myapp.views.upload'),
url(r'^uploaded/', 'myproject.myapp.views.uploaded'),

Cheers!

Session management: Login and logout using cookies

Hey! I know I've not posted for a long time. Truthfully, there was not much new. But there is now, so here I am! Today we'll see how to manage login and logout sessions. If you've noticed when you're on Facebook, if you accidentally close the window, reopen it and go to facebook.com, your homepage is displayed. This is because your browser has received cookies from Facebook and you're still logged in in their database.
For those who don't know, cookies are small packets of information that the user's browser stores in its cache. We'll see how to manage them in Django.

I assume you're familiar with how to use dictionaries in Python. So to create a cookie, simply do the following:

request.session[key] = value

request.session is a dictionary where all cookie data gets stored. For example,

request.session['username'] = 'someUser'

This will store the username as someUser in the browser. Do this when the user logs in. This value can be accessed many views and apps later.

In case the cookie is not erased while logging out then the next time the user opens the site he will stil be logged in.

If no one is logged in and you try to access request.session['username'], it raises a KeyError. You can use this with exception handling to check if anyone is logged into your site.

try:

    username = request.session['username']

    return HttpResponse (username)

except KeyError:

    return HttpRepsonse ("No one is logged in")

On log out, delete the cookies.
del request.session ['username']

Like this, delete all the relevant ones.
Cheers!

Deploying a developmental website

Hey guys! I know I'm posting after a long time. Between semester projects and some of my own I almost forgot about the blog. My bad.
So in this article we'll see how to host your site on a free server. There are three advantages of doing this:

1. Whoever you're working with can see the progress in real time, no matter where they're physically located.
2. You would be editing the code on the server host website instead of your machine, which gives you the freedom to work on it from anywhere with an Internet connection.
3. You need to add libraries to Django to, say, send emails, serve static files like images ('img src' doesn't work straightaway in Django) etc. The server host website will come preloaded with all these, and will constantly update itself, so you won't have to worry about it.

So let's get started. I host it on pythonanywhere.com, where it is mundanely easy. Sign up on it. There's a normal signup procedure, verification email etc. Once you're done, log in. Now you'll see several tabs: Consoles, Files, Web, Schedules, and Databases. Go to the 'Consoles' tab.

Over there, click on Bash console. In it, do the normal startproject and startapp procedure explained earlier in this tutorial.

Once done, go to the 'Files' tab and you'll see all your project files. Now copy code from whatever files you have on your machine to the respective files on the server. Alternatively, you can go to the 'Files' tab and directly upload your entire project.

Then go the 'Web' tab and click on 'Reload username.pythonanywhere.com' and your site is live!

Go back to the 'Files' tab. When you click on a particular file, the contents will be displayed in the code editor. Make your changes. Note that whenever you make any changes you have to reload the site. There's a button at the top of the code editor or you can go to the 'Web' tab as earlier.

That's all for now!

Cheers!