Member Avatar for mishra59

HI,

I am working on a project and need to take the sql table structure out using python language. Basic idea behind is to prepare a program which could run on any sql database and table name provided could fetch what are columns and rows present in that table.

  • 3 Contributors
  • 2 Replies
  • 73 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by zachabesh

Dani AI

Generated

For : the general approach depends on which SQL engine you target, so start by picking the right driver (as pointed out). Two practical, widely used options:

  • Quick DB-API introspection: execute a zero-row SELECT and read cursor.description to get column names and basic type info. This works with most DB-API drivers and is simple to implement; the DB-API defines cursor.description behavior PEP 249.
# generic DB-API pattern
import re

if not re.match(r'^[A-Za-z0-9_]+(\.[A-Za-z0-9_]+)?$', table_name):
    raise ValueError("invalid table name")

cur.execute("SELECT * FROM {} WHERE 1=0".format(table_name))
columns = [col[0] for col in cur.description]

Sanitize or whitelist table names to avoid SQL injection. If cursor.description is empty, check that the query executed successfully and that the driver supports this metadata.

  • Cross-DB reflection / metadata queries: use INFORMATION_SCHEMA.COLUMNS (common on MySQL/Postgres/SQL Server) or vendor APIs (SQL Server, Oracle). For SQLite use PRAGMA table_info(table_name) (see SQLite docs). For a single-library cross-database solution, use SQLAlchemy reflection (inspect(engine).get_columns(...)) which handles many dialects for you SQLAlchemy reflection docs.

Notes: do not SELECT * without limits on large tables — use LIMIT/TOP or fetch samples; ensure the connecting user has metadata/read permissions; handle schema-qualified names (schema.table) and quoting differences between databases.

Recommended Answers

All 2 Replies

Member Avatar for jlm699

What implementation of sql? There are different modules to use depending on whether it's postgresql, mysql, etc...

Member Avatar for zachabesh

That's nice. Have a question? ;)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.