CODEWITHSUNDEEP
pythonsql
Vlad
Vlad

Reputation: 3764

Enclosing column names from SQL script in quotes to use in python list

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?

Upvotes: 0

Views: 314

Answers (1)

johnashu
johnashu

Reputation: 2211

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..

create a table

cursor.execute("""CREATE TABLE albums
                  (title text, artist text, release_date text, 
                  publisher text, media_type text) 
              """)

or

insert some data

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

Upvotes: 1

Related Questions