Create a MySQL database using Python to dynamically add fields and insert data.

Create a MySQL database using Python to dynamically add fields and insert data.

Application scenarios:

We need to design a database to save the keywords of each document in multiple documents. Assume that each document contains more than 1000 characters, and the most frequently-occurring keyword is our keyword.

Assume that each document has more than 300 keywords, And the 0-number of each file stores our keywords. Then we need to create such a database. It is unrealistic to manually enter such a table. We only need to use a program to help me implement this repetitive and boring operation.

The details are as follows:

First, figure 1 shows our original table:

Figure 1

At this time, we need a program to help us create automatic fields and insert data.

Figure 2

Is the overview of our entire table. Next we will use a 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, you should change the No. 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 addtest exists!'conn.select_db('addtest')# create a table named addfieldstry:    curs.execute('create table addfields(id int PRIMARY KEY NOT 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 to remember the last three lines of the statement. 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.

References:

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, the bloggers refer to these blogs for implementation without MySQL. If you have any questions, please submit them.

Criticism and suggestions. I would like to express my sincere thanks for your comments.

Egg:

1. Summary of some errors in Database Operations

If you operate the database for a long time, the following error may occur when you perform the operation again:

Raise errorclass, errorvalue
OperationalError: (2006, 'mysql server has gone away ')

In this case, you need to execute the following command for MySQL server:

Connect your_database

For the IDLE in python, You need to execute:

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.

2 UnicodeEncodeError: 'Latin-1 'codec can't encode characters in position when the above error occurs, you can use the following method to solve it.

 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 are the same as the parameter settings in this article.

Connect Python to MySQL to create a database, create a table, insert data, and query the inserted data in the program. However, the table in the database is empty.

Because no commit is inserted into the data, you can also query the inserted data in the program from the cache.

The id field set in mysql is auto-incrementing, but you still need to manually insert the id when inserting data. If you do not insert the id, an error will occur.

The successful execution example is as follows for your reference only.

Mysql> create table test_create_tab2 (
-> Id INT AUTO_INCREMENT,
-> Val VARCHAR (10 ),
-> Primary key (id)
-> );
Query OK, 0 rows affected (0.09 sec)

Mysql> insert into test_create_tab2 (val) VALUES ('noid ');
Query OK, 1 row affected (0.03 sec)

Mysql> select last_insert_id () as id;
+ ---- +
| Id |
+ ---- +
| 1 |
+ ---- +
1 row in set (0.00 sec)

Mysql> insert into test_create_tab2 (val) VALUES ('no id 2 ');
Query OK, 1 row affected (0.03 sec)

Mysql> select last_insert_id () as id;
+ ---- +
| Id |
+ ---- +
| 2 |
+ ---- +
1 row in set (0.00 sec)

Mysql> select * from test_create_tab2;
+ ---- + --------- +
| Id | val |
+ ---- + --------- +
| 1 | NO id |
| 2 | NO id 2 |
+ ---- + --------- +
2 rows in set (0.00 sec)