Create a MySQL database with Python add dynamic fields and insert data dynamically

Application Scenarios:

We need to design a database to hold the keywords for each document in multiple documents. If we have more than 1000 characters per document, take the most frequent of them as our keywords.

Assuming that each document has more than 300 keywords, the 0-299 number of each file is stored in our keywords. Then we have to build such a database, manually input such a table is not realistic, we only through the program to help me achieve this repetitive boring operation.

The details are as follows:

First, figure 1 is our original table:

Figure 1

This time we need a program to help us complete the creation of automatic fields and insert data.

Figure 2

is an overview of our entire table. Here we use the program to summarize how such a table is implemented.

' Function description:add The fields and data Dynamicly.data:2014-08-04author:chichorunning:python addfileds.py ' "Import Mysqldb#connect the Database#the Argvs based on the database you set. #Generally speaking, and you should change the N  O. of the Port 3306, because it's easy-to-be attack#localhost = 127.0.0.1conn = MySQLdb.connect (host = ' localhost ', port = 3306, user = ' root ', passwd = ' * * * * * ') Curs = Conn.cursor () # Create a database named Addtest#ensure the program can run Multiple times,we should use Try...exceptiontry:curs.execute (' Create Database addtest ') except:print ' database Addt EST exists! ' conn.select_db (' Addtest ') # Create a table named Addfieldstry:curs.execute (' CREATE TABLE addfields (id int PRIMARY KEY N OT null,name text) except:print (' The table AddFields exists! ') # Add the filedstry:for I in range (1): sql = "ALTER TABLE addfields add key%s text"%i curs.execute (SQL)   Except Exception,e:print Efor i in range (4): #insert 5 Lines sql = "INSERT INTO AddFields set id=%s"%i curs.execute (sql) sql = "Update addfields set name = ' hello%s ' where id= %s "% (i,i) curs.execute (SQL) for J in Range (5): sql =" Update addfields set key%s = ' world%s%s ' where id=%s "%     (j,i,j,i) curs.execute (SQL) #this is very importantconn.commit () Curs.close () Conn.close ()

Remember that the last three lines of this statement must be remembered, otherwise your operation will not be written to the database.

Finally, we can get our results, as shown in:

This is the general implementation of the program.

Reference documents:

Http://www.cnblogs.com/rollenholt/archive/2012/05/29/2524327.html

Http://www.blogjava.net/alpha/archive/2007/07/23/131912.html

Http://database.51cto.com/art/200811/97974_all.htm

Thanks to the selfless dedication of several bloggers upstairs, bloggers are not on the basis of MySQL based on the implementation of these blogs. If there is not enough to welcome the proposed

Criticisms and suggestions. I would like to express my heartfelt thanks to you for your opinion.

Eggs:

1. Summary of some errors in the operational database

If you are working on a team database for a long time, the following error may occur when you re-operate it:

Raise Errorclass, Errorvalue
Operationalerror: (2006, ' MySQL server has gone away ')

This time for MySQL server all you have to do is execute the following command

Connect Your_database

For the idle in Python you need to perform:

conn = MySQLdb.connect (host = ' localhost ', port = 3306, user = ' root ', passwd = ' * * * * ') Curs = Conn.cursor () conn.select_db ( ' Addtest ')

Enter the password you set in your own database.

2UnicodeEncodeError: ' latin-1 ' codec can ' t encode characters in positionThe above error can be solved by using the following method.

Conn.set_character_set (' UTF8 ')    curs.execute (' Set names UTF8 ')    curs.execute (' Set character set UTF8; ')    Curs.execute (' SET Character_set_connection=utf8; ')

conn,curs and the parameter settings in this article are the same.