Big Query insert row from python - what is insert_row() method format?

Question

I have a python program that stores values in a database. I'd like to store them in Big Query instead of locally. From the Big Query docs I've looked at this example:

rows_to_insert = [(u"Phred Phlyntstone", 32), (u"Wylma Phlyntstone", 29)]

Where can I find a detailed explanation of how this works? What should go where and how it should be formatted. I have created a table with a schema (name, date, age). When I create my own rows_to_insert do i treat each field ("Phred", 2345-02-19, 44) as the column defined by the schema?

If so, can I use a variable here? If I can, can someone give a quick example of what that looks like? (x, y, z) or ({x}, {y}, {z}) or ((x), (y), (z)).

In the example above each row entry starts with a 'u'. Why?

the general syntax I have seen is:

INSERT INTO ‘table_name’
(column1, column2, column3, - - - )
VALUES (value1, value2, value3 - - -)

Am I able to use this syntax?

Thanks for reading my post. If I've made a mistake in how I've phrased my question of if you have a suggestion on how to present it more clearly please let me know and I'll adjust.

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

You can find the reference for this function here About how it should be formatted, the documentation says the following:

rows (Union[Sequence[Tuple], Sequence[dict]]) – Row data to be inserted. If a list of tuples is given, each tuple should contain data for each schema field on the current table and in the same order as the schema fields. If a list of dictionaries is given, the keys must include all required fields in the schema. Keys which do not correspond to a field in the schema are ignored.

It means that you must use as arguments a list of tuples with ordered values corresponding to the fields in your table. The other possibility is to send a list of dictionaries specifying the fields as keys and the data to be inserted as values in each field. If you're using variables, you should just put your variable name instead of a literal value in your tuple or in your dictionary's value field. For example, if you have two variables x and y, you could do: (x,y) or {'key1':x, 'key2': y} where key1 and key2 are the name of the fields in your table.

About the ubeing used before a string: it's a Python notation for unicode strings. You should use that when you work with unicode encoding.

I hope it helps you

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 24, 2020 at 15:27

rmesteves

4,1359 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gears Over a year ago

Thank you. I'm not sure why I couldn't find that link but it was exactly what I was looking for. I was able to store all values in a list and pass the list to insert_rows(). Regarding unicode encoding, does the 'u' need to precede every string in a list? Which would be correct: x = [u"stuff", u"more stuff", u"less stuff"] or x = [u"stuff", "more stuff", "less stuff"]

rmesteves Over a year ago

It depends on which version Python you are using. If you're using Python 3, you can only do ["stuff", "more stuff", "less stuff"]. If you're using Python 2, you probably should do [u"stuff", u"more stuff", u"less stuff"]. I think this article can clarify it for you b-list.org/weblog/2017/sep/05/how-python-does-unicode

Collectives™ on Stack Overflow

Big Query insert row from python - what is insert_row() method format?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related