Create a function in Python that creates the mySQL database

Question

I created a program in python and mySQL that creates a database and imports data from text files and puts them in 4 different columns. The code works, but I want to change the code and create functions .

Can someone help me create a function that creates mySQL database? Here is the code that I have at the moment. Thanks in advance!

import MySQLdb # Create connection to the MySQL database - Make sure host, user, # passwd are consistent with the database you are trying to conect to def create_database(): 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() 

---

Ok, this is what I still have.

I get this error: TypeError: data_entry () takes no arguments (1 given)

And the data is also not inserted into the table from text files. Any help would be greatly appreciated!

 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(): """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')) db.commit() cursor.close() db.close() 

Answer 1

Your sql for data loading should be

 sql = '''LOAD DATA LOCAL INFILE %s INTO TABLE popularity FIELDS TERMINATED BY ';' LINES TERMINATED BY '\\n' (PersonNumber, Value, Category) SET Category=%s ''' + %s --> '%s' + remove " at the end 

It should work. In addition, load_files -> load_file