Python - MySql access for inserting data into db

Question

I have created a Django application.Now i am trying to populate some data into SQL database tabel EmployeeDetails, using normal python script. But i am not able to insert data into db. I guess its because my db is not connecting with this external python script. I am new with Python, so please help me to solve this.This is my python script :

import MySQLdb
    db=MySQLdb.connect("localhost","root","password123","employee")
    def dumpdata():
        userName = 'John'
        designation = 'Software Engineer'
        employeeID = '2312'
        contactNumber = '9495321257'
        project = 'cricket'
        dateOfJoin = '2009-10-10'
        EmployeeDetails(userName,designation,employeeID,contactNumber,project,dateOfJoin).save()

My settings.py in Django is :

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql', # Add 'postgresql_psycopg2', 'postgresql', 'mysql', 'sqlite3' or 'oracle'.
        'NAME': 'employee',                      # Or path to database file if using sqlite3.
        'USER': 'root',                      # Not used with sqlite3.
        'PASSWORD': 'password123',                  # Not used with sqlite3.
        'HOST': '',                      # Set to empty string for localhost. Not used with sqlite3.
        'PORT': '',                      # Set to empty string for default. Not used with sqlite3.
    }
}

My final need is to put the insertion code in a loop and populate as many data i need into db. Somebody please look into my code and help me to solve this.

Ian Clelland · Accepted Answer · 2011-03-05 07:04:07Z

3

Don't import MySQLdb. Use Django instead.
Don't bother connecting to the database directly. Use Django instead.
If you are going to create objects from your models, you need to import those. They will take care of importing all of the necessary Django modules, if you have written them correctly.
Model.objects.create() is a shorthand for creating a new object and saving it.

Something like this:

from myapp.models import EmployeeDetails

def dumpdata():
    userName = 'John'
    designation = 'Software Engineer'
    employeeID = '2312'
    contactNumber = '9495321257'
    project = 'cricket'
    dateOfJoin = '2009-10-10'
    EmployeeDetails.objects.create(userName,designation,employeeID,contactNumber,project,dateOfJoin)

Or this (using keyword arguments means that you don't have to remember the exact order of parameters to create the object):

EmployeeDetails.objects.create(
    userName='John',
    designation='Software Engineer',
    employeeID='2312',
    contactNumber='9495321257',
    project='cricket',
    dateOfJoin='2009-10-10')

You'll have to run that with the Django package on your Python path, and with your DJANGO_SETTINGS_MODULE environment variable set correctly. Something like this usually works for me:

PYTHONPATH=. DJANGO_SETTINGS_MODULE=settings python my_script.py

But that invocation can depend a lot on your system setup.

edited Mar 5, 2011 at 7:04

answered Mar 2, 2011 at 6:54

Ian Clelland

44.4k8 gold badges90 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rv_k Over a year ago

Thanks for the reply Ian, I tried that way. After giving the run command, its not returning any error. But the data is not getting inserted.

Ian Clelland Over a year ago

Can you add data via the python shell? Run manage.py shell, and then do the import and try to create the object; if you can do it that way, then you just need to figure out how to get the script to do the same thing.

rv_k Over a year ago

i got an error while entering through python cosole ,"raise errorclass, errorvalue IntegrityError: (1048, "Column 'employeeID' cannot be null") "

Ian Clelland Over a year ago

That sounds like your columns are declared in a different order than you're presenting them when you create the object. I've added a better method of calling create() to the answer; try that to see if you still get the error.

Jonathan Livni · Accepted Answer · 2011-03-02 06:48:46Z

0

Have you installed MySQL support for python yet?

In case you're on Windows, the simplest would be to use a precompiled distribution - this one's limited to Python 2.6. I haven't found any precompiled version for higher Python versions.

answered Mar 2, 2011 at 6:48

Jonathan Livni

108k112 gold badges278 silver badges367 bronze badges

1 Comment

rv_k Over a year ago

yes i have installed MySQL support for python. And i am working on Ubuntu 10.04

Collectives™ on Stack Overflow

Python - MySql access for inserting data into db

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related