How to get name of a mysql database name from user input and create tables

Question

I want to create a mysql database in python environment and then create tables in it, but the error says that "NO DATABASE SELECTED".how can I fix it?

import mysql.connector
dbname = input('Please enter the name of database : ')
db = mysql.connector.connect(
host = '127.0.0.1',
user = 'root',
password = ''
)


cursor = db.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS %s" %dbname)
cursor.execute("CREATE TABLE IF NOT EXISTS %s (email varchar(30) pwd varchar(20))" %dbname)

db.close()

"but the error says that "NO DATABASE SELECTED".how can I fix it?" run `cursor.execute("USE ;")` before the CREATE TABLE statement? Are you aware you make a database and within that database you are making a table with a same name as the database? — Raymond Nijland, Jan 07 '19 at 18:44
You have to tell the create table statement which database you want to create it in. Otherwise, it doesn't know where to put it. So, if your database in the create database statement is "bananas" and your table is "gazontas", your table create must specify "create table bananas.gazontas (email ...". — T Gray, Jan 07 '19 at 18:57
Seeing a 20-character password field is extremely concerning. Please, [**use bcrypt**](https://stackoverflow.com/questions/9559549/how-to-compare-plain-text-password-to-hashed-password-using-bcrypt) for user passwords. **DO NOT** store plain-text passwords. — tadman, Jan 07 '19 at 21:21
Email addresses can also be a lot longer than 30 chars. Use `VARCHAR(255)` as a default "string" type field. Only limit this if absolutely necessary. — tadman, Jan 07 '19 at 21:22
After the CREATE DATABASE statement, you should execute "use yourDbName" statement, before executing TABLE CREATE statement — Tarreq, Jan 07 '19 at 22:53

score 2 · Accepted Answer · answered Feb 05 '19 at 10:59

In your code,After creating database connector can't identify database name because database name is not given to connector.It means while creating table in database you must provide database name to connector then table can be created in database which is specified by the connector.

At first you need to create database as follows.

       import mysql.connector
       dbname = input('Please enter the name of database : ')
       db = mysql.connector.connect(
       host = 'localhost',
       user = 'root',
       password = '',
       )

      cursor = db.cursor()
      cursor.execute("CREATE DATABASE IF NOT EXISTS %s" %dbname)
      cursor.close()
      db.close()}

After creating database you need to manually provide name of database in order to create database table inside existing database.Here dbname is entered by user i.e.

      import mysql.connector
      db = mysql.connector.connect(
      host = 'localhost',
      user = 'root',
      password = '',
      database=dbname, #providing name of database to connector
      )

      cursor = db.cursor()
      cursor.execute("CREATE TABLE IF NOT EXISTS %s (email VARCHAR(30),pwd 
      VARCHAR(20))" %dbname)
      cursor.close()
      db.close()}

Finally you can ask user to enter database name and can create table inside database by same name (dbname in your case) using following code.

      import mysql.connector
      dbname = input('Please enter the name of database : ')
      db = mysql.connector.connect(
      host = 'localhost',
      user = 'root',
      password = '',
      )
      cursor = db.cursor()
      cursor.execute("CREATE DATABASE IF NOT EXISTS %s" %dbname)
      cursor.close()
      db.close()

      import mysql.connector
      db = mysql.connector.connect(
      host = 'localhost',
      user = 'root',
      password = '',
      database=dbname,
      )

      cursor = db.cursor()
      cursor.execute("CREATE TABLE IF NOT EXISTS %s (email VARCHAR(30),pwd 
      VARCHAR(20))" %dbname)
      cursor.close()
      db.close()

How to get name of a mysql database name from user input and create tables

1 Answers1