CODEWITHSUNDEEP
pythondatabasecgimysql-python
Jeremy
Jeremy

Reputation: 73

How to insert Records of data into MySQL database with python?

I have an form which has FirstName, Lastname, Age and Gender. I am using MySQL db. While using MySQl db, do we need to create table , do the insert operation in the single pythonic script ?

For example :

#!/usr/bin/python

import MySQLdb

db = MySQLdb.connect("localhost", "usename", "password", "TESTDB")

cursor = db.cursor()
cursor.execute ( """
    CREATE TABLE PERSON 
    ( 
     F_NAME CHAR(20) NOT NULL, 
     L_NAME CHAR(20), 
     AGE INT, 
     GENDER CHAR(4)
    )
 """)
cursor.execute( """
    INSERT INTO PERSON (F_NAME, L_NAME, AGE, GENDER)
    VALUES
        ('Neeraj','Lad','22','Male'),
        ('Vivek','Pal','24','Male')  
 """)
print cursor.rowcount

Edited Code:

#!/usr/bin/python

import MySQLdb
import cgi

print "Content-type: text/html\n"

form = cgi.FieldStorage()
f_Name = form.getvalue('firstname', '')
l_Name = form.getvalue('lastname', '')
age = form.getvalue('age', 0)
gender = form.getvalue('gender', '')

db = MySQLdb.connect(host="", user="", password="", db="")

cursor = db.cursor()

sql =  "INSERT INTO PERSON (F_NAME, L_NAME, Age, Gender) VALUES (%s, %s, %s, %s)" %(f_name, l_name, age, gender) 
cursor.execute(sql)
db.commit()
db.close()

Upvotes: 0

Views: 9243

Answers (2)

abarnert
abarnert

Reputation: 365767

I'm not 100% clear on what you're asking, but I'll take a guess.

You have to create a table exactly once in the database before you can insert into it.

If your Python script is talking to a brand-new database each time it runs, then it needs a CREATE TABLE statement.

If your Python script might be talking to a brand-new database, but will usually be talking to an already-existing one, then you can use CREATE TABLE IF NOT EXISTS.

But, except in toy learning projects, both of these are rare. Normally, you create the database once, then you write Python scripts that connect to it and assume it's already been created. In that case, you will not have a CREATE TABLE statement in your form handler.

If you're asking about inserting multiple values in a single INSERT statement… normally, you won't be inserting hard-coded values like 'Neeraj', but rather values that you get dynamically (e.g., from the web form). So you will be using parameterized SQL statements like this:

cursor.execute("""
    INSERT INTO PERSON (F_NAME, L_NAME, AGE, GENDER)
    VALUES (%s, %s, %s, %s)
""", (f_name, l_name, age, gender))

In that case, if you have, say, a list of 4-tuples, each representing a person, and you want to insert all of them, you do that not by putting multiple copies of the parameter lists in the SQL statement, but by putting a single parameter list, and using the executemany function:

cursor.execute("""
    INSERT INTO PERSON (F_NAME, L_NAME, AGE, GENDER)
    VALUES (%s, %s, %s, %s)
""", list_of_people)

Upvotes: 2

unutbu
unutbu

Reputation: 879681

You only need to create the table once. You can do that using the mysql CLI tool, or phpmyadmin, or python/MySQLdb, or by some other method.

CREATE TABLE PERSON will raise an error if the table PERSON already exists. If you'd like a way to create it in your python script, use IF NOT EXISTS, so subsequent runs of your program do not raise an error:

cursor.execute ( """
    CREATE TABLE IF NOT EXISTS PERSON 
    ( 
     F_NAME CHAR(20) NOT NULL, 
     L_NAME CHAR(20), 
     AGE INT, 
     GENDER CHAR(4)
    )
 """)

Upvotes: 1

Related Questions