More than once I've exported column names from a SQL script, e.g.
SELECT a, b, c from MyTable
to use in a list in python
MyColumns = ['a', 'b', 'c']
The embuggerance is adding the quotes when there are many columns. Currently I paste the names into Excel, do text to columns, transpose and use formulas to add the quotes, then paste back into code.
Is there a better way?
Here is a way you would do it using python using mariadb.
Change the user, password and database name.
import mysql.connector as mariadb
# enter the credentials
mariadb_connection = mariadb.connect(user='python_user', password='some_pass', database='some_database')
# create a cursor to access the DB
cursor = mariadb_connection.cursor()
# Create an empty list to append queries to
columns = []
# loop over the desired columns, appending each one to the list
for column in cursor.execute("SELECT a, b, c from MyTable"):
columns.append(column)
# check the output
print(columns)
You can do any SQL query you like by putting what you would normally do inside the cursor.execute() command
if you just want to INSERT or CREATE you can do something like..
cursor.execute("""CREATE TABLE albums
(title text, artist text, release_date text,
publisher text, media_type text)
""")
or
cursor.execute("INSERT INTO albums VALUES ('Glow', 'Andy Hunter', '7/24/2012', 'Xplore Records', 'MP3')")
# save data to database
conn.commit()
Here is a link to the mariadb documentation for python..
https://mariadb.com/resources/blog/how-connect-python-programs-mariadb
If you wish to add sql data to a list from a string you can implement the following..
some_sql_string = "a b c john ashu jimmy"
lst = []
for x in some_sql_string.split():
lst.append(x)
print(lst)
['a', 'b', 'c', 'john', 'ashu', 'jimmy']
