Trying to insert data into SQL database. Error 1046 No Database Selected

Question

I have some data in a tab-delimited file that i'm trying to insert into an SQL database. Here is my code

import csv
import MySQLdb

new_db = MySQLdb.connect(host='localhost',user='me')
cursor = new_db.cursor()

cursor.execute('create database if not exists my_database')

maketablestr = """CREATE TABLE info (Disease VARCHAR(10), NCBI Acc. Number VARCHAR(10) ,Length VARCHAR(10), Sequence VARCHAR(10));"""

cursor.execute(maketablestr)
new_db.commit()

tsv_read = csv.reader(file('marfan.tsv'), delimiter = '\t')

for row in tsv_read:
    cursor.execute('INSERT INTO info(Disease, NCBI Acc. Number, Length, Sequence ) VALUES(%s, %s, %s, %s)', row)


new_db.commit()
cursor.close()
new_db.close()
print("Database updated")

When I run the code, it gives me the error 1046 'No Database Selected'. I'm a little confused as to why I'm getting this error since the code that I wrote is mostly taken from others who are trying to do the same thing as me.

Lucas · Accepted Answer · 2019-04-09 16:20:11Z

2

There are two approaches to solve this:

You run cursor.execute('use my_database;')
Or you adapt your SQL statements to specify the DB like:

cursor.execute('INSERT INTO my_database.info(Disease, NCBI Acc. Number, Length, Sequence ) VALUES(%s, %s, %s, %s)', row)

Note, with the later approach you need to adapt all sql statements

answered Apr 9, 2019 at 16:20

Lucas

3951 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

flannel_bioinformatician Over a year ago

What would be the syntax for specifying the database when I do cursor.execute(maketablestr)?

Lucas Over a year ago

I believe it would be

"""CREATE TABLE my_database.info (Disease VARCHAR(10), NCBI Acc. Number VARCHAR(10) ,Length VARCHAR(10), Sequence VARCHAR(10));"""

but I'm not a mysql expert

flannel_bioinformatician Over a year ago

Thank you, you helped me solve one issue, but now I have a SQL syntax error somewhere. I'll try to figure it out

Lucas Over a year ago

Happy to hear that: I'm not sure with the second statement: shouldn't you use % to specify the start of the python variables rather than ,. Also you probably need to surround the inserted variables like this '%s' and then the entire string by "..." rather then '...'. In general, you should look into using .format() rather then the "%s" % my_var notation. And finally, use the mysql console for your debugging, it can make things easier, particularly check if the CREATE statement is correct, success :)

Collectives™ on Stack Overflow

Trying to insert data into SQL database. Error 1046 No Database Selected

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related