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 <database>;") 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
– Raymond Nijland, Commented Jan 7, 2019 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
– T Gray, Commented Jan 7, 2019 at 18:57
Seeing a 20-character password field is extremely concerning. Please, use bcrypt for user passwords. DO NOT store plain-text passwords. — tadman
– tadman, Commented Jan 7, 2019 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
– tadman, Commented Jan 7, 2019 at 21:22
After the CREATE DATABASE statement, you should execute "use yourDbName" statement, before executing TABLE CREATE statement — Tarreq
– Tarreq, Commented Jan 7, 2019 at 22:53

npn · Accepted Answer · 2019-02-05 10:59:22Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related