Get list of MySQL databases with python

Question

I'm trying to get the names of all databases associated with my MySQL server via python (2.7), but am instead just getting the number of databases.

I looked around, and it seems like the only answer may be to use sys for a command line call, get the name of the databases, and proceed from there, but I can't believe that's the only way.

Current Code:

import MySQLdb

serv = MySQLdb.connect(host = "localhost", user = "root", passwd = "abcdefg")

c = serv.cursor()

print c.execute("SHOW DATABASES")

Output:

Thanks in advance for the help.

@khelwood: Excellent, thank you! I'm obviously a noob at this. Also added l = c.fetchall(); l = [ i[0] for i in l ] as it was returning a tuple of tuples where the second item in the inner tuples was null - do you know why that would be? — elPastor
– elPastor, Commented Jul 3, 2017 at 20:42
Related: execute returns the number of rows in the result in your context. — agtoever
– agtoever, Commented Jul 3, 2017 at 20:45
Each tuple is a row in the query results. I don't specifically know why the tuple would have a null (or None) in it. — khelwood
– khelwood, Commented Jul 3, 2017 at 20:56

Iman Mirzadeh · Accepted Answer · 2017-07-03 21:13:55Z

11

I am not sure if mysql connector is same as your library, but using msyql connector your answer would be something like this:

import mysql.connector
conn = mysql.connector.connect (user='user', password='password',
                               host='server_address',buffered=True)
cursor = conn.cursor()
databases = ("show databases")
cursor.execute(databases)
for (databases) in cursor:
     print databases[0]

answered Jul 3, 2017 at 21:13

Iman Mirzadeh

13.8k2 gold badges42 silver badges45 bronze badges

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

3 Comments

Nino van Hooff Over a year ago

For python 3 use print(databases[0])

peter Over a year ago

Hi, is there an alternative to the for loop for viewing the databases. I inspected the cursor object but didn't found any trace of the databases. Any help would be greatly appreciated .

lukassliacky Over a year ago

If is not available mysql for python: pip install mysql-connector-python

Rafael Valero · Accepted Answer · 2018-01-05 12:40:09Z

1

Just taking previous comments and reorganicing: Current Code:

import MySQLdb
serv = MySQLdb.connect(host = "localhost", user = "root", passwd = "abcdefg")
c = serv.cursor()
print c.execute("SHOW DATABASES")

With suggested modifications:

import MySQLdb
serv = MySQLdb.connect(host = "localhost", user = "root", passwd = "abcdefg")
c = serv.cursor()
c.execute("SHOW DATABASES")
l = c.fetchall()
print l
l = [ i[0] for i in l ]
print l

In my case the output looks like (which is the desired output):

(('information_schema',), ('data',), ('mysql',), ('performance_schema',), ('sys',))
['information_schema', 'data', 'mysql', 'performance_schema', 'sys']

Please note the following common error to prevent mistakes: if you try c.execute("SHOW DATABASES").fetchall() you will have an error: AttributeError: 'int' object has no attribute 'fetchall'. This is way you should write firstly c.execute("SHOW DATABASES") and secondly c.fetchall().

edited Jan 5, 2018 at 12:40

answered Jan 5, 2018 at 11:53

Rafael Valero

2,83623 silver badges29 bronze badges

3 Comments

ADyson Over a year ago

if you get an error, this suggests that your code doesn't answer the question and provide a solution?

Rafael Valero Over a year ago

True, it works, the last paragraphs is to point out a common mistake. (at least for me)

ADyson Over a year ago

Ok. I think you could clarify the wording or formatting to make that easier to understand.

Collectives™ on Stack Overflow

Get list of MySQL databases with python

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related