Uploading User Details from an excel file using pandas in python to postgres Database
<p>I am uploading user details(username,email,password) from a .csv file to postgres DB using pandas in python. It is all fine till the dataframe gets generated but once I run the code for uploading the user details the substring-"@gmail.com" from their emai-id gets converted to/stored as lowercase in postgres DB. 
This is the code that I have written in python shell of a django application -</p>

<pre><code>&gt;&gt;&gt;import sys
&gt;&gt;&gt;from django.contrib.auth import authenticate
&gt;&gt;&gt;from django.contrib.auth import get_user_model
&gt;&gt;&gt;import pandas as pd

&gt;&gt;&gt;User = get_user_model()

&gt;&gt;&gt;df=pd.read_excel('set_A_results_748_web.xlsx',sheetname='Sheet1',parse_cols=(0,3,4))
</code></pre>

<p>df.head()</p>

<p><a href="http://i.stack.imgur.com/YIIgE.png" rel="nofollow">Dataframe First 10 rows Output Screenshot</a></p>

<pre><code>&gt;&gt;&gt;users = [tuple(x) for x in df.values]

&gt;&gt;&gt;for name, email, password in users:
    try:
        print ('Creating user {0}.'.format(name))
        user = User.objects.create_user(name=name, email=email)
        user.set_password(password)
        user.save()

        assert authenticate(name=name, password=password)
        print ('User {0} successfully created.'.format(name))

    except:
        print ('There was a problem creating the user: {0}.  Error: {1}.' \
            .format(name, sys.exc_info()[1]))
</code></pre>

<p><a href="http://i.stack.imgur.com/M4vQi.png" rel="nofollow">Postgres User Table Data Screenshot After Uploading</a></p>

<p>As shown in the output (first 10 rows of dataframe df) the email ids of every user is acc to the data in excel file but when I upload them to auth_user table in my postgres DB the latter part of email gets converted to lowercase.</p>

<p>Example: The E-mail address of row 6 (Dipak Shah) would be stored as <code>DIPAK.13ME57@gmail.com</code> whereas it should be stored as is i.e. <code>DIPAK.13ME57@GMAIL.COM</code>. This creates a problem in application as the users scores alongwith other details are stored in another scores table which I upload separately through a Kettle transformation. So for records which have email mismatch because of case mismatch there would be no data in the scores table.</p>

<p>Any ideas where I might be going wrong or what can I do to avoid this.Any help would be much appreciated!</p>

<p>This has nothing to do with postgres, it's django that is <a href="https://github.com/django/django/blob/1.10/django/contrib/auth/base_user.py#L23" rel="nofollow">normalizing the email address</a> when a new user <a href="https://github.com/django/django/blob/1.10/django/contrib/auth/models.py#L147" rel="nofollow">is created</a>, as the host part of an email address is case insensitive. Whether the name part is case sensitive usually depends on the mail server.</p>

<p>If you really need to use the email address as a matching criteria, then you should also apply the same normalization to those other addresses.</p>

<p>Edit:</p>

<p>It seems that this normalization is only applied in <code>create_user()</code>, if you later set the email address by using <code>user.email = 'SOMEBODY@EXAMPLE.ORG'</code> then it won't be applied. But I'd not rely too much on that, such a different behaviour could be seen as a bug and corrected in a future version.</p>

python django postgresql pandas