CODEWITHSUNDEEP
pythonmysqlflask
Thara
Thara

Reputation: 281

how to dynamicaly create table column names and insert values using flask and mysql

I would like to create multiple text boxes based on the value given in a particular text box and another text box which will hold table name now I wanted to fetch the table name from the text box and also get the values given in the multiple text boxes as a column name and should create a database in MySQL.

here is my py

from flask import Flask
from flask import request
from flask import render_template
from flaskext.mysql import MySQL

app = Flask(__name__)


mysql = MySQL()
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = 'root'
app.config['MYSQL_DATABASE_DB'] = 'matrimony'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)


@app.route('/')
def index():
    return render_template('index1.html')


@app.route('/searchQuery', methods=['POST'])
def search_query():
    if request.method: "POST"
    conn = mysql.connect()
    cursor = conn.cursor()
    name=request.form.get('name')
    sq_ = request.form.get('searchQuery')
    x=request.form.get('x')
    cursor.execute("""CREATE TABLE `%s`( NAME VARCHAR(50) DEFAULT NULL) """ % (name))
    print(sq_)

    return render_template('simplesearch.html', search_query=sq_)



if __name__ == '__main__':
    app.run()

index1.html

<form action="{{ url_for('search_query') }}" method="post">
    Name:<input type="text"  name="name">
    Column Number:<input type="text" name="searchQuery" id="searchQuery"><input type="submit" value="Submit">
</form>

simplesearch.html

<table>
    {% for x in range(search_query | int) %}
        <tr>
            <td>
                <input type="text"  name="i{{ x }}">
                <input type="hidden" value="{{ search_query }}">
            </td>
        </tr>
    {% endfor %}
<input type="submit" value="Submit">
</table>

It fetches table name from the tex tbox and only one column is created. Any help with this would be much grateful Thankyou

Upvotes: 3

Views: 2578

Answers (1)

arshovon
arshovon

Reputation: 13661

I assume you need to create dynamic MySQL tables with user defined fields. You do not need to submit the form twice. Create the dynamic fields using a little help of jQuery. Then handle the values in the controller.

Here is a way you can accomplish this like following. The program requires Flask-MySQL which can be installed using pip install Flask-MySQL.

application.py:

from flask import Flask
from flask import request
from flask import render_template
from flaskext.mysql import MySQL

app = Flask(__name__)


mysql = MySQL()
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = 'root'
app.config['MYSQL_DATABASE_DB'] = 'matrimony'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)


@app.route('/')
def index():
    return render_template('index1.html')


@app.route('/create_table', methods=['POST'])
def create_table():
    if request.method=="POST":
        try:
            table_name = request.form.get('table_name')
            field_name_list = request.form.getlist('fields[]')
            field_list = []
            for field in field_name_list:
                field_list.append(field+ " VARCHAR(50) DEFAULT NULL")
            field_query = " ( " + ", ".join(field_list) + " ) "
            create_table_query = 'CREATE TABLE `'+table_name+'`' + field_query
            conn = mysql.connect()
            cursor = conn.cursor()
            cursor.execute(create_table_query)
            return "Table: "+table_name+" created successfully"
        except Exception as e:
            return str(e)

if __name__ == '__main__':
    app.run(debug = True)

index1.html:

<form action="{{ url_for('create_table') }}" method="post">
    Name:<input type="text" name="table_name" id="table_name" />
    Column Number:<input type="text" name="number_of_columns" id="number_of_columns" />
    <input type="button" id="generate_field_btn" value="Generate Field" />
    <div id="dynamic_field">

    </div>
    <input type="submit" name="Create Table">
</form>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script type="text/javascript">
    $(document).ready(function(){
        $("#generate_field_btn").on("click",function(){
            $number_of_columns = $("#number_of_columns").val();
            $number_of_columns = parseInt($number_of_columns);
            for($i=0; $i<$number_of_columns; $i++){
                $field_name = "Field "+($i+1)+": ";
                $new_element = $field_name+'<input type="text" name="fields[]"/><br/>';
                $("#dynamic_field").append($new_element);   
            }

        })
    })
</script>

Output:

  1. Getting input fields from user:

Getting input fields from user

  1. Showing success message after table creation:

Showing success message after table creation

  1. Ensure the table is created with desired fields from phpMyAdmin:

Ensure the table is created with desired fields from phpMyAdmin

Upvotes: 2

Related Questions