Python and mySQL! please help!

[kimmi_baby]

Hi,
I have created a program in Python which creates a database called inb104 and loads the data from text files into the mySQL server. The code works perfectly fine. The data is inserted into the table without any issues. The problem is that I would like to place the code into functions. I will also show that code. I tried to create this program but have come across MANY issues. Please help!

Code without Functions-

# Import database module 

import MySQLdb 

# Create connection to the MySQL database - Make sure host, user, 
# passwd are consistent with the database you are trying to conect to


db_connection = MySQLdb.connect(host='localhost', user='root', passwd='password') 

# Variable that exacutes Database calls with MySQL

cursor = db_connection.cursor()

# Create databse with MYSQL query - databasename

cursor.execute('CREATE DATABASE inb104')

# Select which database to use with MYSQL query - databasename

cursor.execute('USE inb104')

# Create database with MYSQL query - tablename & fields 

cursor.execute('''CREATE TABLE popularity (
                    PersonNumber INT,
                    Value VARCHAR(70),
                    Category VARCHAR(25),
                    PRIMARY KEY (PersonNumber, Value, Category)
                    )
                    ''')


cursor.execute("LOAD DATA LOCAL INFILE 'tv.txt' INTO TABLE popularity FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n' (PersonNumber, Value, Category) SET Category='TV'")
cursor.execute("LOAD DATA LOCAL INFILE 'actors.txt' INTO TABLE popularity FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n' (PersonNumber, Value, Category) SET Category='Actors'")
cursor.execute("LOAD DATA LOCAL INFILE 'movies.txt' INTO TABLE popularity FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n' (PersonNumber, Value, Category) SET Category='Movies'")
cursor.execute("LOAD DATA LOCAL INFILE 'sports.txt' INTO TABLE popularity FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n' (PersonNumber, Value, Category) SET Category='Sports'")
cursor.execute("LOAD DATA LOCAL INFILE 'activities.txt' INTO TABLE popularity FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n' (PersonNumber, Value, Category) SET Category='Activities'")
cursor.execute("LOAD DATA LOCAL INFILE 'musicians.txt' INTO TABLE popularity FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n' (PersonNumber, Value, Category) SET Category='Musicians'")
cursor.execute("LOAD DATA LOCAL INFILE 'games.txt' INTO TABLE popularity FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n' (PersonNumber, Value, Category) SET Category='Games'")
cursor.execute("LOAD DATA LOCAL INFILE 'books.txt' INTO TABLE popularity FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n' (PersonNumber, Value, Category) SET Category='Books'")


 


# commit the changes to the database
db_connection.commit()

   
# close the cursor and connection
cursor.close()

db_connection.close()

This is the code with functions-

import MySQLdb 

def connect_to_database(user, password):
 
 return MySQLdb.connect(host='localhost', user=user, passwd=password) 


def create_database(cursor):
 
 cursor.execute('CREATE DATABASE inb104')
 
 cursor.execute('USE inb104')
 
 cursor.execute('''CREATE TABLE popularity (
                     PersonNumber INT,
                     Value VARCHAR(70),
                     Category VARCHAR(25),
                     PRIMARY KEY (PersonNumber, Value, Category)
                     )
                     ''')

def load_file(cursor, *files):
    """Load the files given in (filename, category) format."""
    sql = '''LOAD DATA LOCAL INFILE '%s' INTO TABLE popularity 
             FIELDS TERMINATED BY '\\t' 
             LINES TERMINATED BY '\\n' 
             (PersonNumber, Value, Category) 
             SET Category='%s'")
         '''
    for filename, category in files:
        cursor.execute(sql, (filename, category))


def data_entry(self):
    """Connect to the DB server, create the DB and table and load the table with records
    """ 
    db = connect_to_database('root', 'password')
    cursor = db.cursor()
    create_database(cursor)
    load_file(cursor,('tv.txt', 'TV'), ('actors.txt', 'Actors'), 
               ('movies.txt', 'Movies'))
    db.commit()
    cursor.close()
    db.close()

The code with functions comes up with these errors:
Traceback (most recent call last):
File "C:\Users\Kim\Desktop\Archive\Project 8 - Data Entry\data_entry.py", line 108, in <module>
'activities', 'musicians', 'books'])
File "C:\Users\Kim\Desktop\Archive\Project 8 - Data Entry\data_entry.py", line 88, in data_entry
('movies.txt', 'Movies'))
File "C:\Users\Kim\Desktop\Archive\Project 8 - Data Entry\data_entry.py", line 78, in load_file
cursor.execute(sql, (filename, category))
File "C:\Python25\Lib\site-packages\MySQLdb\cursors.py", line 166, in execute
self.errorhandler(self, exc, value)
File "C:\Python25\Lib\site-packages\MySQLdb\connections.py", line 35, in defaulterrorhandler
raise errorclass, errorvalue
ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tv.txt'' INTO TABLE popularity \n FIELDS TERMINATED BY '\\t' \n ' at line 1")

Please help!

[Gribouillis]

The traceback says it all: you are sending an SQL statement with a syntax error (perhaps the \n that it contains). Check the SQL syntax. This has nothing to do with using functions or not.

[kimmi_baby]

Would it be in there? I'm having a look at the SQL syntax and I'm not having much luck.. I'm not quite sure where the error is exactly.

sql = '''LOAD DATA LOCAL INFILE '%s' INTO TABLE popularity 
             FIELDS TERMINATED BY '\\t' 
             LINES TERMINATED BY '\\n' 
             (PersonNumber, Value, Category) 
             SET Category='%s'")
         '''

[Gribouillis]

Would it be in there? I'm having a look at the SQL syntax and I'm not having much luck.. I'm not quite sure where the error is exactly.

sql = '''LOAD DATA LOCAL INFILE '%s' INTO TABLE popularity 
             FIELDS TERMINATED BY '\\t' 
             LINES TERMINATED BY '\\n' 
             (PersonNumber, Value, Category) 
             SET Category='%s'")
         '''

Write it on a single line to see if it works.

[kimmi_baby]

I'm no longer getting any errors.. but it's not creating the database :S

[Gribouillis]

I'm no longer getting any errors.. but it's not creating the database :S

Did you call the create_database() function ?

[kimmi_baby]

This is the code:

import MySQLdb 

def connect_to_database(user, password):
 
 return MySQLdb.connect(host='localhost', user=user, passwd=password) 


def create_database(cursor):
 
 cursor.execute('CREATE DATABASE inb104')
 
 cursor.execute('USE inb104')
 
 cursor.execute('''CREATE TABLE popularity (
                          PersonNumber INT,
                          Value VARCHAR(70),
                          Category VARCHAR(25),
                          PRIMARY KEY (PersonNumber, Value, Category)
                          )
                          ''')

def load_file(cursor, *files):

    sql = '''LOAD DATA LOCAL INFILE %s INTO TABLE popularity FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\n' (PersonNumber, Value, Category) SET Category= %s'''
    for filename, category in files:
        cursor.execute(sql, (filename, category))

def data_entry():
    """Connect to the DB server, create the DB and table and load the table with records
    """ 
    db = connect_to_database('root', 'password')
    cursor = db.cursor()
    create_database(cursor)
    load_files(cursor,('tv.txt', 'tv'), ('actors.txt', 'actors'), 
               ('movies.txt', 'movies'), ('books.txt', 'books'))
    db.commit()
    cursor.close()
    db.close()

Does it look right?

[Gribouillis]

This is the code:

import MySQLdb 

def connect_to_database(user, password):
 
 return MySQLdb.connect(host='localhost', user=user, passwd=password) 


def create_database(cursor):
 
 cursor.execute('CREATE DATABASE inb104')
 
 cursor.execute('USE inb104')
 
 cursor.execute('''CREATE TABLE popularity (
                          PersonNumber INT,
                          Value VARCHAR(70),
                          Category VARCHAR(25),
                          PRIMARY KEY (PersonNumber, Value, Category)
                          )
                          ''')

def load_file(cursor, *files):

    sql = '''LOAD DATA LOCAL INFILE %s INTO TABLE popularity FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\n' (PersonNumber, Value, Category) SET Category= %s'''
    for filename, category in files:
        cursor.execute(sql, (filename, category))

def data_entry():
    """Connect to the DB server, create the DB and table and load the table with records
    """ 
    db = connect_to_database('root', 'password')
    cursor = db.cursor()
    create_database(cursor)
    load_files(cursor,('tv.txt', 'tv'), ('actors.txt', 'actors'), 
               ('movies.txt', 'movies'), ('books.txt', 'books'))
    db.commit()
    cursor.close()
    db.close()

Does it look right?

You don't call data_entry(), so your code does nothing. Also, you may have the same problem with the CREATE TABLE query.

[kimmi_baby]

Thank you for your help! How should I do this?

[Gribouillis]

Thank you for your help! How should I do this?

Add a line

data_entry()

at the end of your program, or better

if __name__ == "__main__":
    data_entry()

(this version will call the function only if your program is executed as the main program and not if it's imported from another program)

When you write def function():... , the function is only defined (and compiled into bytecode). It is not executed. Functions need to be called to be executed.

[kimmi_baby]

Thank you so much for your help! That did it :)

[Gribouillis]

Thank you so much for your help! That did it :)

Great !