用Python创建一个能够创建mySQL数据库的函数

Question

我用Python和MySQL编写了一个程序，可以创建数据库并将数据从文本文件导入到4个不同的列中。代码可以正常运行，但我想改变代码并创建函数。

请问有人能帮我创建一个创建MySQL数据库的函数吗？以下是我目前拥有的代码。提前感谢您的帮助！

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()

---

好的，这是我目前的进展。

我遇到了这个错误：TypeError: data_entry()需要0个参数，但实际给出了1个

并且，数据也没有从文本文件插入到表格中。非常感谢您的帮助！

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()

- Kim

---

当你说你想要创建函数时，你的意思是什么？你希望这些函数做什么？你的代码已经在创建一个函数了。 - aaronasterling

感谢您的回复。我想要一个函数来创建mySQL数据库。我希望另一个函数能够导入文件并将它们放入数据库中。 - Kim

金姆，你肯定需要更清楚地表达你的期望结果。 - Brighid McDonnell

好的，我需要创建一个名为create_database的函数和另一个名为data_entry的函数。create_database是一个函数，它将创建名为inb104的数据库并设置表等内容。data_entry是一个函数，它将数据加载到表中。希望这样更清晰了。 - Kim

下一次，从头开始将你的代码放入函数中。你甚至可以先编写顶层函数，调用尚不存在的函数。模拟它们，然后随着进展填充它们。 - Jonathon Reinhart

你的代码已经创建了一个数据库。请澄清你的标题和问题。 - user207421

Answer 1

加载数据的 SQL 应该是：

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

应该可以工作。 此外，load_files --> load_file

- Iapilgrim

---

实际上，我仍然遇到了问题 :( - Kim

那么错误是什么？我用你的代码（加上我的修改）进行了测试，它可以工作。我猜想你的模板数据可能有问题。尝试只使用一个文件，并且该文件只包含一条记录。结果如何？ - Iapilgrim