Connecting to MYSQL with Python 2 in three steps

1 - Setting

You must install a MySQL driver before doing anything. Unlike PHP, Only the SQLite driver is installed by default with Python. The most used package to do so is MySQLdb but it's hard to install it using easy_install. Please note MySQLdb only supports Python 2.

For Windows user, you can get an exe of MySQLdb.

For Linux, this is a casual package (python-mysqldb). (You can use sudo apt-get install python-mysqldb (for debian based distros), yum install MySQL-python (for rpm-based), or dnf install python-mysql (for modern fedora distro) in command line to download.)

For Mac, you can install MySQLdb using Macport.

2 - Usage

After installing, Reboot. This is not mandatory, But it will prevent me from answering 3 or 4 other questions in this post if something goes wrong. So please reboot.

Then it is just like using any other package :

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect(host="localhost",    # your host, usually localhost
                     user="john",         # your username
                     passwd="megajonhy",  # your password
                     db="jonhydb")        # name of the data base

# you must create a Cursor object. It will let
#  you execute all the queries you need
cur = db.cursor()

# Use all the SQL you like
cur.execute("SELECT * FROM YOUR_TABLE_NAME")

# print all the first cell of all the rows
for row in cur.fetchall():
    print row[0]

db.close()

Of course, there are thousand of possibilities and options; this is a very basic example. You will have to look at the documentation. A good starting point.

3 - More advanced usage

Once you know how it works, You may want to use an ORM to avoid writing SQL manually and manipulate your tables as they were Python objects. The most famous ORM in the Python community is SQLAlchemy.

I strongly advise you to use it: your life is going to be much easier.

I recently discovered another jewel in the Python world: peewee. It's a very lite ORM, really easy and fast to setup then use. It makes my day for small projects or stand alone apps, Where using big tools like SQLAlchemy or Django is overkill :

import peewee
from peewee import *

db = MySQLDatabase('jonhydb', user='john', passwd='megajonhy')

class Book(peewee.Model):
    author = peewee.CharField()
    title = peewee.TextField()

    class Meta:
        database = db

Book.create_table()
book = Book(author="me", title='Peewee is cool')
book.save()
for book in Book.filter(author="me"):
    print book.title

This example works out of the box. Nothing other than having peewee (pip install peewee) is required.

What is the cause?

Bottom Line

You are trying to use something that is null (or Nothing in VB.NET). This means you either set it to null, or you never set it to anything at all.

Like anything else, null gets passed around. If it is null in method "A", it could be that method "B" passed a null to method "A".

null can have different meanings:

1. Object variables that are uninitialized and hence point to nothing. In this case, if you access members of such objects, it causes a NullReferenceException.
2. The developer is using null intentionally to indicate there is no meaningful value available. Note that C# has the concept of nullable datatypes for variables (like database tables can have nullable fields) - you can assign null to them to indicate there is no value stored in it, for example int? a = null; (which is a shortcut for Nullable<int> a = null;) where the question mark indicates it is allowed to store null in variable a. You can check that either with if (a.HasValue) {...} or with if (a==null) {...}. Nullable variables, like a this example, allow to access the value via a.Value explicitly, or just as normal via a.
   Note that accessing it via a.Value throws an InvalidOperationException instead of a NullReferenceException if a is null - you should do the check beforehand, i.e. if you have another non-nullable variable int b; then you should do assignments like if (a.HasValue) { b = a.Value; } or shorter if (a != null) { b = a; }.

The rest of this article goes into more detail and shows mistakes that many programmers often make which can lead to a NullReferenceException.

More Specifically

The runtime throwing a NullReferenceException always means the same thing: you are trying to use a reference, and the reference is not initialized (or it was once initialized, but is no longer initialized).

This means the reference is null, and you cannot access members (such as methods) through a null reference. The simplest case:

string foo = null;
foo.ToUpper();

This will throw a NullReferenceException at the second line because you can't call the instance method ToUpper() on a string reference pointing to null.

Debugging

How do you find the source of a NullReferenceException? Apart from looking at the exception itself, which will be thrown exactly at the location where it occurs, the general rules of debugging in Visual Studio apply: place strategic breakpoints and inspect your variables, either by hovering the mouse over their names, opening a (Quick)Watch window or using the various debugging panels like Locals and Autos.

If you want to find out where the reference is or isn't set, right-click its name and select "Find All References". You can then place a breakpoint at every found location and run your program with the debugger attached. Every time the debugger breaks on such a breakpoint, you need to determine whether you expect the reference to be non-null, inspect the variable, and verify that it points to an instance when you expect it to.

By following the program flow this way, you can find the location where the instance should not be null, and why it isn't properly set.

Examples

Some common scenarios where the exception can be thrown:

Generic

ref1.ref2.ref3.member

If ref1 or ref2 or ref3 is null, then you'll get a NullReferenceException. If you want to solve the problem, then find out which one is null by rewriting the expression to its simpler equivalent:

var r1 = ref1;
var r2 = r1.ref2;
var r3 = r2.ref3;
r3.member

Specifically, in HttpContext.Current.User.Identity.Name, the HttpContext.Current could be null, or the User property could be null, or the Identity property could be null.

Indirect

public class Person 
{
    public int Age { get; set; }
}
public class Book 
{
    public Person Author { get; set; }
}
public class Example 
{
    public void Foo() 
    {
        Book b1 = new Book();
        int authorAge = b1.Author.Age; // You never initialized the Author property.
                                       // there is no Person to get an Age from.
    }
}

If you want to avoid the child (Person) null reference, you could initialize it in the parent (Book) object's constructor.

Nested Object Initializers

The same applies to nested object initializers:

Book b1 = new Book 
{ 
   Author = { Age = 45 } 
};

This translates to:

Book b1 = new Book();
b1.Author.Age = 45;

While the new keyword is used, it only creates a new instance of Book, but not a new instance of Person, so the Author the property is still null.

Nested Collection Initializers

public class Person 
{
    public ICollection<Book> Books { get; set; }
}
public class Book 
{
    public string Title { get; set; }
}

The nested collection Initializers behave the same:

Person p1 = new Person 
{
    Books = {
         new Book { Title = "Title1" },
         new Book { Title = "Title2" },
    }
};

This translates to:

Person p1 = new Person();
p1.Books.Add(new Book { Title = "Title1" });
p1.Books.Add(new Book { Title = "Title2" });

The new Person only creates an instance of Person, but the Books collection is still null. The collection Initializer syntax does not create a collection for p1.Books, it only translates to the p1.Books.Add(...) statements.

Array

int[] numbers = null;
int n = numbers[0]; // numbers is null. There is no array to index.

Array Elements

Person[] people = new Person[5];
people[0].Age = 20 // people[0] is null. The array was allocated but not
                   // initialized. There is no Person to set the Age for.

Jagged Arrays

long[][] array = new long[1][];
array[0][0] = 3; // is null because only the first dimension is yet initialized.
                 // Use array[0] = new long[2]; first.

Collection/List/Dictionary

Dictionary<string, int> agesForNames = null;
int age = agesForNames["Bob"]; // agesForNames is null.
                               // There is no Dictionary to perform the lookup.

Range Variable (Indirect/Deferred)

public class Person 
{
    public string Name { get; set; }
}
var people = new List<Person>();
people.Add(null);
var names = from p in people select p.Name;
string firstName = names.First(); // Exception is thrown here, but actually occurs
                                  // on the line above.  "p" is null because the
                                  // first element we added to the list is null.

Events (C#)

public class Demo
{
    public event EventHandler StateChanged;
    
    protected virtual void OnStateChanged(EventArgs e)
    {        
        StateChanged(this, e); // Exception is thrown here 
                               // if no event handlers have been attached
                               // to StateChanged event
    }
}

(Note: The VB.NET compiler inserts null checks for event usage, so it's not necessary to check events for Nothing in VB.NET.)

Bad Naming Conventions:

If you named fields differently from locals, you might have realized that you never initialized the field.

public class Form1
{
    private Customer customer;
    
    private void Form1_Load(object sender, EventArgs e) 
    {
        Customer customer = new Customer();
        customer.Name = "John";
    }
    
    private void Button_Click(object sender, EventArgs e)
    {
        MessageBox.Show(customer.Name);
    }
}

This can be solved by following the convention to prefix fields with an underscore:

    private Customer _customer;

ASP.NET Page Life cycle:

public partial class Issues_Edit : System.Web.UI.Page
{
    protected TestIssue myIssue;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
             // Only called on first load, not when button clicked
             myIssue = new TestIssue(); 
        }
    }
        
    protected void SaveButton_Click(object sender, EventArgs e)
    {
        myIssue.Entry = "NullReferenceException here!";
    }
}

ASP.NET Session Values

// if the "FirstName" session value has not yet been set,
// then this line will throw a NullReferenceException
string firstName = Session["FirstName"].ToString();

ASP.NET MVC empty view models

If the exception occurs when referencing a property of @Model in an ASP.NET MVC View, you need to understand that the Model gets set in your action method, when you return a view. When you return an empty model (or model property) from your controller, the exception occurs when the views access it:

// Controller
public class Restaurant:Controller
{
    public ActionResult Search()
    {
        return View();  // Forgot the provide a Model here.
    }
}

// Razor view 
@foreach (var restaurantSearch in Model.RestaurantSearch)  // Throws.
{
}
    
<p>@Model.somePropertyName</p> <!-- Also throws -->

WPF Control Creation Order and Events

WPF controls are created during the call to InitializeComponent in the order they appear in the visual tree. A NullReferenceException will be raised in the case of early-created controls with event handlers, etc., that fire during InitializeComponent which reference late-created controls.

For example:

<Grid>
    <!-- Combobox declared first -->
    <ComboBox Name="comboBox1" 
              Margin="10"
              SelectedIndex="0" 
              SelectionChanged="comboBox1_SelectionChanged">
       <ComboBoxItem Content="Item 1" />
       <ComboBoxItem Content="Item 2" />
       <ComboBoxItem Content="Item 3" />
    </ComboBox>
        
    <!-- Label declared later -->
    <Label Name="label1" 
           Content="Label"
           Margin="10" />
</Grid>

Here comboBox1 is created before label1. If comboBox1_SelectionChanged attempts to reference `label1, it will not yet have been created.

private void comboBox1_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
    label1.Content = comboBox1.SelectedIndex.ToString(); // NullReferenceException here!!
}

Changing the order of the declarations in the XAML (i.e., listing label1 before comboBox1, ignoring issues of design philosophy) would at least resolve the NullReferenceException here.

Cast with as

var myThing = someObject as Thing;

This doesn't throw an InvalidCastException but returns a null when the cast fails (and when someObject is itself null). So be aware of that.

LINQ FirstOrDefault() and SingleOrDefault()

The plain versions First() and Single() throw exceptions when there is nothing. The "OrDefault" versions return null in that case. So be aware of that.

foreach

foreach throws when you try to iterate on a null collection. Usually caused by unexpected null result from methods that return collections.

List<int> list = null;    
foreach(var v in list) { } // NullReferenceException here

More realistic example - select nodes from XML document. Will throw if nodes are not found but initial debugging shows that all properties valid:

foreach (var node in myData.MyXml.DocumentNode.SelectNodes("//Data"))

---

Ways to Avoid

Explicitly check for null and ignore null values.

If you expect the reference sometimes to be null, you can check for it being null before accessing instance members:

void PrintName(Person p)
{
    if (p != null) 
    {
        Console.WriteLine(p.Name);
    }
}

Explicitly check for null and provide a default value.

Methods you call expecting an instance can return null, for example when the object being sought cannot be found. You can choose to return a default value when this is the case:

string GetCategory(Book b) 
{
    if (b == null)
        return "Unknown";
    return b.Category;
}

Explicitly check for null from method calls and throw a custom exception.

You can also throw a custom exception, only to catch it in the calling code:

string GetCategory(string bookTitle) 
{
    var book = library.FindBook(bookTitle);  // This may return null
    if (book == null)
        throw new BookNotFoundException(bookTitle);  // Your custom exception
    return book.Category;
}

Use Debug.Assert if a value should never be null, to catch the problem earlier than the exception occurs.

When you know during development that a method could, but never should return null, you can use Debug.Assert() to break as soon as possible when it does occur:

string GetTitle(int knownBookID) 
{
    // You know this should never return null.
    var book = library.GetBook(knownBookID);  

    // Exception will occur on the next line instead of at the end of this method.
    Debug.Assert(book != null, "Library didn't return a book for known book ID.");

    // Some other code

    return book.Title; // Will never throw NullReferenceException in Debug mode.
}

Though this check will not end up in your release build, causing it to throw the NullReferenceException again when book == null at runtime in release mode.

Use GetValueOrDefault() for nullable value types to provide a default value when they are null.

DateTime? appointment = null;
Console.WriteLine(appointment.GetValueOrDefault(DateTime.Now));
// Will display the default value provided (DateTime.Now), because appointment is null.

appointment = new DateTime(2022, 10, 20);
Console.WriteLine(appointment.GetValueOrDefault(DateTime.Now));
// Will display the appointment date, not the default

Use the null coalescing operator: ?? [C#] or If() [VB].

The shorthand to providing a default value when a null is encountered:

IService CreateService(ILogger log, Int32? frobPowerLevel)
{
   var serviceImpl = new MyService(log ?? NullLog.Instance);
 
   // Note that the above "GetValueOrDefault()" can also be rewritten to use
   // the coalesce operator:
   serviceImpl.FrobPowerLevel = frobPowerLevel ?? 5;
}

Use the null condition operator: ?. or ?[x] for arrays (available in C# 6 and VB.NET 14):

This is also sometimes called the safe navigation or Elvis (after its shape) operator. If the expression on the left side of the operator is null, then the right side will not be evaluated, and null is returned instead. That means cases like this:

var title = person.Title.ToUpper();

If the person does not have a title, this will throw an exception because it is trying to call ToUpper on a property with a null value.

In C# 5 and below, this can be guarded with:

var title = person.Title == null ? null : person.Title.ToUpper();

Now the title variable will be null instead of throwing an exception. C# 6 introduces a shorter syntax for this:

var title = person.Title?.ToUpper();

This will result in the title variable being null, and the call to ToUpper is not made if person.Title is null.

Of course, you still have to check title for null or use the null condition operator together with the null coalescing operator (??) to supply a default value:

// regular null check
int titleLength = 0;
if (title != null)
    titleLength = title.Length; // If title is null, this would throw NullReferenceException
    
// combining the `?` and the `??` operator
int titleLength = title?.Length ?? 0;

Likewise, for arrays you can use ?[i] as follows:

int[] myIntArray = null;
var i = 5;
int? elem = myIntArray?[i];
if (!elem.HasValue) Console.WriteLine("No value");

This will do the following: If myIntArray is null, the expression returns null and you can safely check it. If it contains an array, it will do the same as: elem = myIntArray[i]; and returns the i^th element.

Use null context (available in C# 8):

Introduced in C# 8, null contexts and nullable reference types perform static analysis on variables and provide a compiler warning if a value can be potentially null or have been set to null. The nullable reference types allow types to be explicitly allowed to be null.

The nullable annotation context and nullable warning context can be set for a project using the Nullable element in your csproj file. This element configures how the compiler interprets the nullability of types and what warnings are generated. Valid settings are:

• enable: The nullable annotation context is enabled. The nullable warning context is enabled. Variables of a reference type, string, for example, are non-nullable. All nullability warnings are enabled.
• disable: The nullable annotation context is disabled. The nullable warning context is disabled. Variables of a reference type are oblivious, just like earlier versions of C#. All nullability warnings are disabled.
• safeonly: The nullable annotation context is enabled. The nullable warning context is safeonly. Variables of a reference type are non-nullable. All safety nullability warnings are enabled.
• warnings: The nullable annotation context is disabled. The nullable warning context is enabled. Variables of a reference type are oblivious. All nullability warnings are enabled.
• safeonlywarnings: The nullable annotation context is disabled. The nullable warning context is safeonly. Variables of a reference type are oblivious. All safety nullability warnings are enabled.

A nullable reference type is noted using the same syntax as nullable value types: a ? is appended to the type of the variable.

Special techniques for debugging and fixing null derefs in iterators

C# supports "iterator blocks" (called "generators" in some other popular languages). NullReferenceException can be particularly tricky to debug in iterator blocks because of deferred execution:

public IEnumerable<Frob> GetFrobs(FrobFactory f, int count)
{
    for (int i = 0; i < count; ++i)
    yield return f.MakeFrob();
}
...
FrobFactory factory = whatever;
IEnumerable<Frobs> frobs = GetFrobs();
...
foreach(Frob frob in frobs) { ... }

If whatever results in null then MakeFrob will throw. Now, you might think that the right thing to do is this:

// DON'T DO THIS
public IEnumerable<Frob> GetFrobs(FrobFactory f, int count)
{
   if (f == null) 
      throw new ArgumentNullException("f", "factory must not be null");
   for (int i = 0; i < count; ++i)
      yield return f.MakeFrob();
}

Why is this wrong? Because the iterator block does not actually run until the foreach! The call to GetFrobs simply returns an object which when iterated will run the iterator block.

By writing a null check like this you prevent the NullReferenceException, but you move the NullArgumentException to the point of the iteration, not to the point of the call, and that is very confusing to debug.

The correct fix is:

// DO THIS
public IEnumerable<Frob> GetFrobs(FrobFactory f, int count)
{
   // No yields in a public method that throws!
   if (f == null) 
       throw new ArgumentNullException("f", "factory must not be null");
   return GetFrobsForReal(f, count);
}
private IEnumerable<Frob> GetFrobsForReal(FrobFactory f, int count)
{
   // Yields in a private method
   Debug.Assert(f != null);
   for (int i = 0; i < count; ++i)
        yield return f.MakeFrob();
}

That is, make a private helper method that has the iterator block logic and a public surface method that does the null check and returns the iterator. Now when GetFrobs is called, the null check happens immediately, and then GetFrobsForReal executes when the sequence is iterated.

If you examine the reference source for LINQ to Objects you will see that this technique is used throughout. It is slightly more clunky to write, but it makes debugging nullity errors much easier. Optimize your code for the convenience of the caller, not the convenience of the author.

A note on null dereferences in unsafe code

C# has an "unsafe" mode which is, as the name implies, extremely dangerous because the normal safety mechanisms which provide memory safety and type safety are not enforced. You should not be writing unsafe code unless you have a thorough and deep understanding of how memory works.

In unsafe mode, you should be aware of two important facts:

• dereferencing a null pointer produces the same exception as dereferencing a null reference
• dereferencing an invalid non-null pointer can produce that exception in some circumstances

To understand why that is, it helps to understand how .NET produces NullReferenceException in the first place. (These details apply to .NET running on Windows; other operating systems use similar mechanisms.)

Memory is virtualized in Windows; each process gets a virtual memory space of many "pages" of memory that are tracked by the operating system. Each page of memory has flags set on it that determine how it may be used: read from, written to, executed, and so on. The lowest page is marked as "produce an error if ever used in any way".

Both a null pointer and a null reference in C# are internally represented as the number zero, and so any attempt to dereference it into its corresponding memory storage causes the operating system to produce an error. The .NET runtime then detects this error and turns it into the NullReferenceException.

That's why dereferencing both a null pointer and a null reference produces the same exception.

What about the second point? Dereferencing any invalid pointer that falls in the lowest page of virtual memory causes the same operating system error, and thereby the same exception.

Why does this make sense? Well, suppose we have a struct containing two ints, and an unmanaged pointer equal to null. If we attempt to dereference the second int in the struct, the CLR will not attempt to access the storage at location zero; it will access the storage at location four. But logically this is a null dereference because we are getting to that address via the null.

If you are working with unsafe code and you get a NullReferenceException, just be aware that the offending pointer need not be null. It can be any location in the lowest page, and this exception will be produced.