CODEWITHSUNDEEP
pythonsqlsql-server
CodeCupboard
CodeCupboard

Reputation: 1585

Python to explore SQL Server

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

Upvotes: 0

Views: 803

Answers (1)

Sadie LaBounty
Sadie LaBounty

Reputation: 379

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

Upvotes: 1

Related Questions