Python/Pandas How to get columns names AND values from a sql query
I'm not sure about using a cursor, but I use the following format when doing this and it works.
import mysql.connector
import pandas as pd
db_conn = mysql.connector.connect(
host="SQLServer",
user="sqlusr",
password="usrpasswd",
database="sqldb"
)
query = """SELECT * FROM direction_prix_proxi.matrice_sigma
LEFT JOIN direction_pri.trans_int_prix_gen using(SIGMA, UV)
LEFT JOIN direction_pri.trans_int_prix_module using(SIGMA, UV)
LEFT JOIN direction_pri.trans_int_vol1an_hors_module using(SIGMA, UV)
LEFT JOIN direction_pri.trans_int_vol1an_module using(SIGMA, UV);
"""
df = pd.read_sql(query, db_conn)
df.to_excel(file_path1, index = False, header=True)
How to get column names SQL in python
What work to me is:
field_names = [i[0] for i in self.cursor.description ]
How to write a table output with column names in SQL in python?
I'd recommend using pandas' built in read_sql function to read from your sql database.
This should read your header in properly and the datatypes too!
You'll need to define a sqlAlchemy connection to pass to pd.read_sql but that is simple for mssql:
from sqlalchemy import create_engine
engine = create_engine('mssql://timmy:tiger@localhost:5432/mydatabase')
connection = engine.connect()
query = "SELECT * FROM mytable"
df = pd.read_sql(query, engine)
EDIT:
As per Ratha's comment, it should be noted that the sqlAlchemy connection isn't essential for read_sql to work, and indeed, from the docs a connection is defined as:
con : SQLAlchemy connectable (engine/connection) or database string URI
or DBAPI2 connection (fallback mode)Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported.
How to get fields by name in query Python?
Instead you can use pandas library
import pandas as pd
sql = "SELECT * FROM local"
df = pd.read_sql(sql,conn)
for index,row in df.iterrows():
print(row['name'])
I hope this helps
MySQL: Get column name or alias from query
cursor.description will give you a tuple of tuples where [0] for each is the column header.
num_fields = len(cursor.description)
field_names = [i[0] for i in cursor.description]
How to get column names from a SQL query?
I think that it your are searching for
cnx = mysql.connector.connect(user=DB_USER, password=DB_USER_PASSWORD, host=DB_HOST, database=DB_NAME)
cursor = cnx.cursor()
query = ("SELECT `name`, `ftp_name`, `created_at`, `status` AS `status_customer` FROM `customers"
"WHERE `status` = %(status)s")
cursor.execute(query, { 'status': 1 })
# cursor.description will give you a tuple of tuples where [0] for each is the column header.
num_fields = len(cursor.description)
field_names = [i[0] for i in cursor.description]
print(num_fields)
print(field_names)
>>> 4
>>> [u'name', u'ftp_name', 'created_at', u'status_customer']
# OR just use this cursor function:
print(cursor.column_names)
>>> (u'name', u'ftp_name', 'created_at', u'status_customer')
Hope this helps!
Related Topics
Get Discord User Id from Username
How to Count the Number of Messages
Best Way to Get the Max Value in a Spark Dataframe Column
How to Allocate Array With Shape and Data Type
How to Make a Tkinter Label Background Transparent
Checking If a Button Has Been Pressed in Python
Find Specific Words in Text File and Print the Line Using Python
Counting Non Zero Values in Each Column of a Dataframe in Python
Deal With Overflow in Exp Using Numpy
No Output Displays When Execute Python File
Replace Single Quote With Double Quote in a String Python
How to Divide Each Column of Pandas Dataframe by a Series
Python Format Size Application (Converting B to Kb, Mb, Gb, Tb)
How to Verify If a Button Is Enabled and Disabled in Webdriver Python