Python to explore SQL Server

Question

When I use visual studio I can connect to an SQL server and explore the databases that the server holds.

Is there a way I can do this with python?

I have created a script that allows me to connect to the server

import adodbapi
conn = adodbapi.connect("PROVIDER=SQLOLEDB;Data Source=<location>;Database=<databaseName>; \
 trusted_connection=yes;")
cursor = conn.cursor()
<missing code here>
cursor.close()
conn.close()

This script runs fine, so i assume that the connection is generated fine.

I am hoping to create something like this

for table in sqlserver:
 for row in table:
 print row["name"]

or is it possible to explore the tables as a dictionary?

I am not asking anyone to write this code for me, but any help to allow me to do this would be appreciated, cheers

---

Thank you for the responses - I have found a solution to the question I asked.

To get a list of tables found replacing <missing code here> with the following code worked well.

tables = conn.get_table_names()
#prints all table names
for table in tables:
 print table

Once I pick a table (in this case called "Parts") I can then view the data in each column. I have used the .fetchone() function just pull one row.

sql = r'SELECT * FROM Parts'
cursor.execute(sql)
rows = cursor.fetchone()
rownames = cursor.columnNames
for rowname in rownames: # iterate through all rows
 print str(rowname) + " " + str(rows[rowname])

• Jens: Why the mysql tag if you refer to SQL-server
• Mihai-Daniel Virna: Possible duplicate of Connecting to Microsoft SQL server using Python
• Daniel Roseman: Also, this is really a question about ADO itself, rather than Python or adodbapi. Once you've connected to the server via ADO, you can run any queries you like, including those to return the list of databases as explained here: blogs.technet.microsoft.com/heyscriptingguy/2006/09/14/…
• CodeCupboard: @Jens Cheers I have removed this tag
• CodeCupboard: @Milhai-Daniel Vima I did see that question before posting but it didnt contain enough detail to allow me explore the server to find the tables / columns i want to interact with which is the point of my question, i have no issue connecting.

Answer 1

It sounds like you want to do something like this:

sql = 'SELECT * FROM table'
crsr.execute(sql)
rows = crsr.fetchone()
for row in rows: # iterate through all rows
 name = row[0] # replace 0 with row containing names

See the documentation here for more info: http://adodbapi.sourceforge.net/quick_reference.pdf

• CodeCupboard: thank you for the answer, I get an error when I run that script though, do you happen to know what I doing wrong? Cheers
• Sadie LaBounty: Just to make sure, I presume your table isn't actually called table? If not, you've obviously got to substitute the name of your table. The error says Incorrect syntax near the keyword 'table', so maybe you forgot to change it?
• CodeCupboard: I was using "table" and my table is not called table. Thank you
• Sadie LaBounty: No worries. If that's solved your problem, you might mark this question as answered?